Passing request parameters to Dapper using OleDb

Question

This query causes the 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 get the error: Must declare the scalar variable "@id".

How to build a query string and how to pass a parameter?

Answer 1

The current source code (not yet released for NuGet) solves this problem; 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 this is no for two reasons:

• the code tries to filter out unused parameters - and currently deletes all of them because it cannot find anything like @id :id or ?id in sql
• the code for adding values ​​from types uses an arbitrary (well, ok: alphabetical) order for parameters (since reflection gives no guarantees regarding the order of members), making positional anonymous arguments unstable

The good news is that both are fixed

• we can make the filter condition conditional
• we can define a category of types that has a constructor that matches all property names, and use the position of the constructor argument to determine the synthetic order of properties - anonymous types fall into this category

Performing these changes in my local clone, the following now executes:

 // 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 am currently using DynamicParameters here to avoid adding even more overloads to Query / Query<T> - because this will need to be added to a large number of methods. Adding it to DynamicParameters resolves it in one place.

I am open to feedback before I put it forward - is this right for you?

---

Edit: with the addition of funky smellsLikeOleDb (no, not a joke), we can do it 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 tested using Dapper in my software product using odbc connections (for now). However, one day I intend to move away from odbc and use a different template to support various RDBMS products. However, my problem with implementing the solution is 2 times:

• I want to write SQL code with parameters that correspond to different contents, and therefore I want to write named parameters in my SQL now, so that I do not have return and reuse later.
• I don’t want to rely on getting the order of my properties according to mine ?. This is bad. So my suggestion is to add Named Parameter support for odbc.

At the same time, I hacked into a solution that allows me to do this with Dapper. Essentially, do I have a routine that replaces named parameters? and also restores the parameter object, making sure the parameters are in the correct order. However, looking at the Dapper code, I see that I repeated some of what dapper does anyway, effectively each parameter value is now visited again than necessary. This becomes a problem for bulk updates / inserts. But at least it works ok for me ..

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

Answer 4

? for parameters was part of the solution for me, but it only works with integers, like ID. This still fails for strings because the length of the parameter is undefined.

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

In order for Dapper to pass string parameters to ODBC, I had to specify a length.

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