Dapper parameters not working

Question

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
    });            

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'.

---

Edit1

Couldn't get this to work. Using string.format to insert the parameter as a work around.

Solution

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.

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 = ?

in your command, and:

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

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 });

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

One final observation:

Which means dapper is not replacing the parameter with it's given value.

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:

• there is no SQL injection risk
• maximum query plan re-use
• no issues of formatting

Note that some ADO.NET / ODBC providers could theoretically choose to implement things internally via replacement - but that is separate to dapper.

Comments

[Bitfiddler]

Thanks Marc for the clarifications. Just an FYI: Yes I have to use Odbc (I am connecting to a Progress DB and that is the only connection type I have available). I'm not sure if you work on the project, but as an aside, it would be nice if the documentation mentioned the Odbc limitation on parameters explicitly for those who don't know the details of how ODBC works. The documentation repeatedly uses IDbconnection or var for the connection type which to me implied any connection would work with parameters. Thank you for the explanation. I learned something new about dapper and ODBC!

[Marc Gravell]

@BitFiddler I will update the examples to include ODBC - at the same time as I deploy the NuGet package

[Marc Gravell]

@BitFiddler I should note: none of these limitations are anything to do with dapper; that is simply how ODBC works.

[Bitfiddler]

I hope my comment was not interpreted as blaming dapper. I only meant to suggest some helpful guidance on the docs. Even putting a little asterisk next to the normal examples with a link to your explanation above (until the next version is available) would be sufficient. I'm guessing you are not getting paid much if anything for dapper so I don't want to come across as too needy. I would add the link myself but it's probably more work for you to give me permissions to change the docs and then make sure they are correct, then to just add a link yourself ;-) Cheers thanks for your time.

[Bitfiddler]

@MarkGravell Just downloaded and tried the OleDb solution from git and it did the job perfectly! Thank you for your time and effort. Dapper is quite a handy tool. Do you have any ball park idea when this change will find its way to nuget? I'm using my own build for now but I'd like to stay with the nuget package long term so if you have a guestimate I'll check back around that time.

[Marc Gravell]

@BitFiddler I want to look through the pull requests and issue log. Unfortunately I'm also in the middle of buying/selling a house, among everyday commitments.

[Meer]

@MarcGravell how to use SqlDbType.Xml in dapper

[BWhite]

@MarcGravell This works great for ints but not for strings. I get precision not specified and had to specify the length. var result = await conn.QueryAsync<Customer>(sQuery, new { State = new DbString { Value = state, IsFixedLength = true, Length = 4} });