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
Create a Console Application
add dependency
MySql.Data package is needed

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

Current db with user table and a record having 4 columns
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.


0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x