Share via

Using a DataSet to Update a Database

  • Article

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.

Using UpdateDataSet

The following code shows how to use the UpdateDataSet method. It assumes that you have resolved the Database class you require and stored a reference in the variable named db.

For more information on instantiating objects see Creating and Referencing Enterprise Library Objects.

DataSet productsDataSet = new DataSet();

string sql = "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 productsDataSet As DataSet = new DataSet()

Dim sql As String = "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.

You can provide a value for the UpdateBatchSize parameter of the UpdateDataSet method. This forces the method to attempt to perform updates in batches instead of sending each one to the database individually. This is more efficient, but the return value for the method will show only the number of updates made in the final batch, not the total number for all batches.