Connecting to MySQL from Visual C#

• Download source - 4 Kb

Introduction

The main purpose of this article is to share code and to present some basic principles of creating and connecting to a MySQL database with C#. You may find many examples of how to connect to a MySQL database using ODBC driver, but here we will use MySQL Connector/NET 5.1.7. The DBMS used is MySQL Server 5.1.31 Community Edition.

As you may already be aware, MySQL is one of the most widely used open source relational database management systems available on the internet. It's fast, easy to use and free. It can be downloaded here. If this is your first time downloading MySQL, please also download MySQL GUI Tools and, most importantly, MySQL Connector/NET 5.1 which will allow you to connect from C# to any MySQL database that you create. If you need assistance with the installation, please follow the instructions provided in the website on how to install the product.

Creating a Database in MySQL

I'll briefly explain how to create a simple database. First, start the MySQL Command Line Client and enter the password that you provided during the installation process. After that, you will see the mysql> command prompt in the window. Try entering the following command and see what happens:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> 

This is instructing MySQL to list all the databases for which your user session has privileges. Now let's enter the following command to create the database:

mysql> CREATE DATABASE shop;
Query OK, 1 row affected (0.00 sec)

mysql> USE shop;
Database changed
mysql> CREATE TABLE article (
    ->   article_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   article_name VARCHAR(50),
    ->   article_price DOUBLE(16, 2),
    ->   PRIMARY KEY (article_id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> DESCRIBE article;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| article_id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| article_name  | varchar(50)      | YES  |     | NULL    |                |
| article_price | double(16,2)     | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
3 rows in set (0.05 sec)

mysql> INSERT INTO article (article_name, article_price)
    -> VALUES ('PhotoPC XYZ Camera', 899.00);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM article;
+------------+--------------------+---------------+
| article_id | article_name       | article_price |
+------------+--------------------+---------------+
|          1 | PhotoPC XYZ Camera |        899.00 |
+------------+--------------------+---------------+
1 row in set (0.00 sec)

mysql> 

Some points to take into consideration here: after creating the database, it is not selected by default; we do that explicitly with the USE <database-name> statement. We enter one command at a time.

For the table creation, the convention I've used here is: (1) singular names for tables; (2) lower case characters for column names; (3) words in column names are separated with underlines; (4) every field is prefixed with the name of the table it belongs to and (5) the primary key is named just as the table name suffixed by "_id". You will also see other commands that I've used to describe the table structure, insert data into the table and query the table.

Stored procedures in MySQL do not allow the USE statement, as they operate only in the database in which they are created. The SELECT DATABASE() statement is to make sure that we have the shop database in use. Actually, this is not selecting the database for use. We also need to specify a delimiter when writing stored procedures because lines are terminated with semicolons.

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| shop       |
+------------+
1 row in set (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE append_data
    ->   (IN param_name VARCHAR(50), IN param_price DOUBLE(16, 2))
    ->   BEGIN
    ->     DECLARE var_name VARCHAR(50);
    ->     DECLARE var_price DOUBLE(16, 2);
    ->     SET var_name = param_name;
    ->     SET var_price = param_price;
    ->     INSERT INTO article (article_name, article_price)
    ->       VALUES (var_name, var_price);
    ->   END; $$
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;
mysql> CALL append_data ('ABC 1200 Scanner', 299.00);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM article;
+------------+--------------------+---------------+
| article_id | article_name       | article_price |
+------------+--------------------+---------------+
|          1 | PhotoPC XYZ Camera |        899.00 |
|          2 | ABC 1200 Scanner   |        299.00 |
+------------+--------------------+---------------+
2 rows in set (0.00 sec)

mysql>

Now we are ready to start coding in C#. In the next section, I'll show you how I build the connection string to establish a connection to the database.

Connecting to the Database from C#

What will allow us to work with the data in any MySQL database from C# is a reference to the MySql.Data assembly, which is registered into the Global Assembly Cache after the MySQL Connector/NET installation. First, create a new Console Application project in Visual C#. You may call it MySQLDBConnection or whatever name you decide. Now in the Solution Explorer within the Visual C# IDE, right click on the References folder and choose Add Reference... as shown below:

In the Add Reference dialog box that appears, select the MySQL.Data item from the list:

Now, after doing that, we must add the using MySql.Data.MySqlClient statement to our code in the Visual C# IDE:

Below you will find the basic lines of code needed to perform the connection to any MySQL database from C#:

using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;

namespace MySQLDBConnection
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();

            connBuilder.Add("Database", "shop");
            connBuilder.Add("Data Source", "localhost");
            connBuilder.Add("User Id", "root");
            connBuilder.Add("Password", "masterkey");

            MySqlConnection connection = new MySqlConnection(connBuilder.ConnectionString);

            MySqlCommand cmd = connection.CreateCommand();

            connection.Open();

            // Here goes the code needed to perform operations on the
            // database such as querying or inserting rows into a table

            connection.Close();
        }
    }
}

Notice that I've decided to use the MySqlConnectionStringBuilder class instead of putting all the connection items into a single string. This promotes better readability and maintainability of your code.

First, we start by adding a reference to the MySql.Data.MySqlClient namespace in our code. Then we create a MySqlConnectionStringBuilder instance and we add pairs of name/value items for the database name, data source, user ID and password. After that, we create an instance of the MySqlConnection class and we pass the ConnectionString from our MySqlConnectionStringBuilder instance as a parameter.

Let's create the following two methods within the Program class. One is for reading the contents of the table we are working with and the other is for appending new data into it.

public static void QueryCommand(MySqlCommand cmd)
{
    cmd.CommandText = "SELECT * FROM article";
    cmd.CommandType = CommandType.Text;

    MySqlDataReader reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        Console.WriteLine(String.Format("{0}, {1}, {2}",
            reader.GetInt32(0), reader.GetString(1), reader.GetDouble(2))
        );
    }

    reader.Close();
}

public static void InsertCommand(MySqlCommand cmd, string name, double price)
{
    cmd.CommandText = "append_data";
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new MySqlParameter("param_name", name));
    cmd.Parameters.Add(new MySqlParameter("param_price", price));

    cmd.ExecuteNonQuery();
}

Now let's add some code between the connection.Open() and connection.Close() statements to perform some basic operations on the database.

InsertCommand(cmd, "MQ95 Flat Monitor", 399.00);
QueryCommand(cmd);

Points of Interest

I decided to write this article because many weeks ago I started seeking for something like this, but I couldn't find any example that could teach me what I needed to know. I must say that MySQL is easy to use in some ways, especially when compared to other freely available light versions of commercial DBMS out there. There are some improvements that can be done to the code I've presented here, but I wanted to keep this example as simple as possible.

History

Article originally written on November 30, 2007.