不工作小巧玲珑参数 [英] Dapper parameters not working
问题描述
我试图用小巧玲珑的ORM用下面的简单查询：
I'm trying to use the Dapper orm with the following simple query:
var sqlString = new StringBuilder();
sqlString.Append("select a.acct AccountNumber,");
sqlString.Append(" b.first_name FirstName,");
sqlString.Append(" b.last_name LastName,");
sqlString.Append(" a.rr RrNumber,");
sqlString.Append(" c.addr1 AddressLine1,");
sqlString.Append(" c.addr2 AddressLine2,");
sqlString.Append(" c.addr3 AddressLine3,");
sqlString.Append(" c.addr4 AddressLine4,");
sqlString.Append(" c.addr5 AddressLine5,");
sqlString.Append(" c.addr6 AddressLine6,");
sqlString.Append(" c.addr7 AddressLine7,");
sqlString.Append(" c.addr8 AddressLine8 ");
sqlString.Append("from (pub.mfclac as a left join pub.mfcl as b on a.client=b.client) ");
sqlString.Append("left join pub.mfclad as c on a.client=c.client ");
sqlString.Append("where a.acct = '@ZYX'");
var connection = new OdbcConnection(_connectionString);
var result = connection.Query(sqlString.ToString(),
new
{
ZYX = accountNumber
});
然而，当我知道存在，短小精悍不返回任何一个使用accountNumber执行此。所以我试图删除引号验证参数其实与帐号被取代，但是从服务器返回的错误指示围绕@ZYX语法错误。这意味着短小精悍不更换参数与它的给定值。任何想法，为什么发生这种情况？从有限的资料摆在那里，这应该只是工作。
However when I execute this with an accountNumber known to exist, dapper returns nothing. So I tried to remove the quotes to verify that the parameter is in fact being replaced with the account number, however the error being returned from the server indicates a syntax error around "@ZYX". Which means dapper is not replacing the parameter with it's given value. Any ideas why this is happening? From the limited documentation out there, this should 'just work'.
无法得到这个工作。使用的String.Format插入参数作为变通。
Couldn't get this to work. Using string.format to insert the parameter as a work around.
推荐答案
这里有两个问题;首先（虽然你在你的问题注意本）其中a.acct ='@ZYX'，在SQL规则，不使用任何参数 - 它看起来匹配文字串，恰好包括 @ 标志。对于SQL-Server（请参阅下面的注释），正确的用法是其中a.acct = @ZYX 。
There are two issues here; firstly (although you note this in your question) where a.acct = '@ZYX', under SQL rules, does not make use of any parameter - it looks to match the literal string that happens to include an @ sign. For SQL-Server (see note below), the correct usage would be where a.acct = @ZYX.
不过！既然你是使用 OdbcConnection ，命名参数的不适用的。如果您在实际连接到类似SQL-Server时，我会强烈建议使用纯ADO.NET客户端，它具有更好的功能和性能比ODBC。但是，如果ODBC是你唯一的选择：它的不使用命名参数的。直到前几天，这将是一项重大的问题，但是按照传在小巧玲珑的使用OleDb的，代码（但还不是的NuGet包）查询参数现在支持ODBC。如果你从源代码编译（或等待下一个版本），你应该能够使用：
However! Since you are use OdbcConnection, named parameters do not apply. If you are actually connecting to something like SQL-Server, I would strongly recommend using the pure ADO.NET clients, which have better features and performance than ODBC. However, if ODBC is your only option: it does not use named parameters. Until a few days ago, this would have represented a major problem, but as per Passing query parameters in Dapper using OleDb, the code (but not yet the NuGet package) now supports ODBC. If you build from source (or wait for the next release), you should be able to use:
...
where a.acct = ?
在你的命令，：
var result = connection.Query(sqlString.ToString(),
new {
anythingYouLike = accountNumber
});
请注意，该名称（ anythingYouLike ）是不使用ODBC，所以可以... ...的任何你喜欢的。在更复杂的情况，例如：
Note that the name (anythingYouLike) is not used by ODBC, so can be... anything you like. In a more complex scenario, for example:
.Execute(sql, new { id = 123, name = "abc", when = DateTime.Now });
短小精悍使用的实施如何匿名类型来理解价值的原始顺序的一些知识，使它们被添加到命令正确顺序（ ID ，名称，在）
最后一个观察：
这意味着与它的给定值短小精悍不更换参数。
Which means dapper is not replacing the parameter with it's given value.
小巧玲珑的从不替换它们的给定值参数。这根本就不是参数化SQL的正确方法：该参数通常分开发送，以确保：
Dapper never replaces parameters with their given value. That is simply not the correct way to parameterize sql: the parameters are usually sent separately, ensuring:
- 不存在SQL注入风险
- 最大查询计划重复使用
- 没有格式化的问题
请注意，某些ADO.NET / ODBC提供的理论上可以选择的在内部通过实施替代的东西 - 但这是分开，短小精悍。
Note that some ADO.NET / ODBC providers could theoretically choose to implement things internally via replacement - but that is separate to dapper.
这篇关于不工作小巧玲珑参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
