Purely Technological

C# How-To: Creating a Driver Foundation

// September 22nd, 2008 // .NET, C#, Programming

“Driver Foundation”?
A driver foundation is a class that your program will go through to access several other types of classes that all have something in common (i.e.: A database driver).

Generic Outlook

Actual Example for Database Platform

Why would I need this? Is this really necessary?
If you’re not worried about expandability in your application, then forget about it. However, if you’re designing an application that would need to access and query different types of databases (MySQL, PostgreSQL, SQLite, MSSQL, etc), then having a driver foundation is ideal. Otherwise you will end up with an unmanageable mess of code – you would need to control (for each different database type) the output type for queries, query syntax, and a bunch of other stuff.

Using a driver foundation you really only deal with one type of object, and one type of output for all types of databases.

Designing the foundation class
In this example I will only outline the fundamentals, you may choose to add even more functionality to your foundation – and that’s okay!

Your driver foundation should include a method to tell each different type of driver apart – I chose to use an enum:

    public enum DriverType
    {
        MYSQL = 0,
        PGSQL = 1,
        SQLITE = 2
    }

Next you will be needing a standard singleton layout for the class called “Database”:

    abstract public class Database
    {
        static public Database instance = null; 

        static public void GetInstance(out Database Db)
        {
            Db = instance;
        }
    }

And finally you will be needing to declare basic functions, such as a method to do a basic query, a method to tell if you’re connected to the database, and possibly a method to disconnect from the database:

abstract public void query(string query, out Hashtable table);
abstract public bool connected();
abstract public void close();

The finished product:

public enum DriverType
{
    MYSQL = 0,
    PGSQL = 1,
    SQLITE = 2
}

abstract public class Database
{
    static public Database instance = null;

    static public void GetInstance(out Database Db)
    {
        Db = instance;
    }

    abstract public void query(string query, out Hashtable table);
    abstract public bool connected();
    abstract public void close();
}

Designing the driver class
Alright, now that we have our foundation done, we need to create an actual driver to use the foundation. For this example, I will only construct a MySQL driver – leaving you use your knowledge to construct the other two (PostgreSQL, SQLite).

When creating the driver the construction method will connect it automatically– saving us the hassle of actually calling a method and connecting ourselves. Now in this example, I have not outlined the importance of catching exceptions before it’s too late, and I will not do so in this article – I’m just outlining the fundamentals. If this driver feels incomplete, and totally lacking exception handling, then it probably is, because there is none.

For this driver we will need to declare two variables before we do anything: the ODBC version string, and the ODBC connection object:

private string driver = "MySQL ODBC 5.1 Driver";
private OdbcConnection _Connection = null;

Next we will need to create the construction object, so when the class is created, we instantaneously connect:

public Driver_MySQL(string username, string password, string db, string host)
{
    this._Connection = new OdbcConnection(string.Format("driver={0};server={1};database={2};uid={3};pwd={4};", this.driver, host, db, username, password));

    this._Connection.Open();

    instance = this;
}

And finally we will need to create all of the abstract methods that we declared in the foundation class – failure to do so will raise exceptions. These are to be of types public and override:

public override void close()
{
    if (this._Connection.State == ConnectionState.Open)
    {
        this._Connection.Close();
    }
}

public override bool connected()
{
    if (this._Connection != null)
    {
        return (this._Connection.State == ConnectionState.Open);
    }
    return false;
}

public override void query(string query, out Hashtable table)
{
    table = new Hashtable();

    OdbcCommand com = new OdbcCommand(query, this._Connection);
    OdbcDataReader reader = com.ExecuteReader(CommandBehavior.SingleRow);
    reader.Read();

    for (int i = 0; i < reader.FieldCount; ++i)
    {
        table.Add(reader.GetName(i), reader[i]);
    }
}

The final product should look similar to:

sealed public class Driver_MySQL : Database
{
    private string driver = "MySQL ODBC 5.1 Driver";
    private OdbcConnection _Connection = null;

    public Driver_MySQL(string username, string password, string db, string host)
    {
        this._Connection = new OdbcConnection(string.Format("driver={0};server={1};database={2};uid={3};pwd={4};", this.driver, host, db, username, password));

        this._Connection.Open();

        instance = this;
    }

    public override void close()
    {
        if (this._Connection.State == ConnectionState.Open)
        {
            this._Connection.Close();
        }
    }
    public override bool connected()
    {
        if (this._Connection != null)
        {
            return (this._Connection.State == ConnectionState.Open);
        }
        return false;
    }

    public override void query(string query, out Hashtable table)
    {
        table = new Hashtable();

        OdbcCommand com = new OdbcCommand(query, this._Connection);
        OdbcDataReader reader = com.ExecuteReader(CommandBehavior.SingleRow);
        reader.Read();

        for (int i = 0; i < reader.FieldCount; ++i)
        {
            table.Add(reader.GetName(i), reader[i]);
        }
    }
}

Great! Are we finished?
Wish the driver and the foundation – yes. However, we still have yet to use them. Lets go over how that’s going to work:

• User enters database credentials (user, password, database name, host)
• User selects type of database (from a drop-down menu)
• When user clicks “connect” or “submit”, the form determines what database driver to instantiate by referring to the index selected on the drop-down (it’s very important that the indices coincide with the driver number in the driver foundation enum)
• When the application needs to access the database driver, it goes through the foundation

 

Working with the foundation (part 1)
When the user clicks “connect”/”submit” we will need to instantiate a database driver based on the selected driver in the drop-down menu, as noted above. Here’s how I did it:

                try
                {
                    switch ((DriverType)cmbDBType.SelectedIndex)
                    {
                        case DriverType.MYSQL:
                            new Driver_MySQL(txtUsername.Text, txtPassword.Text, txtDatabase.Text, txtHost.Text);
                            break;
                        default:
                        case DriverType.PGSQL:
                        case DriverType.SQLITE:
                            MessageBox.Show(this, "Invalid or missing driver requested.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);
                            break;
                    }
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(this, string.Format("Connection error, aborting.rn{0}", Ex.Message), "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);
                    this.DialogResult = DialogResult.Abort;
                }

That’s basically all there is to connecting and creating an instance of the database driver. Now you’re free to work with it, as described in the next section.

Working with the foundation (part 2)
Now that we have our database driver formed, we can work with it – through the foundation that is, don’t dare access the driver for it’s actual object!

To start off, we will need to declare a variable to hold the collected instance of the foundation in – we don’t want to keep calling GetInstance(), do we? Didn’t think so:

private Database Db;

Then we will have to fill that variable, by calling GetInstance() on the foundation:

Database.GetInstance(out this.Db);

Now we are free to work with it: query, close, etc. Lets see an example query, and how to access the output from a hashtable, shall we?

Hashtable _Data;
this.Db.query("SELECT * FROM clients ORDER BY id LIMIT 1", out _Data);

MessageBox.Show(this, string.Format("{0}’s favorite food is {1}.", _Data["client_name", _Data["favorite_food"), "Client Information", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);

Tada! You’ve successfully completed your driver foundation and driver, and managed to use it. Now what? Go implement it where you find it useful.

 

Have fun!