CODE Magazine - Article: The Baker’s Dozen: 13 Productivity Tips for ADO.NET 2.0

Reader rating:
Click here to read 5 comments about this article.
Article source: CoDe (2006 - Jan/Feb)

Article Pages: 1 2 3 4 - Next >

The Baker’s Dozen: 13 Productivity Tips for ADO.NET 2.0

This installment of “The Baker’s Dozen” presents a variety of tips and techniques to become productive with data handling techniques using ADO.NET 2.0 in Visual Studio 2005. ADO.NET 2.0 is faster than the first version of ADO.NET; in some instances, significantly faster. While many view ADO.NET 2.0 as more evolutionary than revolutionary, it provides many functions to give developers greater control over data access and data manipulation. It also leverages the new database capabilities in SQL Server 2005. In addition, ADO.NET 2.0 simplifies the creation of multiple-database solutions.

Beginning with the End in Mind

The ability to effectively work with data is essential in a line-of-business application. ADO.NET has a rich object model, but takes time and experience to master, especially when coming from other development environments.

ADO.NET 2.0 introduces a new provider factory class that provides a much easier way to support multiple databases by loading the desired data provider for a specific connection string.

While ADO.NET in Visual Studio .NET 2003 offered tremendous power, ADO.NET 2.0 in Visual Studio 2005 provides even more capabilities, along with major performance gains when inserting, filling, and merging large amounts of data. Here’s a quick rundown of what’s in store for this Baker’s Dozen installment.

New capabilities for the DataTable that previously were only available for the DataSet
Baker’s Dozen Spotlight: maximizing the use of typed DataSets
The new base class Provider model and how to write generic code to leverage it
Performance and optimization enhancements in ADO.NET 2.0
Baker’s Dozen Potpourri: miscellaneous data-munging
Data relations and look-ups using typed DataSets
Asynchronous command execution
New capabilities in the LOAD method to create a DataSet without a DataAdapter
More control over the rowstate
The new SqlBulkCopy class
Multiple active result sets (MARS)
Connection pooling
Connection statistics

In addition to the Spotlight and Potpourri sections, I’ve added a new section to this series-the Baker’s Dozen commentary-which appears at the end.

Tip 1: The DataTable Breaks Free

In Visual Studio .NET 2003, several methods were only available for the DataSet. One example is writing (and reading) XML data: a developer who wanted to write the contents of a data table to an XML file had to create a DataSet, add the data table to the DataSet, and use the DataSet’s WriteXml method.

Developers who wanted to utilize those methods for a single data table had to create a DataSet and add the data table just to gain access to these methods. ADO.NET 2.0 now provides several methods for the data table. Table 1 lists these methods.

In addition, ADO.NET 2.0 allows a developer to serialize a data table in remoting scenarios. The new RemotingFormat property (covered in detail in Tip 4) applies to the data table.

Finally, ADO.NET 2.0 allows developers to create a new data table from any data view. The ToTable method also contains an overload for the developer to create a subset list of columns in the new table. And if that isn’t enough, hold onto your hat-there’s more! ToTable contains an additional overload to filter distinct rows. (Previously, you had to write additional code to accomplish this.) Now a developer simply must specify an array of columns to filter on unique values.

I’m very pleased with the data table enhancements in ADO.NET 2.0. There are some enhancements that I hope Microsoft will consider for the next version. Even with the new independence of the data table, there are still capabilities that require a DataSet. For instance, you can only establish a data relation between two data tables in the same DataSet. Situations where master codes exist in multiple transaction tables requires some additional coding when relations need to be established.

Tip 2: Getting the Most Out of Typed Datasets

A typed DataSet is a subclass of the standard ADO.NET DataSet. It exposes the tables, rows, columns, etc., as strongly-typed properties. Visual Studio 2005 can check these strongly-typed names at compile time, as opposed to runtime. In this sense, strongly-typed DataSets are self-documenting. IntelliSense will display the names in the typed DataSet object hierarchy-exposing column names as properties in the IDE can be very helpful in a large database application. It isn’t necessary to perform the boxing/unboxing that is required when using untyped DataSets.

Typed DataSets simplify handling null values. A strongly-typed data row contains two methods to check if a column value is null, and to set a column value to null.

When used properly, typed DataSets are especially beneficial in a multi-developer environment as well as applications that make heavy use of result sets and reporting. Developers can define typed DataSets as separate project DLLs and then set references to them from other projects.

Listing 1 contains sample code to demonstrate the basic use of typed DataSets. In addition, developers can subclass a typed DataSet class to add validation code or other methods. Listing 2 includes a brief example that extends typed DataSets through an interface and a subclassed definition.

Recently I developed a reporting solution involving a stored procedure that returned ten tables. The scenario represented a one-many-many relationship with a large number of columns that I didn’t want to retype into the VS.NET typed DataSet designer. I wanted to take the structure of the stored procedure results, turn it into an XML schema, and use it to create a typed DataSet for the project (Listing 3).

A complaint about typed DataSets is the default naming conventions in the typed DataSet class for DataTable/DataRow objects and methods/events. The generated names may not be consistent with preferred naming conventions. Fortunately, developers can use Typed Dataset Annotations to solve some of the common naming issues. Annotations allow developers to modify the names of elements in the typed DataSet without modifying the actual schema.

Annotations also allow you to specify a nullValue annotation-this instructs the typed DataSet class how to react when a column is DbNull. You can optionally tell the typed DataSet to return an empty string or a default replacement value. Shawn Wildermuth demonstrates typed DataSet annotations in an excellent online article (see the Recommended Reading sidebar).

By: Kevin S Goff

Kevin S. Goff, a Microsoft MVP award recipient for 2007, is the founder and principal consultant of Common Ground Solutions, a consulting group that provides custom Web and desktop software solutions in .NET, VFP, SQL Server, and Crystal Reports. Kevin is the author of Pro VS 2005 Reporting using SQL Server and Crystal Reports, published by Apress. Kevin has been building software applications since 1988. He has received several awards from the U.S. Department of Agriculture for systems automation. He has also received special citations from Fortune 500 Companies for solutions that yielded six-figure returns on investment. He has worked in such industries as insurance, accounting, public health, real estate, publishing, advertising, manufacturing, finance, consumer packaged goods, and trade promotion. In addition, Kevin provides many forms of custom training. Contact Kevin at kgoff@commongroundsolutions.net

kgoff@commongroundsolutions.net

Fast Facts

ADO.NET 2.0 offers something for everyone: developers looking for performance enhancements and ways to leverage new database capabilities; enterprise architects looking for easier ways to support multiple back-ends; and system administrators looking for ways to manage the database environment.

Table 1: New methods for the DataTable.

Method

ReadXml

WriteXml

ReadXmlSchema

WriteXmlSchema

Merge

Load

GetDataReader

Listing 1: 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);

Listing 2: Using an interface and subclassing from a typed DataSet

public interface IDataSet
{
   void PopulateFromXml(string cXml);
}

public class XmlConverter
{
   public void ImportXml(DataSet DsData, string cXml)
   {
      StringReader sr = new StringReader(cXml);
      DsData.ReadXml(sr,XmlReadMode.InferSchema);
      DsData.AcceptChanges();
   }
}
// In the subclassed DataSet

public class DsOrder : DsTypedDsOrder, IDataSet
{
   BaseDataClass.XmlConverter Converter = new
      BaseDataClass.XmlConverter();

   public void PopulateFromXml(string cXml)
   {
      this.Converter.ImportXml(this,cXml);
   }
}

Listing 3: Test code for creating a schema from a stored procedure

// Test program: returns a stored proc result set
// into a temp DataSet, then writes out schema
SqlConnection oMyConn=new SqlConnection(cMyConnString);
SqlDataAdapter oDA=new SqlDataAdapter(”MyProc”, oMyConn);

// Temporary DataSet to hold our results
DataSet DsReturn = new DataSet();

// Because we haven’t (yet) introduced the typed DataSet
// into our application, we may provide the actual tablenames
// that we’ll want to use in our typed DataSet definition
// I use this with Crystal reports when providing a schema,
// so that I can specify the tablenames that I want

oDA.TableMappings.Add("Table","MyRealTypedTable1");
oDA.TableMappings.Add("Table1","MyRealTypedTable2");
oDA.TableMappintgs.Add("Table2","MyRealTypedTable3");

oDA.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter oParm = new SqlParameter("@nMyPKParm,nMyPKParm));
oDA.Fill( DsReturn);
DsReturn.WriteXmlSchema("c:\\MySchema.XSD"); // Just schema
DsReturn.WriteXml
("c:\\MySchema.XML",XmlWriteMode.WriteSchema); // All data

// Now you can "add existing item" and point to the XSD file,
// instead of typing all the columns in manually….and then
// you'll be on your way to creating a typed DataSet

Article Pages: 1 2 3 4 - Next Page: 'Tip 3: Supporting Multiple Databases with Provider Factories' >>

Page 1:	The Baker’s Dozen: 13 Productivity Tips for ADO.NET 2.0
Page 2:	Tip 3: Supporting Multiple Databases with Provider Factories

Page 3:	Tip 6: Data Relations and Look-Ups with Typed Datasets
Page 4:	Tip 12: Connection Pooling

How would you rate the quality of this article?

	1	2	3	4	5
Poor						Outstanding

Tell us why you rated the content this way. (optional)

Average rating:
3.3 out of 5

92 people have rated this article.