程序中实现回滚,无法回滚的问题,请帮助分析
现场: 使用批处理往数据库服务器上提交记录,但有时候出现重复提交记录的情况;程序大致的逻辑是这样的,每次提交时,如果出现错误或者超市,则执行回滚,等下次继续提交。
试了MSDN上C# SqlBulkCopy示例代码提供的2种事务方式都不行
以前采用数据库连接中BeginTransaction 事务方式,日志中看到:有Rollback() Exception! 回滚异常 代码见“事务1”,后来改成了利用SqlBulkCopy本身带的事务,NEW SqlBulkCopy 增加参数:SqlBulkCopyOptions.UseInternalTransaction 代码见“事务2”.
事务1:
SqlTransaction pTrans = null;
bool bResult = false;
try
{
pTrans = db.Sqlconnection.BeginTransaction();
using (SqlBulkCopy bcp = new SqlBulkCopy(db.Sqlconnection, SqlBulkCopyOptions.FireTriggers, pTrans))
{
try
{
bcp.DestinationTableName = table.TableName;
bcp.WriteToServer(table);
pTrans.Commit();
bResult = true;
}
catch (System.Exception e)
{
bool bRollback = false;
try
{
pTrans.Rollback(); --回滚
bRollback = true;
}
catch (System.Exception ex)
{
String strErrorMessage1 = "Rollback() Exception!";
bRollback = false;
}
if (bRollback)
{
AnalysisException(e, table);
}
bool result = db.Open(); //重连
if (result)
{
error = "reconnect successfully ";
}
else
{
error = "reconnect failed ";
}
return false;
}
}
}
catch (System.Exception e)
{
bool result = db.Open(); //重连
String error;
if (result)
{
error = "reconnect successfully ";
}
else
{
error = "reconnect failed ";
}
return false;
}
事务2:
bool bResult = false;
try
{
using (SqlBulkCopy bcp = new SqlBulkCopy(db.Sqlconnection, SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null))
{
try
{
bcp.DestinationTableName = table.TableName;
bcp.WriteToServer(table);
bResult = true;
}
catch (System.Exception e)
{
bool result = db.Open(); //重连
String error;
if (result)
{
AnalysisException(e, table);
error = "reconnect successfully ";
}
else
{
error = "reconnect failed ";
}
return false;
}
}
}
catch (System.Exception e)
{
bool result = db.Open(); //重连
String error;
if (result)
{
error = "reconnect successfully ";
}
else
{
error = "reconnect failed ";
}
return false;
}