Database Access with Visual Basic .NET (3rd Edition)

Chapter 6. ADO.NET The DataAdapter

IN THIS CHAPTER

• Populating a DataSet from a Data Source

• Updating the Data Source

Even with everything we presented in Chapter 5 including the DataSet, DataTable, DataRow, DataColumn, DataRelation, and Constraint objects something is still missing. You might well be tempted to stand up and scream (with flashbacks to Jerry Maguire): "Show me the database!" That's right, we still haven't demonstrated how to load the DataSet with data from a database or other data source and subsequently update the data source with the changes made to the data in the DataSet. The final piece of the puzzle that allows you to do so is the DataAdapter object.

The DataAdapter is the intermediary, or bridge, between the DataSet and the actual data source. Thus it is also the bridge between the two ADO.NET worlds the connected world of the .NET Data Provider objects discussed in Chapter 4 (Connection, Command, and DataReader) and the disconnected world of the DataSet and its associated objects analyzed in Chapter 5.

Again, the DataAdapter is the final core object that is a required element of a .NET Data Provider. Each .NET Data Provider must supply its own implementation of the DataAdapter (SqlDataAdapter, OledbDataAdapter, OdbcDataAdapter, and so on). The reason that we delayed presenting it until now is that, without an understanding of the DataSet and its associated objects, you can't do anything useful with the DataAdapter. We now show how the DataAdapter brings together and enhances everything that we've presented so far.

Figure 6.1 illustrates the use of the DataAdapter. Its fundamental task is to manage the transfer of data from the data source to the DataSet and from the DataSet to the data source. This task is accomplished with a small set of essential properties and methods.

The DataAdapter features two main methods. The Fill method fills a DataTable with data retrieved from a data source, and the Update method updates a data source with changes made to the data in the DataSet tables.

The DataAdapter contains a set of four command properties (SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand) that are ADO.NET Command objects configured for each of the respective operations. The SelectCommand object is executed when the DataAdapter's Fill method is called. When the DataAdapter's Update method is called, the appropriate one of the other three command objects is executed for each modified DataRow. The database developer has complete control over these commands, which allows for customization of the commands used for the Insert, Update, and Delete operations.

Finally, a collection of table and column mappings, DataTableMappings, allows the definition of mappings between data source and DataTable table and column names.