Connect to MySQL in .NET 5

To connect to MySQL databases in .NET 5, ASP.NET Core and .NET applications, we can utilize MySQL.Data library published by MySQL. This article provides step by step guide of connecting to MySQL using C# as programming language. 

.NET or .NET Core SDKs are required for the following steps. 

For the sample table used in the following example, it was created as part of this tutorial: Python: Load Data from MySQL. Refer to that article to find out the schema information of the sample table. 

Create a test project

Follow these steps to create a test project that references MySQL.Data library. 

1. Create a project using the following command and the change directory to the created folder:
   

   mkdir dotnetcore-mysql
   cd dotnetcore-mysql

2. Run the following command to create a console project:
   

   dotnet new console

   This command will create a project that contains a Program.cs file with the following content:
   

   using System;
   
   namespace dotnetcore_mysql
   {
       class Program
       {
           static void Main(string[] args)
           {
               Console.WriteLine("Hello World!");
           }
       }
   }

3. Add a package reference to MySQL.Data library using the following command:
   

   dotnet add package MySQL.Data

   Version 8.0.23 is picked up when this article is published. 

4. Reference types in MySQL.Data.MySqlClient namespace by adding the following line to the top section of Program.cs file:
   

   using MySql.Data.MySqlClient;

5. Now create a static function named ConnectToMySQL in the Program class:
   

   static void ConnectToMySQL()
           {
               MySqlConnection conn = null;
               var sb = new MySqlConnectionStringBuilder
               {
                   Server = "127.0.0.1",
                   UserID = "hive",
                   Password = "hive",
                   Port = 10101,
                   Database = "test_db"
               };
   
               try
               {
                   Console.WriteLine(sb.ConnectionString);
                   conn = new MySqlConnection(sb.ConnectionString);
                   conn.Open();
   
                   var cmd = conn.CreateCommand();
                   cmd.CommandText = "select * from test_table";
                   var reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                   while (reader.Read())
                   {
                       Console.WriteLine("id={0}, value={1}", reader.GetInt32("id"), reader.GetString("value"));
                   }
               }
               catch (MySqlException ex)
               {
                   Console.Write(ex.Message);
               }
               finally
               {
                   if (conn != null)
                       conn.Close();
               }
           }

   This function establish a connection using a connection string builder; the connection is then used to create a command that executes a SELECT statement. 

   The generated connection string is like the this:
   

   server=127.0.0.1;user id=hive;password=hive;port=10101;database=test_db

   You can directly create MySql connection objection using the above connection string. 
   

6. Invoke this function in Main function:
   

   static void Main(string[] args)
   {
      ConnectToMySQL();
   }

Now Program.cs file looks like the following:

using System;
using MySql.Data.MySqlClient;

namespace dotnetcore_mysql
{
    class Program
    {
        static void Main(string[] args)
        {
            ConnectToMySQL();
        }

        static void ConnectToMySQL()
        {
            MySqlConnection conn = null;
            var sb = new MySqlConnectionStringBuilder
            {
                Server = "127.0.0.1",
                UserID = "hive",
                Password = "hive",
                Port = 10101,
                Database = "test_db"
            };

            try
            {
                Console.WriteLine(sb.ConnectionString);
                conn = new MySqlConnection(sb.ConnectionString);
                conn.Open();

                var cmd = conn.CreateCommand();
                cmd.CommandText = "select * from test_table";
                var reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    Console.WriteLine("id={0}, value={1}", reader.GetInt32("id"), reader.GetString("value"));
                }
            }
            catch (MySqlException ex)
            {
                Console.Write(ex.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
        }
    }
}

Build and run

Build and then run the application using the following commands:

dotnet build
dotnet run

The results looks like the following:

dotnet run
server=127.0.0.1;user id=hive;password=hive;port=10101;database=test_db
id=0, value=Record 0
id=1, value=Record 1
id=2, value=Record 2
id=3, value=Record 3
id=4, value=Record 4