November 26, 2014
Hot Topics:

What .NET 2.0 Has in Store for ADO.NET

  • May 11, 2005
  • By Mark Strawmyer
  • Send Email »
  • More Articles »

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




Page 1 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel