DBConcurrency出现异常，同时更新使用的DataAdapter [英] DBConcurrency Exception Occured While Updating Using Dataadapter

问题描述

我试图修改数据表按 NpgsqlDataAdapter 填充。
调用后填写（）的方法，我在只有一行数据表。然后，我改变只有一列的值，并试图如下更新。

I am trying to edit DataTable Filled by NpgsqlDataAdapter. After calling Fill() method, I have only one row in DataTable. Then I changed value of one column only and tried to update as below.

的

然后我得到这个错误：

DBConcurrencyException发生

DBConcurrencyException occured

我的代码是：

NpgsqlDataAdapter getAllData = new NpgsqlDataAdapter("SELECT sn,
code,product, unitprice, quantity, InvoiceNo, Date FROM stocktable WHERE Code='" + product + "'
 ORDER BY EDate ASC", DatabaseConnectionpg);
DataTable ds1 = new DataTable();
ds1.Clear();
getAllData.Fill(ds1);

if (ds1.Rows.Count > 0)
{
    ds1.Rows[0]["Quantity"] = qty;// calculated value
}
ds1 = ds1.GetChanges();

NpgsqlCommandBuilder cb = new NpgsqlCommandBuilder(getAllData);
//getAllData.RowUpdating += (sender2, e2) => { e2.Command.Parameters.Clear(); };
//cb.SetAllValues = false;
getAllData.DeleteCommand = cb.GetDeleteCommand();
getAllData.InsertCommand = cb.GetInsertCommand();
getAllData.UpdateCommand = cb.GetUpdateCommand();
int x = getAllData.Update(ds1);
if (x > 0)
{
    ds1.AcceptChanges();
}

编辑：我有三个字段作为主键和我打电话只有两个select语句的字段。它是原因 DBConcurrency 错误？但我能更新相同的表（三个字段作为主键）参数在SQL Server 2005

I have three fields as primary keys and I am calling only two fields in select statement. Is it reason for DBConcurrency error? But I am able to update the table with same (three fields as primary key) parameters in SQL Server 2005.

更新：

我发现溶液并将该溶液是
口创建和使用第二DataAdapter的更新数据。
我用的 getAllData （NpgSqlDataAdapter）填写表格为

I found the solution and the solution is I created and used second DataAdapter to update data. I used getAllData(NpgSqlDataAdapter) To fill table as

NpgsqlDataAdapter getAllData = new NpgsqlDataAdapter("SELECT 
code,product, unitprice, quantity, InvoiceNo, Date FROM stocktable WHERE Code='" + product + "'
 ORDER BY EDate ASC", DatabaseConnectionpg);

又创造下一个适配器更新为

And Also created next Adapter to update as

NpgsqlDataAdapter updateadap= new NpgsqlDataAdapter("SELECT sn, quantity FROM stocktable WHERE Code='" + product + "'
 ORDER BY EDate ASC", DatabaseConnectionpg);
NpgsqlCommandBuilder cb = new NpgsqlCommandBuilder(updateadap);
    //getAllData.RowUpdating += (sender2, e2) => { e2.Command.Parameters.Clear(); };
    //cb.SetAllValues = false;
    updateadap.DeleteCommand = cb.GetDeleteCommand();
    updateadap.InsertCommand = cb.GetInsertCommand();
    updateadap.UpdateCommand = cb.GetUpdateCommand();
    int x = updateadap.Update(ds1);
    if (x > 0)
    {
        ......
    }

我想了很多，发现NpgsqlDataAdapter曾与列问题的代码的。当我ommited它，然后它的工作。列码数据类型为varchar。我不知道为什么这发生。任何人有想法呢？

I tried alot and found that NpgsqlDataAdapter had problem with Column Code. When i ommited it then it worked. DataType of column code is varchar. I don't know why this was happening. Anybody has idea about it?

推荐答案

这是因为的DataAdapter 使用< A HREF =htt​​ps://msdn.microsoft.com/en-us/library/aa0416cz(v=vs.110).aspx相对=nofollow> 乐观并发默认的。这意味着，如果你正试图更新数据库中已不存在或改变，从更新一排的DataAdapter 将失败与上面的除外。

This is because DataAdapter uses Optimistic Concurrency by default. This means that if you are trying to update a row that no longer exists in the database or changed, the update from the DataAdapter will fail with the exception above.

可能出现的情况

• 您之间进行选择数据到客户端和发送
  更新其他用户已删除或从自己的应用程序更新此行。


• 它可以是你从别的某处删除数据您的应用程序

例如：

1. 您填写数据表将用于更新。


2. 删除一行代码= 1101 （例如）直接从数据库，即你不使用数据表在这里。这是模拟另一个用户删除与行代码= 1101 从其他应用程序。或者在你的代码其他部分删除与行代码= 1101 。


3. 选择了以行代码= 1101 从数据表，这只是表明，它仍然存在，即使您已在数据库本身删除了它。


4. 编辑行中的数量柱代码= 1101 在数据表。这必须完成，否则调用Update进行更新时会忽略此行。


5. 执行更新，这将抛出，因为你正试图更新一行的异常（不再）存在于数据库中。

1. You fill the DataTable that will be used for the update.
2. Deletes the row with Code = 1101 (for example) directly from the database, i.e. you do not use the DataTable here. This is emulating another user deleting the row with Code = 1101 from another application. Or some other part in your code deleting the row with Code = 1101.
3. Selects out the row with Code = 1101 from the DataTable, this is just to show that it is still there even though you have deleted it from the database itself.
4. Edits the Quantity column in the row with Code = 1101 in the DataTable. This has to be done, otherwise the call to Update will ignore this row when updating.
5. Executes the update, this will throw the exception since you are trying to update a row that (no longer) exists in the database.

如果你想实现的 最后写入者优先 ，添加以下代码：

If you want to implement Last Writer Wins, Add the following code:

cb.ConflictOption = ConflictOption.OverwriteChanges;

另外还有一个可能的事情：如果你有小数 / 数字作为数据库列，他们可能会导致数据虽然看起来是一样的这个错误。这是由于小数舍入误差

Also there is one more possible thing : if you have Decimal/numeric as columns in the DB they may cause this error even though the data looks the same. This is due to a decimal rounding error.

重要提示：
你应该总是使用的 参数化查询 顺便一提。这类字符串连接的是开放的 SQL注入

这篇关于DBConcurrency出现异常，同时更新使用的DataAdapter的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！