This tutorial explains how to connect to a MySQL database using the Connector/Net ADO.NET driver.

In this mini tutorial I will teach you how to get connected to a MySQL database. This is really quite simple. All you need to do is download Connector/Net which is a fully-managed ADO.NET driver written in 100% pure C#. Download the installer and install Connector/Net. After installation load your C# IDE. Begin a new console project.

The first thing you need to do is add a reference to your project. Open the “Add Reference” dialog box. Under the .Net tab scroll down to MySQL.Data and add this reference to your project. Before we start with the actual code we need to add two namespaces. Add the following namespaces.

using MySql.Data.MySqlClient;
using MySql.Data.Types;

Finally it’s now time to write some code. When connecting to any database you usually need to set up a provider. This provder is of type string and simply consists of information such as database to connect to, username, password and url/name of the machine in which the database is hosted. This provider sring is different for different databases. The provider string needed to connect to a MySQL database is listed below.

string strProvider = "Data Source=" + host + ";Database=" + database + ";User ID=" + user + ";Password=" + password;

In the provider string above we simply supply the data source, which is the url/ip/name of the computer which the database is hosted on. We also supply the database name to connected to and the username and password.

If your using a default MySQL installation, the username should be “root” and the password should be the password you used in the installation process.

After setting up a provider, you need to create a connection to the database. You do this by using the MySqlConnection object.

When creating an instance of MySqlConnection, you supply the provider in it’s constructor.

MySqlConnection mysqlCon = new MySqlConnection(strProvider);

We then use the Open() method of mysqlCon object to open a connection to the database.

MySqlConnection mysqlCon = new MySqlConnection(strProvider);
mysqlCon.Open();

Now that the connection is open, you need to query the database. You need to send an SQL statement to get the results from the database. This is done using the MySqlCommand object. In the constructor of the MySqlCommand you supply an SQL statement and also the connection object.

string strSQL = "SELECT * FROM [Your Table Here]";
MySqlCommand mysqlCmd = new MySqlCommand(strSQL,mysqlCon);

So now you can send an SQL statement to the database. But you need a way to store the records, for this you use the MySqlDataReader object.

MySqlDataReader mysqlReader = mysqlCmd.ExecuteReader();

Now the MySqlDataReader object will contain all the records from the database. The MySqlDataReader object is a read only onject, which allows you to quickly get records out of a database table. It can not be used to update a database table.

To get records from the MySqlDataReader object you use a while loop. The loop uses the MySqlDataReader objects Read() method, to get the data for each row.

while (mysqlReader.Read())
{
Console.WriteLine(mysqlReader.GetInt32(0) + "\t" + mysqlReader.GetString(1) + "\t" + mysqlReader.GetString(2));
}

Finally you use the appropriate methods of the MySqlDataReader object to get the data from each column. For example the first column is returned as an Integer using the GetInt32() method, while the second and third columns return string data.