Dapper with Access, update statement partially not working

Question

I have a product class and tried to evaluate Dapper with Access database.. Select, Delete and Insert operations are working fine, but I have a problem with update operation. It is working in one way only code below)

When I tried to change the Description based on ProductNumber it works (updateStatement2) and Description get updated, but when I tried to change the ProductNumber based on Description (updateStatement1) it doesn't work and ProductNumber doesn't get updated. It bit strange to me. Is it a bug or am I missing anything?. My database is just a basic one and no primary keys set. I have attached a screenshot below

(For more information see my code below)

public class Products
{
    public string ProductNumber { get; set; }
    public string Description { get; set; }
}

static void Main(string[] args)
{            
    using (var con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb"))
    {
        Products product2 = new Products();
        product2.ProductNumber = "P2";
        product2.Description = "TestProduct2Changed";
        var updateStatement2 = @"Update Products Set Description = @Description Where ProductNumber = @ProductNumber";
        int outp2 = con.Execute(updateStatement2, product2);


        Products product1 = new Products();
        product1.ProductNumber = "P3Changed";
        product1.Description = "TestProduct3";
        var updateStatement1 = @"Update Products Set ProductNumber = @ProductNumber Where Description = @Description";
        int outp1 = con.Execute(updateStatement1, product1);
    }
}

I am using Dapper version 1.50.2. This is my database screenshot

Aldus

Is there whitespace in the value stored (Description)? – Northernmost

It works if we pass like Dynamic parameters. I reckon few issues with Dapper. con.Execute(updateStatement2, new { ProductNumber = "P3Changed", Description = "TestProduct3"}); – Aldus

Answer 1

It looks like ADO Access commands require the parameters to be present in the same order as they appear in the SQL query.

In your original code, for the query that works, the parameters appear in the query string in alphabetical order -

Update Products Set Description = @Description Where ProductNumber = @ProductNumber

This works because the properties are taken from "product2" in alphabetical order. This may not be by design, it might just be the order in which reflection lists them.

In your query that fails, the parameters appear in reverse alphabetical order -

Update Products Set ProductNumber = @ProductNumber Where Description = @Description

.. and this fails because the parameter values get mis-assigned within Access.

You should be able confirm this by changing the order of the parameters in your dynamic parameter alternative. I tried using dynamic parameters and it worked when the parameters were in the same order as which they appeared in the SQL query but failed if they weren't. The database I'm using isn't quite the same as yours but the following should illustrate what I'm talking about:

// Doesn't work (parameter order is incorrect)
con.Execute(
    "Update People Set PersonName = @PersonName Where Notes = @Notes",
    new { Notes = "NotesChanged", PersonName = "New Name" }
);

// DOES work (parameter order is correct)
con.Execute(
    "Update People Set PersonName = @PersonName Where Notes = @Notes",
    new { PersonName = "New Name", Notes = "NotesChanged" }
);

While trying to find more information about this, I came across this answer that unfortunately seems to confirm the issue: https://mcmap.net/q/88102/-is-order-of-parameters-for-database-command-object-really-important

I guess that it might be possible for the custom SQL generator that you've mentioned in one of your other questions to do some magic to parse the query and retrieve the parameters in the order in which they must appear and to then ensure that they are provided in the correct order.. if someone is maintaining an Access connector for DapperExtensions then it might be worth raising an issue. Because, at the moment, I think that you are correct and that it is an issue with the library.

Norse