DATA ADAPTERS

A data adapter transfers data between a connection and a DataSet. This object’s most important methods are Fill and Update, which move data from and to the database, respectively. A data adapter also provides properties and other methods that can be useful. The following table describes the object’s most useful properties.

You can create the command objects in a couple of ways. For example, if you use the Data Adapter Configuration Wizard (described shortly) to build the adapter at design time, the wizard automatically creates these objects. You can select the adapter and expand these objects in the Properties window to read their properties, including the CommandText property that defines the commands.

Another way to create these commands is to use a command builder object. If you attach a command builder to a data adapter, the adapter uses the command builder to generate the commands it needs automatically.

Example program GenerateCommands uses the following code to determine the commands used by a data adapter. The code creates a new OleDbCommandBuilder, passing its constructor the data adapter. It then uses the command builder’s GetDeleteCommand, GetInsertCommand, and GetUpdateCommand methods to learn about the automatically generated commands.

' Attach a command builder to the data adapter
' and display the generated commands.
Dim command_builder As New OleDbCommandBuilder(OleDbDataAdapter1)
        
Dim txt As String = ""
        
txt &= command_builder.GetDeleteCommand.CommandText & vbCrLf & vbCrLf
txt &= command_builder.GetInsertCommand.CommandText & vbCrLf & vbCrLf
txt &= command_builder.GetUpdateCommand.CommandText & vbCrLf & vbCrLf
        
txtCommands.Text = txt
txtCommands.Select(0, 0)

The following text shows the results of the previous Debug statements. The DELETE and UPDATE statements are wrapped across multiple lines. The command builder generated these commands based on the select statement SELECT * From Books that was used to load the DataSet.

DELETE FROM Books WHERE ((Title = ?) AND ((? = 1 AND URL IS NULL) OR (URL = ?))
AND ((? = 1 AND Year IS NULL) OR (Year = ?)) AND ((? = 1 AND ISBN IS NULL) OR
(ISBN = ?)) AND ((? = 1 AND Pages IS NULL) OR (Pages = ?)))
 
INSERT INTO Books (Title, URL, Year, ISBN, Pages) VALUES (?, ?, ?, ?, ?)
 
UPDATE Books SET Title = ?, URL = ?, Year = ?, ISBN = ?, Pages = ? WHERE
((Title = ?) AND ((? = 1 AND URL IS NULL) OR (URL = ?)) AND ((? = 1 AND Year IS
NULL) OR (Year = ?)) AND ((? = 1 AND ISBN IS NULL) OR (ISBN = ?)) AND ((? = 1
AND Pages IS NULL) OR (Pages = ?)))

A data adapter’s TableMappings property enables you to change how the adapter maps data in the database to the DataSet. For example, you could make it copy the Employees table in the database into a DataSet table named People. You don’t usually need to change the table and column names, however, and you can make these changes interactively at design time more easily than you can do this in code, so you will usually leave these values alone at run time.

To create a data adapter control at design time, open a form in the Windows Forms Designer, select the Toolbox’s Data tab, and double-click the appropriate data adapter control. (If the data adapter you want doesn’t appear in the Toolbox, right-click the Toolbox, select Choose Items, and pick the data adapter that you want to use.)

When you create a data adapter, the Data Adapter Configuration Wizard appears. The wizard’s first page lets you select or build a data connection much as the Data Source Configuration Wizard does in Figure 19-2. Select or create a connection as described in the section “Connecting to the Data Source” earlier in this chapter.

Click Next to display the page shown in Figure 19-10. Use the option buttons to select the method the adapter should use to work with the data source. This determines how the data adapter will fetch, update, delete, and insert data in the database.

FIGURE 19-10: Select the method the data adapter will use to manipulate database data.

Your options are:

• Use SQL Statements — Makes the adapter use simple SQL statements to manipulate the data
• Create New Stored Procedures — Makes the wizard generate stored procedures in the database to manipulate the data
• Use Existing Stored Procedures — Makes the wizard use procedures you have already created to manipulate the data

In Figure 19-10, only the first option is enabled because it is the only option available to the OleDbDataAdapter used in this example.

When you select the Use SQL Statements option and click Next, the form shown in Figure 19-11 appears. If you are experienced at writing SQL statements, enter the SELECT statement that you want the data adapter to use to select its data.

FIGURE 19-11: Enter a SQL SELECT statement or click the Query Builder button.

If you have less experience or are not familiar with the database’s structure, click the Query Builder button to use the Query Builder shown in Figure 19-12. The area in the upper left shows the tables currently selected for use by the SQL query. Check boxes indicate which fields in the tables are selected. To add new tables to the query, right-click in this area and select Add Table.

FIGURE 19-12: You can use the Query Builder to interactively define the data that a data adapter selects.

Figure 19-12 shows the Query Builder. The top part shows that the Books table is included in the query and that its Title, Year, and Pages fields are selected.

Below the table and field selection area is a grid that lists the selected fields. Columns let you specify modifiers for each field. A field’s Alias indicates the name the field is known by when it is returned by the query. In Figure 19-12, the Year field will be returned with the alias PubYear.

The Output check box determines whether the field is selected. This check box does the same thing as the one in the upper field selection area.

The Sort Type column lets you indicate that the results should be sorted in either ascending or descending order. Sort Order determines the order in which the fields are sorted. The query shown in Figure 19-12 sorts first by Year in descending order. If more than one book has the same Year, they are ordered by Title in ascending order.

The Filter column lets you add conditions to the fields. The values in Figure 19-12 make the query select records only where the Year is greater than 2005. Additional fields scrolled off to the right in Figure 19-12 let you add more filters combined with OR. For example, you could select books where the Year is greater than 2005 OR less than 1990.

If you place filters on more than one field, their conditions are combined with AND. For example, the values shown in Figure 19-12 select records where Year is greater than 2005 AND Pages is greater than 400.

Below the grid is a text box that shows the SQL code for the query. If you look at the query, you can see that it selects the fields checked in the field selection area at the top, uses an appropriate WHERE clause, and orders the results properly.

Click the Execute Query button to make the Query Builder run the query and display the results in the bottom grid. You can use this button to test the query to verify that it makes sense before you finish creating the data adapter.

Click OK to close the Query Builder and return to the Data Adapter Configuration Wizard.

When you click Next, the Data Adapter Configuration Wizard displays a summary page indicating what it did and did not do while creating the data adapter. Depending on the query you use to select data, the wizard may not generate all of the commands to select, update, insert, and delete data. For example, if the query joins more than one table, the wizard will be unable to figure out how to update the tables, so it won’t generate insert, update, or delete commands.

Click Finish to close the wizard and see the new data adapter and its new connection object. You can see the adapter’s DeleteCommand, InsertCommand, SelectCommand, and UpdateCommand objects in the Properties window. These objects’ CommandText values show the corresponding SQL statements used by the objects. The wizard also generates default table mappings to transform database values into DataSet values.