C# – Connect to a MySQL database

by

The simplest way to connect to a MySQL database in a .NET project is to use the MySql.Data package (from Oracle). It provides classes that implement the standard ADO.NET interfaces (such as IDbConnection).

First, add the MySql.Data package to your project (this is using View > Other Windows > Package Manager Console):

Install-Package MySql.Data
Code language: PowerShell (powershell)

Now use the MySqlConnection class to connect to the MySQL database (providing it with a connection string) and execute a query. Here’s an example:

using MySql.Data.MySqlClient;

var connectionString = "server=remoteserver;database=moviesdb;user id=sqluser;password=abc;";

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

    Console.WriteLine($"There are {movieCount} movies");
}
Code language: C# (cs)

Note: Close the connection automatically by creating the MySqlConnection object in a ‘using’ block.

This successfully connects, executes the query, and outputs:

There are 20 moviesCode language: plaintext (plaintext)

Using Dapper with MySQL

When you execute queries, you often have to add query parameters and then map the query results to one or more classes. This can be really tedious.

Dapper simplifies things by doing the tedious parts for you. It does this by extending the IDbConnection interface. Since MySqlConnection implements this interface, this means you can use Dapper with it. I’ll show an example.

First, add the Dapper package to your project:

Install-Package Dapper
Code language: PowerShell (powershell)

Now create the connection with MySqlConnection like usual. Then use Dapper to execute a query and map the results to Movie objects:

using MySql.Data.MySqlClient;
using Dapper;

var connectionString = "server=remoteserver;database=moviesdb;user id=sqluser;password=abc;";

using (var connection = new MySqlConnection(connectionString))
{
    var movies = connection.Query<Movie>("SELECT * FROM movies");
}
Code language: C# (cs)

Note: Don’t need to call connection.Open(). Dapper does that for you.

This is far simpler than mapping the query results manually (MySqlCommand.ExecuteReader() + looping over the result rows with MySqlDataReader and mapping each column to a Movie property).

Related Articles