Dapper & MS Access - Read works, Write doesn't

Question

Let's start by getting this out of the way: I'm stuck using an MS Access DB and I can't change it.

This works fine:

using (OleDbConnection conn = ConnectionHelper.GetConnection())
{
  conn.Open();
  var results = conn.Query<string>(
    "select FirstName from Students where LastName = @lastName", 
    new { lastName= "Smith" }
  );
  conn.Close();
}

This works fine:

using (OleDbConnection conn = ConnectionHelper.GetConnection())
{
  OleDbCommand cmd = new OleDbCommand(
    "update Students set FirstName = @firstName, City = @city where LastName = @lastName", 
    conn
  );
  cmd.Parameters.AddWithValue("firstName", "John");
  cmd.Parameters.AddWithValue("city", "SomeCity");
  cmd.Parameters.AddWithValue("lastName", "Smith");

  conn.Open();
  var result = cmd.ExecuteNonQuery();
  conn.Close();
}

This doesn't... it executes without error but it sets the FirstName as "SomeCity" in the DB and the City as "John":

using (OleDbConnection conn = ConnectionHelper.GetConnection())
{
  conn.Open();
  var results = conn.Query<string>(
    "update Students set FirstName = @firstName, City = @city where LastName = @lastName", 
    new { firstName = "John", city = "SomeCity", lastName = "Smith" }
  );
  conn.Close();
}

Any ideas?

EDIT BELOW

Dapper works if I use DynamicParameters:

using (OleDbConnection conn = ConnectionHelper.GetConnection())
{
  DynamicParameters parameters = new DynamicParameters();
  parameters.Add("firstName", "John");
  parameters.Add("city", "SomeCity");
  parameters.Add("lastName", "Smith");

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

Summarize

That's odd.. I'll need to try to repro... – Sadden

Note: Just tried to reproduce with sql express 2008 and it works fine... – Hagiographa

I've done this successfully with Sql Server... so I think this is with MS Access only – Summarize

@MarcGravell See the accepted answer below, and my comment... do you know if removing the .OrderBy(p => p.Name) code will affect other parts of Dapper? – Summarize

@Summarize intriguing; I will have to check – Sadden

According to a comment to the answer here, this is no longer an issue with the current version of Dapper. – Hymnody

@GordThompson the issue is not fixed still. We need to preserve the order still. Please refer this question #39339978 – Dolan

@Dolan - Thanks for the update. I seem to recall testing a subsequent version of Dapper itself and confirming that it no longer sorted the parameters by name, but it was quite a while ago and I can no longer be certain of that. I wonder if Dapper-Extensions may still be sorting the parameters by name, perhaps because it is still tied to an older version of Dapper. (Dapper-Extensions on GitHub has not been updated in over two years.) – Hymnody

Answer 1

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.

Hagiographa

It turns out MS Access is fussy about the order of the parameters. I tried removing the .OrderBy(p => p.Name) code from SqlMapper and it works now. I'd also like to know from Marc or Sam whether removing this code would have any implications elsewhere. – Summarize

I did not know that as well; good to know! And I'm also curious about the purpose of the OrderBy() clause. – Hagiographa

According to a comment to the answer here, this is no longer an issue with the current version of Dapper. – Hymnody

Having this issue still on 1.42 from NuGet. – Backscratcher

@Bob Having this issue still in 1.50.2 – Dolan

Answer 2

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 });
}

Unroof

Things may have changed since you last worked with Dapper. According to a comment to the answer here, this is no longer an issue with the current version. – Hymnody

@GordThompson Thanks for the link. I am having the same problem with version 1.13 via NuGet. – Unroof

I am having same issue in 1.50.2 – Dolan