Walkthrough: Using a DataSet to Update a Database

  • Article
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

  1. Configure the database. For the necessary steps, see "QuickStart Configuration" in Data Access QuickStart.
  2. 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.
  3. Retrieve the initial DataSet by adding the following code.
  4. Modify the DataSet by adding the following code.
  5. The following code shows how to create the DbCommand objects that insert a new product, delete a product, and update data in the DataSet.
  6. Submit the DataSet by adding the following code, which shows how to update the database using the local data cached in the DataSet.