Database Abstraction

• Download demo - 72.4 KB
• Download source - 202.6 KB

Simplifying Database Abstraction

One would think that in today's world, database abstraction would be a totally solved problem, with a proper, generic approach available for most (if not all) platforms. I found this not to be the case when the company I was with at the time decided to switch our web environment from PHP to .NET -- they had many good reasons to do this given the nature of the company's development environment.

The first database abstraction library I tried for .NET was one written by one of the new ".NET specialists" hired by that company, which proved to be a clumsy though functional solution. Looking for something better I tried out Microsoft's Database library found in Enterprise library. While this solution was quite a bit better than our internal library in many regards, it made certain assumptions which were not true for our environment, and I felt that it required the end programmer to directly manage and manipulate far too many objects (I just want access to my data)!

So I decided to set out and write what I hoped would be a better database abstraction solution that would work fairly well for both small projects and on large corporate systems. I had the following goals for my library:

• It had to offer quick and easy access to any number of database servers.
• The very same library had to be capable of being used with numerous different data sources within the same application all using the same syntax and conventions (even if the underlying connection is based on a data provider which breaks Microsoft conventions, say, requiring ? prefix characters for parameter names).
• Allow for query logging to be quickly and easily set up both from within the application, and at the config file level.
• Define methods and properties which simplify data access and manipulation for end programmer convenience.
• Be based as much on ADO.NET syntax and conventions as possible to lessen the learning curve.

Using the Code

Using this library (particularly in conjunction with the XML configuration file) is a fairly simple process. In line with this article I will show the following: creating a database connection, inserting a record, retrieving the auto-id number, and querying data from the table.

Attached to this article is a sample application which will allow you to play with the library more. Step one: create a connection. Using the configuration file you simply name the connection you want to connect to, in this case MainDatabaseServer is our connection of interest

//MainDatabaseServer is defined with in the configuration file
DBCommon Connection = DBProvider.Connect("MainDatabaseServer");

Next, insert data into a table with the following schema:

CREATE TABLE Accounts(
    AccountNumber BIGINT IDENTITY( 1, 1 ) PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    AccountBalance MONEY NOT NULL,
    Street VARCHAR(128),
    ..... 
    //whatever else you might need
    );

//Function params used are: ( paramName, sourceColumn, paramValue )
Connection.Parameters.Add("@fname", "FirstName", "John");
Connection.Parameters.Add("@lname", "LastName", "Doe");
Connection.Parameters.Add("@bal", "AccountBalance", 160.00);
....//more params
Commmection.ExecuteAutoInsert("Accounts");

Now I will show how the automatically generated primary key can be accessed:

long AccountNumber = ((long)Connection.LastAutoID;

Finally this shows how end programmers can quickly access data using the AutoSelect feature

//Parameters used in this case: ( paramName, sourceColumn, paramValue, 
    isSearchKey )
Connection.Parameters.Add("@acctNum", "AccountNumber", 123, true);
DataTable Account = Connection.ExecuteAutoSelect("Accounts");

//Finally close the connection
Connection.Disconnect();

Well, these are the very basics of the library. I certainly do hope that others out there will find this library useful and/or interesting. If you have any comments/suggestions please don't hesitate to email me, or post a message at the forum below -- I'm all for making modifications to improve this library.