August 4, 2009

Generic database configuration

In the business world, we spend much of our time trying to write software that is as flexible as possible. This is where the concept of interfaces and loose coupling come in. For most of my career I've written applications where my data layer is some abstract layer that sits between my application and my database on the pretence that if for whatever reason someone decides they want the application to sit on top of a different database, the only thing that would need replacing is the data layer.

Recently though, this concept has been troubling me - I don't know why it only just occurred to me but I was thinking - if I did want to sit my application atop another database, I sure as hell wouldn't want to have to rewrite my data layer - what a pain in the ass that would be!

I guess the plethora of ORMs that are on the market in the last few years culminating with LINQ to SQL was what got me thinking. Sure ORMs are a fantastic tool for what they're worth but what if you have a simple model and don't want the overhead of an ORM?

So I got to wondering - what would it take to specify my connection string and data client type in my configuration file and build everything atop that? We're used to specifying a connection string in our configuration file but I thought I'd take it a step further and define my data client too. Then I would build a generic database helper that would sit on top of this and do it's thing. This way, if I wanted to switch out the database from SQL Server to say Oracle, or MySQL, I could just change my configuration file and carry on as normal - without having to modify my data layer in the slightest.

First up, what does it take to grab our connection string and provider from the configuration file? Not much at all.

Let's consider the following app.config:

<?xml version="1.0" encoding="utf-8" ?>
    <add name="TestDb" connectionString="Data Source=.\SQLExpress;Initial Catalog=MyTestDatabase;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>

To grab our connection settings we add a reference to System.Configuration and use the following code:

var ConStrSettings = ConfigurationManager.ConnectionStrings["MyConnectionString"];

If we need access to the connection string, or the provider name from here, we can access them very simply using:

var ConStr = ConStrSettings.ConnectionString;
var ProvName = ConStrSettings.ProviderName;

So there we have it, we can grab our ConnectionString and Provider right in from the app.config.

Next up, we need to instantiate a connection object that will connect to our database with nothing more than the information provided in the config file.

Thankfully there's a great little tool called the ProviderFactory which has a method to do the heavy lifting for us - CreateConnection().

First up we will check our connection string to see if it included a provider name - after all, the person writing the config file may not be as thorough as us.

public DbProviderFactory GetProviderFactory(string provider, string connectionString)
 var providerName = provider;

 //If the provider argument was empty, attempt to grab it from the connection string itself.
 if (string.IsNullOrEmpty(providerName))
  var csb = new DbConnectionStringBuilder() { ConnectionString = connectionString };
  if (csb.ContainsKey("provider"))
   providerName = csb["provider"].ToString();
 //If we still don't have a connection string, throw an exception.
  throw new InvalidReferenceException("Missing provider name"); 

 //If the provider doesn't exist, throw an exception.
 if (!DbProviderFactories.GetFactoryClasses().Rows.Cast().Any(r => r[2].Equals(providerName)))
  throw new MissingTyhpeReferenceException(
   String.Format("Specified provider {0} could not be found.", providerName));

 return DbProviderFactories.GetFactory(providerName);

So, now we've got our provider factory for the database of our choosing or we've thrown an exception because the provider was either missing or invalid.

Now it's simple to create our connection object using the GetProviderFactory method:

DbConnection con = GetProviderFactory(ConStrSettings).CreateConnection();

From here, we can very easily create our command objects and anything else we would normally do with our database connection.

DbCommand cmd = con.CreateCommand();

No comments:

Post a Comment