具有Access的Dapper，更新语句部分不起作用 [英] Dapper with Access, update statement partially not working

问题描述

我有一个产品类，并尝试使用Access数据库评估Dapper.选择，删除和插入操作正常，但是更新操作有问题.它仅以下面的一种方式工作)

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)

当我尝试基于 ProductNumber 更改 Description 时，它可以正常工作(updateStatement2)和Description得到更新，但是当我尝试更改 ProductNumber 时，基于说明(updateStatement1)的em>无效，并且ProductNumber不会更新.对我来说有点奇怪.是一个错误还是我错过了任何东西?我的数据库只是一个基本数据库，没有设置主键.我已经在下面附上了屏幕截图

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

我正在使用Dapper 1.50.2版.这是我的数据库截图

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

推荐答案

似乎ADO Access命令要求参数的出现顺序与SQL查询中出现的顺序相同.

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

之所以可行，是因为属性是按字母顺序从"product2"中获取的.这可能不是设计使然，可能只是反射列出它们的顺序.

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

..并且失败，因为参数值在Access中被错误分配.

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

您应该能够通过更改动态参数替代中的参数顺序来确认这一点.我尝试使用动态参数，当参数的顺序与它们在SQL查询中出现的顺序相同时，它起作用了，但如果不是，则失败.我正在使用的数据库与您的数据库不太相同，但是以下内容可以说明我在说什么:

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

在尝试查找有关此问题的更多信息时，我遇到了这个答案，很不幸，该答案似乎可以确认问题: https://stackoverflow .com/a/11424444/3813189

While trying to find more information about this, I came across this answer that unfortunately seems to confirm the issue: https://stackoverflow.com/a/11424444/3813189

我想自定义SQL生成器您在其他问题之一中提到要做一些魔术，以解析查询并按它们必须出现的顺序检索参数，然后确保以正确的顺序提供它们. DapperExtensions的连接器，那么可能值得提出一个问题.因为此刻，我认为您是正确的，并且与图书馆有关.

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.

这篇关于具有Access的Dapper，更新语句部分不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！