Dapper & MS Access - reading works, writes not

Question

To begin with, this is out of the question: I'm stuck using MS Access DB, and I can't change it.

This works great:

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 great:

 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 is not ... it runs without errors, but sets the FirstName value as "SomeCity" in the database and 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?

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

Answer 1

After some digging, I was able to find the reason:

The following is a delegate to CreateParamInfoGenerator from dapper SqlMapper:

  public static Action<IDbCommand, object> CreateParamInfoGenerator(Identity identity) { // code above here IEnumerable<PropertyInfo> props = type.GetProperties().OrderBy(p => p.Name); 

Details is your unanimous parameter, which is ordered by OrderBy (p => p.Name), which moves the city forward.

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

Then the details are added to the IDbCommand parameters, where the order is important.

If I comment on the OrderBy () clause, then everything will work.

I also tested DynamicParameters and intentionally reordered the attributes for moving around the city:

  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, so the order of the attributes is the reason!

I think you can now change your local copy of SqlMapper and remove OrderBy () and wait for the official sentence from Marc ...

Hope this helps.

Answer 2

I had a similar problem, I did this instead of use parameter names like @param1, @param2 instead of @name, @id, @price so that the order remains the same without changing the 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 }); }