CODEWITHSUNDEEP
sql-serversql-server-2008
Krishanu Dey
Krishanu Dey

Reputation: 6406

if there is any technique to insert values into multiple tables in sql Server 2008 Database?

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.

Upvotes: 1

Views: 265

Answers (2)

BluesRockAddict
BluesRockAddict

Reputation: 15683

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

Upvotes: 1

Valamas
Valamas

Reputation: 24759

Use transactions

using (SqlConnection sqlConn = new SqlConnection(ConnectionString))
{
    sqlConn.Open();

    using (SqlTransaction sqlTrans = sqlConn.BeginTransaction())
    {
        // ... insert 1
        // ... insert 2

        sqlTrans.Commit();
    }
}

Upvotes: 0

Related Questions