/// <summary>
/// 执行Sql和Oracle滴混合事务
/// </summary>
/// <param name="list">SQL命令行列表</param>
/// <param name="oracleCmdSqlList">Oracle命令行列表</param>
/// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
public static int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo myDE in list)
{
string cmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (isHave)
{
//引发事件
myDE.OnSolicitationEvent();
}
}
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
//return 0;
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
//return 0;
}
continue;
}
int val = cmd.ExecuteNonQuery();
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
//return 0;
}
cmd.Parameters.Clear();
}
string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
if (!res)
{
tx.Rollback();
throw new Exception("Oracle执行失败");
// return -1;
}
tx.Commit();
return(1);
}
catch (System.Data.SqlClient.SqlException e)
{
tx.Rollback();
throw e;
}
catch (Exception e)
{
tx.Rollback();
throw e;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="list"></param>
/// <param name="oracleCmdSqlList"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
/// <exception cref="SqlException"></exception>
public int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList)
{
int num2;
using (SqlConnection connection = new SqlConnection(this.connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand
{
Connection = connection
};
SqlTransaction trans = connection.BeginTransaction();
cmd.Transaction = trans;
try
{
foreach (CommandInfo info in list)
{
object obj2;
bool flag;
string commandText = info.CommandText;
SqlParameter[] parameters = (SqlParameter[])info.Parameters;
PrepareCommand(cmd, connection, trans, commandText, parameters);
if (info.EffentNextType == EffentNextType.SolicitationEvent)
{
if (info.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();
throw new Exception("违背要求" + info.CommandText + "必须符合select count(..的格式");
}
obj2 = cmd.ExecuteScalar();
flag = false;
if ((obj2 == null) && (obj2 == DBNull.Value))
{
flag = false;
}
flag = Convert.ToInt32(obj2) > 0;
if (flag)
{
info.OnSolicitationEvent();
}
}
if ((info.EffentNextType == EffentNextType.WhenHaveContine) || (info.EffentNextType == EffentNextType.WhenNoHaveContine))
{
if (info.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();
throw new Exception("SQL:违背要求" + info.CommandText + "必须符合select count(..的格式");
}
obj2 = cmd.ExecuteScalar();
flag = false;
if ((obj2 == null) && (obj2 == DBNull.Value))
{
flag = false;
}
flag = Convert.ToInt32(obj2) > 0;
if (!((info.EffentNextType != EffentNextType.WhenHaveContine) || flag))
{
trans.Rollback();
throw new Exception("SQL:违背要求" + info.CommandText + "返回值必须大于0");
}
if ((info.EffentNextType == EffentNextType.WhenNoHaveContine) && flag)
{
trans.Rollback();
throw new Exception("SQL:违背要求" + info.CommandText + "返回值必须等于0");
}
}
else
{
int num = cmd.ExecuteNonQuery();
if ((info.EffentNextType == EffentNextType.ExcuteEffectRows) && (num == 0))
{
trans.Rollback();
throw new Exception("SQL:违背要求" + info.CommandText + "必须有影响行");
}
cmd.Parameters.Clear();
}
}
if (!OracleHelper.ExecuteSqlTran(PubConstant.GetConnectionString("ConnectionStringPPC"), oracleCmdSqlList))
{
trans.Rollback();
throw new Exception("Oracle执行失败");
}
trans.Commit();
num2 = 1;
}
catch (SqlException exception)
{
trans.Rollback();
throw exception;
}
catch (Exception exception2)
{
trans.Rollback();
throw exception2;
}
}
return(num2);
}