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

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • On-demand Event Event Date: October 29, 2014 It's well understood how critical version control is for code. However, its importance to DevOps isn't always recognized. The 2014 DevOps Survey of Practice shows that one of the key predictors of DevOps success is putting all production environment artifacts into version control. In this webcast, Gene Kim discusses these survey findings and shares woeful tales of artifact management gone wrong! Gene also shares examples of how high-performing DevOps …

Most Popular Programming Stories

More for Developers

RSS Feeds