C# .NET and MySQL Integration: Practical Data Operations with Dapper

Last weekend, I went camping with my spouse and child at the foothills of Uludağ. Sitting by the campfire, an idea for a project came to mind—I was going to establish a database connection with C#, but I quickly tested Dapper from my phone. I must have written the connection string incorrectly, because it kept giving errors, of course that was frustrating. Anyway, I didn’t cancel the trip, but after returning home, I fixed that mistake, and it worked 🙂

Yes, such small mistakes sometimes motivate us. Now, let’s get to the main topic; working with databases is unavoidable when developing with C#. I especially use .NET for REST APIs and love Dapper because it’s lighter compared to Entity Framework. Integrating with MySQL is practical, especially if you’re looking for an alternative to PostgreSQL. I’ll share my experiences and give some practical tips, not step-by-step.

Why choose MySQL, you might ask. It’s open source, free, performant, and handles large projects well. I used MySQL in a project when I worked in Bursa, and as data volume grew, no issues occurred. Dapper is a micro-ORM, it allows you to write SQL but automates the mapping. Ultimately, it results in cleaner code, with less abstraction.

Installing Dapper and Connection Settings

First, add Dapper via NuGet. Create a console app for .NET Core, then run Install-Package Dapper in Package Manager Console. You also need the MySQL connector, so install the MySqlConnector package. Store your connection string in appsettings.json, for example: “Server=localhost;Database=TestDB;Uid=root;Pwd=pass;”. In my experience, there are no issues testing locally, but don’t forget to add SSL in production.

Opening a connection is simple; use the IDbConnection interface. For example, within a using (var connection = new MySqlConnection(connectionString)) { } block, run your queries. Yes, it works very well. Once, I forgot to close the connection, which caused a memory leak, and I spent hours debugging. Anyway, the using statement automatically disposes of the connection, so no worries.

By the way, I just remembered, I had a similar issue last month while designing an electronic circuit; the connection kept disconnecting. Probably a cable issue, but the same logic applies in coding—keep the connection stable.

Simple CRUD Examples

Let’s look at adding data. Assume a Users table with id, name, email columns. To insert, you can do: connection.Execute(“INSERT INTO Users (Name, Email) VALUES (@Name, @Email)”, new { Name = “Ali”, Email = “ali@example.com” }); This is it. Dapper automatically binds parameters, and it’s safe against SQL injection. I used this in an API, adding 1000 records in seconds, performance was excellent.

For reading, there’s the Query method. var users = connection.Query(“SELECT * FROM Users WHERE Name = @Name”, new { Name = “Ali” }).ToList(); Then you loop through the list. You can combine with LINQ for better syntax, but Dapper encourages raw SQL. I remember writing a join query in one project, which became a bit complex, so keeping it simple is better.

Similarly for update and delete. connection.Execute(“UPDATE Users SET Email = @Email WHERE Id = @Id”, new { Id = 1, Email = “new@example.com” }); For delete, be careful to include a WHERE clause, otherwise, all rows will be deleted. I once forgot this and cleared the test database, but we laughed it off.

Here’s a practical example: suppose you’re developing a REST API and want to use Dapper in a controller’s [HttpGet] method. Inject the connection via dependency injection, add AddScoped<MySqlConnection> to IServiceCollection. Then, call the query inside the method. This way, dependency management becomes easier.

MySQL has an advantage: strong community support, problems are often resolved on forums. The MySQL docs site provides basic setup guides, or I usually ask on Stack Overflow for quick responses.

Do you prefer MySQL or PostgreSQL? I think both are good, but MySQL is ideal for smaller projects. I believe PostgreSQL is better for large-scale data, if I’m not mistaken.

Advanced Usage and Tips

Now, let’s talk about stored procedures. Dapper is compatible with them; call them like connection.Query(“CALL GetUsersByAge(@Age)”, new { Age = 30 });. I used this in a project for logic encapsulation on the database side. The advantage is keeping the code clean, but debugging can be difficult. For simple queries, raw SQL suffices.

Don’t forget the async version, especially for APIs. Use connection.QueryAsync<User> with await. It boosts performance and prevents thread blocking. I initially used synchronous in my REST API, which slowed down the site; switching to async fixed it. Also, in the bustling traffic of Bursa, thinking asynchronously is essential—everything runs in parallel : )

You can also write custom type handlers, e.g., for DateTime. But default usually works fine. I once struggled with enum mapping; Dapper doesn’t map enums automatically, so I added a manual handler. No detailed info here, but search for dapper enum mapping mysql for examples.

Sometimes I revert to ADO.NET instead of Dapper for more control, but Dapper’s speed is enticing, especially with millions of rows. I recall a benchmark showing it was 3 times faster than EF, though I don’t remember the exact numbers.

While working with embedded systems, I also deal with similar data streams, writing sensor data to MySQL with C#. Dapper is practical for embedded systems since it’s lightweight. Anyway, I won’t stray off-topic here.

Let me mention potential issues. Enable connection pooling; in MySQL connector, it’s on by default, but adjust poolingSize. I set it to 100 in a project, and there were no issues with concurrent requests. Also, ensure to use UTF8 charset for Turkish characters.

Here’s a sample code for inserting data.

using Dapper;
using MySqlConnector;

public void AddUser(string name, string email)
{
    using var conn = new MySqlConnection(_connectionString);
    conn.Execute("INSERT INTO Users (Name, Email) VALUES (@Name, @Email)", new { Name = name, Email = email });
}

You can add this into your controller to run it. Explained briefly, parameters are passed with anonymous objects, which is secure. Alternatively, you can call stored procedures, but for simple cases, this is enough.

Transactions are also important, especially when you need to perform multiple operations. Use connection.UseTransaction(transaction);. I used it in an API to update two tables, and it saved me when dealing with rollbacks during errors.

In summary, the combination of C# with MySQL and Dapper is my favorite for its speed and flexibility. If you’re just starting out, make small projects and get familiar. I saw a detailed tutorial somewhere (though I can’t find the link now); try searching google ‘csharp dapper mysql tutorial’. Dabble in it and share your experience—would be wonderful, don’t you think?

Honestly, I am satisfied with this setup, especially when integrating with Vue.js frontend through REST APIs. Anyway, next time I might write about fetching data with jQuery. Take care.