c# - ADO.NET - Updating Multiple DataTables

Question

So I have some code like this:

        DataSet dataSet = new DataSet();            
        DataTable dataTable1 = new DataTable("Table1");
        DataTable dataTable2 = new DataTable("Table2");
        DataTable dataTable3 = new DataTable("Table3");
        DataTable dataTable4 = new DataTable("Table4");
        dataSet.Tables.Add(dataTable1);
        dataSet.Tables.Add(dataTable2);
        dataSet.Tables.Add(dataTable3);
        dataSet.Tables.Add(dataTable4);

        SqlDataAdapter dataAdapter1 = new SqlDataAdapter("SELECT * FROM Table1 WHERE ID = 1", sqlConnection);
        SqlDataAdapter dataAdapter2 = new SqlDataAdapter("SELECT Column1, Column2, Column3 FROM Table2", sqlConnection);
        SqlDataAdapter dataAdapter3 = new SqlDataAdapter("SELECT Column1, Column2, Column3 FROM Table3", sqlConnection);
        SqlDataAdapter dataAdapter4 = new SqlDataAdapter("SELECT Column1, Column2, Column3 FROM Table4", sqlConnection);

        SqlCommandBuilder commandBuilder1 = new SqlCommandBuilder(dataAdapter1);
        SqlCommandBuilder commandBuilder2 = new SqlCommandBuilder(dataAdapter2);
        SqlCommandBuilder commandBuilder3 = new SqlCommandBuilder(dataAdapter3);
        SqlCommandBuilder commandBuilder4 = new SqlCommandBuilder(dataAdapter4);            

        dataAdapter1.Fill(dataTable1);
        dataAdapter2.FillSchema(dataTable2, SchemaType.Source);
        dataAdapter3.FillSchema(dataTable3, SchemaType.Source);
        dataAdapter4.FillSchema(dataTable4, SchemaType.Source);

        //do a bunch of code that updates the one row from Table1
        //and adds lots of new rows to Table2, Table3, Table4

        dataAdapter1.Update(dataTable1);
        dataAdapter2.Update(dataTable2);
        dataAdapter3.Update(dataTable3);
        dataAdapter4.Update(dataTable4);
        dataSet.AcceptChanges();

Is there anyway to make this a lot simpler? What would happen if the computer crashed on the line after "dataAdapter2.Update(dataTable2);"? I would like to be able to somehow use just one Update call to update everything. Is that possible?

Also, is this even the best way to do this? With "this" being creating a bunch of new rows in multiple tables depending on what is in one specific row in one specific table.

Answer 1

You can pass a dataset into a DataAdapter's Update statement, which will update all of the tables in the dataset. UPDATE: No, it doesn't. DataAdapters always update only one table. The overload to Update() that takes a DataSet as its parameter, from the MSDN documentation, "Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable named "Table"." Sorry for the confusion. The rest of the answer is still valid, though.

If you want to assure that all the updates succeed or fail as an atomic unit, use the SqlTransaction object:

DataSet ds = new DataSet();
// do something with the dataset

SqlDataAdapter dataAdapter = new SqlDataAdapter();
SqlConnection cn = new SqlConnection(connString);
cn.Open();

SqlTransaction trans = cn.BeginTransaction();

SqlDataAdapter dataAdapter = new SqlDataAdapter();

// set the InsertCommand, UpdateCommand, and DeleteCommand for the data adapter

dataAdapter.InsertCommand.Transaction = trans;
dataAdapter.UpdateCommand.Transaction = trans;
dataAdapter.DeleteCommand.Transaction = trans;

try
{
    dataAdapter.Update( ds );
    trans.Commit();
}
catch
{
    trans.Rollback();
}

cn.Close();