Using ADO.NET DataAdapter Objects to Submit Updates

Using the Data Adapter Configuration Wizard to Generate Updating Logic

In Chapter 5, you saw that you could use the Data Adapter Configuration Wizard to create DataAdapter objects using the OLE DB and SQL Client .NET Data Providers. The wizard also generates updating logic and stores that logic in your code.

One purpose of the Data Adapter Configuration Wizard is to generate updating logic for you at design time to let you build efficient updating code quickly and easily. Obviously, that's an ambitious goal. Although the wizard is not foolproof (what wizard is?), it actually achieves this goal in the vast majority of situations.

Open a project in Visual Studio .NET that contains a project item that exposes a designer (such as a Windows form, a Web form, a Web service, or a component), and add an OleDbDataAdapter to your designer. Specify a connection to your favorite Northwind database, and then enter the following query in the SQL statement page of the wizard:

SELECT OrderID, ProductID, UnitPrice, Quantity     FROM [Order Details]     WHERE OrderID = ? ORDER BY ProductID

Click Next. On the View Wizard Results screen, you'll see the output shown in Figure 10-3.

Figure 10-3

The View Wizard Results screen of the Data Adapter Configuration Wizard.

Examining the Structure of the DataAdapter

As you can see in Figure 10-3, the wizard generated UPDATE, INSERT, and DELETE queries for the new DataAdapter. Click the wizard's Finish button. Select the new DataAdapter in the Component Tray, and then go to the Properties window and drill down into the DataAdapter object's DeleteCommand. Select the CommandText property, and then click the button to the right of the property's value. This will bring up the Query Builder and display the CommandText for the DataAdapter object's DeleteCommand, as shown in Figure 10-4.

Figure 10-4

The wizard-generated DeleteCommand.

As you can see, the query that the Data Adapter Configuration Wizard generated to submit pending deletions is identical to the one we created by hand earlier in the chapter. You can also drill down into the DataAdapter object's InsertCommand and UpdateCommand to view the rest of the updating logic that the wizard generated.

Options for Building Updating Logic

The SQL Statement screen of the wizard has an Advanced Options button that you can click to display a dialog box that offers a series of options, as shown in Figure 10-5. These options offer you a small level of control over the updating logic that the Data Adapter Configuration Wizard generates.

If you're using your DataAdapter only to fetch data from your database, you can save some time, both at design time and at run time, by deselecting the Generate Insert, Update And Delete Statements option.

By default, the Data Adapter Configuration Wizard will add all non-BLOB columns to the WHERE clause of the queries for submitting pending updates and deletes. If you deselect the Use Optimistic Concurrency option, the wizard will include only primary key columns in the WHERE clauses for these queries.

Figure 10-5

Advanced options offered by the Data Adapter Configuration Wizard.

Some databases, such as SQL Server, support batched queries that can return rows of data. If you're using the Data Adapter Configuration Wizard to build a DataAdapter that talks to such a database, the Refresh The DataSet option will be enabled and selected. When this option is selected, the wizard will generate queries to refetch the contents of your modified row immediately after submitting that change. This means that new server-generated values such as timestamp and auto-increment values will be available in your DataSet after you call DataAdapter.Update.

We'll discuss this feature in more depth in the next chapter, and we'll also look at how to implement similar functionality against databases that don't support batched queries that return results.

Using Stored Procedures to Submit Updates

The Data Adapter Configuration Wizard can also help you build DataAdapter objects that submit updates to your SQL Server database using stored procedures. On the Choose A Query Type screen of the wizard, you'll see a Use Existing Stored Procedures option, as shown in Figure 10-6. Select this option, and then click Next.

Figure 10-6

The Choose A Query Type screen of the Data Adapter Configuration Wizard.

The next screen allows you to select the stored procedures for each of the Command objects of your DataAdapter. The first step is to select the stored procedure for your DataAdapter object's SelectCommand. The drop-down list will contain the available stored procedures, as shown in Figure 10-7. When you select a stored procedure, the columns that the stored procedure returns will appear in the list to the right.

Figure 10-7

Selecting a stored procedure for the DataAdapter object's SelectCommand.

Once you've set the DataAdapter object's SelectCommand, you can specify the stored procedures for the updating Command objects. To set the SourceColumn property for the parameters of your updating stored procedures, use the drop-down lists on the right side of the wizard screen, as shown in Figure 10-8.

Figure 10-8

Setting the SourceColumn property for the parameters of the InsertCommand.

	The Data Adapter Configuration Wizard does not give you the option to set the SourceVersion property for the Parameter objects. Because the default value for this property is Current, you must change the value of this property using the Properties window for all parameters you want to bind to the original value of your modified columns.

If you have the Enterprise edition of Visual Studio .NET installed, you can also supply a SQL query and the Data Adapter Configuration Wizard will generate new SQL Server stored procedures for your DataAdapter object's SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand. Select the Create New Stored Procedures option on the Choose A Query Type screen of the Data Adapter Configuration Wizard, and the wizard will prompt you for the SQL query that returns data from your database, as shown in Figure 10-9.

Figure 10-9

Specifying a SQL query for your new stored procedures.

The next wizard screen lets you supply names for the stored procedures that the wizard generates. This screen also includes a Preview SQL Script button, which you can click to bring up a dialog box showing the SQL script that the wizard generated to create your stored procedures (as shown in Figure 10-10). If you're building your application against a sample database, you can use the dialog box to save the SQL script to a file so that you can run the script later against your production database.

Figure 10-10

Viewing the SQL script for creating your new stored procedures.

Once you've completed the wizard, you'll have new stored procedures in your database and your new DataAdapter will be set to use those stored procedures.

Benefits and Drawbacks of Using the Wizard

I mentioned that one of the Data Adapter Configuration Wizard's goals is to generate updating logic to let you build efficient updating code quickly and easily. The wizard offers more options than the CommandBuilder object. It also generates the tedious code that most developers would rather not write.

Although the wizard requests the same schema information from your database to generate updating logic that the CommandBuilder object does, it requests this information once at design time and then stores the newly generated logic in your code. Thus, your application avoids the run-time performance penalty that goes with using the CommandBuilder object.

But alas, the Data Adapter Configuration Wizard is not perfect. In the initial release of Visual Studio .NET, the wizard works only with the DataAdapter objects in the OLE DB and SQL Client .NET Data Providers. The wizard also offers limited concurrency options. You can modify the updating Command objects that the wizard generates, but you'll lose those changes if you reconfigure the DataAdapter. However, even though the wizard is not perfect, it is still a powerful and helpful tool.