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!

Marc Gravell · Accepted Answer · 2016-02-12 18:41:03Z

7

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.

answered Feb 12, 2016 at 18:41

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AlighaThor Over a year ago

The colon-prefixed IIRC parameters did'nt worked. Trying this... var query = @"INSERT INTO cardtypes (type, description) values (:Type, :Description)"; _db.Execute(query, cardType); ... I got an exception: "ERROR [42601] ERROR: error de sintaxis en o cerca de «:»;". So?

Marc Gravell Over a year ago

@AlighaThor ah, you're using the ODBC connector. Options: 1) use psuedo-positional parameters instead, i.e. ?type?, ?description? - dapper will detect this and replace them with positional parameters ? and ?, using the names to determine which order to add them. 2) use the Npgsql ADO.NET connector instead of ODBC; dapper will detect this and enable bind-by-name instead of just positional binding. Nuget link: nuget.org/packages/Npgsql

Collectives™ on Stack Overflow

Questions about Dapper SQL queries and parameters with PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related