Generic Database Access with .NET 2.0
June 30, 2010 Michael Sansoterra
|
A difficult task for developers is to foresee how an application may evolve in the future. As we all know, sometimes the unrealistic becomes reality. Say you have an application that will start on SQL Server and possibly later be moved to DB2 for i or vice versa. How do you create a data-centric application in .NET that isn’t tied to a particular database server? The answer lies in writing “database-agnostic” code. Let’s back up for a moment to when .NET was in its infancy. When .NET was first released, each ADO.NET database provider had its own set of classes. If you wanted to program against an Oracle database, you’d have to use the Oracle specific classes. Likewise, if you wanted to program against DB2 or SQL Server, you’d need to use the classes designed specifically for accessing those databases. Of course you could access any number of databases using the .NET ODBC or OLE DB wrapper classes, but these providers have overhead issues of their own as they rely on the older Microsoft COM technology. Further, when a database vendor creates a .NET-managed provider to access its database engine, the vendor has to write a specific set of classes for implementing the following general functionality:
In Table 1, the connection, command, data reader, and data adapter class names are shown for four popular database providers:
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:
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:
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.
|
