DB Independent Code using Wrappers

• Download DBC.zip - 200.8 KB

Introduction

I always had trouble convincing some customers to Stick to one database. They wanted their .NET applications to use SQL Server or MySQL or Oracle or....Probably even God could not think of what they will come with next ! 

So, we came to a conclusion that we needed some good wrappers built on top of the Data Client libraries which our programmers will use and never ever bother when the DB changes. This gives uniformity to the code no matter what Database is being used at the Backend. Furthermore we just need to replace the DLL files as and when our library or Database was updated thereby keeping the existing code in working condition.

Note: This is a VERY basic code written in a matter of 60-70 mins just to highlight a problem and to show a resolution. This is NOT a complete library for achieving DB independence. 

Using the code 

In this quick library I have included a few Basic Public functions like OpenConnection, BeginTransaction, etc. These will never change for which ever DB we use as the backend. Ofcourse the code in the library should be updated as and when needed.

I have also included a sample application with scripts to showcase the usage of the library. Feel free to point out bugs, issues, suggestions, etc.

There are 3 Projects in the Solution:

1. DBCTest - This is the test windows application using the library
2. DBCSQL - The Library for SQL Server 
3. DBCMySQL - The Library for Mysql Server

I have also placed 2 script files for creating quick tables in a database. (SQL Server as well as MySQL) 

Step By Step: 

1. Run the SQL Scripts on SQL Server and MySQL Server respectively to get the tables with some dummy data. 
2. Once you open the Solution Check the connection string in App.config.
3. Change the string as per your database ID/Password.
4. The DBC.dll reference is already added to the Test project
5. When using SQL Server make sure to build DBCSQL and then run the test application. 
6. When using MySQL make sure to build DBCMySQL and then run the test application.
7. Run the Test Application and Click on Get Data SQL or Get Data MySQL.
8. Notice that you dont have to change any code in your test application even though you change the connection string or database.

VOILA ! we thus achieve DB independence and now as a developer dont need to bother about what is used as the backend ! 

To explore more try the other functions as below.

1. AddParameter
2. BeginTransaction
3. CloseConnection
4. CommitTransaction
5. ExecuteCommand
6. ExecuteReader
7. IsConnectionOpen
8. OpenConnection
9. RemoveParameters
10. RollBackTransaction 

Points of Interest 

Note that the Library is separate for each Database Type. The only thing we are actually trying to do is to build a wrapper on top of the existing DB libraries for consistency of code in the main application. Many people argue that we should use the power of the DB layer (Stored Procedures, Triggers, etc), which I completely agree. But there are still cases when you have a beautiful app and customers dont want to pay for DB License or just want your code to adapt to the DB they have. An approach as mentioned in this tip will solve this problem. Ofcourse optimum performance will not be achieved.

Please feel free to make your own wrapper for Oracle, Postgres, etc and also add complicated features like parameters and transactions.

History

29/12/2013 - Basic Version Uploaded