How to Connect to a MySQL database in C#

This package provides classes that implement standard ADO.NET interfaces, such as IDbConnection.

Step 1: Add the MySql.Data Package to Your Project

To get started, add the MySql.Data package to your project. This can be done using the Package Manager Console in Visual Studio by running the following command:

Install-Package MySql.Data

Step 2: Connect to the MySQL Database and Run a Query

Now you can use the MySqlConnection class to connect to your MySQL database. You just need to provide a connection string and then execute a query. Here’s an example of retrieving the total number of products:

using MySql.Data.MySqlClient;

var connectionString = "server=yourserver;database=storedb;user id=dbuser;password=secretpass;";

using (var connection = new MySqlConnection(connectionString))
{
    connection.Open();
    
    using var command = new MySqlCommand("SELECT COUNT(*) FROM products", connection);
    var productCount = command.ExecuteScalar();

    Console.WriteLine($"There are {productCount} products in the store.");
}

Important: The connection is automatically closed by placing the MySqlConnection object inside a using block.

The output will look like:

There are 150 products in the store.

Using Dapper with MySQL

When working with queries, you often need to pass query parameters and then manually map the results to classes. This process can be cumbersome.

Dapper simplifies this by automatically handling many of these tasks for you. It extends the IDbConnection interface, and since MySqlConnection implements this interface, you can easily use Dapper in your project.

Step 1: Add the Dapper Package to Your Project

To use Dapper, install it via the Package Manager Console with the following command:

Install-Package Dapper

Step 2: Simplify Database Queries with Dapper

Now, you can create a connection to the MySQL database and use Dapper to execute a query and automatically map the results to a class, such as a Product class:

using MySql.Data.MySqlClient;
using Dapper;

var connectionString = "server=yourserver;database=storedb;user id=dbuser;password=secretpass;";

using (var connection = new MySqlConnection(connectionString))
{
    var products = connection.Query<Product>("SELECT * FROM products");
}

Note: With Dapper, there’s no need to manually call connection.Open(). Dapper handles that internally for you.

This is much simpler than manually processing query results with MySqlCommand.ExecuteReader() and looping through rows with a MySqlDataReader to map each column to a property of the Product class.

This method of working with MySQL makes your code cleaner and easier to maintain, especially as the complexity of your queries increases.