Questions about Dapper SQL queries and parameters with PostgreSQL

Question

I´m currently learning about Dapper. I have searched a lot here and other places (including this) and I could not find concrete answers to my doubts:

¿Does Dapper use a generic SQL dialect or it's specific for DB engine? I mean, it uses the SQL syntax expected in the underlaying database engine? At first and after reading over a dozen of examples I thought that the SQL queries where generic, but now trying on PostgresSQL ODBC I have encountered problems with syntax and parameters.

Using this example POCO class ...

public class CardType {

    //Autoincremented Key
    public int Id { get; set; }

    public string Type { get; set; }

    public string Description { get; set; }
}

... the following line does not work for me:

_connection.Execute(@"INSERT cardtypes (type, description) VALUES (@Type,  @Description)", cardType);

First, this line trows an ODBC exception beacuse expects the clause "INTO" before specifying the table. Also the parameters does not work either, because if I'm not wrong, PostgresSQL parameters are setted with the "?" symbol and not with "@keyword". On the GitHub page we can find this:

Dapper has no DB specific implementation details, it works across all .NET ADO providers including SQLite, SQL CE, Firebird, Oracle, MySQL, PostgreSQL and SQL Server.

So I'm lost with this. :) After experimenting a lot I found that putting all the PostgresSQL things works as expected. For example all the next cases worked:

//Manually parameters

var query = @"INSERT INTO cardtypes (type, description) values ('Administrator', 'The Boss')";
_db.Execute(query);


//Dynamic parameters

var dynamicParameters = new DynamicParameters();
dynamicParameters.AddDynamicParams(new {
    cardType.Type,
    cardType.Description
});

var query = @"INSERT INTO cardtypes (type, description) values (?, ?)";    //Note the '?' symbol
_db.Execute(query, dynamicParameters);


//Interpolating values
var query = $@"INSERT INTO cardtypes (type, description) values ('{cardType.Type}', '{cardType.Description}')";
_db.Execute(query);

These previuos cases worked fine. But I'm confused at understanding if the SQL queries are a generic SQL dialect or an specific database engine dialect.

Also I've tried out Dapper.Contrib and fails too with the INSERT statement, for example:

_db.Insert(new CardType() {
    Type = "Administrator",
    Description = "The Boss"
});

It fails too...a weird "[" character exception.

What I'm doing wrong?

My regards!

Answer 1

Dapper does not attempt to parse your query or offer a custom DSL. Rather: it passes your query direct to your chosen ADO.NET provider. It does do a few tweaks along the way in some cases, but in the general sense: it is untouched.

In the case of postgresql, IIRC parameters are colon-prefixed, not at-prefixed. Try using :foo instead of @foo.