title: sqldataadapter multiple tables and transaction
body: <p><a href="https://stackoverflow.com/questions/5889102/ado-net-updating-multiple-datatables">ADO.NET - Updating Multiple DataTables</a></p>
  
  <p>I found the question above which is exactly what I am looking for and trying to implement a solution...</p>
  
  <p>I am trying to see a best solution to updating multiple table into sqlserver 2008 and ensure transaction on the multiple tables.  If inserting into one table fails, rollback the previous updates on other tables.</p>
  
  <ol>
  <li>sqldataadapter only updates one table at a time</li>
  <li>I know we can do transaction on one dataadapter at a time but need to update multiple tables</li>
  </ol>
  
  <p>Below are codes modified from the answer provided above, but it error out with the dataadapter.  I think I need to set the insert, update and delete commands but not sure how that works if there are multple tables??? can someone fill in the missing link?</p>
  
  <pre><code>        using (SqlConnection con = new SqlConnection(SqlServerConnectionString))
          {
              con.Open();
              //table is an array of datatable and contains data to be inserted
              for (int i = 0; i &lt; table.Length; i++)
              {
                  ds.Tables.Add(table[i]);
              }
  
              SqlTransaction trans = con.BeginTransaction();
  
              SqlDataAdapter dataAdapter = new SqlDataAdapter();
              //Need to set insert,select, update, delete???? on multiple tables?
  
              //dataAdapter.Fill(ds);
              dataAdapter.InsertCommand.Transaction = trans; //null object error here
              dataAdapter.UpdateCommand.Transaction = trans;
              dataAdapter.DeleteCommand.Transaction = trans;
  
              try
              {
  
                  dataAdapter.Update(ds);
                  trans.Commit();
              }
              catch(Exception ex)
              {
                  trans.Rollback();
  
              }
  </code></pre>