DataSet is an in-memory cache of data retrieved from a data source; it is especially very useful during disconnected mode. Records can be added, updated, and deleted in memory; the DataSet keeps track of these changes and can be used to make batch updates to the database. Typically a DataSet object is created or loaded using the ExecuteDataSet or LoadDataSet methods respectively. The only difference is that LoadDataSet loads data on existing DataSet objects; this approach is useful while retrieving data through multiple execution. Once the data is retrieved and records have been added/modified/deleted, the DataSet can be passed on to the UpdateDataSet method of Database to update the database with the changes.
The following code snippet shows a typical record update using DataSet:
DataSet customerDataSet = new DataSet();
//Step 1: Create Default Database instance
Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
//-----------------------------------------------------
//Step 2: Create Database Command to retrieve Customers
DbCommand selectCommand = db.GetSqlStringCommand("Select CustomerID, FirstName, LastName From Customers");
//Step 3: Retrieve Customers using LoadDataSet
db.LoadDataSet(selectCommand, customerDataSet, "Customers");
//-----------------------------------------------------
//Step 4: Get the Customer DataTable Object for convenience
DataTable customerTable = customerDataSet.Tables["Customers"];
//-----------------------------------------------------
//Step 5: Create Database Command to insert Customers
DbCommand insertCommand = db.GetSqlStringCommand("INSERT INTO Customers(FirstName, LastName) VALUES(@FirstName, @LastName)");
//Step 6: Add input parameters to insert Customers
db.AddInParameter(insertCommand, "FirstName", DbType.String, "FirstName", DataRowVersion.Current);
db.AddInParameter(insertCommand, "LastName", DbType.String, "LastName", DataRowVersion.Current);
//Add new Customer to the table
customerTable.Rows.Add(new object[] { DBNull.Value, "Mark", "Twain" });
//-----------------------------------------------------
//-----------------------------------------------------
//Step 7: Create Database Command to update Customers
DbCommand updateCommand = db.GetSqlStringCommand("UPDATE Customers SET FirstName = @FirstName, LastName = @LastName WHERE CustomerID = @CustomerID");
//Step 8: Add input parameters to update Customers
db.AddInParameter(updateCommand, "CustomerID", DbType.Int32, "CustomerID", DataRowVersion.Current);
db.AddInParameter(updateCommand, "FirstName", DbType.String, "FirstName", DataRowVersion.Current);
db.AddInParameter(updateCommand, "LastName", DbType.String, "LastName", DataRowVersion.Current);
//Modifying First & Last Name of Customer
customerTable.Rows[0]["FirstName"] = "Rob";
customerTable.Rows[0]["LastName"] = "Connery";
//-----------------------------------------------------
//-----------------------------------------------------
//Step 9: Add input parameters to delete Customers
DbCommand deleteCommand = db.GetSqlStringCommand("DELETE FROM Customer WHERE CustomerID = @CustomerID");
//Step 10: Add input parameters to delete Customers
db.AddInParameter(deleteCommand, "CustomerID", DbType.Int32, "CustomerID", DataRowVersion.Current);
//Deleting Customer
customerTable.Rows[4].Delete();
//-----------------------------------------------------
//-----------------------------------------------------
//Step 11: Update DataSet
int rowsAffected = db.UpdateDataSet(customerDataSet, "Customers",
insertCommand, updateCommand, deleteCommand,
UpdateBehavior.Standard);
//-----------------------------------------------------
Although this code snippet demonstrates this functionality in a single method, the retrieval and modification will be two separate tasks. We have to provide a DbCommand object for Insert, Update, and Delete to the UpdateDataSet method as these commands are required to perform the appropriate operations. The Database class is abstracting us from writing the boilerplate code of creating and executing the DataAdapter method to update the data.