Dapper query with DateTime

Question

I'm trying to use Dapper to query a table from my ODBC driver. The specific query I'm trying to run needs to return rows that have a DateTime value greater than the passed parameter to the query.

This is what I have tried:

string delta = "05/02/2019 08:41:37";
DateTime deltaDt = DateTime.Parse(delta);

using (IDbConnection connection = new OdbcConnection(/**Connection string**/))
{
    connection.Open();

    try
    {
        string query = "SELECT * FROM MyTable WHERE Date > @Delta";
        IEnumerable<object> records = connection.Query<object>(query, new { Delta = deltaDt });

        Console.WriteLine(records.Count());
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

However, with the above I get the following error:

{"ERROR [22018] Cannot convert non-numeric data"}

I then tried using IDBConnection without Dapper to see if I was just doing something incorrect, however the query works fine if I just do the following:

string delta = "05/02/2019 08:41:37";
DateTime deltaDt = DateTime.Parse(delta);

using (IDbConnection connection = new OdbcConnection(/**Connection string**/))
{
    connection.Open();

    string odbcQuery = "SELECT * FROM MyTable WHERE Date > ?";

    IDbCommand command = connection.CreateCommand();

    IDbDataParameter parameter = command.CreateParameter();
    parameter.Value = deltaDt;
    command.Parameters.Add(parameter);

    command.CommandText = odbcQuery;
    System.Data.IDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        object[] values = new object[1];
        reader.GetValues(values);
        console.log(values);
    }
}

Am I doing something wrong when binding the DateTime using the Dapper query? As far as I can see the value is the same whether I use Dapper query or IDbDataParameter. I have also tried the following, but still get the same issue:

string query = "SELECT * FROM MyTable WHERE Date > @Delta";
DynamicParameters dp = new DynamicParameters();
dp.Add("@Delta", deltaDt, DbType.DateTime);
IEnumerable<object> records = connection.Query<object>(query, dp);

What is the data type of the `MyTable.Date` column? Is it actually a date? — DavidG, Feb 05 '19 at 10:21
@mjwills That did help thanks. However I still don't understand why '@Delta' does not work but ?Delta? does. — Kakalokia, Feb 05 '19 at 10:42

score 7 · Accepted Answer · answered Feb 05 '19 at 10:28

7

Try in this way:

SELECT * FROM MyTable WHERE Date > ?Delta?

then

 IEnumerable<object> records = connection.Query<object>(query, new { Delta = deltaDt });

answered Feb 05 '19 at 10:28

Derviş Kayımbaşıoğlu

28,492
4
50
72

1

That does indeed work. Thank you. Can you please explain why this works whereas @Delta does not? Thanks. – Kakalokia Feb 05 '19 at 10:39
9

@Kakalokia (hi, Dapper author here) - basically, the problem here is that your ODBC provider doesn't support named parameters, so `@Delta` *means nothing* to your database engine - it wants positional parameters; for convenience, dapper provides "pseudo-positional parameters" (naming is hard) - if you use `?Delta?`, dapper replaces it with the correct positional token, while also knowing which value to use for the parameter value (i.e. `args.Delta`) – Marc Gravell Feb 05 '19 at 10:56
1

@MarcGravell Thanks for the explanation Marc, appreciate it. It makes sense now. – Kakalokia Feb 05 '19 at 10:57

Dapper query with DateTime

1 Answers1