How to Connect to a MySQL database in C#
By FoxLearn 3/7/2025 4:14:02 AM 72
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.
- How to use JsonConverterFactory in C#
- How to serialize non-public properties using System.Text.Json
- The JSON value could not be converted to System.DateTime
- Try/finally with no catch block in C#
- Parsing a DateTime from a string in C#
- Async/Await with a Func delegate in C#
- How to batch read with Threading.ChannelReader in C#
- How to ignore JSON deserialization errors in C#