Microsoft & .NET.NETWhat .NET 2.0 Has in Store for ADO.NET

What .NET 2.0 Has in Store for ADO.NET

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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   >   <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();}

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 poolsSystem.Data.SqlClient.SqlConnection.ClearAllPools();// Clear a specific poolSqlConnection 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, MCSD, MCSE, MCDBA is a Senior Architect of .NET applications for large and mid-size organizations. Mark is a technology leader with Crowe Chizek in Indianapolis, Indiana. 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 second year in a row. You can reach Mark at mstrawmyer@crowechizek.com.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories