My Software Notes

Useful things I discover

DB Class – Pattern or Anti-Pattern?

leave a comment »

An alternative title for this article could be “When is a Data Access Layer not a Data Access Layer?”

In the last couple of years I’ve come across a “pattern” that is used for database access that I think deserves the title of anti-pattern.  I’m going to call it the “DB Class” pattern because the examples of it I’ve come across have used the name “DB”, “DBCommon” and similar to name the class.

The way it works is that the class has methods that are passed the name of a stored procedure or a sql statement and an array of parameters.  The methods generally return data tables or scalar values or data sets.

Here is an example of a call to such a method:

DataTable customers = DB.GetTable("dbo.GetCustomers", params);

Where “dbo.GetCustomers” is the name of a stored procedure and “params” is a SQLParameter array.

What’s wrong with this pattern is that the calling code has to know the structure of the database – name of stored procedure, names and types of the parameters.  A true Data Access Layer hides the database from the other parts of the application.

A better example of a call to such a method would be:

DataTable customers = DB.GetCustomers(nameStartsWith, custType);

Where the method parameters are standard .NET data types, not SQLParameters.

An even better example would be:

List<Customer> customers = DB.GetCustomers(nameStartsWith, custType);

Where a collection of objects is returned.  This would be using a Transaction Script pattern.

I’ve seen this “DB Class pattern” used in two ways:

  1. The methods are called directly from all over the place.  For example, in the code-behind of an aspx page or in an event handler of a Windows Form.
  2. The methods are called by a “data access layer”.  For example, in the code-behind a call is made to a method in a data access class, then the data access class makes the call to DB.

The 1st way exposes the database structure to the harsh light of day – not a good idea.  Try to make some database changes or change the parameters of a stored procedure and you will be doing text searches and endless regression testing to make sure you didn’t break something.

The 2nd way is not as bad, at least the stored procedure and the parameters are hidden from the UI code but these methods return generic DataTable objects and then the code that made the call has to know the names of the table columns, which brings us back to the same issue as before – code outside the data access layer has to know the database structure.

If you really have to use DataSets and DataTables for passing your data around then use Typed DataSets.  Visual Studio has designers and wizards that make it easy and they also create a “TableAdapter” class that encapsulates your database interactions.

Better still is to use a Domain Model pattern and an ORM tool that converts data from a database into objects and collections of objects.  NHibernate, LLBLGen and now Entity Framework are just three of the many choices available.

So, to conclude, I think this “DB Class” is an anti-pattern, and if no one else has named it as such then I claim that distinction and all the honors antendent to it 🙂

More on Anti-Patterns:


Written by gsdwriter

July 29, 2009 at 12:55 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: