Database layer design question

Question

Hi all :)

I've been designing a database access layer that allows us to support multiple databases in our programs. In the end, the users of our programs shall be able to choose the underlying database system from a range of database systems. Some small clients might be happy with MS Access, others prefer MySql, others DB2. Those db systems are the ones I want to target for now.

Given those requirements, I've come up with an abstract class DatabaseConnection. Internally, I use the System.Data.Common.Data.DbConnection-class, which already gives me quite some flexibility.
The things that need concrete instances (OleDbCommand instead of DbCommand e.g.) are hidden in abstract methods like CreateDbCommand(). Subclasses (like AccessDbConnection) implement those and provide the concrete instances. Currently that leads to this hierarchy (class names abbreviated for readability):

           DatabaseConnection
          /         |        \
 AccessConn     MySqlConn     DB2Conn

However, there are a few operations that are specific to the underlying database system, such as retrieving all table names. It feels wrong to place an abstract method GetTableNames() into the DatabaseConnection-class and have the subclasses overwrite it.

I thought maybe I can create another abstract base class called DatabaseTools, declare those operations there and then implement them in subclasses that resemble the subclasses of the DatabaseConnection class. Which means that for a AccessDbConnection, I'd also have a class AccessTools etc etc:

           DatabaseConnection                      DatabaseTools
          /         |        \                   /       |      \
 AccessConn     MySqlConn     DB2Conn    AccessTools MySqlTools  DB2Tools

Somehow I'm not really thrilled by this idea.

What ideas do you have to solve this design problem?

Thanks in advance for your time and answers :)

Cheers

Christian

Answer 1

Rather than an abstract method, why not implement one that retrieves table names using the ANSI-standard INFORMATION_SCHEMA views, and then just override it in a DatabaseConnection implementation for a provider that is not ANSI compliant?

That apart, I don't see anything wrong with the abstract method approach from a design point of view.

Answer 2

Since you get no points for having 100% OO purity follow Swingline Rage's advice. You could also make it pure just by renaming the class ;-)

Only thing is, if you use Interfaces instead of Abstract Classes and inheritance you are less likely to get burned. Interfaces can inherit from each other but you probably won't need it. A little harder to write but you can use them the same way.

http://www.codeproject.com/KB/cs/abstractsvsinterfaces.aspx
http://stackoverflow.com/questions/1814821/interface-vs-abstract-class

"what about shared code in the base class?" - You can stay DRY if you Use static classes and call them (it's not a sin to use some procedural techniques in OO). Q#2 in the stack overflow topic above looks interesting but I can't vouch for it.

Answer 3

In my view, defining the db operations in separate hierarchy is a better Idea. I would prefer to encapsulated the tools inside the concrete connections objects and then get the connections via a single Factory.

Precisely, to perform a task to get all table names, the call will look like:

ConnectionFactory(ACSESS).getConnection().getTools().fetchSchema();