Parameters not working with Dapper.Net

.net c# dapper mysql

Question

I've downloaded the SqlMapper.cs file from the link here.

Though I'm having a bit of confusing trouble. Whenever I use parameters my queries return empty collections, even though I'm quite sure I'm using the same string data.

Here is a snippet of code that is used to extract the data.

using (PhoenixConnection connection = new PhoenixConnection(Open: true))
{
    //this example works, returns 1 token object
    string queryWorks = @"Select AccountName, AccountToken from btsource.accounts
    where AccountName = 'RekindledPhoenix'";

    // replaces 'RekindledPhoenix' with a parameter, returns empty collection
    string queryDoesnt = @"Select AccountName, AccountToken from btsource.accounts 
    where AccountName = @AccountName";

    var tokenList = connection.Query<TokenRequest>(queryWorks);
    var tokenList = connection.Query<TokenRequest>(queryDoesnt, new { AccountName = "RekindledPhoenix" });
    return tokenList.FirstOrDefault();
}

Here is my class I'm using...

public class TokenRequest
{
    public string AccountName { get; set; } //`AccountName` VARCHAR(50) NOT NULL
    public long AccountToken { get; set; } //`AccountToken` BIGINT(20) NOT NULL
}

And here is the wrapper function in my PhoenixConnection object.

public IEnumerable<T> Query<T>(string Query, dynamic Parameters = null)
{
    return _connection.Query<T>(Query, (object)Parameters);
}

I've tried:

  • double-checking mysql table names and values
  • changing wrapper to use anything but dynamic (didn't work)
  • verifying connection is open
  • raw mysqlConnection object, commandtext, etc

What could I be missing?

Is there some special setting used for parameters?

Edit:

Using raw MySqlConnection parameters work better with ? and not @, though still doesn't work with Dapper.

I noticed there were specific lines within Dapper that extract parameters with regex ([@:]) statements, though question marks seem to be ignored. What should I change to give me the expected results?

Accepted Answer

I found the issue. After using a raw MySqlConnection object to execute my queries, it lead me on a search for why @ symbols didn't work in any of my statements. In the past I've always used ?, so this behavior was odd to me.

Changing SqlMapper.cs

Change the following @ symbols to a ? (with approximate line numbers):

Line 1863: if (identity.sql.IndexOf("?" + prop.Name, StringComparison.InvariantCultureIgnoreCase) < 0

Line 1831: return parameters.Where(p => Regex.IsMatch(sql, "[?:]" + p.Name + "([^a-zA-Z0-9_]+|$)", RegexOptions.IgnoreCase | RegexOptions.Multiline));

Solved my problem!



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why