Click here to Skip to main content
11,484,752 members (63,946 online)
Click here to Skip to main content

Database Abstraction

, 10 Aug 2007 14.6K 266 12
Rate this:
Please Sign up or sign in to vote.
Database abstraction using factory design pattern

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

C Razzeto

United States United States
No Biography provided

Comments and Discussions

 
QuestionConfig File? Pin
ACTMate15-Jan-09 4:33
memberACTMate15-Jan-09 4:33 
GeneralNot really abstracted Pin
Tim McCurdy14-Aug-07 7:56
memberTim McCurdy14-Aug-07 7:56 
GeneralComments Pin
lallous13-Aug-07 22:35
memberlallous13-Aug-07 22:35 
Hello

Please consider my comment from a constructive point of view:

Your code might be good and really useful, however the article does not talk much about the solution or how it works. Perhaps the article would be more appreciated if more explanation was put in the article.

Keep the good work,
Elias

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150520.1 | Last Updated 10 Aug 2007
Article Copyright 2007 by C Razzeto
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid