Unified Data Access for .NET

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.