OleDbException on writing to MS Access using Dapper

Question

OleDbException on writing to MS Access using Dapper

dapper f# ms-access oledb

Question

I'm trying to do an insert with Dapper into my MS Access database, now querying the data works fine but when I do try an insert I get an System.Data.OleDb.OleDbException : Data type mismatch in criteria expression

The code is :

let conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path;Persist Security Info=False;")

let exec f =
    try
        conn.Open()
        do f() 
    finally
        conn.Close()

[<CLIMutable>]
type Timecard =
    { Id          : int
      Employee    : string
      WorkDate    : DateTime
      Hours       : int
      Description : string
      BillType    : string }

exec (fun () -> 
        let tc = { Id = 0
                   Employee = "John Doe" 
                   WorkDate = DateTime.Today 
                   Hours = 10
                   Description = "working 9 to 5"
                   BillType = "BillableInternal" }
        conn.Execute("insert into Timecards (Employee, WorkDate, Hours, Description, BillType) values (@Employee, @WorkDate, @Hours, @Description, @BillType)", tc)             
       |> ignore)

Overly Excessive

Popular Answer

I had a similar issue, what I did was to use parameter names like @param1, @param2 instead of @name,@id,@price so the order stays the same without having to modify SQLMapper.cs file.

Something like

public void Update(Movie movie)
{
  var sql = "UPDATE myDB.movies set title=@param1, genre=@param2 where ID=@param3";
  db.Execute(sql, new { param1 = movie.Title, param2 = movie.Genre, param3 = movie.ID });
}

Zo Has

View more on Stack Overflow

Accepted Answer

After some digging, I was able to find a cause:

Below is CreateParamInfoGenerator delegate from dapper's SqlMapper:

    public static Action<IDbCommand, object> CreateParamInfoGenerator(Identity identity)
    {

        // code above here
        IEnumerable<PropertyInfo> props = type.GetProperties().OrderBy(p => p.Name);

The props is your unanimous param which gets re-ordered by OrderBy(p => p.Name), which moves city upfront.

new { firstName = "John", city = "SomeCity", lastName = "Smith" }

Props is then being added to the IDbCommand Parameters where the order is important.

If I comment out OrderBy() clause, then everything works.

I also tested DynamicParameters and intentionally re-ordered the attributes to move city upfront:

        var parameters = new DynamicParameters();
        parameters.Add("city", "SomeCity");
        parameters.Add("firstName", "John");
        parameters.Add("lastName", "Smith");

        var result = dbConnection.Query<string>(
          "update Students set FirstName = @firstName, City = @city where LastName = @lastName",
          parameters
        );

The above did not work as well, so the order of attributes is the reason!

I guess you can modify your local copy of SqlMapper for now and remove OrderBy() and wait for an official verdict from Marc...

Hope this helps.

OleDbException on writing to MS Access using Dapper

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator