If there is a technique for inserting values ​​into multiple tables in sql Server 2008 Database?

advertisements

I just wanted to know, if there is any technique to insert values into multiple tables in sql Server 2008 Database?

I've got the following

    cmd.CommandText = "Insert into tb1 (col1, col2, col3) values (@col1, @col2, @col3); Insert into tb2 (col1, col2, col3) values (@col11, @col12, @col13);";

    cmd.Parameters.AddWithValue("col1","val1"); 

    cmd.Parameters.AddWithValue("col2", "val2");
    cmd.Parameters.AddWithValue("col3", "val3"); 

    cmd.Parameters.AddWithValue("col11","val4"); cmd.Parameters.AddWithValue("col12", "val5");
    cmd.Parameters.AddWithValue("col13", "val6");

But will values be inserted to "tb1" even if "Insert into tb2..." part gets an error? if yes then it is not what i wanted. i wanted that, values should not be inserted into tb1 if tb2 part gets an error.

please help me out.


You should wrap your code into a transaction:

  using (SqlConnection conn = new SqlConnection(connectionString))
  {
      conn.Open();

      // Begin the transaction
      tran = cnn.BeginTransaction();

      cmd = new SqlCommand();
      cmd.Connection = conn;
      cmd.Transaction = tran;

      try
      {
           //place your current code here with multiple cmd.ExecuteNonQuery

           // Commit the transaction ....
           tran.Commit();
      }
      catch(Exception e)
      {
           tran.Rollback();
      }
 }