A Helpful Database Library

• Download demo project - 43.7 Kb
• Download source - 114 Kb

Introduction

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:

_OleConn.Open();
//or
_OleCmd.Connection.Open();
OleCmd.ExecuteNonQuery();
_OleConn.Close();
//or
_OleCmd.Connection.Open();

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);
            MyOleComm.Connection.Open();
            MyOleComm.ExecuteNonQuery();
            MyOleComm.Connection.Close();
        }
        else
        {
            SqlConnection MySqlConn = 
              new SqlConnection(_MySQLData.MyConnectionString);
            SqlCommand MySqlComm = 
              new SqlCommand(_MySQLData.MySQLString, MySqlConn);
            MySqlComm.Connection.Open();
            MySqlComm.ExecuteNonQuery();
            MySqlComm.Connection.Close();
        }
    }
}

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;
    }
    else
    {
        _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.

Warning

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.

MyDatabaseLibrary

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.

Conclusion

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!