Click here to Skip to main content
Click here to Skip to main content

DB Independent Code using Wrappers

, 20 Aug 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
A simple Data Connection Library to avoid rewriting code when the DB changes

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Amol M Vaidya
Architect
Japan Japan
My computer career started as an Avid Gamer Smile | :) . I loved the old times Xatax and Wolf. But, curiosity took over my gaming side and I started to search on ways to create all the mesmerising programs that I used.
I started programming in Qbasic and then moved on to every possible programming language I could lay my hands on. Qbasic, Pascal, C, C++, Win32 SDK, VB 6.0, VB.NET, C#, Struts, Core Java, Javascript, VbScript, Unix Shell Programming, SQL SERVER PROGRAMMING, WMI scripting....are some of the languages I can boast of being proficient in.
My formal introduction to computers was when I took the Bachelor of Computer Sciences (Pune, India) degree.Later on I got Masters from the same university.
My Professional Certifications include
MCP SQL Server 2000 (70-229)
Brainbench Certified MS SQL Server Programmer
Brainbench Certified MS SQL Server Administrator
Brainbench Certified ASP.NET Programmer
Brainbench Certified Javascript Programmer
Brainbench Certified RDBMS Concepts

Comments and Discussions

 
GeneralThoughts PinmemberPIEBALDconsult25-Aug-14 9:00 
GeneralRe: Thoughts PinprofessionalAmol M Vaidya25-Aug-14 15:21 
GeneralRe: Thoughts PinmemberPIEBALDconsult25-Aug-14 15:35 
QuestionSource Missing PinprofessionalAmol M Vaidya24-Aug-14 17:35 
QuestionDBC.zip missing PinmemberLuis Carlos23-Aug-14 7:11 
QuestionFrom Manuel to Amol. Wrap string connection PinmemberMember 1055260623-Aug-14 1:36 
AnswerRe: From Manuel to Amol. Wrap string connection PinprofessionalAmol M Vaidya25-Aug-14 22:20 
QuestionIs this that common? Pinmembercjb11021-Aug-14 2:15 
AnswerRe: Is this that common? PinprofessionalAmol M Vaidya25-Aug-14 22:28 
Questioncode file PinmemberMember 461770120-Aug-14 23:48 
QuestionYour article is broken PinprotectorPete O'Hanlon20-Aug-14 21:51 
The source code is missing.
 
To be honest, I'd have preferred it if you'd actually shown us some of your source code here. It would be good to see how you work through the challenges of features such as the way different databases reference parameters. How you cope with MARS and multiple transactions.

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
Web03 | 2.8.141216.1 | Last Updated 21 Aug 2014
Article Copyright 2014 by Amol M Vaidya
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid