SqlCommandBuilder生成不正确的update语句

Question

我正在尝试使用SqlDataAdapter来更新数据库中的表。

我从表中检索模式，它用列和主键正确地填充了DataTable。DataTable中只有一个约束，就是正确的三个PK列。

DataSet ds = new DataSet();
DataTable dt = ds.Tables.Add(tableName);
adapter.SelectCommand = new SqlCommand("select * from " + tableName, connection);
adapter.FillSchema(dt, SchemaType.Source);

// Add datarows

我为适配器创建了一个SqlCommandBuilder，...

SqlCommandBuilder cmdBld = new SqlCommandBuilder(adapter);

..。但是它生成的update命令在WHERE子句中包含许多不是主键的字段组合。据我所知，构建器应该根据DataTable的PK约束生成update语句

UPDATE [MyTable] 
SET [CCode] = @p1, 
    [RefNumber] = @p2, 
    [BrandId] = @p3, 
    [BasePrice1] = @p4, 
    [BasePrice2] = @p5,
    ....
WHERE 
    (([CCode] = @p14) -- PK
    AND ([RefNumber] = @p15) -- PK
    AND ([BrandId] = @p16) -- PK
    AND ([BasePrice1] = @p17)
    AND ([BasePrice2] = @p18)
    AND ((@p19 = 1 AND [AddedBy] IS NULL) OR ([AddedBy] = @p20))
    AND ((@p21 = 1 AND [SFlag] IS NULL) OR ([SFlag] = @p22))
    AND ((@p23 = 1 AND [CartonCode] IS NULL) OR ([CartonCode] = @p24))
    AND ((@p25 = 1 AND [AvailableOrder] IS NULL) OR ([AvailableOrder] = @p26))
    AND ((@p27 = 1 AND [AvailableRegistration] IS NULL) OR ([AvailableRegistration] = @p28))
    AND ((@p29 = 1 AND [IsFOC] IS NULL) OR ([IsFOC] = @p30))
    AND ((@p31 = 1 AND [ItemBrand] IS NULL) OR ([ItemBrand] = @p32))
    AND ((@p33 = 1 AND [QuantityMultiply] IS NULL) OR ([QuantityMultiply] = @p34)))

有人能告诉我为什么我没有得到预期的基于PK的更新命令吗？

谢谢。

Answer 1

这个答案有点晚，但请看一下SqlCommandBuilder.ConflictOption属性。默认值是: CompareAllSearchableValues，这是您看到的行为。如果您只想检查主键，请尝试使用OverwriteChanges。

例如：

SqlCommandBuilder cmdBld = new SqlCommandBuilder(adapter);
cmdBld.ConflictOption = ConflictOption.OverwriteChanges; 

对ConflictOption枚举的引用：https://docs.microsoft.com/en-us/dotnet/api/system.data.conflictoption?view=netframework-4.8