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();
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.