Dapper with Access, the update statement partially does not work

Question

I have a product class and you are trying to evaluate Dapper with an Access database. Select, Delete, and Insert operations work fine, but I have a problem with the update operation. It only works in one code below)

When I tried to change the description based on ProductNumber, it works (updateStatement2), and the description will be updated, but when I tried to change the ProductNumber based on Description (updateStatement1), it does not work, and ProductNumber is not updated. This is a little strange for me. Is this a mistake or is something missing? My database is basic and primary keys are not set. I added 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 a screenshot of my database.

Answer 1

It appears that ADO Access commands require the parameters to be present in the same order as in the SQL query.

In the source code for a 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. It may not be in design, it may just be the order in which their reflection is reflected.

In a query that fails, the parameters are displayed in reverse alphabetical order -

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

.. and this fails because parameter values ​​are not assigned in Access.

You must confirm this by changing the order of the parameters in the alternate dynamic parameter. I tried to use dynamic parameters, and it worked when the parameters were in the same order in which they appeared in the SQL query, but failed if they were not. The database I'm using is not exactly 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 additional information about this, I came across this answer, which, unfortunately, confirms this problem: https://stackoverflow.com/a/416829/

I think it's possible to create a custom SQL generator , which you mentioned in one of your other questions, to do some magic analyzing the query and getting the parameters in the order in which they should appear, and then make sure that they are provided in the correct okay. If someone supports an access connector for DapperExtensions, then there may be a problem with the problem. Because at the moment I believe that you are right and that this is a problem with the library.