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.

Idiocy

what happens if you already have a table with the same name but no data in it? Also have you considered what happens if someone types SQL into one of your textboxes that you're inserting? – Virnelli

Greg poses an important question, and here is an answer: How and why to use parameterized queries – Impermissible

It doesn't get to the else branch because if the table does not exist the query will error with an exception rather than return no rows. You should also probably dispose the reader before executing another command on the same connection (although your close and re-open might be cleaning that up indirectly? It's bad form though). There are much better ways to detect whether a table exists, but to do it in this way you could perhaps use a try/catch. It might also be easier to use ExecuteScalar() instead of ExecuteReader() since you don't use any of the actual results, anyway. – Dispassionate

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

Assignor

var cmd = new SqlCommand("select * from " + mytags.Text + " ", con); var rd = cmd.ExecuteReader(); if (rd.Read()) { cmd.CommandText="insert into " + mytags.Text + " values ('[email protected]','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','"+mytags.Text+"')"; cmd.ExecuteNonQuery(); – Idiocy

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
}

Simp

my first command is to create a temp table. my next command needs to use that temp table. in your example, the temp table disappears. when i did a trace, the trace showed me "exec CreateTableSproc ". how would i get the temp table to be seen by the next command? – Federation

@l--''''''---------'''''''''''': wrap the whole block with TransactionScope. Sorry for a latest response :)) – Simp

Answer 3

Just enable this property in your connection string:

sqb.MultipleActiveResultSets = true;

This property allows one open connection for multiple datareaders.

Alatea

This I believe should be the answer. – Solarize

And if you plan on enabling MARS make sure you read up on the "special considerations" here: msdn.microsoft.com/en-us/library/h32h3abf(v=vs.110).aspx – Struve

I found my answer.and answered it above – Alatea

This is correct except the entry in the connection string should be: MultipleActiveResultSets = true; – Zoubek

Answer 4

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 Is it possible to have multiple SQL instructions in a ADO.NET Command.CommandText property? too

Hemidemisemiquaver

Answer 5

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.

Barrie

I believe parameterized queries are used to mitigate this issue. – Gooey

Anyone reading this, ignore the reply and just google "parametrized query". This post is just confusing. – Pigeonhole

@filipe, the create table statement isn't really going to work with parameterized queries. When building dynamic sql like the original poster is doing, it is prudent to use something like sp_executesql to protect against misuse or abuse. This is especially true if you are ally DDL as well as DML for the user with the active connection. – Barrie

Answer 6

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

        }
    }

Weitman

This has the additional advantage that there will only be one round trip to the database, because there is only one command that is executed. However, be advised that the number of parameters for an SqlCommand is limited to 2100. – Aga

Answer 7

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

Jeanejeanelle

Thanks, I needed this. – Fissi

Answer 8

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

Mer

Answer 9

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.

Impregnate