Questions That Should Be Asked More Frequently

What Is a DataAdapter Object?

The DataAdapter class acts as a bridge between the connected and disconnected halves of the ADO.NET object model. You can use a DataAdapter to pull data from your database into your DataSet. The DataAdapter can also take the cached updates stored in your DataSet and submit them to your database. Chapter 10 will cover updating your database with DataAdapter objects. In this chapter, we will focus on using DataAdapter objects to fetch data from your database.

How the DataAdapter Differs from Other Query Objects

When I describe the DataAdapter object to database programmers, most nod their heads and say that it sounds similar to the ADO Command object, the RDO rdoQuery object, and the DAO QueryDef object—all of which let you submit queries to your database and store the results in a separate object.

But there are some major differences between the DataAdapter and its predecessors, as I'll detail in the following sections.

The DataAdapter Is Designed to Work with Disconnected Data

ADO, RDO, and DAO all support disconnected data. Each object model can store the results of a query in a disconnected structure. For example, you can use an ADO Command object to fetch data into a Recordset that's disconnected from the Connection object. However, none of these object models provided disconnected functionality in their initial release. As a result, their query-based objects were never truly designed for disconnected data.

The DataAdapter is designed to work with disconnected data. Perhaps the best example of this design is the Fill method. You don't even need a live connection to your database to call the Fill method. If you call the Fill method on a DataAdapter whose connection to your database is not currently open, the DataAdapter opens that connection, queries the database, fetches and stores the results of the query into your DataSet, and then closes the connection to your database.

There Is No Direct Connection Between the DataAdapter and the DataSet

You fill a DataTable in your DataSet by passing your DataSet as a parameter to the DataAdapter object's Fill method, as shown here:

Visual Basic .NET

OleDbDataAdapter.Fill(DataSet)

Visual C# .NET

OleDbDataAdapter.Fill(DataSet);

Once this call completes, there is no connection between the two objects. The DataSet does not maintain a reference, internally or externally, to the DataAdapter, and the DataAdapter does not maintain a reference to the DataSet. Also, the DataSet contains no information indicating where the data originated—no connection string, no table name, and no column names. Thus, you can pass DataSet objects from your middle-tier server to your client applications without divulging any information about the location or structure of your database.

The DataAdapter Contains the Updating Logic to Submit Changes Stored in Your DataSet Back to Your Database

The DataAdapter acts as a two-way street. You can use a DataAdapter to submit a query and store its results in a DataSet, and you can use it to submit pending changes back to your database. This is a major change from previous data access models.

For example, in ADO, you use a Command, explicitly or implicitly, to fetch the results of your query into your Recordset object. When you want to update your database, you call the Update method of the Recordset. The Command object is not involved in the update process.

With ADO.NET, you use the DataAdapter object's Update method to submit the changes stored in your DataSet to your database. When you call the Update method, you supply the DataSet as a parameter. The DataSet can cache changes, but it's the DataAdapter object that contains your updating logic.

You Control the Updating Logic in the DataAdapter

That statement bears repeating: you control the updating logic in the DataAdapter. As far as I'm concerned, this is the number-one reason to move from ADO, DAO, or RDO to ADO.NET. You can use your own custom INSERT, UPDATE, and DELETEqueries or submit updates using stored procedures. The first time I noticed this feature while examining the structure for the DataAdapter, three thoughts ran through my mind: "Wow!," "I can't wait to see developers' reactions to this feature!," and "Why didn't we think of this earlier?"

Because none of the previous data access models offer this level of control over updating logic, many developers have been unable to use many of the rapid application development (RAD) features offered by those object models. Many database administrators will permit users to modify data in the database only by calling a stored procedure. Users don't have permissions to modify data by running UPDATE, INSERT INTO, or DELETEqueries. But these are the queries that DAO, RDO, and ADO generate to translate changes made to Recordset objects and rdoResultset objects into changes in your database. This means that developers building a database application with ADO cannot take advantage of the Recordset object's ability to submit changes to the database.

I spoke to one developer during the Visual Studio .NET beta who was skeptical about moving to ADO.NET. As if attempting to dismiss the new object model, he asked, "Can I use stored procedures to update my database?" He looked shocked when I smiled and responded, "Yes." I could almost hear the wheels in his brain turning for a moment or two before he asked, "How?"

Part of the reason he was so perplexed is that database administrators create separate stored procedures for updating, inserting, and deleting rows. So, in order for a data access object model to support submitting updates using stored procedures in a RAD way, the data access model must let you specify separate stored procedures for updates, inserts, and deletes.

And that's exactly what the DataAdapter does. The DataAdapter has four properties that contain Command objects—one for the query to fetch data, one for submitting pending updates, one for submitting pending insertions, and one for submitting pending deletions. You can specify your own action queries or stored procedures for each of these Command objects, as well as parameters that can move data from your DataSet to your stored procedure and back.

Developers can be a difficult bunch. (As a developer, I'm allowed to make that observation.) We like control and performance, but we also like ease of use. The DataAdapter offers all of the above. You can provide your own updating logic, or you can request that ADO.NET generate action queries similar to the ones that ADO and DAO automatically generate behind the scenes. You can even use some features in Visual Studio .NET to generate the updating logic at design time, an option that combines ease of use with control and performance.

We'll look at sample updating code and discuss the actual mechanics of updating your database using the DataAdapter in Chapter 10. In this chapter, we'll focus on the structure of the DataAdapter and how to use it to fetch the results of your queries.

Anatomy of the DataAdapter

Now that you understand a little more about what the DataAdapter does, let's look at the structure of the object to understand how it works.

The DataAdapter is designed to help you store the results of your query in DataSet objects and DataTable objects. As you learned in Chapter 4, the Command object lets you examine the results of your query through a DataReader object. The DataAdapter object consists of a series of Command objects and a collection of mapping properties that determines how the DataAdapter will communicate with your DataSet. Figure 5-1 shows the structure of the DataAdapter.

Figure 5-1.

The structure of the DataAdapter

Child Commands

When you use a DataAdapter to store the results of a query in a DataSet, the DataAdapter uses a Command to communicate with your database. Internally, the DataAdapter uses a DataReader to fetch the results and then copies that information into new rows in your DataSet. This process is roughly similar to the snippet of code shown at the start of this chapter.

The Command object that the DataAdapter uses to fetch data from your database is stored in the DataAdapter object's SelectCommand property.

The DataAdapter object also has other properties that contain Command objects—InsertCommand, UpdateCommand, and DeleteCommand. The DataAdapter uses these Command objects to submit the changes stored within your DataSet to your database. We'll look closely at how the DataAdapter uses these Command objects to submit updates to your database in Chapter 10.

TableMappings Collection

By default, the DataAdapter assumes that the columns in the DataReader match up with columns in your DataSet. However, you might encounter situations in which you want the schema of your DataSet to differ from the schema in your database. You might want to use a different name for a particular column in your DataSet. Traditionally, developers have renamed columns in this fashion within the query by using an alias. For example, if your Employees table had columns named EmpID, LName, and FName, you could use aliases within the query to change the column names to EmployeeID, LastName, and FirstName within the results, as shown in the following query:

SELECT EmpID AS EmployeeID, LName AS LastName, FName AS FirstName        FROM Employees

The DataAdapter offers a mechanism for mapping the results of your query to the structure of your DataSet: the TableMappings collection.

The query above describes a table with column names such as EmpID, LName, and FName. Let's take this example a step further and say that the database's table name is something obscure, like Table123. We want to map that data to a table in our DataSet named Employees that contains friendlier column names such as EmployeeID, LastName, and FirstName. The DataAdapter object's TableMappings collection allows you to create such a mapping layer between your database and the DataSet.

The TableMappings property returns a DataTableMappingsCollection object that contains a collection of DataTableMapping objects. Each object lets you create a mapping between a table (or view or stored procedure) in your database and the corresponding table name in your DataSet. The DataTableMapping object also has a ColumnMappings property that returns a DataColumnMappingsCollection object, which consists of a collection of DataColumnMapping objects. Each DataColumnMapping object maps a column in your database to a column in your DataSet.

	The DataColumnMappingCollection class has the longest name I've encountered so far. Thanks to the wonders of IntelliSense and statement completion, I don't have to type the entire class name when I'm working in Visual Studio .NET.

Figure 5-2 shows how the DataAdapter object's TableMappings collection maps the employee data structure from our database table to the corresponding structure in our DataSet.

Figure 5-2.

The DataAdapter object's TableMappings collection

In the figure, we're mapping the database's Table123 table to the DataSet object's Employees table, but the mapping information implies that it's mapping Table to Employees. This is because the DataAdapter really has no idea which table it's communicating with in the database. The DataAdapter can retrieve column names from the result of the query using the DataReader but has no way of determining the table name. As a result, the DataAdapter assumes that the table name is Table, and the entry in the TableMappings collection maps Table to Employees.

The following code snippet shows how you can populate the DataAdapter object's TableMappings collection in our example.

Visual Basic .NET

Dim da As OleDbDataAdapter 'Initialize DataAdapter. Dim TblMap As DataTableMapping Dim ColMap As DataColumnMapping TblMap = da.TableMappings.Add("Table", "Employees") ColMap = TblMap.ColumnMappings.Add("EmpID", "EmployeeID") ColMap = TblMap.ColumnMappings.Add("LName", "LastName") ColMap = TblMap.ColumnMappings.Add("FName", "FirstName")

Visual C# .NET

OleDbDataAdapter da; //Initialize DataAdapter. DataTableMapping TblMap; DataColumnMapping ColMap; TblMap = da.TableMappings.Add("Table", "Employees"); ColMap = TblMap.ColumnMappings.Add("EmpID", "EmployeeID"); ColMap = TblMap.ColumnMappings.Add("LName", "LastName"); ColMap = TblMap.ColumnMappings.Add("FName", "FirstName");