The Baker's Dozen: 13 Productivity Tips for ADO.NET 2.0 : Page 2

Tip 3: Supporting Multiple Databases with Provider Factories
In ADO.NET 1.0, supporting multiple databases meant coding against interfaces for the ADO.NET objects (IdbConnection, IdbDataAdapter, IdbCommand, etc.), as well as writing a case statement to determine which connection object to use. ADO.NET 2.0 introduces a new provider factory class, which provides a much easier way to support multiple databases by loading the desired data provider for a specific connection string.

For line-of-business, data-driven applications, the DataSet approach provides many benefits. Tasks such as two-way data-binding, RowFilter/Sorting, XML integration, setting up relationships between data objects, preserving rowstate information, merging changes, and complex reporting operations are much easier with DataSets.

A developer can code against the new classes DbProviderFactory and DbProviderFactories, and pass the desired namespace at runtime. Table 2 displays the methods that a new DbProviderFactory object exposes. Note that the names of the providers must appear in the machine configuration file. Listing 3 demonstrates the use of provider factories.

Table 2: New methods for DbProviderFactory.

Method

CreateConnectionStringBuilder

CreateConnection

CreateCommand

CreateParameter

CreateCommandBuilder

CreateDataAdapter

CreateDataSourceEnumerator

Tip 4: Performance Enhancements in ADO.NET 2.0
Microsoft has made two significant enhancements to ADO.NET that result in better performance. First, they rewrote the indexing engine for better performance. As a result, many operations will execute more quickly, especially as the size of a DataSet grows. Any increase in performance will vary based on many application environment factors, but many should see increases by a factor of two or greater. One test published on MSDN that inserted a million rows into a DataTable took 30 minutes in Visual Studio .NET 2003 and 45 seconds in Visual Studio 2005! Lesser amounts of data will result in lower orders of magnitude; but in general, insert, update, delete, fill, and merge operations will execute more quickly in Visual Studio 2005.

Second, many developers know that passing and returning DataSets across physical boundaries carries substantial overhead and incurs performance penalties. ADO.NET 2.0 supports true binary serialization in remoting environments by providing a new RemotingFormat property, which a developer can set to SerializationFormat.Binary.

Depending on the size of the DataSet, the binary serialization may yield a result as much as six times smaller than XML serialization, resulting in faster transfer speeds and smaller required bandwidth resources. Note that this enhancement only works in remoting environments because Web services (by definition) pass XML.

Tip 5: Baker's Dozen Potpourri: Data Munging
Roll up your sleeves! It's time for some elbow grease. Listing 4 demonstrates some miscellaneous tasks that developers new to ADO.NET often need to accomplish:

• Filtering rows that contain NULL values
• Filtering on columns in a parent/child relationship
• Defining primary keys and performing lookups
• Adding a new row
• Creating a filter and looping through the results
• Handling null data with a strongly-typed datarow

Tip 6: Data Relations and Look-Ups with Typed Datasets
The following code references a typed DataSet with two tables.

   // Sample code to demonstrate use of typed DataSets
   // Assumed a typed DataSet, DsOrder
   // It consists of DtOrderHeader and DtOrderDetail
   DsOrder oDsOrder = new DsOrder(); 
   oDsOrder = SomeProcessToRetrieveAnOrder();
   DsOrder.DtOrderHeaderDataTable oDtHeader = new 
   DsOrder.DtOrderHeaderDataTable();
   DsOrder.DtOrderDetailDataTable oDtDetail = new 
   DsOrder.DtOrderDetailDataTable();
   // Add an item 
   DsOrder.DtOrderDetailRow oDetailRow = 
   new DsOrder.DtOrderDetailRow();
   oDetailRow.ItemFK = 100;    // Column names show in Intellisense
   oDetailRow.QtyOrder = 5;
   oDetailRow.UnitPrice = 5.50;
   // Add<TableName>Row is automatically part of the typed DataSet
   oDsOrder.DtOrderDetail.AddDtOrderDetailRow(
      oDetailRow);

If a developer defines a relation between the two tables in the typed DataSet, the XML Schema Definition Tool will add methods to navigate through the data without the need to specify the name of the DataRelation.

In the specific example of a master table called DtOrderHeader and a child table called DtOrderDetail, the XML Schema Definition Tool automatically creates a GetDtOrderDetailRows method (similar to GetChildRows) to the strongly typed DataRow class for DtOrderHeader, and a GetDtOrderHeaderRow method (similar to GetParentRow) to the strongly typed DataRow class for DtOrderDetail. This demonstrates yet another advantage of strongly typed DataSets—the XML Schema Definition Tool has already done some of the work.

Strongly-typed DataSets are self-documenting. IntelliSense will display the names in the typed DataSet object hierarchy. They are especially beneficial in a multi-developer environment as well as applications that make heavy use of result sets and reporting.

Additionally, if a typed data table contains a primary key, the class exposes a Find method associated with the column name. A developer can perform a Find against a table with a primary key of OrderID by using the method FindByOrderID. If the primary key is a concatenation of two columns (such as CustomerID and OrderID), the developer would use FindByCustomerIDOrderID. (As mentioned in Tip #2, developers can use Typed Dataset Annotations for alternate naming conventions.

Tip 7: Asynchronous Command Execution
ADO.NET 2.0 introduces a new asynchronous API that allows a program to continue while specific database commands execute. Previously, a developer could simulate asynchronous processing using asynchronous delegates or the ThreadPool class. However, ADO.NET 2.0 provides true asynchronous functionality.

Three methods in ADO.NET 2.0 provide asynchronous capabilities: ExecuteNonQuery, ExecuteReader, and ExecuteXMLReader. ADO.NET provides Begin and End prefix/suffix references to these methods.

   IAsyncResult oAR = command.BeginExecuteReader();
   // other processing
   while(oAR.IsCompleted==false) {
      // do something while waiting
   }
   SqlDataReader r = command.EndExecuteReader(oAR);

The developer also needs to know when the operation is complete. ADO.NET 2.0 provides three models to detect when an asynchronous command has completed:

• Callback model: the developer specifies a function to be executed when the command has executed. The begin methods contain an overload for a delegate parameter.
• Synchronization objects (wait model): the IasyncResult object listed above contains a WaitHandle property
• Polling: the IasyncResult object also contains an IsCompleted property that subsequent code can evaluate for completion. You can write subsequent code and test for whether IsCompleted is true.

Author's Note: You must add async=true to the connection string for asynchronous execution to work. If you don't plan to use asynchronous commands, Microsoft recommends setting this to false.

---

Previous Page

Next Page