ADO.NET - 更新多个数据表 [英] ADO.NET - Updating Multiple DataTables

问题描述

让我有些code是这样的：

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

反正有做这个有很多更简单？会发生什么，如果计算机崩溃后行dataAdapter2.Update（dataTable2）;？我希望能够以某种方式使用只是一个更新调用来更新一切。这可能吗？

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.

推荐答案

您可以通过一个数据集中到一个DataAdapter的Update语句，<击>这将更新所有的表中的数据集更新：不，它没有。 DataAdapters随时更新只有一个表。过载，以更新（）采用一个数据集作为它的参数，从的 MSDN文档，调用相应的INSERT，UPDATE或DELETE每个插入，更新或删除行从数据表名为表指定的DataSet声明。对困惑感到抱歉。答案的其余部分仍然有效，但。

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.

如果你想确保所有的更新成功或失败作为一个原子单元，使用的的SqlTransaction 对象：

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

这篇关于ADO.NET - 更新多个数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！