Generic Database Access with .NET 2.0

DB2 for i	SQL Server	Oracle	ODBC
iDB2Connection	SqlConnection	OracleConnection	OdbcConnection
iDB2Command	SqlCommand	OracleCommand	OdbcCommand
iDB2DataReader	SqlDataReader	OracleDataReader	OdbcDataReader
iDB2DataAdapter	SqlDataAdapter	OracleDataAdapter	OdbcDataAdapter

Table 1–Selected class names used for four popular database providers.

Starting in .NET 2.0, Microsoft added a set of generic database provider classes intended to allow developers to access any database. Click here for a quick introduction to this “factory design pattern,” which essentially allows access to multiple database providers using a single API.

If you’re familiar with Java Database Connectivity (JDBC), this concept is very similar to the JDBC API where a database specific provider is loaded and thereafter all database access is subsequently accomplished through a single programming interface (provided by the java.sql package).

Prior to .NET 2.0, an application that needed to interact with multiple databases would have to conditionally instantiate objects from the various provider classes (or use a generic provider such as ODBC). However, a .NET 2.0 compatible provider is required to implement certain framework interfaces such as IDbConnection, IDbCommand, IDataReader, IDataAdapter, etc. So when a database vendor creates a .NET 2.0 data provider that implements these interfaces, the provider functionality can also be accessed through a single set of generic classes in the System.Data.Common namespace. Here are four of the generic database classes:

• DbConnection
• DbCommand
• DbDataReader
• DbDataAdapter

Let’s look at a simple example. Whereas an application designed for use with the DB2 for i provider might be coded using IBM‘s specific classes like this (examples are C#):

public DataTable GetSalesOrderDetail(string ConnectionString, int OrderId)
{
//
// Open a connection using the DB2 for i managed provider
//
iDB2Connection conn = new iDB2Connection(ConnectionString);
 conn.Open();
 //
 // SQL: Retrieve sales order detail for requested order id
 //
 iDB2Command cmd = conn.CreateCommand();
 cmd.CommandText = "Select * From SalesOrderDetail Where 
 SalesOrderId="+OrderId;
 //
 // Get the results in a DataReader and load into a DataTable
 //
 iDB2DataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 DataTable dt = new DataTable("SalesOrderDetail");
 dt.Load(dr);
 return dt;
 }

Using .NET 2.0, it can be coded like this to accept multiple database providers:

 public DataTable GetSalesOrderDetail(string ProviderName, 
 string ConnectionString, int OrderId)
 {
 //
 // Load provider by name
 //
 DbProviderFactory db = DbProviderFactories.GetFactory(ProviderName);
 //
 // Open a connection using the provider's connection string
 //
 DbConnection conn = db.CreateConnection();
 conn.ConnectionString = ConnectionString;
 conn.Open();
 //
 // SQL: Retrieve sales order detail for requested order id
 //
 DbCommand cmd = conn.CreateCommand();
 cmd.CommandText = "Select * From SalesOrderDetail Where 
 SalesOrderId="+OrderId;
 //
 // Get the results in a DataReader and load into a DataTable
 //
 DbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 DataTable dt = new DataTable("SalesOrderDetail");
 dt.Load(dr);
 return dt;
 }

When comparing the code, both examples are almost identical except the first uses provider specific classes and the latter uses the generic classes. The DbProviderFactories class is the key to generic database programming, as it contains a collection of .NET 2.0 managed providers installed on the machine. You retrieve your specific installed provider by passing it with what is referred to as the “invariant provider name.” (See samples below.) For those familiar with Java, this is similar to loading a JDBC driver using the Class.forName method.

Although this example is simplistic, it’s easy to understand how the same code can service many different databases by soft-coding the provider information and connection string. This information can easily be soft-coded by storing it in an app.config or web.config file.

If, for example, you’re using the IBM DB2 for i provider with the generic classes, the IBM specific classes are still used under the covers but access is done via a generic, single API. In fact, assuming other databases in your company had identical schema and table info, the sample method GetSalesOrderDetail could run equally well against, Oracle, ODBC-based, SQL Server, or DB2 for i database servers as shown here:

DataTable dtSalesOrderDetail;

// NOTE: 15234 is the order number to retrieve

// Oracle Provider
dtSalesOrderDetail=GetSalesOrderDetail(
"System.Data.OracleClient", 
"Data Source=localhost;User ID=MyUser;Password=MyPassword;
Unicode=True",15234);

// ODBC Driver (IBM DB2 for i connection string)
dtSalesOrderDetail=GetSalesOrderDetail(
"System.Data.Odbc",
"DSN=AS400;System=db2.mycompany.com;uid=MyUser;pwd=MyPassword;
nam=0;DBQ=ADVWORKS,*USRLIBL;" ,15234);

// SQL Server Provider
dtSalesOrderDetail=GetSalesOrderDetail(
"System.Data.SqlClient",
"Data Source=localhost;Initial Catalog=AdventureWorks;Integrated 
Security=SSPI;" ,15234);

// IBM DB2 UDB for iSeries .NET Managed Provider
dtSalesOrderDetail=GetSalesOrderDetail(
"IBM.Data.DB2.iSeries",
"DataSource=iSeries.mycompany.local;UserID=MyUser;Password=
MyPassword;Naming=System;LibraryList=,*USRLIBL;DefaultCollection=
ADVWORKS;" ,15234);

While it’s great to be able to access multiple databases with one set of code, a few obvious hindrances to this programming model are:

• Provider specific functionality may not be accessible from the generic API
• Not all database providers will implement all the functionality offered by the generic API
• SQL statements may not use database specific SQL features (unless you put database specific forks in your code)

In the cases where database specific functionality is required that is not offered by the generic API, the generic data provider name can be cast back to its underlying data type. For example, to retrieve the DB2 for i provider specific ClientProgramID property from the iDB2Connection object, you could code to access the provider specific property as follows:

//
// Open a connection using the provider's connection string
//
DbConnection conn = db.CreateConnection();
conn.ConnectionString = ConnectionString;
conn.Open();
//
// Do something DB2 specific
//
if (conn is iDB2Connection)
{
Console.WriteLine(
(conn as iDB2Connection).ClientProgramID);
}

Finally, if you’re using the DB2 for i provider in your application, make sure you have System i Access V6R1 or higher installed. Prior versions of the DB2 for i .NET managed provider are not .NET 2.0 compatible.

All in all, making your application “database-agnostic” will allow the application greater ability to service multiple database servers, or make a database specific application easier to migrate to another database platform if and when the time is right.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

---

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot