data access

there's lots of ways to do data access, but here's something I've been doing that seems kinda cool.

First, create a namespace (DataObjects, in my case) .

Next, a BaseDataObject this is the parent that all the data accessors will inherit from.

In the BaseDataObject go the common things. For example, I have a public property called "DatabaseName". Using this, the code will query for the correct connection string parameters when needed.

The BaseDataObject also owns the actual connection. This is interesting. The Connection goes into a thread static collection of connections (which is hard to say quickly :D ). When the connection is needed, the collection is searched. If the connection isn't there, it gets created and inserted for the life of the thread. Took a while to nail this, but this means that the connections are correctly cached by database name for the life of the thread and no longer.

In order for this to work, the connection is actually of type IDbConnection. Anything else will bind the data object to one database type.

All of the methods of the BaseDataObject follow that pattern -- each uses IDbCommand, IDataReader and the like.

The simplest subclasses only override the connection itself. For example, I've got a MySqlDataObject that allows connection to a MySql database. It overrides the "connectToDatabase" method to secretly append a mySql designation to the DatabaseName. This means the connection string resolution returns a MySql compatible connection string, not a SqlServer one. In addition, when it creates the database connection, it creates a "MySqlConnection", which gets implicitly cast upward to a IDBConnection on the return.

The more complex subclasses (the SqlServer one, for example) have some additional functionality. For example, I've added an ExecuteNonQueryWithParameters method to the BaseDataObject, which accepts a Dictionary of key-value parameters and then uses this to populate the Sql Statement. The BaseDataObject resolves all this by looping through the dictionary and ingloriously building the sql string. The SqlDataObject overrides this to use the Parameters.AddWithValue method on the SqlCommand, since this is available.

In addition, I've occasionally further subclassed the child data objects to allow versions of the objects that toss exceptions back to the caller and versions that don't, but log the exceptions and return a null result.

To wrap this all up, I added an object factory to the DataObjects namespace. Pass in the DatabaseName, and you get an appropriate subclass of BaseDataObject, based on whatever business rules are applied to decide this. The returning object is basically a live connection ready to be used.

So far, this has worked pretty well. My big frustration is the lookup on the database name -> connection string. I have been using a Microsoft "Settings" object for that, but, frankly, it sucks and doesn't provide any real benefit. The good news is that, since this is hidden by the objects in the namespace, I can change it anytime without issue. Moreover, the structure allows me to reference databases as objects. Not only do I like that, but the polymorphism and use of object factories allows that I can move data from one database to another with no changes to code.

Just thought it was cool enough to post.