By: Philip Miseldine

November 19th, 2003

Reader Rating: 8.5

Nearly all of today’s Web applications use some sort of database to store persistent data. .NET applications often use SQL Server, PHP applications mostly use MySQL, and so on. When deploying an application to clients, however, there are many occasions on which they may wish to use a different database than that which your application has implemented. They might use Oracle throughout their enterprise, for example, and simply will not use your system as it stands without support for it. It is also far better practice to give the end-user choice rather than tying your system to a single third party database.

Normally, this means a great deal of recoding to make your application talk to different DBMSs (Database Management Systems). The following article will show how, with just a little planning, you can make your applications support almost every professional DBMS made today, out of the box.

ADO.NET: Almost There

ADO.NET has certainly made matters easier for the developer. DataReader and DataSet offer types that can be manipulated and queried without our having to worry about the underlying access method. Even so, to fill either structure, traditionally we need to use different types to handle different databases. ADO.NET gives us access to SQL Server, OLE DB, and ODBC, with many other database providers available (Oracle [1] and MySQL [2], for instance).

 using System.Data.SqlClient; // SQL Server 7.0/2000
using System.Data.Odbc; // ODBC

SqlConnection sqlConn = new SqlConnection(connectionString);
OdbcConnection odbcConn = new OdbcConnection(connectionString);

Imagine we’d written an application that connected to SQL Server, and a new client wanted us to use their proprietary DBMS that connects through ODBC. To do this, we’d have to convert types throughout our application from those contained in System.Data.SqlClient to System.Data.Odbc, or place conditional statements throughout our code, leaving behind a lot of essentially repeated code. Neither option is very appetising for programmers.

What is interesting, however, is that each provider inherits the same set of interfaces that the framework provides for, and, as such, they can be handled in a uniform manner; all connection objects (like SqlConnection, and OdbcConnection) inherit from the IDbConnection interface, for example.

What we really want is a class that we can tell, “I want to use this type of database” only once, then ask it to “run this query and return a DataSet or DataReader with the results” without worrying about the underlying connection. To be more specific, we want a class that will create an instance of an object based on criteria we give it, with an unknown and unseen implementation behind it. This is known as a factory pattern.

The Factory Pattern

Erich Gamma defines the Factory pattern as a “method [that] lets a class defer instantiation to subclasses.” Let’s have a look at a real-world example to understand what a factory pattern can mimic, and where it can be used.

Imagine a biscuit factory. This factory can produce a wide variety of different biscuits, each with their own recipe; the way in which a chocolate chip cookie is made, for example, differs from the way a ginger snap is made. The factory management doesn’t need to know how to make the biscuits, they just need to be able to package them up and ship them to their clients’ shops. The workers (be they machines or people) do require the recipe of the biscuit. They follow the instructions set out in the recipe, and produce a biscuit for the manager. If the manager needs to make a different biscuit, he tells the workers to use a different recipe, and a different type of biscuit is produced.

So, to create different types of biscuit, all that needs to change is the recipe (and of course, making sure we have all the ingredients to hand!). In this way, the manager defers creation of the biscuits to their workers. Moving higher up the supply chain, we can consider the purchasing clients (the shops and businesses who purchase the biscuits), and those who consume the biscuits (the public). Again, they do not need to know the biscuit recipe (indeed it is advantageous that we keep the recipe secret so as to protect our business). They simply ask for the creation of the biscuits.

So now we have a structure for the factory. We want a biscuit form to which all other biscuits will have to conform. This allows our management to handle biscuits in the same manner (to the manager, a ginger snap and a cookie are essentially the same). Specialised biscuits, like our cookies, then inherit all the properties of our standard biscuit, but have different behaviour (the cookie might be more chewy than a ginger snap, or have a different taste). This can be represented in UML as the following:

The Factory class defers creation to the Biscuit class (the makeBiscuit method), but specifies which type of biscuit it wants (the biscuitType parameter). The biscuit class then creates the appropriate biscuit (createGingerSnap, or createCookie methods), and returns its creation.

As both GingerSnap and Cookie are of type Biscuit, the Factory does not need to worry what type of biscuit it has, as they all can be treated as biscuits, rather than ginger snaps or cookies.

From Biscuits to Databases

Enough about biscuits! Back to our original problem: how to unify data access. I hope the example we’ve talked about so far has given you some idea of how to do this. Replacing our general biscuit with a general database connection we wish to achieve, and our different types of biscuit (ginger snap and cookie) as different types of databases (SQL Server, ODBC), we can call on the factory to produce a database connection for us with the functionality we require, without having to know the underlying specifics we’re using (the recipe). All connection objects inherit from the IDbConnection interface already, and therefore different connections can share the same code; all we need to do is tell the factory which type of connection we wish to use.

We can tell our factory which type to use through a parameter passed to a makeConnection method, which will return an IDbConnection to represent our database connection. We can make this static, as there’s no need for the factory class itself to be created manually: we need only one factory, which never changes itself:

public class ConnectionFactory  
 {  
 public enum connectionTypes {SQLServer, ODBC};  
 
public static IDbConnection makeConnection  
(int connectionType, string connectionString)  
   {  
     switch (connectionType)  
     {  
       case ((int)connectionTypes.SQLServer):  
         return (IDbConnection)new SqlConnection(connectionString);        
       case ((int)connectionTypes.ODBC):  
         return (IDbConnection)new OdbcConnection(connectionString);  
         
     }  
     //no match  
     return null;  
   }  
 }

Notice the use of the enum or enumerator. An enumerator is a nice way of giving integers a friendly name. In the code above, connectionTypes.SQLServer equates to 0, connectionTypes.ODBC equates to 1 and so forth. This way, a user doesn’t need to remember a specific string or an anonymous integer, which helps with readability and validation.

The factory queries the value connectionType passed to it and creates the appropriate connection object. As each of these connection objects is of a type IDbConnection, we can cast the created object to this type so that we can handle all of the connection objects in the same way, no matter what their implementation... our ultimate goal!

Open For Business

Now, let’s see how we can use this in our application. Before, if we wished to connect to SQL Server, we’d have created a SqlConnection. Now, we can now use our factory:

private void Form1_Load(object sender, System.EventArgs e)  
{  
 string connectionString = "";  
 IDbConnection conn = ConnectionFactory.makeConnection((int)ConnectionFactory.connectionTypes.SQLServer, connectionString);  
   
 IDbCommand comm = conn.CreateCommand();  
 comm.CommandText = "select * from mytable";  
 conn.Open();  
IDataReader dr = comm.ExecuteReader();  
//do what we want to do with the datareader  
 
//finally close the connection.  
conn.Close();  
}

Notice the only line that’s now dependent on the database type we wish to connect to is as follows:

IDbConnection conn = ConnectionFactory.makeConnection((int)ConnectionFactory.connectionTypes.SQLServer, connectionString);

Here, our specification of database type is made through a flexible parameter.

All other calls to our database are now abstracted from the actual database type we’re connecting to. Our factory returns to us an object that will use the “recipe” for SQL Server, yet we can easily instruct it to return us an object based on the “recipe” for ODBC connections, and reuse the same code. A simple if statement can be used to select the which database type we wish to use:

string connectionString = "";  
IDbConnection conn;  
if (database == "SQL Server")  
{  
 conn = ConnectionFactory.makeConnection((int)ConnectionFactory.connectionTypes.SQLServer, connectionString);  
}  
else  
{  
 //if not SQL Server, then ODBC  
 conn = ConnectionFactory.makeConnection((int)ConnectionFactory.connectionTypes.ODBC, connectionString);  
}  
 
 IDbCommand comm = conn.CreateCommand();  
 comm.CommandText = "select * from mytable";  
 conn.Open();  
IDataReader dr = comm.ExecuteReader();  
conn.Close();

As you can see, once the conn object is created, we never have to think about what database we’re using, nor alter any code again: all code for database queries in our applications is now database independent, using the built-in interfaces provided by ADO.NET.

Notes

So why not write applications to communicate through ODBC? Most databases support it, after all. Take the SQL Server ADO.NET components. These allow applications to communicate with SQL Server through a TDS (Tabular Data Stream, which is the native SQL Server data format). This provides an estimated 30-40% speed increase over calls made through ODBC [Sack. J. (2003) SQL Server 2000 Fast Answers for DBAs and Developers. Curlingstone Publishing.]. Similar enhancements are available for many dedicated ADO.NET component sets. These sorts of performance gains are simply too big to ignore, when a unified data access framework, as outlined in this article is so easy to achieve.

There is plenty of room for improvement. For a more robust and advanced data layer, we should include factories for the IDbDataAdapter, IDataReader and IDbCommand. We could also use the Reflection classes to see exactly which types of database we can connect to. Indeed, you could write your own set of interfaces which expose only the functionality you need in your applications, and create them through a factory.

Summary

To me, the greatest benefit ASP.NET brings to a project is the power of excellent object orientation support. I hope this article has shown how easy it is to harness this power to make your applications extendible and reusable, with very little effort needed on your part.

[1] http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectingtooracledatasourceusingadonet.asp
[2] http://sourceforge.net/projects/mysqlnet/