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