What .NET 2.0 Has in Store for ADO.NET

In one of my early .NET Nuts & Bolts columns, I wrote about database-independent data access. The article basically demonstrates the use of the factory design pattern to decide at runtime which specific instances of database-related objects to create. It was a necessary evil for those of us unfortunate enough to have to move between different databases such as SQL Server and Oracle and not wanting to produce a bunch of provider-specific code in case the database changed (I'm convinced there's a Dilbert in there somewhere). The factory determines the concrete connection, command, and other objects to create based on the database being accessed. Code needing to access the database uses interface objects, which get assigned provider-specific instances by the factory.

As the article demonstrated, you had to create and maintain this code in order to operate in a database-independent manner. ADO.NET 2.0 introduces some new objects that provide this behavior so the custom factory is no longer required:

  • DbProviderFactories—used to generate provider-specific instances of a DbProviderFactory class.
  • DbProviderFactory—used to generate provider-specific instances of the database access classes, such as the connection, command, and parameter objects. The DbProviderFactory has a series of CreateXXX methods that can be used to create the desired instance objects. An alternative is the DbConnection object, which now has a CreateCommand() method, the DbCommand object, which now has a CreateParameter() method, and so forth.

Provider Factory Sample Code

The following sample example demonstrates using the DbProviderFactories to get a provider-specific instance of DbProviderFactory, and then using it to create the necessary objects to perform database operations. I use the factory to create the connection object, and then create the command from the connection. It's a rudimentary example, but it will effectively demonstrate the topic.

The following settings are contained in the application's configuration file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration
   xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

   <appSettings>
      <add key="DbProvider" value="System.Data.SqlClient"/>
      <add key="ConnectionString"
           value="Server=localhost;
                  Database=Northwind;
                  Integrated Security=true;"/>
   </appSettings>
</configuration>

Here is the sample code:

IDataReader dbreader = null;
DbProviderFactory dbfactory = 
   DbProviderFactories.GetFactory(
   ConfigurationSettings.AppSettings.Get("DbProvider"));
IDbConnection dbconn = dbfactory.CreateConnection();
dbconn.ConnectionString = 
   ConfigurationSettings.AppSettings.Get("ConnectionString");
try
{
   dbconn.Open();
   IDbCommand dbcomm  = dbconn.CreateCommand();
   dbcomm.Connection  = dbconn;
   dbcomm.CommandText = "SELECT * FROM Orders";
   dbreader = dbcomm.ExecuteReader(CommandBehavior.CloseConnection);
   while (dbreader.Read())
   {
      Console.WriteLine("Order ID:{0}",
                        dbreader["OrderId"].ToString());
   }
}
finally
{
   if( dbreader != null ) dbreader.Dispose();
}

The use of ConfigurationSettings.AppSettings is going to be replaced by a ConfigurationManager object in .NET 2.0, but the build I use was lacking the ConfigurationManager. So, I used the existing object for reading from the configuration to demonstrate how everything can be dictated at runtime.

Switch Between DataReader and DataSet

In the first versions of ADO.NET, the debate regarding whether to use a DataReader or a DataSet raged. Each has its place based on the usage. However, it wasn't easy—or in some cases even possible—to start with one and switch to the other in your code. For example, if you had a DataReader, you had to manually build a DataSet from its contents. It wasn't possible to get a DataReader from a DataSet. ADO.NET 2.0 includes some functionality to simplify this. The DataSet now has a Load() method that accepts a DataReader as a parameter and will load the DataSet from the reader. The DataSet also has a GetDataReader method that returns a DataReader.

Switch Between DataReader and DataSet Sample Code

This section reuses the code from the previous provider factory example to demonstrate the switch. It's not a practical example, but it will suffice. It reads to a DataReader, moves to a DataSet, then moves back to another DataReader:

IDataReader dbreader  = null;
IDataReader dbreader2 = null;
DbProviderFactory dbfactory = 
   DbProviderFactories.GetFactory(
   ConfigurationSettings.AppSettings.Get("DbProvider"));
IDbConnection dbconn = dbfactory.CreateConnection();
dbconn.ConnectionString = 
   ConfigurationSettings.AppSettings.Get("ConnectionString");
try
{
   dbconn.Open();
   IDbCommand dbcomm  = dbconn.CreateCommand();
   dbcomm.Connection  = dbconn;
   dbcomm.CommandText = "SELECT * FROM Orders";
   dbreader = dbcomm.ExecuteReader(CommandBehavior.CloseConnection);
   DataSet ds = new DataSet();
   ds.Load(dbreader, LoadOption.PreserveChanges, new string[]
          { "Orders" });
   dbreader2 = ds.CreateDataReader();
   while (dbreader2.Read())
   {
      Console.WriteLine("Order ID:{0}", dbreader2["OrderId"].ToString());
   }
}
finally
{
   if( dbreader != null ) dbreader.Dispose();
   if( dbreader2 != null ) dbreader2.Dispose();
}

What .NET 2.0 Has in Store for ADO.NET

Connection Pool Control

Not all of the providers support connection pooling. The SQL Server and Oracle providers do. There was previously no way to programmatically clear the pool of open connections. The upcoming release of ADO.NET will include the ability to clear the connection pool in the SqlClient and OracleClient providers through static methods on the corresponding connection objects.

Connection Pool Control Sample Code

The following sample code demonstrates the use of the static methods to clear the connection pools:

// Clear all the pools
System.Data.SqlClient.SqlConnection.ClearAllPools();

// Clear a specific pool
SqlConnection dbconn = new SqlConnection();
dbconn.ConnectionString = 
   ConfigurationSettings.AppSettings.Get("ConnectionString");
SqlConnection.ClearPool(dbconn);

Other Considerations

You may want to examine many more new additions in ADO.NET 2.0 than the ones covered here. You'll find a number of additional articles that may serve your research. The following links on related topics will get you started:

Future Columns

The next column has yet to be determined. If you have something in particular that you would like to see explained, please e-mail me at mstrawmyer@crowechizek.com.



About the Author

Mark Strawmyer

Mark Strawmyer is a Senior Architect of .NET applications for large and mid-size organizations. He specializes in architecture, design and development of Microsoft-based solutions. Mark was honored to be named a Microsoft MVP for application development with C# for the fifth year in a row. You can reach Mark at mark.strawmyer@crowehorwath.com.

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: September 10, 2014 Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild." This loop of continuous delivery and continuous feedback is how the best mobile …

  • The explosion in mobile devices and applications has generated a great deal of interest in APIs. Today's businesses are under increased pressure to make it easy to build apps, supply tools to help developers work more quickly, and deploy operational analytics so they can track users, developers, application performance, and more. Apigee Edge provides comprehensive API delivery tools and both operational and business-level analytics in an integrated platform. It is available as on-premise software or through …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds