Walkthrough: Using a DataSet to Update a Database

Article
05/09/2013

Retired Content
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
The latest Enterprise Library information can be found at the Enterprise Library site.

This walkthrough demonstrates how to use a DataSet to update a database. It assumes that the following stored procedures have been created in the database to which the connection string in the code refers.

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

To reproduce the demonstration

Configure the database. For the necessary steps, see "QuickStart Configuration" in Data Access QuickStart.
Create the database (when you are not using the Unity Integration approach). The following code uses the factory to create a Database object that has the default configuration.
```
Database db = DatabaseFactory.CreateDatabase();
```
```
'Usage
Dim db As Database = DatabaseFactory.CreateDatabase()
```

Retrieve the initial DataSet by adding the following code.

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

'Usage
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)

Modify the DataSet by adding the following code.

// 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";

'Usage
' 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"

The following code shows how to create the DbCommand objects that insert a new product, delete a product, and update data in the 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);

'Usage
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)

Submit the DataSet by adding the following code, which shows how to update the database using the local data cached in the DataSet.

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

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

Share via

Walkthrough: Using a DataSet to Update a Database

Additional resources