有关PostgreSQL的Dapper SQL查询和参数的问题 [英] Questions about Dapper SQL queries and parameters with PostgreSQL
问题描述
我目前正在了解Dapper。我在这里和其他地方（包括此）进行了很多搜索，但找不到具体内容我的疑问的答案：
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:
¿Dapper是使用通用SQL方言还是特定于DB引擎的？我的意思是，它使用底层数据库引擎中预期的SQL语法？刚开始阅读完多个示例之后，我认为SQL查询是通用的，但是现在尝试使用PostgresSQL ODBC时，我遇到了语法和参数问题。
¿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.
使用此示例POCO类...
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);
首先，此行引发ODBC异常，因为在指定表之前期望子句 INTO。另外，这些参数也不起作用，因为如果我没记错的话，PostgresSQL参数将设置为？符号，而不是 @keyword。在GitHub 页面上，我们可以找到以下内容：
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没有特定于数据库的实现细节，它适用于所有
.NET ADO提供程序，包括SQLite，SQL CE，Firebird，Oracle，MySQL，
PostgreSQL和SQL Server。
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.
所以我对此一无所知。 :)经过大量实验后，我发现将所有PostgresSQL东西都按预期工作了。例如，接下来的所有情况都起作用：
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);
这些之前的案例效果很好。但是我对理解SQL查询是通用SQL方言还是特定的数据库引擎方言感到困惑。
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.
我也尝试了Dapper.Contrib并同样失败了使用INSERT语句，例如：
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.
我在做什么错了？
我的问候！
推荐答案
Dapper不会尝试解析您的查询或提供自定义DSL。而是：它将查询直接传递给您选择的ADO.NET提供程序。在某些情况下，它确实做了一些调整，但是从一般意义上来说，它是不变的。
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.
在postgresql的情况下，IIRC参数是冒号前缀，而不是前缀。尝试使用：foo 代替 @foo 。
In the case of postgresql, IIRC parameters are colon-prefixed, not at-prefixed. Try using :foo instead of @foo.
这篇关于有关PostgreSQL的Dapper SQL查询和参数的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
