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   fname lname gmail com       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

User · Answer

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.

User · Answer

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

User · Answer

using  var connection   new SqlConnection  quot Enter Your Connection String quot                  connection Open                 using  var command   connection CreateCommand                          command CommandText    quot Enter the First Command Here quot               command ExecuteNonQuery                     command CommandText    quot Enter Second Comand Here quot               command ExecuteNonQuery           Similarly You can Add Multiple

User · Answer

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   lt SQL Command gt    myConnection   var reader   command ExecuteReader        Change the SQL Command and execute command CommandText     lt New SQL Command gt    command ExecuteNonQuery

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Just enable this property in your connection string   sqb MultipleActiveResultSets   true   This property allows one open connection for multiple datareaders

User · Answer

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

[c#] How to run multiple SQL commands in a single SQL connection?

Examples related to c#

Examples related to sql-server

Examples related to ado.net

Examples related to sqlconnection

Examples related to sqlcommand