Click here to Skip to main content
13,596,861 members
Click here to Skip to main content
Add your own
alternative version


26 bookmarked
Posted 6 Dec 2006

A Helpful Database Library

Rate this:
Please Sign up or sign in to vote.
This article illustrates a helpful database library.


There is a question that all programmers ask themselves, "What is that connection string format?" Another one that I ask myself still is, "Is it OLE, ODBC, or SQL?" Well, I know there are many different ways to interact and connect with databases. I am going to show you a way that I like to write code for database programming. Please, forgive me for any mistakes; this is my first article, and I am not a great technical writer.

How do we work with databases

First, let's ask ourselves a few questions:

  1. Does my program need to connect to a database?
  2. What kind of database will it be, i.e., MS SQL, MS Access, MySQL?
  3. What action will I be making on the database, i.e., SELECT, INSERT, CREATE?

For this example, let's say:

  1. Yes, we need a database,
  2. The database will be MS Access, and
  3. We need to return (SELECT) records from the database.

Now that we know what it is we will be doing, let's get to the basics. To programmatically run a SQL statement against a database, we need a couple of things at the bare minimum.

  1. A Connection class
  2. A Command class

For an Access database, it would be something like this:

OleDBConnection _OleConn = new OleDbConnection(ConnectionString);
OleDbCommand _OleCmd = new OleCommand(SQLstring, _OleConn;

But we still need to run the SQL against the database, so:


Pretty easy, well at least after you have done it a few times. I know that starting out as a newbie, that stuff can get confusing. But with places like CodeProject and Google, you can figure out almost any problem you have. Now, let's try to get our hands good and dirty.

I do not like loops and hoops

One of the biggest problems that a young programmer can face is OOP. What I mean is, we can be tempted to put too much code into a method (or a function) either to be able to see it in one place or just not knowing any better. Breaking up your code into logical sections helps a lot. Let's take a database call for instance. If you know that you will be making repeated calls to a database from multiple methods, do not put the code in each method. Create a method that holds the database code, and call it every time. I know I am talking about basics here, but hang in there. So here is what I do: I have a 'DB' class that has a static method 'RunSqlCommand', and it takes an argument 'MySqlData', which is a different class that hold values like connection string, provider (I'll explain), and the SQL command to be run.

public static class MyDatabase
    public static void RunSqlCommands(MySqlData _MySQLData)
        if (_MySQLData.MyDatabaseProvider == DatabaseProvider.MicrosoftJet)
            OleDbConnection MyOleConn = 
              new OleDbConnection(_MySQLData.MyConnectionString);
            OleDbCommand MyOleComm = 
              new OleDbCommand(_MySQLData.MySQLString, MyOleConn);
            SqlConnection MySqlConn = 
              new SqlConnection(_MySQLData.MyConnectionString);
            SqlCommand MySqlComm = 
              new SqlCommand(_MySQLData.MySQLString, MySqlConn);

You do not need to make the class static, but it does help if you do so. This way, all you have to do is call MyDatabase.RunSqlCommands(_MySQLData);. It just make things easier.

Connection string

Getting the connection string for a database is pretty easy (Google) once you have the right format. You can store it in your program in many different ways. One thing I do stress on is do not hard code it into your program unless you know that the database will never move or have a name change, or if your program will never work with a different database. Even then, I wouldn't hard code it in. Okay, so let's say that our program will create tables in and work with those tables. If designed right, your program can work with SQL Server, Access, MySQL, and Oracle. So, you will need a way to generate a connection string for each one of them. Well, I think I have a real good way to do just that.

Data Link Properties Dialog Box:

You can implement this into your program with ease, and this is very useful. Here is how. Add a reference to:

  • Microsoft ActiveX Data Objects 2.8 Library
  • Microsoft OLE DB Service Component 1.0 Type Library

Then add the following using statements:

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using MSDASC; // add this
using ADODB;  // add this

The code to implement it:

public static void GenerateConnectionString(ref MySqlData _MySqlData)
    DataLinksClass MyDataLink = new DataLinksClass();
    Connection MyADOConn = (Connection)MyDataLink.PromptNew();
    if (MyADOConn.Provider == "Microsoft.Jet.OLEDB.4.0")
        _MySqlData.MyDatabaseProvider = DatabaseProvider.MicrosoftJet;
        _MySqlData.MyConnectionString = MyADOConn.ConnectionString;
        _MySqlData.MyDatabaseProvider = DatabaseProvider.MicrosoftSQL;
        _MySqlData.MyConnectionString  = MyADOConn.ConnectionString;
        _MySqlData.MyConnectionString  = _MySqlData.MyConnectionString.Substring(
            (_MySqlData.MyConnectionString.IndexOf(";") + 1),
                (_MySqlData.MyConnectionString.Length - (
                    (_MySqlData.MyConnectionString.IndexOf(";") + 1))));

Again, I use a static method because I don’t need to implement it everywhere. Also, I passed a ‘ref’ of _MySqlData to it and the method handle assigning values to many connection strings and providers.


I have found that when using these methods in a multithreaded program, I get errors when when one thread tries to execute a method while another is in the process of executing the same method. I have tried Monitor.Lock and just Lock. But the best method that I found is to change the method to non-static and create an instance of the class. Now I am sure someone probably just called me stupid for saying that, but I also know someone just said thanks for showing me that.


I have something I use called MyDatabaseLibrary. It is a DLL file that contains a lot of database code. The library is very simple (simple=good), you can see it here:

With this library, I can use:

  • MyDatabse class
    1. Run SQL, i.e., INSERT, CREATE against a database with ‘RunSqlCommands
    2. Get the tables from a database with ‘GetDataBaseSchema
    3. Return a record, or sets of records, or a whole table with ‘GetDataTable
    4. Generate a connection string with ‘GenerateConnectionString
  • MySqlData class stores:
    1. Connection string
    2. Database provider
    3. SQL command to execute

The DatabaseProvider enum stores a list of database providers the library uses.

Using MyDatabaseLibrary

This is the good part. To use this library, here are the steps you need to follow:

  1. Create a new instance of MySqlData, say, _msd
  2. Call GenerateConnectionString(ref _msd)
  3. Add a SQL command _msd.MySQLString = “SELECT * FROM <table>”;
  4. Call GetDataTable(_msd)

Of course, you have to add a reference to the library and add your ‘using’ statement. Also, make sure you have the two files the library uses with it: Interop.Adodb.dll and Interop.Msdasc.dll.


I have built a very simple program that uses this library so you can see how to use it yourself if you want. You are free to use it however you want. But please give me some feedback. Let me know what you think. Have a good one!


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


About the Author

lost in transition
Web Developer
United States United States
I am a software, database, and gis developer. I love the challenge of learning new ways to code.

You may also be interested in...


Comments and Discussions

GeneralDeploying an aplication using this code Pin
eliseobc20-Sep-07 9:42
membereliseobc20-Sep-07 9:42 
GeneralRe: Deploying an aplication using this code Pin
jason_lakewhitney20-Sep-07 9:49
memberjason_lakewhitney20-Sep-07 9:49 
GeneralRe: Deploying an aplication using this code Pin
eliseobc20-Sep-07 13:44
membereliseobc20-Sep-07 13:44 
GeneralCheck the EntLib Pin
Frank Olorin Rizzi6-Dec-06 7:27
memberFrank Olorin Rizzi6-Dec-06 7:27 
GeneralRe: Check the EntLib Pin
jason_lakewhitney6-Dec-06 8:02
memberjason_lakewhitney6-Dec-06 8:02 
GeneralRe: Check the EntLib Pin
Marc Leger6-Dec-06 11:36
memberMarc Leger6-Dec-06 11:36 
GeneralRe: Check the EntLib Pin
Joergen Sigvardsson30-Jul-07 13:14
memberJoergen Sigvardsson30-Jul-07 13:14 
GeneralRe: Check the EntLib Pin
Jan Seda6-Dec-06 11:52
memberJan Seda6-Dec-06 11:52 
GeneralRe: Check the EntLib Pin
Frank Olorin Rizzi6-Dec-06 13:16
memberFrank Olorin Rizzi6-Dec-06 13:16 
I didn't mean to insult the author of this article. If I did so, please forgive me.
I simply meant to bring to the readers' attention an alternative that, in my opinion, is a better answer *for nontrivial or enterprise-grade applications*. If you are here to write the database equivalent of Hello World, please don't let my comments get in your way.
When I read CodeProject I hardly assume everyone has read and tried out the same things; that is why I mentioned there is such a thing as the EntLib. If I had known that my comment was going to be read by such experts on the EntLib as it turned out, I would have been very quiet instead. Since I have the ear of such experts, though, let me take the opportunity: has anyone released any extension of the EntLib that we all might find useful ?

I completely agree that CodeProject is a very valuable repository, and I don't see why my original post would have prompted anyone to think that I don't value CodeProject or that I think the EntLib is the answer to all problems. heck, why else would I even be reading these pages ?

On the positive side, the author of this article points out that

"One of the biggest problems that a young programmer can face is OOP. What I mean is, we can be tempted to put too much code into a method (or a function) either to be able to see it in one place or just not knowing any better. Breaking up your code into logical sections helps a lot"

and that

You should "not hard code [the connection string] into your program"

Aside from that, though, I feel it is important to point out the shortcomings of the library presented. For instance: how do i extend it ?


Incidentally, in regard to the appropriate format for connection strings for the various DBMSes, I think the best reference to link is

GeneralRe: Check the EntLib Pin
jason_lakewhitney7-Dec-06 3:00
memberjason_lakewhitney7-Dec-06 3:00 
NewsRe: Check the EntLib Pin
Ilíon8-Dec-06 4:05
memberIlíon8-Dec-06 4:05 
GeneralRe: Check the EntLib Pin
Bat Gurl19-Jul-07 1:00
memberBat Gurl19-Jul-07 1:00 

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

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

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04 | 2.8.180621.3 | Last Updated 6 Dec 2006
Article Copyright 2006 by lost in transition
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid