oracle数据库事务不能Rollback（）的问题。（c#编码）
public Execute_SQL_Array(ArrayList CommandList ) 说明：CommandList 中存放一组SQL语句
{
//创建一个连接实例
if ( ConnectDatabase () < 0)
{
//连接数据库失败
return ;
}
OracleCommand _Command = new OracleCommand () ;
_Command.Connection =mConn.Conn ;
//创建事务
OracleTransaction _trans;
//启动一个事务
_trans = mConn.Conn.BeginTransaction() ;
_Command.Transaction = _trans ;
int i = 0;
try
{
for ( i = 0; i < CommandList.Count ; i++)
{
_Command.CommandType = CommandType.Text ;
_Command.CommandText = CommandList[i].ToString ();
_Command.ExecuteNonQuery() ;
}
//提交事务
_trans.Commit () ;
}
catch (Exception ex)
{
mResult = -40003 ;
mResultString = "执行SQL语句错误: "+ex.ToString ()+ "\r\n "+CommandList[i].ToString () ;
_trans.Rollback(); return ;
}
finally
{
mConn.Conn.Close () ;
}
}
CommandList 中存放一组SQL语句如下：
"insert into \ "TableList\ " (\ "ID\ ",\ "tableName\ ",\ "tableNameCN\ ",\ "createDepart\ ",\ "createDate\ ",\ "isInUse\ ") values (47, 'UDT_0002_NJXX ', '年检信息 ', '0002 ',SYSDATE,0) "
"insert into \ "ColumnList\ " (\ "ID\ ",\ "tableBelongTo\ ",\ "columnName\ ",\ "columnNameCN\ ",\ "dataType\ ",\ "showOrder\ ",\ "ColumnNameDescript\ ",\ "ShowMaster\ ") values (128, 'UDT_0002_NJXX ', 'QYZCH ', '企业注册号 ', 'varchar2(26) ',001, '企业注册号码 ',0) "
"insert into \ "ColumnList\ " (\ "ID\ ",\ "tableBelongTo\ ",\ "columnName\ ",\ "columnNameCN\ ",\ "dataType\ ",\ "showOrder\ ",\ "ColumnNameDescript\ ",\ "ShowMaster\ ") values (129, 'UDT_0002_NJXX ', 'QYMC ', '企业名称 ', 'varchar2(100) ',002, '企业登记的名称 ',0) "
"insert into \ "ColumnList\ " (\ "ID\ ",\ "tableBelongTo\ ",\ "columnName\ ",\ "columnNameCN\ ",\ "dataType\ ",\ "showOrder\ ",\ "ColumnNameDescript\ ",\ "ShowMaster\ ") values (130, 'UDT_0002_NJXX ', 'NJND ', '年检年度 ', 'varchar2(4) ',003, '工商年检年度 ',0) "
"insert into \ "ColumnList\ " (\ "ID\ ",\ "tableBelongTo\ ",\ "columnName\ ",\ "columnNameCN\ ",\ "dataType\ ",\ "showOrder\ ",\ "ColumnNameDescript\ ",\ "ShowMaster\ ") values (131, 'UDT_0002_NJXX ', 'NJJG ', '年检情况 ', 'varchar2(1000) ',004, '年检情况 ',0) "
"Create Table \ "UDT_0002_NJXX\ " (QYZCH varchar2(26) NOT NULL,QYMC varchar2(100) NOT NULL,NJND varchar2(4) NOT NULL,NJJG varchar2(1000) NOT NULL) "
该方法可以执行，但是第二次执行时Rollback（）不能退回。
请高手指导一下，oracle刚刚接触，谢谢！！
[解决办法]
第一次rollback 后,要把事务先清空.
_trans.Rollback();
_trans.dispose();
_trans = null
[解决办法]
你第一次能够退回?
[解决办法]
DDL有自己的transaction，不会包含在现在的事务里面。
ps：我狂不喜欢你的代码结构
[解决办法]
你可以这样试验一下,即一条命令用一个Command...
for example:
....
SqlCommand cmd1=new SqlCommand(commandList[0].ToString(),con);
cmd1.Transaction=tran;
SqlCommand cmd2=new SqlCommand(commandList[1].ToString(),con);
cmd2.Transaction=tran;
....
//分别执行...
这样是绝对可行的...
你出现那样的问题,我怀疑是不是因为你把所有的sql都用的同一个Command,才出现的这个原因呢?
你的那种方式我没用过,我上面写的这种方式用过很多次了,可行..
试试看,仅供参考...
[解决办法]
for ( i = 0; i < CommandList.Count ; i++)
{
try
{
_Command.CommandType = CommandType.Text ;
_Command.CommandText = CommandList[i].ToString ();
_Command.ExecuteNonQuery() ;
//提交事务
_trans.Commit () ;
}
catch (Exception ex)
{
mResult = -40003 ;
mResultString = "执行SQL语句错误: "+ex.ToString ()+ "\r\n "+CommandList[i].ToString () ;
_trans.Rollback(); return ;
}
finally
{
mConn.Conn.Close () ;
}
}
