Using MySQL With C#

This article gives a basic introduction to using MySQL in conjunction with Microsoft C#.

Some of the features in C# are ideally designed to integrate with Microsoft SQL Server, however not everyone will be able to afford SQL Server licenses and it’s also overkill for many smaller scale developments.

Creating the Database
Making an ODBC Link
Classes
Example Program
Example Output

Creating the Database

First let’s create a simple database to use with our program. All of the techniques that we use in C# later on will scale to much larger programs so this doesn’t need to be anything complicated.

First install MySQL, it doesn’t matter whether this is on a Windows, Mac or Linux machine. Then we can go ahead and create our database and add a few records with the following commands:

create database csharpdb;
use csharpdb;
CREATE TABLE people (id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), employed TINYINT(1) NOT NULL DEFAULT 0, jobtitle VARCHAR(50), age INT(3));
INSERT INTO people (name, employed, jobtitle, age) VALUES ("Rimmer", 1, "2nd Technician", 28);
INSERT INTO people (name, employed, jobtitle, age) VALUES ("Lister", 1, "3rd Technician", 25);
INSERT INTO people (name, employed, jobtitle, age) VALUES ("Cat", 0, null, 15);
INSERT INTO people (name, employed, jobtitle, age) VALUES ("Kryten", 1, "Mechanoid", 540);
GRANT ALL ON csharpdb.* to 'csharpuser'@'%' IDENTIFIED BY 'password';

To give us the following:

mysql> describe people;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50)      | YES  |     | NULL    |                |
| employed | tinyint(1)       | NO   |     | 0       |                |
| jobtitle | varchar(50)      | YES  |     | NULL    |                |
| age      | int(3)           | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.08 sec)

mysql> SELECT * FROM people;
+----+--------+----------+----------------+------+
| id | name   | employed | jobtitle       | age  |
+----+--------+----------+----------------+------+
|  1 | Rimmer |        1 | 2nd Technician |   28 |
|  2 | Lister |        1 | 3rd Technician |   25 |
|  3 | Cat    |        0 | NULL           |   15 |
|  4 | Kryten |        1 | Mechanoid      |  540 |
+----+--------+----------+----------------+------+
4 rows in set (0.00 sec)

Making an ODBC Link

For this article we will be connecting to MySQL using ODBC though the MySQL ODBC Connector. This is not a part of .NET and needs installing but all the other functionality that you need is contained within the .NET framework.

There are two ways that you can use the ODBC connector. You can set up a user or system DSN within Windows Control Panel and then simply reference the DSN name from within the program, or you can supply all the connection details within the program.

Which method you use will depend on the deployment scenario but obviously if you hard code the parameters into the program then you can not update the connection without re-compiling.

The code in the example program will specify the parameters, but if you want to use a DSN then see the example just below.

Classes

There are three main classes that we need:

OdbcConnection the connection to the database
OdbcCommand a SQL statement
OdbcDataReader works with an OdbcCommand where you need to iterate over a set of results e.g. from a SELECT statement

The OdbcConnection object is created (and importantly opened) first:


	OdbcConnection db = new OdbcConnection("DSN=dsn_name");

	db.Open();

Then the other two classes have that object passed to them so they know which connection to operate on:


	OdbcCommand update = new OdbcCommand("UPDATE people SET age=10", db);

	update.ExecuteNonQuery();


	OdbcCommand query = new OdbcCommand("SELECT name FROM people", db);

	OdbcDataReader queryResults = query.ExecuteReader();

Finally the database connection should be closed once it’s finished with:


	db.Close();

Example Program

Download the code here.

The code below puts all of the above together and demonstrates how to connect to the database and retrieve some records which are then used to create person objects in the program.

Creating a class to match the results from the database in this way seems to me to be the easiest way to manage transactions between MySQL and C#. Once the objects are created in your program it’s very easy to interact with them, create collections of them and so on.

You have to be careful about writing the translation functions to retrieve and update the data though, especially since MySQL will let you null values that C# won’t, such as int types.

MySQL also does not have a bool value (as such) so it is necessary to handle the binary TINYINT values and set any bool types to true or false accordingly, which can be seen in the example program.

Other problems exist as well for example translating MySQL’s DATE type into C# DateTime objects, but most of these can be handled with a few small functions.

I have not used a lot of error handling in the example in order to keep it as compact and easy to understand as possible! In real application any sort of database connectivity code should be well wrapped with exception handling.

If you were to run the code on a machine other than the MySQL server then localhost would obviously need changing to the server name or IP. If you are using a different version of the MySQL ODBC driver to 5.1 then the DRIVER parameter would also need changing.

using System;
using System.Collections.Generic;
using System.Data.Odbc;

namespace mysql_example
{
    class Program
    {
        // globals

        public static List<person> people = new List<person> { };
        public static OdbcConnection db   = new OdbcConnection();

        public static String conString =
            "DRIVER={MySQL ODBC 5.1 Driver};" +
            "SERVER=localhost;"  +
            "DATABASE=csharpdb;" +
            "USER=csharpuser;"   +
            "PASSWORD=password;";

        // main function

        static void Main(string[] args)
        {
            // update and show users

            refreshPeople();
            listPeople();

            // promote Lister and kill Kryten

            Console.WriteLine("Promoting Lister and taking Kryten out of service...");

            db = new OdbcConnection(conString);
            db.Open();

            String sql = @"UPDATE people SET jobTitle=""Chef"" WHERE name=""Lister""";
            OdbcCommand update = new OdbcCommand(sql, db);
            update.ExecuteNonQuery();

            sql = @"DELETE FROM people WHERE name=""Kryten""";
            update = new OdbcCommand(sql, db);
            update.ExecuteNonQuery();

            db.Close();

            // update and show users

            refreshPeople();
            listPeople();

            Console.Read();
        }

        public static void refreshPeople()
        {
            people.Clear();

            // connect to database

            db = new OdbcConnection(conString);
            db.Open();

            // create and execute query

            String         sql          = "SELECT id, name, employed, jobtitle, age FROM people";
            OdbcCommand    query        = new OdbcCommand(sql, db);
            OdbcDataReader queryResults = query.ExecuteReader();

            while (queryResults.Read())
            {
                person newPerson = new person();

                try { newPerson.id = queryResults.GetInt16(0); }
                catch (InvalidCastException) { }

                try { newPerson.name = queryResults.GetString(1); }
                catch (InvalidCastException) { }

                int isEmployed = 0;
                isEmployed = queryResults.GetInt16(2);

                if (isEmployed == 1) newPerson.employed = true;
                else                 newPerson.employed = false;

                try { newPerson.jobTitle = queryResults.GetString(3); }
                catch (InvalidCastException) { }

                try { newPerson.age = queryResults.GetInt16(4); }
                catch (InvalidCastException) { }

                people.Add(newPerson);
            }

            // close connection

            db.Close();
        }

        public static void listPeople()
        {
            Console.WriteLine("\nJMC Personnel\n");
            Console.WriteLine("==========================================\n");

            foreach (person p in people)
                p.getDetails();

            Console.WriteLine();
        }
    }

    public class person
    {
        // class to match the SQL person table

        public int    id       { get; set; }
        public String name     { get; set; }
        public bool   employed { get; set; }
        public String jobTitle { get; set; }
        public int    age      { get; set; }

        public person() { }

        public String empStatus()
        {
            return this.employed ? "employed" : "not employed";
        }

        public void getDetails()
        {
            Console.WriteLine(String.Format("{0,-10} {1,-4} {2,-15} {3,-15}", 
                name, age, jobTitle, empStatus()));
        }
    }
}

Example Output

Here is the output: