CodeProject - Inserting relational data using DataSet and DataAdapter(上)

作者：Mauricio Ritter       翻译：小新0574

原文（代码）链接：http://www.codeproject.com/cs/database/relationaladonet.asp

Introduction

Since the introduction of ADO.NET, we started to use a different concept in the development of database-driven applications, the disconnected environment. Most of you are already aware of this change, but this new development model brings some problems that need to be solved, in order to make our application work in a fully disconnected environment.

自从介绍了ADO.NET，我们就开始在数据驱动的应用程序开发里使用一个不同的概念，脱机环境。大多数人已经知道了这个改变，但是这个新的开发模型带来一些我们需要解决的问题，以使我们的程序运行在完全的脱机环境中。

One of the problems I've found when I was developing an application using this approach was the method of updating relational data using a DataSet and a DataAdapter objects. If you update just one DataTable there is no big deal in creating the code for the DataAdapter, but if you work on more than one table, and these tables have a parent-child relationship, the update/insert process can be a little tricky, specially if the parent table has an IDENTITY/AutoNumber column. In this article, I'll show some techniques to workaround this problem, specially if you work with AutoNumber/IDENTITY columns in your database.

在我使用这个方法开发程序的时候我发现使用DataSet和DataAdapter对象更新关系数据的方法有问题。如果你仅仅更新一个DataTable，那么为DataAdapter创建代码就没多大问题，但是如果你使用多于一个表，那些表之间有parent-child关系，那么更新/插入就要有点手段了，特别是父表有一个IDENTITY/AutoNumber列。在这篇文章里，我会展示一些技术来处理这个问题，特别是你在你的数据库里有AutoNumber/IDENTITY列要处理的时候。

In the example I'll use a database with 2 tables, one that holds Order and one that holds OrderDetails. The OrderDetails have a foreign key relationship with the Order table in the OrderId column, that is an IDENTITY/AutoNumber column.

在示例里我将使用含有两个表的一个数据库，一个保存Order，一个保存OrderDetails。OrderDetails表与Order表有一个外键关系，是用过OrderId 列来关联的，这是一个IDENTITY/AutoNumber列。

The article is divided in two parts, one that shows an implementation using an Access 2000 database, and another that using a SQL Server Database with stored procedures. Hope you enjoy this!

这篇文章分为两个部分，一个展示了使用Access 2000 数据库的实现，另一个展示SQL Server数据库使用储存过程的实现。希望你会喜欢！

Creating the structure (Access 2000)

The first thing you need to do in order to complete our example is to create some basic variables to hold our DataSet and two DataAdapter objects (one for the parent and one for the child table). We are going to use the System.Data.OleDb namespace, since we are working with Access 2000.

为了完成我们的示例你需要做的第一件事就是创建一些基本变量来保存我们的DataSet和两个DataAdapter对象（一个为父表，一个为子表）。因为我们处理的是Access 2000，所以我们将使用System.Data.OleDb命名空间。

// Create the DataSet object
DataSet oDS = new DataSet();
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; 
                        Data Source=orders.mdb");
conn.Open();

// Create the DataTable "Orders" in the Dataset and the OrdersDataAdapter
OleDbDataAdapter oOrdersDataAdapter = new 
    OleDbDataAdapter(new OleDbCommand("SELECT * FROM Orders", conn));
OleDbCommandBuilder oOrdersCmdBuilder = new
    OleDbCommandBuilder(oOrdersDataAdapter);
oOrdersDataAdapter.FillSchema(oDS, SchemaType.Source);

DataTable pTable = oDS.Tables["Table"];
pTable.TableName = "Orders";

// Create the DataTable "OrderDetails" in the Dataset 
//and the OrderDetailsDataAdapter
OleDbDataAdapter oOrderDetailsDataAdapter = new
    OleDbDataAdapter(new OleDbCommand("SELECT * FROM OrderDetails", conn));
OleDbCommandBuilder oOrderDetailsCmdBuilder = new
    OleDbCommandBuilder(oOrderDetailsDataAdapter);
oOrderDetailsDataAdapter.FillSchema(oDS, SchemaType.Source);

pTable = oDS.Tables["Table"];
pTable.TableName = "OrderDetails";

In the previous code sample, we have created a connection to our database and two DataAdapter objects, one to update the parent table (Orders) and one to update the child table (OrderDetails). We have used the FillSchema method of the DataAdapter to create both DataTable objects in the DataSet, so that they have the same structure of the database tables (including the AutoNumber field).

在前面的代码示例中，我们为我们的数据库创建一个连接和两个DataAdapter对象，一个用来更新父表（Orders），一个用来更新子表（OrderDetails）。我们已经使用了DataAdapter 的FillSchema 方法同时创建了DataSet里的两个DataTable，所以它们在数据库表中有相同的结构（包括AutoNumber字段）。

We've also used the OleDbCommand builder to create the additional commands for both DataAdapter, so that we can submit our changes in the DataSets to the database later on.

我们还使用了OleDbCommand builder来为两个DataAdapter创建附加的命令，这样我们待会就能把在DataSets里的修改提交给数据库。

Creating the relationship between the DataTables

Now we need to add a relationship between the two key columns of both tables. We'll do this by creating a new DataRelation object and attaching it to the Relations collection of our DataSet. The following code does exactly this.

现在我们需要为两个表的键添加关系。我们创建一个新的DataRelation 对象，把它放在DataSet的Relations 集合里。以下代码做了这些：

 

// Create the relationship between the two tables
oDS.Relations.Add(new DataRelation("ParentChild",
    oDS.Tables["Orders"].Columns["OrderId"],
    oDS.Tables["OrderDetails"].Columns["OrderId"]));

In the above code, we used the DataSet (oDS) to get a reference to both key columns of the DataTable objects, and created a DataRelation object, adding it to the Relations collection of the DataSet.

在以上代码里，我们使用DataSet (oDS)来获得对两个DataTable对象的键的引用，同时创建了DataRelation 对象，把它加到了DataSet的Relations 集合里。

Inserting Data

Now that we have everything set, we need to insert the data into to the DataSet, prior to update the data in the Access database. We'll insert a new row in the Order table and a new row in the OrderDetails table.

现在我们已经设置了所有东西，我们需要在更新Access数据库里的数据之前把数据插入到DataSet里。我们将分别插一行记录到Order 表和 OrderDetails 表里。

// Insert the Data
DataRow oOrderRow = oDS.Tables["Orders"].NewRow();
oOrderRow["CustomerName"] = "Customer ABC";
oOrderRow["ShippingAddress"] = "ABC street, 12345";
oDS.Tables["Orders"].Rows.Add(oOrderRow);

DataRow oDetailsRow = oDS.Tables["OrderDetails"].NewRow();
oDetailsRow["ProductId"] = 1;
oDetailsRow["ProductName"] = "Product 1";
oDetailsRow["UnitPrice"] = 1;
oDetailsRow["Quantity"] = 2;

oDetailsRow.SetParentRow(oOrderRow);
oDS.Tables["OrderDetails"].Rows.Add(oDetailsRow);

Notice that we have used the SetParentRow method to create the relationship between the two rows. This is the most important part when you want to update to tables that have a relationship and a AutoNumber column.

请注意我们已经使用SetParentRow 方法创建了两行记录之间的关系。这是当你想更新有一个关系和一个AutoNumber 列时最关键的一步。

Updating the DataSet

Now that we have the data inserted into the DataSet, its time to update the database.

现在我们已经把数据插入了DataSet,是时候更新数据库了。

oOrdersDataAdapter.Update(oDS, "Orders");
oOrderDetailsDataAdapter.Update(oDS, "OrderDetails");

conn.Close();

Solving the AutoNumber column issue

If you check the data in the database you'll notice that the rows inserted in the OrderDetails table have the OrderId column set to zero, and the inserted OrderId in the Orders table is set to one. This occurs due to some issues with Access 2000 and the DataAdapter object. When the DataAdapter object updates the data in the first table (Order) it does not return the generated AutoNumber column value, so the DataTable Orders in the DataSet stays with the value zero within it. In order to correct the problem, we need to map the RowUpdate event to update this information.

如果你检查数据库里的数据，你将会注意到插入到OrderDetails 表里的记录行把OrderId 列设为了0，插入到Orders 表的OrderId 设为了1。发生这样的情况跟Access2000和DataAdapter的一些问题有关。

To map the RowUpdate event we'll create an event handler, and get the value of the new auto-generated number to save in the DataSet. You'll new to add this line of code after the creation of the oOrdersDataAdapter object.

为了描绘RowUpdate事件，我们将创建一个事件句柄，把得到的新的自增数的值保存在DataSet里。你要在oOrdersDataAdapter对象创建以后新加入这行代码： 

oOrdersDataAdapter.RowUpdated += new
     OleDbRowUpdatedEventHandler(OrdersDataAdapter_OnRowUpdate);

Now we need to create an EventHandler function to handle this RowUpdate event.

现在我们需要创建一个EventHandler函数来操作这个RowUpdate事件：

static void OrdersDataAdapter_OnRowUpdate(object sender,
                    OleDbRowUpdatedEventArgs  e)
{
    OleDbCommand oCmd = new OleDbCommand("SELECT @@IDENTITY"
                                         e.Command.Connection);

    e.Row["OrderId"] = oCmd.ExecuteScalar();
e.Row.AcceptChanges();
}

For each update in the OrdersDataAdapter, we'll call a new SQL command that will get the newly inserted AutoNumber column value. We'll do this by using the SELECT @@IDENTITY command. This command works only in Access 2000/2002, not in the prior versions. After the value update in the DataSet, we need to call the AcceptChanges method of the Row, in order to maintain this row in an "updated" state, and not in a "changed" state.

在OrdersDataAdapter的每次更新时，我们调用一个用来获取新插入的AutoNumber 列值的新的SQL命令。我们使用SELECT @@IDENTITY命令来做这个。这个命令只用在Access 2000/2002，而不是在（Access）以前的版本里。在DataSet里面的值更新以后，我们需要调用Row的AcceptChanges方法，以使这个行保持在“Updated”状态，而不是“changed”状态。

If you try to execute the code again you'll see that now the row in the OrderDetails table contains the correct value in the column.

如果你再次试着执行代码，你将发现现在OrderDetails表的行在列里保存了正确的值。

Now we'll see how to target this same issue in SQL Server 2000. The method that I presented for Access database can be as well applied to SQL Server, but if you're working with stored procedures, there are other ways to do this.

现在我们将来看看在SQL Server2000来处理相同的问题。我为Aceess数据库展现的方法也能应用在SQL Sever里，但是如果你想使用储存过程，有其他的方法来做这个。

相关链接：CodeProject - Inserting relational data using DataSet and DataAdapter(下)