问题描述
我用OleDbDataAdapter的和OleDbCommandBuilder来填充DataSet对象与数据库内容，然后根据我在DataSet做了修改更新数据库。问题是，我得到异常：并发冲突：UpdateCommand会影响预期1条记录0。我发现这个错误的解释：
I use OleDbDataAdapter and OleDbCommandBuilder to fill DataSet object with database contents, and then update database according to a changes that I made in the DataSet. The problem is that I get the exception: "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records". I've found an explanation of this error:
由于创纪录的可能它是从
中的SELECT返回后，已被修改声明中，但在此之前的UPDATE或DELETE语句执行
，自动生成的UPDATE或DELETE语句
包含一个WHERE子句，指定行则仅当
和更新包含所有原始值尚未从数据
源中删除。其中，自动生成的更新试图更新已删除的
行或不包含在DataSet中找到的原始值
，则该命令不影响任何记录，和
DBConcurrencyException被抛出。
这意味着，自动生成的UPDATE命令在数据库中受影响的0行。我与悖论（DB-文件），数据库的工作，没有人改变它除了我。我想我的程序更改了同一行的地方两次。我想通过手动执行所有生成的查询，发现其中一个不会影响任何行（因为实际上我敢肯定，所有的改变都只有一次和错误是其他地方）））来调试我的程序。是否可以运行自动手动生成的命令？
That means that auto generated UPDATE command affected 0 rows in the database. I work with paradox(db-file) database and no one changes it except for me. I guess that my program changes the same row two times somewhere. I wanted to debug my program by executing all generated queries manually and finding which one doesn't affect any row(because actually I'm pretty sure that all changes are made only once and the bug is somewhere else))). Is it possible to run auto generated commands manually?
我的代码是太大而复杂，张贴在这里，但通常它的工作原理是这样的（我做了一个工作项目，把它从那里）
My code is too big and complicated to post it here but generally it works like this(I made a working project and took it from there)
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;
namespace OleDBCommandBuilder
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string cs = @"Provider=Microsoft.Jet.OLEDB.4.0;";
cs += @"Data Source=C:\FOLDER\1\SPR_KMZ\;";
cs += @"Extended Properties=Paradox 5.x;";
OleDbConnection Connection = new OleDbConnection();
Connection.ConnectionString = cs;
try
{ Connection.Open(); }
catch (Exception ex)
{ MessageBox.Show("Error openning database! " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); Environment.Exit(0); }
string SQLQuery = "SELECT * FROM SPR_KMZ WHERE REZ<>0";
DataSet SPR_KMZ = new DataSet();
OleDbDataAdapter DataAdapter = new OleDbDataAdapter();
DataAdapter.SelectCommand = new OleDbCommand(SQLQuery, Connection);
OleDbCommandBuilder builder = new OleDbCommandBuilder(DataAdapter);
try
{
DataAdapter.Fill(SPR_KMZ);
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(String.Format("Error \n{0}\n{1}", ex.Message, SQLQuery));
Environment.Exit(0);
}
DataRow[] SPR_KMZ_rows = SPR_KMZ.Tables[0].Select("Fkmz=10000912 AND REZ=1");
foreach (DataRow SPR_KMZ_row in SPR_KMZ_rows)
{
SPR_KMZ_row["DN"] = Convert.ToDateTime("30.12.1899");//26.12.2008
SPR_KMZ_row["Price"] = Convert.ToDouble(0);//168,92
}
DataAdapter.Update(SPR_KMZ);
System.Windows.Forms.MessageBox.Show("Success!");
Environment.Exit(0);
}
}
}
P.S。以前它没有更新并发异常数据库，但很多的变化后（我注释掉行DataAdapter.Update（SPR_KMZ）;很长一段时间进行调试的原因，所以我不知道什么时候开始这个错误扔）
P.S. Previously it updated the database without concurrency exception, but after a lot of changes(I commented out the line "DataAdapter.Update(SPR_KMZ);" for a long time for debugging reason, so I don't know when exactly this error started to throw)
PSS有在我的代码没有插入或删除，只更新...
P.S.S. there are no INSERTs or DELETEs in my code, only UPDATEs...
<< UPDATE>>
<<UPDATE>>
我发现是什么问题：如果DN字段改变后有NULL值，那么，自动生成的UPDATE语句不影响任何东西，显然是因为DN是包含在主键和命令建设者没想到主键字段中有NULL值（谁曾会））），毫无疑问这款发动机被称为悖论）））
I've found what was the problem: if "DN" field has NULL value then after changing it, the auto-generated UPDATE Statement don't affect anything, obviously because "DN" is contained in a primary key and command builder didn't expect for primary key field to have NULL values(who ever would))), no surprise this engine is called "Paradox")))
这就是为什么在
CommandBuilder.GetUpdateCommand().CommandText
在where子句DN字段中有这样一种模式：
in where clause for "DN" field there was this kind of pattern:
... WHERE ((REZ = ?) AND (DN = ?) AND ...
而？可空字段这样的描述：
while nullable fields are described like this:
... AND ((? = 1 AND Price IS NULL) OR (Price = ?)) AND ((? = 1 AND Nmed IS NULL) OR (Nmed = ?)) AND ...
PSSS嘿，我可以尝试手动设置更新命令来解决这个问题！）））
P.S.S.S. Hey, I can try to set UpdateCommand manually to fix this!)))
推荐答案
下面是我'已经成功地手动设置的UpdateCommand甚至获得SQL代码为正在执行的每个UPDATE命令！（或多或少））
Here is how I've managed to set the UpdateCommand manually and even get SQL code for every UPDATE command that is being executed!(more or less))
public void Update(DataSet DBDataSet)
{
DataAdapter.RowUpdating += before_update;
DataAdapter.Update(DBDataSet);
}
public void before_update(object sender, EventArgs e)
{
//Convert EventArgs to OleDbRowUpdatingEventArgs to be able to use OleDbCommand property
System.Data.OleDb.OleDbRowUpdatingEventArgs oledb_e = (System.Data.OleDb.OleDbRowUpdatingEventArgs) e;
//Get query template
string cmd_txt = oledb_e.Command.CommandText;
//Modify query template here to fix it
//cmd_txt = cmd_txt.Replace("table_name", "\"table_name\"");
//fill tamplate with values
string cmd_txt_filled = cmd_txt;
foreach(System.Data.OleDb.OleDbParameter par in oledb_e.Command.Parameters)
{
string par_type = par.DbType.ToString();
string string_to_replace_with = "";
if (par.Value.GetType().Name == "DBNull")
{
string_to_replace_with = "NULL";
}
else
{
if (par_type == "Int32")
{
par.Size = 4;
string_to_replace_with=Convert.ToInt32(par.Value).ToString();
}
else if (par_type == "Double")
{
par.Size = 8;
string_to_replace_with=Convert.ToDouble(par.Value).ToString().Replace(",",".");
}
else if (par_type == "DateTime")
{
par.Size = 8;
/* In Paradox SQL queries you can't just specify the date as a string,
* it will result in incompatible types, you have to count the days
* between 30.12.1899 and the required date and specify that number
*/
string_to_replace_with = DateToParadoxDays(Convert.ToDateTime(par.Value).ToString("dd.MM.yyyy"));
}
else if (par_type == "String")
{
string_to_replace_with = '"' + Convert.ToString(par.Value) + '"';
}
else
{
//Break execution if the field has a type that is not handled here
System.Diagnostics.Debugger.Break();
}
}
cmd_txt_filled = ReplaceFirst(cmd_txt_filled, "?", string_to_replace_with);
}
cmd_txt_filled = cmd_txt_filled.Replace("= NULL", "IS NULL");
//Get query text here to test it in Database Manager
//System.Diagnostics.Debug.WriteLine(cmd_txt_filled);
//Uncomment this to apply modified query template
//oledb_e.Command.CommandText = cmd_txt;
//Uncomment this to simply run the prepared update command
//oledb_e.Command.CommandText = cmd_txt_filled;
}
public string ReplaceFirst(string text, string search, string replace)
{
int pos = text.IndexOf(search);
if (pos < 0)
{
return text;
}
return text.Substring(0, pos) + replace + text.Substring(pos + search.Length);
}
private static string DateToParadoxDays(string date)
{
return (Convert.ToDateTime(date) - Convert.ToDateTime("30.12.1899")).TotalDays.ToString();
}
这篇关于获取OleDbCommandBuilder生成的SQL命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！