原文(代码)链接:http://www.codeproject.com/cs/database/relationaladonet.asp
Creating the structure (SQL Server 2000)
We'll start by creating the same structure that we used for Access 2000, but instead of creating the DataAdapter
commands using the CommandBuilder
, we'll create them by code, since we're going to use a SQL Server stored procedure to update the data.
我们将开始于创建我们为Access2000使用的相同的结构,但是与前面使用CommandBuilder
创建DataAdapter
命令不同,我们使用代码创建它们,因为我们要用一个SQL储存过程来更新数据。
DataSet oDS = new DataSet();
SqlConnection conn = new SqlConnection("Data Source=.;
Initial Catalog=Orders;Integrated Security=SSPI");
conn.Open();
// Create the DataTable "Orders" in the Dataset and the OrdersDataAdapter
SqlDataAdapter oOrdersDataAdapter = new
SqlDataAdapter(new SqlCommand("SELECT * FROM Orders", conn));
oOrdersDataAdapter.InsertCommand = new
SqlCommand("proc_InsertOrder", conn);
SqlCommand cmdInsert = oOrdersDataAdapter.InsertCommand;
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.Parameters.Add(new SqlParameter("@OrderId", SqlDbType.Int));
cmdInsert.Parameters["@OrderId"].Direction = ParameterDirection.Output;
cmdInsert.Parameters["@OrderId"].SourceColumn = "OrderId";
cmdInsert.Parameters.Add(new SqlParameter("@CustomerName",
SqlDbType.VarChar,50,"CustomerName"));
cmdInsert.Parameters.Add(new SqlParameter("@ShippingAddress",
SqlDbType.VarChar,50,"ShippingAddress"));
oOrdersDataAdapter.FillSchema(oDS, SchemaType.Source);
DataTable pTable = oDS.Tables["Table"];
pTable.TableName = "Orders";
// Create the DataTable "OrderDetails" in the
// Dataset and the OrderDetailsDataAdapter
SqlDataAdapter oOrderDetailsDataAdapter = new
SqlDataAdapter(new SqlCommand("SELECT * FROM OrderDetails", conn));
oOrderDetailsDataAdapter.InsertCommand = new
SqlCommand("proc_InsertOrderDetails", conn);
cmdInsert = oOrderDetailsDataAdapter.InsertCommand;
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.Parameters.Add(new SqlParameter("@OrderId", SqlDbType.Int));
cmdInsert.Parameters["@OrderId"].SourceColumn = "OrderId";
cmdInsert.Parameters.Add(new SqlParameter("@ProductId", SqlDbType.Int));
cmdInsert.Parameters["@ProductId"].SourceColumn = "ProductId";
cmdInsert.Parameters.Add(new
SqlParameter("@ProductName", SqlDbType.VarChar,50,"ProductName"));
cmdInsert.Parameters.Add(new
SqlParameter("@UnitPrice", SqlDbType.Decimal));
cmdInsert.Parameters["@UnitPrice"].SourceColumn = "UnitPrice";
cmdInsert.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int ));
cmdInsert.Parameters["@Quantity"].SourceColumn = "Quantity";
oOrderDetailsDataAdapter.FillSchema(oDS, SchemaType.Source);
pTable = oDS.Tables["Table"];
pTable.TableName = "OrderDetails";
// Create the relationship between the two tables
oDS.Relations.Add(new DataRelation("ParentChild",
oDS.Tables["Orders"].Columns["OrderId"],
oDS.Tables["OrderDetails"].Columns["OrderId"]));
In this piece of code we're manually creating a SqlCommand
to do all the inserts in the database table through the DataAdapter
. Each SqlCommand
calls a stored procedure in the database that has the parameters structure equal to the table structure.
在这段代码里我们手工创建了一个SqlCommand
通过DataAdapter
来实现数据表的所有插入。每个SqlCommand
调用数据库里的一个储存过程,这个数据库有等同于表结构的参数结构。
The most important thing here is the OrderId
parameter of the first DataAdapter
's command. This parameter has a different direction than the others. The parameter has an output direction and a source column mapped to the OrderId
column of the DataTable
. With this structure, after each execution, the stored procedure will return the value to this parameter, that will be copied to the OrderId
source column. The OrderId
parameter receives the @@IDENTITY inside the procedure, like the one below.
在这最重要的东西就是在第一个DataAdapter
命令里的OrderId
参数。这个参数跟其他的相比有一个不同的direction。这个参数有一个output direction和一个映射到DataTable
的OrderId
列的源列。有了这个结构,在每次执行以后,储存过程就会为这个参数返回一个值,这样这个值就会被拷贝到源OrderId
列。OrderId
参数在储存过程里接受@@IDENTITY,就像下面的这个:
(@OrderId int output,
@CustomerName varchar(50),
@ShippingAddress varchar(50)
)
AS
INSERT INTO Orders (CustomerName, ShippingAddress)
VALUES
(@CustomerName, @ShippingAddress)
SELECT @OrderId=@@IDENTITY
Inserting the Data
Now that we set the entire structure, its time to insert the data. The process is exactly the same as we have done with the Access database, using the SetParentRow
method to maintain the relationship and guarantee that the IDENTITY
column will be copied to the child table (OrderDetails
).
现在我们设置了整个结构,是时候来插入数据了。过程就跟我们处理Access数据库一样,使用SetParentRow
方法维持关系,保证IDENTITY
列被拷贝到子表里(OrderDetails
)。
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);
oOrdersDataAdapter.Update(oDS, "Orders");
oOrderDetailsDataAdapter.Update(oDS, "OrderDetails");
conn.Close();
If you check the database you'll see that the OrderId
column is updated with the correct IDENTITY
column value.
如果你检查数据库你就会发现OrderId
列更新了正确的IDENTITY
列值。