Connect to MySQL with Dapper using C#
Dapper is a lightweight object-relational mapping (ORM) library designed for the Microsoft .NET platform that allows developers to easily interact with databases by executing SQL queries and mapping the results directly to C# objects. Dapper offers full control over SQL, making it an ideal choice for high-performance applications where speed and efficiency are critical.
Written in C#, Dapper is widely used for data access in C# projects due to its simplicity and performance. Its capabilities can be further extended by integrating solutions like dotConnect for MySQL.
This tutorial will explain how to use Dapper for data operations in .NET applications. We'll walk through creating a Dapper model, building a console application, and implementing basic CRUD (Create, Read, Update, Delete) operations.
Why dotConnect for MySQL?
dotConnect for MySQL is the data provider that suits ideally for all MySQL and MariaDB-related operations. Its numerous features enhance functionality, performance, and ease of development for .NET developers working with MySQL databases.
Download and activate dotConnect for MySQL
30-day free trial version
Download and install dotConnect for MySQL directly on your machine, or install the Devart.Data.MySQL NuGet package.
No license key is required, and you can start exploring the product immediately.
Full version
After purchasing the full version, go to your profile's Licenses page. Choose your product and click Details. Here, you'll find the license details and the Activation Key.
To activate a connection in your application, add the License Key to your connection string.
Create a .NET project
- 1. Open Visual Studio and select Create a new project.
- 2. Give your project a name. For this tutorial, we created a project called MySqlDapper.
- 3. Right-click your project in Solution Explorer and select Manage NuGet Packages.
- 4. Search for and install the following packages:
Check MySQL database objects
To connect to MySQL using the built-in Data Explorer, click Tools and select Connect to Database.
Choose MySQL as the data source, enter your server details and credentials, and click Connect.
Once connected, you can browse tables, execute queries, and manage data directly within the Data Explorer.
Connect to the database and retrieve data
The application will connect to the MySQL database, execute the query, and display the data from the Customer table in the console.
Create Customers.cs (model class)
This class maps to the Customers table:
namespace DapperMySqlExample {
public class Customers {
public int Customer_Id { get; set; }
public string First_Name { get; set; }
public string Last_Name { get; set; }
public string Email { get; set; }
public string Phone { get; set; }
public string Address { get; set; }
}
}
Create DatabaseConnection.cs (connection strings)
This class keeps connection details separate for better maintainability.
namespace DapperMySqlExample {
public static class DatabaseConnection {
public static string ConnectionString = "Host=127.0.0.1;UserId=TestUser;Password=TestPassword;Database=Sakila;License Key=**********";
}
}
Add the below code to your Program.cs file.
class Program {
static void Main(string[] args) {
using(IDbConnection db = new MySqlConnection(DatabaseConnection.ConnectionString)) {
try {
db.Open();
Console.WriteLine("Connection to MySQL successful!");
// Fetch and display customers
DisplayCustomers(db);
} catch (Exception ex) {
Console.WriteLine($"Connection failed: {ex.Message}");
}
}
}
private static void DisplayCustomers(IDbConnection db) {
Console.WriteLine("\nFetching first 10 customers...");
string selectQuery = "SELECT customer_id AS Customer_Id, first_name AS First_Name, last_name AS Last_Name, email, phone, address FROM Customers ORDER BY customer_id LIMIT 10;";
var customers = db.Query<Customers>(selectQuery);
foreach(var customer in customers) {
Console.WriteLine($"{customer.Customer_Id}: {customer.First_Name} {customer.Last_Name} - {customer.Email}, {customer.Phone}, {customer.Address}");
}
}
}
Update the connection string
Replace the placeholders in the connection string with your actual MySQL database credentials.
Run the application
Build and run your application by pressing F5 or selecting Start from the menu.
Batch insert new records using Dapper
We can insert multiple records into the database in a single transaction to improve performance and ensure atomicity.
The code for this operation is as follows:
static void Main(string[] args) {
using(IDbConnection db = new MySqlConnection(DatabaseConnection.ConnectionString)) {
try {
db.Open();
Console.WriteLine("Connection to MySQL successful!");
// Batch insert new customers
InsertCustomers(db);
// Fetch and display the newly inserted customers
DisplayInsertedCustomers(db);
} catch (Exception ex) {
Console.WriteLine($"Connection failed: {ex.Message}");
}
}
}
private static void InsertCustomers(IDbConnection db) {
Console.WriteLine("\nInserting 10 new customers...");
var newCustomers = new List<Customers>
{
new Customers {
First_Name = "Ethan", Last_Name = "Harris", Email = "[email protected]", Phone = "123-111-7890", Address = "321 Main St"
},
new Customers {
First_Name = "Sophia", Last_Name = "Clark", Email = "[email protected]", Phone = "234-222-7890", Address = "432 Elm St"
},
new Customers {
First_Name = "Mason", Last_Name = "Lewis", Email = "[email protected]", Phone = "345-333-7890", Address = "543 Oak St"
},
new Customers {
First_Name = "Olivia", Last_Name = "Walker", Email = "[email protected]", Phone = "456-444-7890", Address = "654 Maple St"
},
new Customers {
First_Name = "Liam", Last_Name = "Hall", Email = "[email protected]", Phone = "567-555-7890", Address = "765 Pine St"
},
new Customers {
First_Name = "Isabella", Last_Name = "Allen", Email = "[email protected]", Phone = "678-666-7890", Address = "876 Birch St"
},
new Customers {
First_Name = "Noah", Last_Name = "Young", Email = "[email protected]", Phone = "789-777-7890", Address = "987 Cedar St"
},
new Customers {
First_Name = "Ava", Last_Name = "King", Email = "[email protected]", Phone = "890-888-7890", Address = "198 Walnut St"
},
new Customers {
First_Name = "Lucas", Last_Name = "Wright", Email = "[email protected]", Phone = "901-999-7890", Address = "209 Chestnut St"
},
new Customers {
First_Name = "Mia", Last_Name = "Scott", Email = "[email protected]", Phone = "012-000-7890", Address = "310 Spruce St"
}
};
string insertQuery = @"
INSERT INTO Customers(first_name, last_name, email, phone, address)
VALUES(:First_Name,:Last_Name,:Email,:Phone,:Address);
";
using(var transaction = db.BeginTransaction()) {
try {
db.Execute(insertQuery, newCustomers, transaction);
transaction.Commit();
Console.WriteLine("Batch insert successful!");
} catch (Exception ex) {
transaction.Rollback();
Console.WriteLine($"Insert failed: {ex.Message}");
}
}
}
private static void DisplayInsertedCustomers(IDbConnection db) {
Console.WriteLine("\nFetching last 10 inserted customers...");
string selectQuery = "SELECT customer_id AS Customer_Id, first_name AS First_Name, last_name AS Last_Name, email, phone, address FROM Customers ORDER BY customer_id DESC LIMIT 10;";
var customers = db.Query<Customers>
(selectQuery);
foreach(var customer in customers) {
Console.WriteLine($"{customer.Customer_Id}: {customer.First_Name} {customer.Last_Name} - {customer.Email}, {customer.Phone}, {customer.Address}");
}
}
We can see the results in our application:
Update MySQL data using Dapper
Let us add a new method to update customer records and display them updated:
static void Main(string[] args) {
using(IDbConnection db = new MySqlConnection(DatabaseConnection.ConnectionString)) {
try {
db.Open();
Console.WriteLine("Connection to MySQL successful!");
// Batch update customers
UpdateCustomers(db);
// Fetch and display updated customers
DisplayUpdatedCustomers(db);
} catch (Exception ex) {
Console.WriteLine($"Connection failed: {ex.Message}");
}
}
}
private static void UpdateCustomers(IDbConnection db) {
Console.WriteLine("\nUpdating customers with IDs between 23 and 32...");
var updatedCustomers = new List<Customers>
{
new Customers {
Customer_Id = 23, First_Name = "UpdatedA", Last_Name = "Smith", Phone = "999-111-1111", Address = "New Address A"
},
new Customers {
Customer_Id = 24, First_Name = "UpdatedB", Last_Name = "Johnson", Phone = "999-222-2222", Address = "New Address B"
},
new Customers {
Customer_Id = 25, First_Name = "UpdatedC", Last_Name = "Brown", Phone = "999-333-3333", Address = "New Address C"
},
new Customers {
Customer_Id = 26, First_Name = "UpdatedD", Last_Name = "White", Phone = "999-444-4444", Address = "New Address D"
},
new Customers {
Customer_Id = 27, First_Name = "UpdatedE", Last_Name = "Davis", Phone = "999-555-5555", Address = "New Address E"
},
new Customers {
Customer_Id = 28, First_Name = "UpdatedF", Last_Name = "Miller", Phone = "999-666-6666", Address = "New Address F"
},
new Customers {
Customer_Id = 29, First_Name = "UpdatedG", Last_Name = "Wilson", Phone = "999-777-7777", Address = "New Address G"
},
new Customers {
Customer_Id = 30, First_Name = "UpdatedH", Last_Name = "Moore", Phone = "999-888-8888", Address = "New Address H"
},
new Customers {
Customer_Id = 31, First_Name = "UpdatedI", Last_Name = "Taylor", Phone = "999-999-9999", Address = "New Address I"
},
new Customers {
Customer_Id = 32, First_Name = "UpdatedJ", Last_Name = "Anderson", Phone = "999-000-0000", Address = "New Address J"
}
};
string updateQuery = @"
UPDATE Customers
SET first_name =:First_Name, last_name =:Last_Name, phone =:Phone, address =:Address
WHERE customer_id =:Customer_Id;
";
using(var transaction = db.BeginTransaction()) {
try {
db.Execute(updateQuery, updatedCustomers, transaction);
transaction.Commit();
Console.WriteLine("Batch update successful!");
} catch (Exception ex) {
transaction.Rollback();
Console.WriteLine($"Update failed: {ex.Message}");
}
}
}
private static void DisplayUpdatedCustomers(IDbConnection db) {
Console.WriteLine("\nFetching updated customers (IDs 23-32)...");
string selectQuery = "SELECT customer_id AS Customer_Id, first_name AS First_Name, last_name AS Last_Name, phone, address FROM Customers WHERE customer_id BETWEEN 23 AND 32;";
var customers = db.Query<Customers>
(selectQuery);
foreach(var customer in customers) {
Console.WriteLine($"{customer.Customer_Id}: {customer.First_Name} {customer.Last_Name} - {customer.Phone}, {customer.Address}");
}
}
See the output in the application:
Delete MySQL data using Dapper
Assume we need to delete some records from our database. In the below example, we will delete all customers records with customer_id values between 23 and 32. The code we use is:
static void Main(string[] args) {
using(IDbConnection db = new MySqlConnection(DatabaseConnection.ConnectionString)) {
try {
db.Open();
Console.WriteLine("Connection to MySQL successful!");
// Batch delete customers
DeleteCustomers(db);
} catch (Exception ex) {
Console.WriteLine($"Connection failed: {ex.Message}");
}
}
}
private static void DeleteCustomers(IDbConnection db) {
Console.WriteLine("\nDeleting customers with IDs between 5 and 12...");
string deleteQuery = "DELETE FROM Customers WHERE customer_id BETWEEN 5 AND 12;";
using(var transaction = db.BeginTransaction()) {
try {
int rowsDeleted = db.Execute(deleteQuery, transaction: transaction);
transaction.Commit();
Console.WriteLine($"Batch delete successful! {rowsDeleted} customers deleted.");
} catch (Exception ex) {
transaction.Rollback();
Console.WriteLine($"Delete failed: {ex.Message}");
}
}
}
The application output proves that the operation is successful.
