The ODBC .NET Data Provider

ADO.NET DataAdapter Builder

The CommandBuilder object and the Visual Studio .NET Data Adapter Configuration Wizard each do a wonderful job of generating DataAdapter updating logic, but they each have limitations. The CommandBuilder object is a very helpful run-time tool, but it doesn't generate code. The Data Adapter Configuration Wizard generates code, but only for the .NET data providers that are included in the .NET Framework. Neither component generates updating logic if your query doesn't include primary key columns.

I created the ADO.NET DataAdapter Builder to address these scenarios. You want to generate updating logic but your OLE DB provider or ODBC driver doesn't return table names, column names, or key column information? No problem. You want to generate updating logic for a DataAdapter that's not part of the .NET Framework? No problem.

The user interface for the DataAdapter Builder is similar to that of the Ad Hoc Query Tool. The Connect dialog box works in the same way. You enter the connection string and select the .NET data provider you want to use. The DataAdapter Builder will display a form, shown in Figure B-6, where you can enter and execute queries. Like the Ad Hoc Query Tool, the DataAdapter Builder executes queries and displays their results, along with the schema information for the result set.

Figure B-6

Generating updating logic using the ADO.NET DataAdapter Builder

The major difference between the tools is that the DataAdapter Builder lets you specify the updating logic for your DataAdapter, and it displays the ADO.NET code used to generate that DataAdapter.

Specifying Your Updating Logic

The DataAdapter Builder generates updating logic for the DataAdapter by creating parameterized INSERT, UPDATE, and DELETE queries based on the results of the query you've supplied.

You can control the logic that the tool generates for the UPDATE and DELETE queries by selecting the desired Concurrency Check option, as shown in Figure B-6. By default, the DataAdapter Builder will use the original values of all columns in the WHERE clause for these queries. You can specify that you want to use just the primary key columns or the primary key and timestamp columns instead.

If you select the Custom option and execute your query, you'll see the dialog box shown in Figure B-7. This dialog box displays column and table information from the query schema as well as delimiter information for your connection.

Figure B-7

Specifying custom updating logic

You control which columns are used in the SET and WHERE clauses of your DataAdapter object's updating logic by selecting or deselecting the various columns in the dialog box's grid. Be forewarned that this feature can generate UPDATE and DELETE queries that update multiple rows in your database. You can also specify the name of the base table to update in this dialog box.

I designed this custom concurrency check feature with three scenarios in mind. First, you can use it if you're working with OLE DB providers and ODBC drivers that do not report the key column information. The Microsoft Visual FoxPro OLE DB Provider and Oracle's OLE DB Provider fall into this group. Tools such as the CommandBuilder and the Visual Studio .NET Data Adapter Configuration Wizard require key column information to guarantee that the UPDATE and DELETE queries that they generate will update a single row at most. Without that key column information, those tools are unable to generate updating logic. If you're working with an OLE DB provider that does not return this information, you can still use the DataAdapter Builder's custom concurrency check feature to generate updating logic for your DataAdapter.

The custom concurrency check feature is also handy if you want to use more customized concurrency checks than All Columns, Primary Key Only, or Primary Key And Timestamp. For example, if you're querying the Customers table in the sample Northwind database, you might want to use just a subset of those columns in the WHERE clause for your INSERT and UPDATE queries. The custom concurrency check feature lets you do this.

Finally, the custom concurrency check feature can be handy if your query returns data from multiple tables. For example, you might want to use the following query to retrieve information from the Order Details table but also include the product name from the Products table:

SELECT O.OrderID, O.ProductID, P.ProductName, O.Quantity, O.UnitPrice     FROM [Order Details] O, Products P     WHERE P.ProductID = O.ProductID AND O.OrderID = 10643

In Chapter 10, you learned that the DataAdapter is designed to submit updates against a single table. You can use the DataAdapter Builder's custom concurrency checks to submit changes to just the Order Details table. In the Custom Updating Logic dialog box, specify that you want to submit changes to the Order Details table and that you want to exclude the ProductName column from both the SET and WHERE clauses for your updating queries.



Microsoft ADO. NET Core Reference
Microsoft ADO.NET (Core Reference) (PRO-Developer)
ISBN: 0735614237
EAN: 2147483647
Year: 2002
Pages: 104
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net