Passing query parameters in Dapper using OleDb

Question

This query produces an error No value given for one or more required parameters:

using (var conn = new OleDbConnection("Provider=..."))
{
  conn.Open();
  var result = conn.Query(
    "select code, name from mytable where id = ? order by name",
    new { id = 1 });
}

If I change the query string to: ... where id = @id ..., I will get an error: Must declare the scalar variable "@id".

How do I construct the query string and how do I pass the parameter?

Answer 1

The following should work:

var result = conn.Query(
"select code, name from mytable where id = ?id? order by name",
new { id = 1 });

Answer 2

Important: see newer answer

---

In the current build, the answer to that would be "no", for two reasons:

• the code attempts to filter unused parameters - and is currently removing all of them because it can't find anything like @id, :id or ?id in the sql
• the code for adding values from types uses an arbitrary (well, ok: alphabetical) order for the parameters (because reflection does not make any guarantees about the order of members), making positional anonymous arguments unstable

The good news is that both of these are fixable

• we can make the filtering behaviour conditional
• we can detect the category of types that has a constructor that matches all the property names, and use the constructor argument positions to determine the synthetic order of the properties - anonymous types fall into this category

Making those changes to my local clone, the following now passes:

// see https://stackoverflow.com/q/18847510/23354
public void TestOleDbParameters()
{
    using (var conn = new System.Data.OleDb.OleDbConnection(
        Program.OleDbConnectionString))
    {
        var row = conn.Query("select Id = ?, Age = ?", new DynamicParameters(
            new { foo = 12, bar = 23 } // these names DO NOT MATTER!!!
        ) { RemoveUnused = false } ).Single();
        int age = row.Age;
        int id = row.Id;
        age.IsEqualTo(23);
        id.IsEqualTo(12);
    }
}

Note that I'm currently using DynamicParameters here to avoid adding even more overloads to Query / Query<T> - because this would need to be added to a considerable number of methods. Adding it to DynamicParameters solves it in one place.

I'm open to feedback before I push this - does that look usable to you?

---

Edit: with the addition of a funky smellsLikeOleDb (no, not a joke), we can now do this even more directly:

// see https://stackoverflow.com/q/18847510/23354
public void TestOleDbParameters()
{
    using (var conn = new System.Data.OleDb.OleDbConnection(
        Program.OleDbConnectionString))
    {
        var row = conn.Query("select Id = ?, Age = ?",
            new { foo = 12, bar = 23 } // these names DO NOT MATTER!!!
        ).Single();
        int age = row.Age;
        int id = row.Id;
        age.IsEqualTo(23);
        id.IsEqualTo(12);
    }
}

Answer 3

I've trialing use of Dapper within my software product which is using odbc connections (at the moment). However one day I intend to move away from odbc and use a different pattern for supporting different RDBMS products. However, my problem with solution implementation is 2 fold:

1. I want to write SQL code with parameters that conform to different back-ends, and so I want to be writing named parameters in my SQL now so that I don't have go back and re-do it later.
2. I don't want to rely on getting the order of my properties in line with my ?. This is bad. So my suggestion is to please add support for Named Parameters for odbc.

In the mean time I have hacked together a solution that allows me to do this with Dapper. Essentially I have a routine that replaces the named parameters with ? and also rebuilds the parameter object making sure the parameters are in the correct order. However looking at the Dapper code, I can see that I've repeated some of what dapper is doing anyway, effectively it each parameter value is now visited once more than what would be necessary. This becomes more of an issue for bulk updates/inserts. But at least it seems to work for me o.k...

I borrowed a bit of code from here to form part of my solution...

Answer 4

The ? for parameters was part of the solution for me, but it only works with integers, like ID. It still fails for strings because the parameter length isn't specifed.

OdbcException: ERROR [HY104] [Microsoft][ODBC Microsoft Access Driver]Invalid precision value
System.Data.Odbc. OdbcParameter.Bind(OdbcStatementHandle hstmt, OdbcCommand command, short ordinal, CNativeBuffer parameterBuffer, bool allowReentrance)
System.Data.Odbc.OdbcParameterCollection.Bind(OdbcCommand command, CMDWrapper cmdWrapper, CNativeBuffer parameterBuffer) System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, string method, bool needReader, object[] methodArguments, SQL_API odbcApiMethod)
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, string method, bool needReader)
System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
Dapper.SqlMapper.QueryAsync(IDbConnection cnn, Type effectiveType, CommandDefinition command) in SqlMapper.Async.cs
WebAPI.DataAccess.CustomerRepository.GetByState(string state) in Repository.cs
var result = await conn.QueryAsync(sQuery, new { State = state });
WebAPI.Controllers.CustomerController.GetByState(string state) in CustomerController .cs
return await _customerRepo.GetByState(state);

For Dapper to pass string parameters to ODBC I had to specify the length.

var result = await conn.QueryAsync<Customer>(sQuery, new { State = new DbString { Value = state, IsFixedLength = true, Length = 4} });