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:
- Does my program need to connect to a database?
- What kind of database will it be, i.e., MS SQL, MS Access, MySQL?
- What action will I be making on the database, i.e., SELECT, INSERT, CREATE?
For this example, let's say:
- Yes, we need a database,
- The database will be MS Access, and
- 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.
- A Connection class
- 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();
_OleCmd.Connection.Open();
OleCmd.ExecuteNonQuery();
_OleConn.Close();
_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;
using ADODB;
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
- Run SQL, i.e.,
INSERT
, CREATE
against a database with ‘RunSqlCommands
’
- Get the tables from a database with ‘
GetDataBaseSchema
’
- Return a record, or sets of records, or a whole table with ‘
GetDataTable
’
- Generate a connection string with ‘
GenerateConnectionString
’
MySqlData
class stores:
- Connection string
- Database provider
- 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:
- Create a new instance of
MySqlData
, say, _msd
- Call
GenerateConnectionString(ref _msd)
- Add a SQL command
_msd.MySQLString = “SELECT * FROM <table>”;
- 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!