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.

Databases must be periodically updated with new information. For example, in a Web-based online retail application, you may want to add a new customer to the database, modify the name associated with a customer ID, or delete a customer record entirely.

Typical Goals

The goal in this scenario is to transfer data stored in a DataSet object to the database. (Remember that a DataSet is a local cache of information; changes are not automatically propagated back to the original data source.)

Solution

To propagate changes from a DataSet object to the database, use the UpdateDataSet method.

QuickStart

For an extended example of how to update a database using a DataSet object, see the QuickStart walkthrough, Walkthrough: Using a DataSet to Update a Database.

Using UpdateDataSet

The following code shows how to use the UpdateDataSet method.

Database db = DatabaseFactory.CreateDatabase();

DataSet productsDataSet = new DataSet();

string sql = "Select Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate From Products";
DbCommand cmd = db.GetSqlStringCommand(sql);

string productsTable = "Products";

// Retrieve the initial data.
db.LoadDataSet(cmd, productsDataSet, productsTable);

// Get the table that will be modified.
DataTable dTable = productsDataSet.Tables[productsTable];

// Add a new product to existing DataSet.
DataRow addedRow = dTable.Rows.Add(new object[] {DBNull.Value, "New product", 11, 25});

// Modify an existing product.
dTable.Rows[0]["ProductName"] = "Modified product";

// Establish the Insert, Delete, and Update commands.
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);

// Submit the DataSet, capturing the number of rows that were affected.
int rowsAffected = db.UpdateDataSet(productsDataSet, "Products", insertCommand, updateCommand, deleteCommand,
                    Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard);

'Usage
Dim db As Database = DatabaseFactory.CreateDatabase()

Dim productsDataSet As DataSet = new DataSet()

Dim sql As String = "Select Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate From Products"
Dim cmd As DbCommand = db.GetSqlStringCommand(sql)

Dim productsTable As String = "Products"

' Retrieve the initial data.
db.LoadDataSet(cmd, productsDataSet, productsTable)

' Get the table that will be modified.
Dim dTable As DataTable = productsDataSet.Tables(productsTable)

' Add a new product to existing DataSet.
Dim addedRow As DataRow = dTable.Rows.Add(New Object() {DBNull.Value, "New product", 11, 25})

' Modify an existing product.
dTable.Rows(0)("ProductName") = "Modified product"

' Establish the Insert, Delete, and Update commands.
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, capturing the number of rows that were affected.
Dim rowsAffected As Integer = db.UpdateDataSet(productsDataSet, "Products", insertCommand, updateCommand, deleteCommand, _
                               Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard)

Usage Notes

The UpdateDataSet method can specify the behavior that should occur when it encounters an error. This behavior must be supported by the Database object subtype. The preceding example uses UpdateBehavior.Standard, which indicates that if an error occurs, the update will stop at the point of the error. No additional rows will be affected, and no rollback will occur for rows that are already changed. The available update behaviors are the following:

  • Standard. There is no interference with the DataAdapter object's Update command. If the Update command encounters an error, the update stops. Additional rows in the Datatable are unaffected.
  • Continue. If the DataAdapter object's Update command encounters an error, the update will continue. The Update command will try to update the remaining rows.
  • Transactional. If the DataAdapter object encounters an error, all updated rows will be rolled back.