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. A data adapter also provides properties and other methods that can be useful. The following table describes the object’s most useful properties.

Open table as spreadsheet

Property	Purpose
DeleteCommand	The command object that the adapter uses to delete rows.
InsertCommand	The command object that the adapter uses to insert rows.
SelectCommand	The command object that the adapter uses to select rows.
TableMappings	A collection of DataTableMapping objects that determine how tables in the database are mapped to tables in the DataSet. Each DataTableMapping object has a ColumnMappings collection that determines how the columns in the database table are mapped to columns in the DataSet table.
UpdateCommand	The command object that the adapter uses to update rows.

There are a couple of ways you can create the command objects. 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.

The following code shows how a program could associate an OleDbCommandBuilder object with an OleDbDataAdapter. When it calls the adapter’s Update method, the adapter uses the command builder if necessary to generate insert, update, and delete commands. The Debug.WriteLine statements display the text of the automatically generated commands.

  Private Sub Form1_Load(ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles MyBase.Load     'TODO: This line of code loads data into the 'BooksDataSet.     ' You can move, or remove it, as needed.     Me.BooksTableAdapter.Fill(Me.BooksDataSet.Books)     ' Attach a command builder to the data adapter     ' and display the geneated commands.     Dim command_builder As New OleDbCommandBuilder(OleDbDataAdapter1)     Debug.WriteLine(command_builder.GetDeleteCommand.CommandText)     Debug.WriteLine(command_builder.GetInsertCommand.CommandText)     Debug.WriteLine(command_builder.GetUpdateCommand.CommandText) End Sub  

The following text shows the results of the previous Debug statements. The UPDATE statement is wrapped across two lines, but is one line in the Output window. The command builder generated these commands based on the select statement SELECT Title, URL From Books that was used to load the DataSet.

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

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 runtime.

To create a data adapter control at design time, open a form in the Form 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 shown in Figure 11-22 appears. The first steps are similar to those described earlier for configuring a new data source. Select a connection or click the New Connection button and define one.

Figure 11-22: The Data Adapter Configuration Wizard helps you define a data adapter.

Click Next to display the page shown in Figure 11-23. 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. Select the Use SQL Statements option to make the adapter use simple SQL statements. Select the Create new stored procedures option to make the wizard generate stored procedures in the database. Select the Use existing stored procedures option to make the wizard use procedures you have already created. In Figure 11-23, only the first is enabled because it is the only option available to the OleDbDataAdapter that was used in this example.

Figure 11-23: Select the method the data adapter will use to manipulate database data.

When you select the Use SQL Statements option and click Next, the form shown in Figure 11-24 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 11-24: Enter an 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 11-25. The upper area 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 11-25 shows the Add Table dialog box above the Query Builder. The dialog box lists only one table, Books, because this database only contains one table.

Figure 11-25: You can use the Query Builder to interactively define the data that a data adapter selects.

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 11-25, 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 11-25 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 11-25 make the query select records only where the Year is greater than 1998. Additional fields scrolled off to the right in Figure 11-25 let you add more filters combined with OR. For example, you could select books where the Year is greater than 1998 OR less than 1995.

If you place filters on more than one field, their conditions are combined with AND. For example, the values shown in Figure 11-25 select records where Year is greater than 1998 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 some 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 similar to the one shown in Figure 11-26. The summary describes the actions that the wizard will and will not perform. Depending on the query you use to select data, the wizard may not generate all 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.

Figure 11-26: The Data Adapter Configuration Wizard displays a summary of the tasks it will perform.

When you click Finish, the wizard creates the new data adapter and a new connection object to go with it. It sets the adapter’s DeleteCommand, InsertCommand, SelectCommand, and UpdateCommand properties to new command objects with appropriate CommandText values and with the Connection property set to the database connection you selected. It also generates default table mappings to transform database values into DataSet values.