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

Answer 1

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

Answer 2

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

Example Steps:

1. Create new connection
2. Open Connection
3. Create command 1
4. Add params to Command 1
5. Execute Command 1
6. Create command 2
7. Add params to Command 2
8. Execute Command 2
9. Close Connection
10. Dispose Items

Answer 3

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

Answer 4

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.