Saving Changes to a Data Set Back to a Database

If you use a DataAdapter to fill your DataSet with elements from a data store, you will also use a DataAdapter object to update your data stores with any changes made within your DataSet. The Update method of the DataAdapter makes this a simple process, just as easy as it was to use the Fill command. Here is an example of the Update method being called:

OleDbDataAdapter1.Update(DataSet1)

This one simple little method performs a whole slew of functions on your behalf to efficiently merge the changes in your DataSet with the original data source. The Update method first examines your DataSet to see which items have been changed since the DataSet was last filled. As you already learned, the DataSet maintains three copies of the data contained within it. By comparing the current values in the DataSet to the original values that were in the DataSet after the last Fill was done, the Update method can determine which records need updating. By fully concentrating on communicating back to the data store only those records that have changed, the Update method prevents a great deal of unnecessary data from being sent across the network.

In order to communicate these changes to the data store, the Update command translates these changed values into SQL commands, such as the INPUT, DELETE, and UPDATE commands. The DataAdapter by itself is not able to create these SQL statements, so it must rely on an object called the CommandBuilder to do this job. In a procedure that uses the DataAdapter's Update method, you need to declare an instance of a CommandBuilder object at the top of your procedure, preferably right after you declare the DataAdapter object. This is how you would declare both of these objects:

Dim daAdapter As OleDb.OleDbDataAdapter

Dim SQLBuilder As OleDb.OleDbCommandBuilder = New _

OleDb.OleDbCommandBuilder(daAdapter)

Notice that the CommandBuilder's declaration points to the DataAdapter object it will be working with. If you try to use the DataAdapter's Update method without first assigning that DataAdapter a CommandBuilder, your Update method will fail to affect the data source.

0 0

Post a comment