Dapper options not working

Question

I am trying to use Orp Dapper 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 }); 

However, when I do this with a known accountNumber, dapper returns nothing. Therefore, I tried to remove the quotation marks to make sure that this parameter was actually replaced by the account number, however, the error returned from the server indicates a syntax error around "@ZYX". This means that dapper does not replace the parameter with the given value. Any idea why this is happening? From the limited documentation there, it should "just work."

---

Edit1

Failed to get this to work. Using string.format to insert a parameter into the work.

Answer 1

There are two questions here; firstly (although you notice this in your question) where a.acct = '@ZYX' , in accordance with SQL rules, does not use any parameter - it looks like it matches a literal string that includes the @ sign . For SQL Server (see note below), the correct use would be where a.acct = @ZYX .

But! Since you are using OdbcConnection , named parameters are not applied. If you really connect to something like SQL-Server, I highly recommend using pure ADO.NET clients that 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 be a serious problem, but by Passing query parameters to Dapper using OleDb , the code (but not the NuGet package yet) now supports ODBC. If you build the source code (or wait for the next release), you can use:

 ... where a.acct = ? 

on your team and:

 var result = connection.Query(sqlString.ToString(), new { anythingYouLike = accountNumber }); 

Note that the name ( anythingYouLike ) is not used by ODBC, so it could be ... anything. In a more complex scenario, for example:

 .Execute(sql, new { id = 123, name = "abc", when = DateTime.Now }); 

dapper uses some knowledge of how anonymous types are implemented to understand the original order of values ​​so that they are added to the command in the correct sequence ( id , name , when ).

One last note:

This means that dapper does not replace the parameter with the given value.

Dapper never replaces parameters with its given value. This is simply not the right way to parameterize sql: parameters are usually sent separately, providing:

• no risk of SQL injection
• reuse re-request plan
• No formatting issues

Note that some ADO.NET/ODBC providers can theoretically choose to implement things internally with a replacement - but this is separate for dapper.