How can I create two commands in one connection?

Question

For example I want to INSERT data in database and also UPDATE another table. My code is like this

SqlConnection con = new SqlConnection("**");
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "INSERT Borrowbook VALUES (@StudentID, @ISBN, @Title, @Date)";
SqlParameter p1 = new SqlParameter("@StudentID", SqlDbType.NChar);
p1.Value = textBox2.Text;
cmd.Parameters.Add(p1);
SqlParameter p2 = new SqlParameter("@ISBN", SqlDbType.NVarChar);
p2.Value = textBox4.Text;
cmd.Parameters.Add(p2);
SqlParameter p3 = new SqlParameter("@Title", SqlDbType.VarChar);
p3.Value = textBox3.Text;
cmd.Parameters.Add(p3);
SqlParameter p4 = new SqlParameter("@Date", SqlDbType.DateTime);
p4.Value = dateTimePicker1.Text;
cmd.Parameters.Add(p4);    
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("The books has been successfully borrowed!", 
    "Information ... ",
    MessageBoxButtons.OK, 
    MessageBoxIcon.Information, 
    MessageBoxDefaultButton.Button1);

I don't quite understand what's the problem. To create two commands, you can just call `con.CreateCommand()` twice. — Branko Dimitrijevic, Aug 22 '13 at 14:21
Is your question actually how to use the new identity value to update another table? Then append `;SELECT CAST(SCOPE_IDENTITY AS INT);` to the insert and `int newID = cmd.ExecuteScalar();` to execute the insert **and** retrieve it in one step. — Tim Schmelter, Aug 22 '13 at 14:24

score 4 · Accepted Answer · edited May 23 '17 at 10:29

First of all you really should be using using statements so your connections get closed in the event of an exception

using(SqlConnection con = new SqlConnection("**********************************************"))
using(SqlCommand cmd = con.CreateCommand()) //The create command can happen before the open
{
    con.Open();
    cmd.CommandText = "INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES    (  @StudentID,  @ISBN , @Title, @Date)";
    //(Snip adding parameters)
    cmd.ExecuteNonQuery();
    //You don't need to call close if you are using "using"
}

That out of the way there are three ways to do it.

You could put both commands in a single command statement.

using(SqlConnection con = new SqlConnection("**********************************************"))
using(SqlCommand cmd = con.CreateCommand())
{
    con.Open();
    cmd.CommandText = @"INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES    (  @StudentID,  @ISBN , @Title, @Date); 
                        INSERT INTO StudentActvitiy ([Student ID], Date) VALUES    (  @StudentID, GETDATE())";
    //(Snip adding parameters)
    cmd.ExecuteNonQuery();
}

or you could change the command text and run it again

using(SqlConnection con = new SqlConnection("**********************************************"))
using(SqlCommand cmd = con.CreateCommand())
{
    con.Open();
    cmd.CommandText = "INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES    (  @StudentID,  @ISBN , @Title, @Date)";
    //(Snip adding parameters)
    cmd.ExecuteNonQuery();

    cmd.CommandText = "INSERT INTO StudentActvitiy ([Student ID], Date) VALUES    (  @StudentID, GETDATE())"
    cmd.ExecuteNonQuery();
}

or you could do two commands

using(SqlConnection con = new SqlConnection("**********************************************"))
using(SqlCommand cmd = con.CreateCommand())
using(SqlCommand cmd2 = con.CreateCommand())
{
    con.Open();
    cmd.CommandText = "INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES    (  @StudentID,  @ISBN , @Title, @Date)";
    //(Snip adding parameters)
    cmd.ExecuteNonQuery();

    cmd2.CommandText = "INSERT INTO StudentActvitiy ([Student ID], Date) VALUES    (  @StudentID, GETDATE())"
    SqlParameter p21 = new SqlParameter("@StudentID", SqlDbType.NChar);
    p21.Value = textBox2.Text;
    cmd2.Parameters.Add(p21);
    cmd2.ExecuteNonQuery();
}

To do Tim's solution it is kind of a combination of the first and the 3rd.

using(SqlConnection con = new SqlConnection("**********************************************"))
using(SqlCommand cmd = con.CreateCommand())
using(SqlCommand cmd2 = con.CreateCommand())
{
    con.Open();
    cmd.CommandText = @"INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES    (  @StudentID,  @ISBN , @Title, @Date);
                        SELECT CAST(SCOPE_IDENTITY AS INT);";
    //(Snip adding parameters)
    var resultId = (int)cmd.ExecuteScalar();

    cmd2.CommandText = "INSERT INTO StudentActvitiy ([Student ID], Date, BorrowBookId) VALUES    (  @StudentID, GETDATE(), @borrowBookId)"
    SqlParameter p21 = new SqlParameter("@StudentID", SqlDbType.NChar);
    p21.Value = textBox2.Text;
    cmd2.Parameters.Add(p21);

    SqlParameter p22 = new SqlParameter("@borrowBookId", SqlDbType.Int);
    p22.Value = resultId;
    cmd2.Parameters.Add(p22);
    cmd2.ExecuteNonQuery();
}

Would not it be better to dispose of `cmd` and `cmd2` immediately after they are not needed? In this case you can even keep the same name (i.e. `cmd`). — Victor Zakharov, Aug 22 '13 at 14:36
@Neolisk "better" in what way? I don't think you will get any performance improvement and IMHO nesting the using statements instead of all having them up front decreases readability which I think makes it "worse" (but that is a matter of coding style). For `cmd` and `cmd2` I was just following the OP's naming style I would have called them `borrowInsertCommand` and `logInsertCommand`. — Scott Chamberlain, Aug 22 '13 at 14:39
Scalability I guess. If the number of commands goes up to 10, readability may become a problem. Blocks of `cmd` and `cmd2` could be independent functions, each creating its own command and disposing of it. Then you could have a create&dispose command wrapper, to keep it DRY. In the end you may even get a slight improvement in code metrics. — Victor Zakharov, Aug 22 '13 at 14:49
Like I said, there are many ways to do it, do not blindly follow any advice on the internet. Always test, benchmark, and make sure it follows the rest of your project's coding style. For your goes up to 10 example, I agree with you, I would make separate blocks as that would be likely more readable. I am not trying to say you must always put all of your usings at the top, I am just saying in this specific case, for this short of a function, it makes sense to do it. — Scott Chamberlain, Aug 22 '13 at 14:54

score 1 · Answer 2 · answered Aug 22 '13 at 14:25

You can. Don't close your connection before you execute the second command.

Example Steps:

Create new connection

Open Connection

Create command 1

Add params to Command 1

Execute Command 1

Create command 2

Add params to Command 2

Execute Command 2

Close Connection

Dispose Items

score 1 · Answer 3 · answered Aug 22 '13 at 14:28

You can do as following

        SqlConnection cn = new SqlConnection("********");
        string stmt = "insert projects(projectname) values('" + name + "' )";
        string stmt1="update dept set deptid="+id;

        SqlCommand cmd = new SqlCommand(stmt, cn);
        sqlcommand cmd1=new sqlcommand(stmt1,cn);           

        cn.Open();
        cmd.ExecuteNonQuery();

        cmd1.ExecuteNonQuery();
        cn.close()

Gary Walker · Answer 4 · 2013-08-23T16:55:28.090

When I see questions like this, I almost always ask, "Why aren't you using a stored proc for this?" You can avoid extra round trips to the database server. It is easier to encapsulate transaction logic, and it is a great place to enforce data based business logic.

The technical answers are good, but you are still "doing it wrong" unless you are stuck in the multi-database platform enviroment, but in that case, you are probably doing it wrong unless you are writing a data-tier to handle the database differences for you.

I thought I should also add that whether you are using a stored proc and multiple statement, you should reference tables, etc., with the qualified name dbo.tablename instead of tablename. This optimizes performance by avoiding unnecessary security checks in a proc, but it is critical for peformance when using client generated dynamic sql because leaving off dbo. prevents sql server from reusing the compiled sql and fills up the compiled sql cache very quickly if it is called frequently.

How can I create two commands in one connection?

4 Answers4

Linked