How to Access MySQL with C#

Using a MySQL Database with a C# Windows Application

May 5, 2009

C# and MySQL make a very powerful combination, and it's all made possible by using the MySQL .NET connector

Many C# applications use a database for the storage of any information that they need, and one of the most commonly used databases is MySQL. That's not only because of MySQL's reliability but it's also because the contents of a MySQL database can be easily accessed and manipulated from C#.

Obtaining the MySQL .Net Connector

The first step to using the MySQL .Net Connector is to download its installation program from the MySQL Connector/Net web page. Once the programmer has run the downloaded file then the connector will be available for any C# projects.

Preparing the MySQL Database

No additional work is required for a MySQL database to work with the MySQL .NET connector except, of course, for the database and its contents to be created. For example:

Create database aec;

Grant insert, update, select, delete on aec.* to 'aec_user'@'localhost' identified by 'aec';

Create table aec.samples (

id int auto_increment,

description varchar(255),

site_id int,

received datetime,

processed datetime,

primary key (id));

Insert into aec.samples (description, site_id, received) values ('clay desposit', 27, now());

Insert into aec.samples (description, site_id, received) values ('burial urn', 103, now());

Adding the MySQL Connector to the C# Project as a Reference

The MySQL .NET connector will not automatically be available to a new C# project. In order for that to happen the programmer must add it as a reference. How this happens will depend on the IDE (Integrated Design Environment) being used.

For example, in SharpDevelop the programmer needs to click on "Projects" and then "References". They will then be able to select "MySql.Data" from the list of available references. It can then be accessed from C# project's code.

Loading the MySQL Connector with C#

With the MySQL .NET connecter to be included in the C# project, the next step is to declare the reference as part of the code:

using MySql.Data.MySqlClient;

In this example a dataset is to be used. Therefore the System Data object must be accessed:

using System;

using System.Data;

using System.Windows.Forms;

using System.Drawing;

Using MySQL Data in a Windows Application Form

public class MainForm : Form {

private MySqlConnection connection = new MySqlConnection ();

private MySqlDataAdapter data = new MySqlDataAdapter ();

DataGrid gridInfo = new DataGrid();

The MySqlConnection object uses a connection string and the "open" method to make a connection to the MySQL database:

public static void Main () {

Application.Run(new MainForm ());

}

public MainForm() {

connection.ConnectionString =

"server=localhost;"

+ "database=aec;"

+ "uid=aec_user;"

+ "password=aec;";

connection.Open ();

MySqlCommand command = connection.CreateCommand ();

command.CommandText = "select * from samples";

And then the results loaded into a data set (which, in this example, is given the name "sample_data"):

data.SelectCommand = command;

DataSet dataset = new DataSet();

data.Fill(dataset,"sample_data");

gridInfo.DataSource = dataset;

gridInfo.DataMember = "sample_data";

gridInfo.Dock = DockStyle.Fill;

this.Controls.Add (gridInfo);

}

If the form is compiled at this point then it will contain a grid containing the results of the MySQL query.

Closing the Connection

A MySQL connection ends by using the "close" method. Here the connection is closed when the form is unloaded:

~MainForm() {

connection.Close();

}

And with just those few lines of code the MySQL database can be accessed and read, with the result of a query being used in the C# application.

The copyright of the article How to Access MySQL with C# in Windows Programming is owned by Mark Alexander Bain. Permission to republish How to Access MySQL with C# in print or online must be granted by the author in writing.