今天在使用vs2010+Oracle11g+win7 64位中作数据库的批量更新时,出现了:
违反并发性: UpdateCommand影响了预期 1 条记录中的 0 条
这样的错误,感觉很纳闷,因此所用的批量更新的程序我以前多次使用,不过使用的环境为vs2005+Oracle9.2i+xp/win2003下面,下面是以前所用的批量更新的代码:
public static int BulkUpdate(List<FldEngAndChs> Lst, int NonKeyStartIdx,string TableName,DataTable Dt,
int? AutoIncrementIdx, string SeqName, OracleConnection OConn)
{
bool IsNewConn = false;
if (OConn == null)
{
OConn = new OracleConnection(DbInfo.GetConStr());
IsNewConn = true;
}
try
{
OracleDataAdapter Oda = new OracleDataAdapter();
OracleParameter Opar = null;
//插入
string SqlInsert = "insert into " + State.DbUser + "." + TableName + " (";
for (int i = 0; i < Lst.Count; ++i)
SqlInsert += Lst[i].Eng + ",";
SqlInsert = SqlInsert.Substring(0, SqlInsert.Length - 1) + ") values (";
for (int i = 0; i < Lst.Count; ++i)
{
if (AutoIncrementIdx != null && i == AutoIncrementIdx)
SqlInsert += "trim(to_char(" + SeqName + ".nextval,'" + StringOp.Zero10 + "')),";
else
SqlInsert += ":p_" + Lst[i].Eng + ",";
}
SqlInsert = SqlInsert.Substring(0, SqlInsert.Length - 1) + ")";
OracleCommand OCommInsert = new OracleCommand(SqlInsert, OConn);
OCommInsert.Parameters.Clear();
for (int i = (AutoIncrementIdx == null ? 0 : 1); i < Lst.Count; ++i)
{
Opar = new OracleParameter();
Opar.ParameterName = "p_" + Lst[i].Eng;
Opar.SourceColumn = Lst[i].Chs;
if (Dt.Columns[Lst[i].Chs].DataType.ToString() == "System.DateTime")
Opar.OracleDbType = OracleDbType.Date;
OCommInsert.Parameters.Add(Opar);
}
Oda.InsertCommand = OCommInsert;
//更新
string SqlUpdate = "update " + State.DbUser + "." + TableName + " set ";
for (int i = NonKeyStartIdx; i < Lst.Count; ++i)
SqlUpdate += Lst[i].Eng + "=:p_" + Lst[i].Eng + ",";
SqlUpdate = SqlUpdate.Substring(0, SqlUpdate.Length - 1) + " where ";
for (int i = 0; i < NonKeyStartIdx; ++i)
{
SqlUpdate += Lst[i].Eng + "=:p_" + Lst[i].Eng;
if (i < NonKeyStartIdx - 1)
SqlUpdate += " and ";
}
OracleCommand OCommUpdate = new OracleCommand(SqlUpdate, OConn);
OCommUpdate.Parameters.Clear();
for (int i = 0; i < Lst.Count; ++i)
{
Opar = new OracleParameter();
Opar.ParameterName = "p_" + Lst[i].Eng;
Opar.SourceColumn = Lst[i].Chs;
if (Dt.Columns[Lst[i].Chs].DataType.ToString() == "System.DateTime")
Opar.OracleDbType = OracleDbType.Date;
OCommUpdate.Parameters.Add(Opar);
}
Oda.UpdateCommand = OCommUpdate;
//删除
string SqlDelete = "delete from " + State.DbUser + "." + TableName + " where ";
for (int i = 0; i < NonKeyStartIdx; ++i)
{
SqlDelete += Lst[i].Eng + "=:p_" + Lst[i].Eng;
if (i < NonKeyStartIdx - 1)
SqlDelete += " and ";
}
OracleCommand OcommDelete = new OracleCommand(SqlDelete, OConn);
OcommDelete.Parameters.Clear();
for (int i = 0; i < NonKeyStartIdx; ++i)
{
Opar = new OracleParameter();
Opar.ParameterName = "p_" + Lst[i].Eng;
Opar.SourceColumn = Lst[i].Chs;
OcommDelete.Parameters.Add(Opar);
}
Oda.DeleteCommand = OcommDelete;
//开始更新
int AffectedRow = Oda.Update(Dt);
return AffectedRow;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (OConn.State == ConnectionState.Open && IsNewConn)
OConn.Close();
}
}
以上的批量更新代码在插入、删除时没有问题,问题在于修改某行的数据时就会出现上述的问题,查阅了大量的资料后发现问题在于更新时的参数的顺序有问题,可能在Oracle11g中要求Sql语句中的参数顺序与UpdateCommand设置的参数顺序必须严格一致,在上述的代码中Lst中的序号从0~NonKeyStartIdx-1为主键,从NonKeyStartIdx之后的为非主键,而在UpdateSqlStr中的参数的顺序为NonKeyStartIdx~Lst.Count,然后再添加参数:0~NonKeyStartIdx-1的,而之后的 OCommUpdate.Parameters的参数添加的顺序则为0~Lst.Count,这样就导致前后参数的顺序不一致,明白这一点后,则将更新部分添加参数部分的代码修改成如下的,则更新顺利进行了。
OracleCommand OCommUpdate = new OracleCommand(SqlUpdate, OConn);
OCommUpdate.Parameters.Clear();
for (int i = NonKeyStartIdx; i < Lst.Count; ++i)
{
Opar = new OracleParameter();
Opar.ParameterName = "p_" + Lst[i].Eng;
Opar.SourceColumn = Lst[i].Chs;
if (Dt.Columns[Lst[i].Chs].DataType.ToString() == "System.DateTime")
Opar.OracleDbType = OracleDbType.Date;
OCommUpdate.Parameters.Add(Opar);
}
for (int i = 0; i < NonKeyStartIdx; ++i)
{
Opar = new OracleParameter();
Opar.ParameterName = "p_" + Lst[i].Eng;
Opar.SourceColumn = Lst[i].Chs;
if (Dt.Columns[Lst[i].Chs].DataType.ToString() == "System.DateTime")
Opar.OracleDbType = OracleDbType.Date;
OCommUpdate.Parameters.Add(Opar);
}
导致这一问题的原因应该是Oracle11g对参数的顺序有了更为严格的规定,而在Oracle9.2i中则只要参数集合中的元素相同即可,这一严格要求,不知道要折腾多少人啊。