Editorials

Use ADO.Net Interfaces

In the last few months I have been using ADO to work with many different database engines (MySql, SqLite, SQL Server, Oracle and DB2). I have a basic framework I put together allowing me to easily construct and bundle one or more queries into a Unit of Work so that all of the queries are included in a single transaction. This is extremely useful when you do not have the ability to use procedures with your target engine.

There is one mantra about object oriented programming that stands out as a result of these many projects, “Declare Interfaces; instantiate Objects.” This is a common statement in the object oriented programming world, and my experience with this ADO implementation has proven its value once again.

Yesterday I wrote about two of the basic objects used in programming using ADO, implemented using the OleDb library, when connecting with DB2. There is an Oracle specific library for ADO, a SQL Server specific library, a SqLite specific library, and I use the ODBC library when working with MySql.

One of the Cool things about the ADO.Net framework is that all of the database driver implementations are all based on interfaces defined in ADO.Net. This means I can build my framework using the ADO.Net interfaces as declarations, and then inject the necessary objects into the code when I instantiate my instances.

There are many different ways the injection can be implemented, but that is a different topic. The key here is the reminder to declare your variables as an interface, and instantiate them using the specific objects needed to perform your work. So, I am able to use my little unit of work framework with many different ADO database specific drivers, because I have designed it based on interface objects. All I have to do is enable Injection of the appropriate database driver, and I am ready to go with a completely new database engine.

If you’re interested in the interfaces I’m talking about, here’s a short list:

  • IDbConnection – An object that handles the connection of the client with the database engine
  • IDbCommand – Execute queries against the database
  • IDataParameter – A Parameter that may be added to an IDbCommand
  • IDataReader – A stream like object that processes results from a query one row at a time (much like a cursor in SQL)

Each of these interfaces have object implementations needed to work with your database.

Cheers,

Ben