How to run multiple SQL commands in a single SQL connection?
C#Sql Serverado.netSqlconnectionSqlcommandC# Problem Overview
I am creating a project in which I need to run 2-3 SQL commands in a single SQL connection. Here is the code I have written:
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\project.mdf;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand("select * from " + mytags.Text + " ", con);
SqlDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
con.Close();
con.Open();
SqlCommand cmd1 = new SqlCommand("insert into " + mytags.Text + " values ('[email protected]','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','"+mytags.Text+"')", con);
cmd1.ExecuteNonQuery();
label.Visible = true;
label.Text = "Date read and inserted";
}
else
{
con.Close();
con.Open();
SqlCommand cmd2 = new SqlCommand("create table " + mytags.Text + " ( session VARCHAR(MAX) , Price int , Description VARCHAR(MAX), Date VARCHAR(20),tag VARCHAR(10))", con);
cmd2.ExecuteNonQuery();
con.Close();
con.Open();
SqlCommand cmd3 = new SqlCommand("insert into " + mytags.Text + " values ('" + Session + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" + mytags.Text + "')", con);
cmd3.ExecuteNonQuery();
label.Visible = true;
label.Text = "tabel created";
con.Close();
}
I have tried to remove the error and I got that the connection is not going to else condition. Please review the code and suggest if there is any mistake or any other solution for this.
C# Solutions
Solution 1 - C#
Just change the SqlCommand.CommandText
instead of creating a new SqlCommand
every time. There is no need to close and reopen the connection.
// Create the first command and execute
var command = new SqlCommand("<SQL Command>", myConnection);
var reader = command.ExecuteReader();
// Change the SQL Command and execute
command.CommandText = "<New SQL Command>";
command.ExecuteNonQuery();
Solution 2 - C#
The following should work. Keep single connection open all time, and just create new commands and execute them.
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command1 = new SqlCommand(commandText1, connection))
{
}
using (SqlCommand command2 = new SqlCommand(commandText2, connection))
{
}
// etc
}
Solution 3 - C#
Just enable this property in your connection string:
sqb.MultipleActiveResultSets = true;
This property allows one open connection for multiple datareaders.
Solution 4 - C#
I have not tested , but what the main idea is: put semicolon on each query.
SqlConnection connection = new SqlConnection();
SqlCommand command = new SqlCommand();
connection.ConnectionString = connectionString; // put your connection string
command.CommandText = @"
update table
set somecol = somevalue;
insert into someTable values(1,'test');";
command.CommandType = CommandType.Text;
command.Connection = connection;
try
{
connection.Open();
}
finally
{
command.Dispose();
connection.Dispose();
}
Update: you can follow https://stackoverflow.com/questions/6696417/is-it-possible-to-have-multiple-sql-instructions-in-a-ado-net-command-commandtex too
Solution 5 - C#
This is likely to be attacked via SQL injection by the way. It'd be worth while reading up on that and adjusting your queries accordingly.
Maybe look at even creating a stored proc for this and using something like sp_executesql which can provide some protection against this when dynamic sql is a requirement (ie. unknown table names etc). For more info, check out this link.
Solution 6 - C#
No one has mentioned this, but you can also separate your commands using a ; semicolon in the same CommandText:
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand comm = new SqlCommand())
{
comm.Connection = conn;
comm.CommandText = @"update table ... where myparam=@myparam1 ; " +
"update table ... where myparam=@myparam2 ";
comm.Parameters.AddWithValue("@myparam1", myparam1);
comm.Parameters.AddWithValue("@myparam2", myparam2);
conn.Open();
comm.ExecuteNonQuery();
}
}
Solution 7 - C#
Multiple Non-query example if anyone is interested.
using (OdbcConnection DbConnection = new OdbcConnection("ConnectionString"))
{
DbConnection.Open();
using (OdbcCommand DbCommand = DbConnection.CreateCommand())
{
DbCommand.CommandText = "INSERT...";
DbCommand.Parameters.Add("@Name", OdbcType.Text, 20).Value = "name";
DbCommand.ExecuteNonQuery();
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@Name", OdbcType.Text, 20).Value = "name2";
DbCommand.ExecuteNonQuery();
}
}
Solution 8 - C#
Here you can find Postgre example, this code run multiple sql commands (update 2 columns) within single SQL connection
public static class SQLTest
{
public static void NpgsqlCommand()
{
using (NpgsqlConnection connection = new NpgsqlConnection("Server = ; Port = ; User Id = ; " + "Password = ; Database = ;"))
{
NpgsqlCommand command1 = new NpgsqlCommand("update xy set xw = 'a' WHERE aa='bb'", connection);
NpgsqlCommand command2 = new NpgsqlCommand("update xy set xw = 'b' where bb = 'cc'", connection);
command1.Connection.Open();
command1.ExecuteNonQuery();
command2.ExecuteNonQuery();
command2.Connection.Close();
}
}
}
Solution 9 - C#
using (var connection = new SqlConnection("Enter Your Connection String"))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "Enter the First Command Here";
command.ExecuteNonQuery();
command.CommandText = "Enter Second Comand Here";
command.ExecuteNonQuery();
//Similarly You can Add Multiple
}
}
It worked for me.