How to run multiple SQL commands in a single SQL connection?

Question

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.

Answer 1

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(); 

Answer 2

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 }