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