Enterprise Library 4.1数据访问应用程序块快速入门【6】使用DataSet更新数据库

本演练演示如何使用DataSet更新数据库。它假定以下存储过程已在被引用的数据库中被创建。
CREATE PROCEDURE AddProduct
(
    @ProductName nvarchar(50),
    @CategoryID int,
    @UnitPrice money
)
AS
INSERT INTO Products (ProductName, CategoryID, UnitPrice)
VALUES (@ProductName, @CategoryID, @UnitPrice)

SELECT ProductID, ProductName, CategoryID, UnitPrice
FROM Products
WHERE ProductID = SCOPE_IDENTITY()
GO

CREATE PROCEDURE DeleteProduct
(
    @ProductID int
)
AS
DELETE Products
WHERE ProductID = @ProductID
GO

CREATE PROCEDURE UpdateProduct
(
    @ProductID int,
    @ProductName nvarchar(50),
    @LastUpdate datetime
)
AS
UPDATE Products
SET ProductName = @ProductName
WHERE ProductID = @ProductID AND LastUpdate = @LastUpdate

IF @@ROWCOUNT > 0
  -- This statement is used to update the DataSet if changes are done
  -- on the updated record (identities, timestamps or triggers )
  SELECT ProductID, ProductName, CategoryID, UnitPrice
  FROM Products
  WHERE ProductID = @ProductID
GO

1、配置数据库。必要的步骤，请参阅数据访问快速入门。
2、创建Database（当你不使用统一的集成方法）。下面的代码使用工厂创建一个默认的配置数据库对象。

Database db = DatabaseFactory.CreateDatabase();

Dim db As Database = DatabaseFactory.CreateDatabase()

3、检索添加以下代码初始数据集。

DataSet productsDataSet = new DataSet(); string sqlCommand = "Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate From Products"; DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand); string productsTable = "Products"; // Retrieve the initial data. db.LoadDataSet(dbCommand, productsDataSet, productsTable);

Dim productsDataSet As DataSet = New DataSet Dim sqlCommand As String = "Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate From Products" Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand) Dim productsTable As String = "Products" ' Retrieve the initial data. db.LoadDataSet(dbCommand, productsDataSet, productsTable)

4、修改通过添加下面的代码集。

// Get the table that will be modified. DataTable table = productsDataSet.Tables[productsTable]; // Add a new product to existing DataSet. DataRow addedRow = table.Rows.Add(new object[] {DBNull.Value, "New product", 11, 25}); // Modify an existing product. table.Rows[0]["ProductName"] = "Modified product";

' Get the table that will be modified. Dim table As DataTable = productsDataSet.Tables(productsTable) ' Add a new product to existing DataSet. Dim addedRow As DataRow = table.Rows.Add(New Object() {DBNull.Value, "New product", 11, 25}) ' Modify an existing product. table.Rows(0)("ProductName") = "Modified product"

5、以下代码显示了如何创建DbCommand对象，插入一个新的产品，删除产品，并更新DataSet中的数据。

DbCommand insertCommand = db.GetStoredProcCommand("AddProduct"); db.AddInParameter(insertCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current); db.AddInParameter(insertCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current); db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current); DbCommand deleteCommand = db.GetStoredProcCommand("DeleteProduct"); db.AddInParameter(deleteCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current); DbCommand updateCommand = db.GetStoredProcCommand("UpdateProduct"); db.AddInParameter(updateCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current); db.AddInParameter(updateCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current); db.AddInParameter(updateCommand, "LastUpdate", DbType.DateTime, "LastUpdate", DataRowVersion.Current);

Dim insertCommand As DbCommand = db.GetStoredProcCommand("AddProduct") db.AddInParameter(insertCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current) db.AddInParameter(insertCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current) db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current) Dim deleteCommand As DbCommand = db.GetStoredProcCommand("DeleteProduct") db.AddInParameter(deleteCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current) Dim updateCommand As DbCommand = db.GetStoredProcCommand("UpdateProduct") db.AddInParameter(updateCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current) db.AddInParameter(updateCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current) db.AddInParameter(updateCommand, "LastUpdate", DbType.DateTime, "LastUpdate", DataRowVersion.Current)

6、添加以下代码提交DataSet，显示如何使用DataSet中缓存本地数据集更新数据库。

int rowsAffected = db.UpdateDataSet(productsDataSet, "Products", insertCommand, updateCommand, deleteCommand, Microsoft.Practices.EnterpriseLibrary.DataAccess.UpdateBehavior.Standard);

Dim rowsAffected As Integer = db.UpdateDataSet(productsDataSet, "Products", insertCommand, updateCommand, _ deleteCommand, Microsoft.Practices.EnterpriseLibrary.DataAccess.UpdateBehavior.Standard)

文章由唐勇(http://www.tangyong.net/)翻译自微软Enterprise Library系列英文文档，翻译得辛苦，转载请保留，谢谢！