Connect to MySQL database using C#

Problem

Connect to MySQL database using C# and Visual Studio. This blog post is about using the connection string to connect to a local MySQL database and read data and print them in the console.

Tool

The official IDE from Microsoft is used for this tutorial. Install Visual Studio.

Create

add dependency

Now let’s put the code to connect to the MySQL db instance and read some data. A local MySQL instance is running with the database and table already created.

using System;
using MySql.Data.MySqlClient;


namespace mysqlTest
{
    public class MySQLReader
    {
        public static void Main()
        {
            string connectionString = "server=localhost;user=root;database=test;";
            MySqlConnection connection = new MySqlConnection(connectionString);

            try
            {
                Console.WriteLine("Connecting to MySQL...");
                connection.Open();

                string sql = "SELECT * FROM users";
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                MySqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine(reader[0] + " -- " + reader[1]);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            connection.Close();
            Console.WriteLine("Done.");
        }
    }
}

The code above connects to a database named test and reads all the rows from the table users. It’s printing in the console the first two fields/ columns of each record. Also, the connection string above has no password provided for simplicity. The user: root didn’t setup password at installation phase of MySQL (which is discouraging).

Running

Executing the code above shows the following output in the console.

Correct!

Crash test

The reader[0] + ” — ” + reader[1] is shown above. If we were to run it with reader[5] or any index greater than 3 it should crash and the catch exception clause should catch it and print the error.

Let’s try updating the line below

Console.WriteLine(reader[0] + " -- " + reader[1]);

to

Console.WriteLine(reader[3] + " -- " + reader[4]);

and by running it..breaks!

As expected with IndexOutOfRangeException (4 columns are indexed from 0 to 3 inclusive).

Conclusion

In this post, the code simply connects and reads data from the table specified in a local database. This code can be adapted to your database and table names, also the server can be a remote IP address instead of localhost. One service that I found helpful for development purposes is the db4free.net website where you can create a free MySQL database and connect to it remotely.