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!
One big gotcha with using Postgres, that needs to be said.
Based on your example code, how was your MyTable and it’s columns created?
If they were created using the default of most database tools, example pgAdmin4, Navicat etc. then MyTable is created with double quotes “MyTable” which makes it case sensitive. However, your query example does not use the double quotes around MyTable, which means it will be processed by Postgres as lowercase. Since there is no lowercase mytable in the database your query will return an error that MyTable does not exist.
Even if we omit the double quotes when creating the MyTable (not use pgAdmin or Navicat or write the table creation script our self without quotes), MyTable will actually be created as mytable. Therefore again your example query above fails.
So then if we want to actually make this query work and have the ability to use Pascal case naming (such as MyTable), we need to use double quotes in our SQL query string.
But then arises the problem of C# not allowing these quotes as it breaks the string. So we need to escape the quotes and write the query as “SELECT * FROM \”MyTable\”” or @”SELECT * FROM “”MyTable””” , which will make our query work, but is a very frustrating way to code or rewrite queries. Existing MSSQL queries wont work out of the box.
To totally remove the need for using these quotes in our SQL query strings, we could rename all databases and column names to lowercase (hard to read) or we could use snake case such as my_table. Then in this situation if we were using dapper we would have to use Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true; in order for Dapper to map our Pascal cased Object names to the snake case equivalent in our database.
I just wanted to point this out as the move from MS SQL to Postgres can present some annoying issues.
Hi
Just wanted to let you know that .Net Core (any version) works fantastically with PostgreSQL when combined with EF Core and Npgsql. I’ve been working with this combination for over 2 years now with 3 apps now in production.
https://www.npgsql.org/
Hi,
Thanks for nice dapper series , well can you also add how to call procedure in postgresql with parameter. Actually I tried somethig, but getting error Npgsql.PostgresException (0x80004005): 42601: syntax error at or near “@”
at Npgsql.NpgsqlConnector.g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async,
Code I tried is as below:
var p = new DynamicParameters();
p.Add(“@book_id”, entity.book_id);
p.Add(“@book_name”, entity.book_name);
p.Add(“@book_desc”, entity.book_desc);
string callSP = “CALL sp_Insert_product (@book_id,@book_name,@book_desc)”;
await conn.ExecuteAsync(callSP, p, commandType: CommandType.StoredProcedure);
CommandType.StoredProcedure is reserved for functions rather that stored procedures (seems strange, but I believe it’s because Postgres originally never supported stored procedures).
If you update your code CommandType.Text I think it should work.
Hi,
What about MariaDB and stored procedures?
I’ve tried all sorts of permutation but it doesn’t seem to work.
DynamicParameters_parameters = new DynamicParameters();
_parameters.Add(“@_entity_number”, 111, DbType.Int32, SetDirection(parameterDirection), 11);
_parameters.Add(“@_price”, 123.5501, DbType.Decimal, SetDirection(parameterDirection));
_parameters.Add(“@_valuation_date”, “2021-01-01”, DbType.Date, SetDirection(parameterDirection);
_dapper.Execute(“dbname.proc_EnterRow”, _parameters, commandType: CommandType.StoredProcedure);
The error I get everytime is Incorrect number of arguments for PROCEDURE db_a6371b_evocon.proc_EnterRow expected 3, got 0
Any help will be appreciated.