MySQL Connection in C# (Console Application Example)

: 11047

Last Updated : 22/06/2022

Posted By :- vikas_jk

In previous article, I have explained about connecting to SQL server database in C# and have provided SQL server connection string examples, now in this article, I am providing working sample to connect to MySQL database in C# using Console application, you can use same code to connect to MySQL in ASP.NET, if needed.

Download and Install MySQL before using it
Connecting to MySQL in C# using Visual Studio
To Insert New Row in MySQL database using C#

Download and Install MySQL before using it

Before we begin, I hope you have downloaded and installed and MySQL in your PC, if not you can download and install MySQL first.

Once you have downloaded MySQL Installer from above link, open it, you will see windows like below

Select "Full" option and then click on "Next"

Then click on "Execute" in the next screen, to download and install all set up.

Note: It must install everything, including MySQL Server and you should also create default Username/Password to login into MySQL.

Once you have configured MySQL, download and install MySQL Workbench which help us to check data in our MySQL database easily. You will have to use above created Username/Password to login into MySQL database using MySQL workbench, you can connect to your MySQL and check sample database as shown in the below Gif Image.

Table which we will be using to show data, looks like below

Connecting to MySQL in C# using Visual Studio

Step 1: Once you have downloded and Installed MySQL with all of the above steps, let's create a new Console Application project in Visual Studio, so open your Visual Studio, navigate to File-> New -> Project ->Select "Windows desktop" from left-pane and "Console Application(.NET framework)" from right-pane, give a name to your project and Click "OK"

Let's Visual Studio generate the Console application template.

Step 2: Once Visual Studio Completes generation of template, you need add Reference of MySQL in your Visual Studio project, so navigate to "Solution Explorer" -> Right-Click "Add Reference"

Search for "MySQL" and add "MySQL.Data"

That's it, we are done, we need to now write C# code to connect to MySQL, which is as shown below

using MySql.Data.MySqlClient;
using System;

namespace MySQLConnectionExample
{
    class Program
    {
        static void Main(string[] args)
        {
            //your MySQL connection string
            string connStr = "server=localhost;user=root;database=sakila;port=3306;password=your_password";

            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();

                //SQL Query to execute
                //selecting only first 10 rows for demo
                string sql = "select * from sakila.actor limit 0,10;";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();

                //read the data
                while (rdr.Read())
                {
                    Console.WriteLine(rdr[0] + " -- " + rdr[1] + " -- " + rdr[2]);
                }
                rdr.Close();
            }
            catch (Exception err)
            {
                Console.WriteLine(err.ToString());
            }

            conn.Close();
            Console.WriteLine("Connection Closed. Press any key to exit...");
            Console.Read();
        }
    }
}

Output:

To Insert New Row in MySQL database using C#

To Insert a new row in database, we will pass all rows values in SQL query and then execute Query, here is the complete C# code using Console Application.

using MySql.Data.MySqlClient;
using System;

namespace MySQLConnectionExample
{
    class Program
    {
        static void Main(string[] args)
        {
            //your MySQL connection string
            string connStr = "server=localhost;user=root;database=sakila;port=3306;password=your_password";

            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();

                //SQL Query to execute
                //insert Query
                // we are inserting actor_id, first_name, last_name, last_updated columns data
                string sql = "INSERT INTO sakila.actor VALUES ('202','First Name Actor test','Last Name Actor test', '2020-11-05 04:34:33')";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.ExecuteNonQuery();

                
            }
            catch (Exception err)
            {
                Console.WriteLine(err.ToString());
            }

            conn.Close();
            Console.WriteLine("Connection Closed. Press any key to exit...");
            Console.ReadKey();
        }
    }
}

Output:

As you can see out new row is added in the database, if we compare "Insert" code with "Fetch" Query code, this C# code is changed

 //SQL Query to execute
                //insert Query
                // we are inserting actor_id, first_name, last_name, last_updated columns data
                string sql = "INSERT INTO sakila.actor VALUES ('202','First Name Actor test','Last Name Actor test', '2020-11-05 04:34:33')";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.ExecuteNonQuery();

Rest of the code remains same, i have explained most of the code using comments, so please read it carefully to understand each line of code.