Unified Data Access for .NET

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 and MySQL, 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.