ADO.NET - 更新多个数据表

问题描述

所以我有一些这样的代码:

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);"之后电脑死机了会怎样?我希望能够以某种方式仅使用一个 Update 调用来更新所有内容.这可能吗?

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 语句中， UPDATE: 不，它没有.DataAdapter 总是只更新一张表.将 DataSet 作为其参数的 Update() 重载，来自 MSDN 文档，从名为表"的数据表中为指定数据集中的每个插入、更新或删除的行调用相应的 INSERT、UPDATE 或 DELETE 语句."对困惑感到抱歉.不过，其余答案仍然有效.

You can pass a dataset into a DataAdapter's Update statement, 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 - 更新多个数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！