Once we had a project where we had to execute an SQL query to an external source based fully on configuration, because it was not known at the time of design what kind of database it would be. The data source (type and connection string) was configurable and so was the query.
A simplified example of connection and query execution would look like this:
Very short, clear and easy, but only if database was MS SQL. What if we have two different database types ?
Ok, now we have two types of databases, and the code is still maintainable. It does not look good though (repeated code). What if we had several database types ? Going in this direction we would end up with spaghetti code with if else after if else after if else, etc. Unmaintainable. One change would require looking at and modifying several places.
Move object creation process to a new class
We need to create a factory class that receives database type as a parameter and returns an object that can run a query. The returned objects must have a common interface.
Now we need to create classes that can connect to a certain type of database and run a query on it. In our example we’re creating two classes. In real life there may be five, ten or more.
Now we can use our new database query creator and run a query.
It’s easy to use, and can accept many different database types. It also allows us to separate business logic from the technical side of the database connection. If any technical changes occur or there is a need to add a new database type, it can be done with no changes to the business layer whatsoever.
The problem is that we have repeated code in our connection classes. It’s not entirely identical, but some parts are similar. We should move common code into a base class or even better – an abstract class. In our example the code running a query can be moved into common code thanks to IdbCommand and IdbConnection interfaces that are available in .Net Framework.
Move common, repeated code to abstract class
Also closing connection and constructor may be shared. The only thing we can’t share is the code for opening a connection – we have to use a type that is database specific.
Then we have to change database classes to make them shorter by deriving from SqlQuery abstract class.
Unfortunately constructors have to be implemented but what we need to do is to invoke the base class constructor with the same connectionString parameter. There are some specific database types like Oracle or Informix that would require different approach to RunSql method because they don’t have ExecuteNonQuery method implemented. In such cases we need to override RunSql method with a new code in OracleQuery and InformixQuery classes, that’s why I marked RunSql as virtual.
Mark methods as virtual to inform everybody that these methods can be substituted if needed.
Thanks for reading and regards
CEO, .Net developer, software architect
If you need help with your software project, or need customized software for your company, contact me at: dominik.steinhauf ( at) cys.biz.pl