Dapper With MySQL/PostgreSQL On .NET Core

I contract/freelance out a lot to companies that are dipping their toes into .NET Core, but don’t want to use Microsoft SQL Server – so they either want to use PostgreSQL or MYSQL. The thing that gets me is often these companies are so wary about the ability for .NET Core to talk to anything non-Microsoft. The amount of time I’ve spent on calls trying to explain it really doesn’t matter for the most part which tech choice they go with if all they are expecting from .NET Core’s point of view is to run simple commands.

Maybe if you’re overlaying something like EF Core or a very heavy ORM you might have issues. But in my experience, when using something like Dapper that allows you to really control the queries you are running, it really doesn’t make a heck of a lot of difference between any SQL Server.

I would also add that for both MySQL and Postgres, I’ve had .NET Core apps running inside Linux (Containers and VM’s) with absolutely no issue. That also seems to get asked a lot, “OK so this can talk to MySQL but can it talk to MySQL from Linux”… errr… yes, yes it can!

This is going to be a really short and sweet post because there really isn’t a lot to it!

Intro To Dapper

If you’ve never used Dapper before, I highly recommend this previous write up on getting started with Dapper. It covers a lot of the why and where we might use Dapper, including writing your first few queries with it.

If you want to skip over that. Just understand that Dapper is a lightweight ORM that handles querying a database and turning the rows into your plain objects with very minimal fuss and overhead. You have to write the queries yourself, so no Linq2SQL, but with that comes amazing control and flexibility. In our case, that flexibility is handy when having to write slightly different commands across different types of SQL Databases, because Dapper itself doesn’t have to translate your LINQ to actual queries, instead that’s on you!

MySQL With Dapper

When working with MySQL in .NET Core, you have to install the following nuget package :

Install-Package MySql.Data

Normally when creating a SQL Connection you would do something like so :

using (var connection = new SqlConnection("Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"))
{
	connection.Query<MyTable>("SELECT * FROM MyTable");
}

With MySQL you would do essentially the same thing but instead you use the MySQLConnection class :

using (var connection = new MySqlConnection("Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"))
{
	connection.Query<MyTable>("SELECT * FROM MyTable");
}

And that’s pretty much it! Obviously the syntax for various queries may change (e.g. Using LIMIT in MySQL instead of TOP in MSSQL), but the actual act of talking to the database is all taken care for you and you literally don’t have to do anything else.

PostgreSQL With Dapper

If you’ve read the MySQL portion above.. well.. You can probably guess how Postgres is going to go.

First install the following nuget package :

Install-Package Npgsql

Then again, our normal SQL Connection looks like so :

using (var connection = new SqlConnection("Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"))
{
	connection.Query<MyTable>("SELECT * FROM MyTable");
}

And our Postgres connection instead looks like so using the NpgsqlConnection class :

using (var connection = new NpgsqlConnection("User ID=root;Password=myPassword;Host=localhost;Port=5432;Database=myDataBase;"))
{
	connection.Query<MyTable>("SELECT * FROM MyTable");
}

Too easy!

ENJOY THIS POST?
Join over 3.000 subscribers who are receiving our weekly post digest, a roundup of this weeks blog posts.
We hate spam. Your email address will not be sold or shared with anyone else.

Leave a Reply

Your email address will not be published. Required fields are marked *