private static void GetParameterOut(LBDbParameterCollection tsParms, System.Data.Common.DbParameterCollection dbParms)
{
if (tsParms != null && tsParms.Count > 0)
{
foreach (LBDbParameter parm in tsParms)
{
if (parm.Direction != ParameterDirection.Input)
{
parm.Value = dbParms["@" + parm.ParameterName].Value;
}
}
}
}
private static void GetParameterOut(LBDbParameterCollection tsParms, System.Data.Common.DbParameterCollection dbParms, DataTable dtResult)
{
if (tsParms != null && tsParms.Count > 0)
{
foreach (LBDbParameter parm in tsParms)
{
if (parm.Direction != ParameterDirection.Input)
{
if (dtResult != null &&
dtResult.Rows.Count > 0 && dtResult.Columns.Contains(parm.ParameterName))
{
parm.Value = dtResult.Rows[0][parm.ParameterName];
}
else
{
parm.Value = dbParms["@" + parm.ParameterName].Value;
}
}
}
}
}
//public static DataSet ExecuteQuery4DataSet( DALExecuteArgs args, string cmdText, TSDbParameterCollection cmdParms )
//{
// return ExecuteQuery4DataSet( args, CommandType.Text, cmdText, cmdParms, enQueryTransType.ReadCommitted );
//}
#endregion --DataSet-
#endregion -- 查询 --
#region -- 参数的处理 --
private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, LBDbParameterCollection cmdParms)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (LBDbParameter parm in cmdParms)
{
if (parm != null)
{
cmd.Parameters.Add(Provider.CreateParameter(parm));
}
}
}
}
public static DataSet ExecuteQuery4DataSet(FactoryArgs args, string cmdText, LBDbParameterCollection cmdParms, enQueryTransType transType)
{
return(ExecuteQuery4DataSet(args, CommandType.Text, cmdText, cmdParms, transType));
}
private static DataSet ExecuteQuery4DataSet(FactoryArgs args, CommandType cmdType, string cmdText, LBDbParameterCollection cmdParms, enQueryTransType transType)
{
DataSet dsResult = null;
if (args.DbConnection == null)
{
if (transType != enQueryTransType.Non)
{
#region -- 没有传入 DbConnection，需要事务 --
using (DbConnection conn = Provider.CreateConnection(args.DBName))
{
conn.Open();
IsolationLevel level;
if (transType == enQueryTransType.Snapshot && Provider.SnapshotSupported(args.DBName))
{
level = IsolationLevel.Snapshot;
}
else
{
level = IsolationLevel.ReadCommitted;
}
using (DbTransaction trans = conn.BeginTransaction(level))
{
using (DbCommand cmd = Provider.CreateCommand())
{
try
{
// 准备参数
PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);
// 执行
dsResult = new DataSet();
DbDataAdapter adapter = Provider.CreateDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dsResult);
// 提交
trans.Commit();
// 取输出参数值
GetParameterOut(cmdParms, cmd.Parameters);
return(dsResult);
}
catch (Exception ex)
{
try
{
trans.Rollback();
}
catch
{
}
throw ex;
}
}
}
}
#endregion -- 没有传入 DbConnection，需要事务 --
}
else
{
#region -- 没有传入 DbConnection，不需要事务 --
using (DbConnection conn = Provider.CreateConnection(args.DBName))
{
using (DbCommand cmd = Provider.CreateCommand())
{
// 准备参数
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
// 执行
dsResult = new DataSet();
DbDataAdapter adapter = Provider.CreateDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dsResult);
// 取输出参数值
GetParameterOut(cmdParms, cmd.Parameters);
return(dsResult);
}
}
#endregion -- 没有传入 DbConnection，不需要事务 --
}
}
else if (args.DbTrans != null)
{
#region -- 同时传入 DbConnection, DbTrans：事务 Commit, Rollback 由外部控制 --
using (DbCommand cmd = Provider.CreateCommand())
{
// 准备参数
PrepareCommand(cmd, args.DbConnection, args.DbTrans, cmdType, cmdText, cmdParms);
// 执行
dsResult = new DataSet();
DbDataAdapter adapter = Provider.CreateDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dsResult);
// 取输出参数值
GetParameterOut(cmdParms, cmd.Parameters);
return(dsResult);
}
#endregion -- 同时传入 DbConnection, DbTrans：事务 Commit, Rollback 由外部控制 --
}
else if (transType != enQueryTransType.Non)
{
#region -- 传入 DbConnection，没有传入 DbTrans，但需要事务：报错 --
throw new Exception("当需要事务执行时，如果传入 DbConnection，必须同时传入 DbTransaction。");
#endregion -- 传入 DbConnection，没有传入 DbTrans，但需要事务：报错 --
}
else
{
#region -- 传入 DbConnection，没有传入 DbTrans，不需要事务 --
using (DbCommand cmd = Provider.CreateCommand())
{
// 准备参数
PrepareCommand(cmd, args.DbConnection, null, cmdType, cmdText, cmdParms);
// 执行
dsResult = new DataSet();
DbDataAdapter adapter = Provider.CreateDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dsResult);
// 取输出参数值
GetParameterOut(cmdParms, cmd.Parameters);
return(dsResult);
}
#endregion -- 传入 DbConnection，没有传入 DbTrans，不需要事务 --
}
}
public static DataTable ExecuteQueryUnCommitted(FactoryArgs args, string cmdText, LBDbParameterCollection cmdParms)
{
return(ExecuteQuery(args, CommandType.Text, cmdText, cmdParms, enQueryTransType.ReadUncommitted));
}
public static int ExecuteNonQuery(FactoryArgs args, CommandType cmdType, string cmdText, LBDbParameterCollection cmdParms, bool bNeedTrans)
{
if (args.DbConnection == null)
{
if (bNeedTrans)
{
#region -- 没有传入 DbConnection，需要事务 --
using (DbConnection conn = Provider.CreateConnection(args.DBName))
{
try
{
conn.Open();
IsolationLevel level;
/*if( TS.DW.Setting.SettingDW.TransMode == 0 )
* {
* level = IsolationLevel.ReadCommitted;
* }
* else
* {
* level = IsolationLevel.ReadUncommitted;
* }*/
level = IsolationLevel.ReadUncommitted;
using (DbTransaction trans = conn.BeginTransaction(level))
{
using (DbCommand cmd = Provider.CreateCommand())
{
int iResult = 0;
try
{
iResult = _ExecuteNonQuery(cmd, conn, trans, cmdType, cmdText, cmdParms);
trans.Commit();
}
catch (Exception ex)
{
try
{
trans.Rollback();
}
catch
{
}
throw ex;
}
return(iResult);
}
}
}
finally
{
conn.Close();
}
}
#endregion -- 没有传入 DbConnection，需要事务 --
}
else
{
#region -- 没有传入 DbConnection，不需要事务 --
using (DbConnection conn = Provider.CreateConnection(args.DBName))
{
try
{
using (DbCommand cmd = Provider.CreateCommand())
{
return(_ExecuteNonQuery(cmd, conn, null, cmdType, cmdText, cmdParms));
}
}
finally
{
conn.Close();
}
}
#endregion -- 没有传入 DbConnection，不需要事务 --
}
}
else if (args.DbTrans != null)
{
#region -- 同时传入 DbConnection, DbTrans：事务 Commit, Rollback 由外部控制 --
using (DbCommand cmd = Provider.CreateCommand())
{
return(_ExecuteNonQuery(cmd, args.DbConnection, args.DbTrans, cmdType, cmdText, cmdParms));
}
#endregion -- 同时传入 DbConnection, DbTrans：事务 Commit, Rollback 由外部控制 --
}
else if (bNeedTrans)
{
//#region -- 传入 DbConnection，没有传入 DbTrans，但需要事务：报错 --
////throw new Exception( "当需要事务执行时，如果传入 DbConnection，必须同时传入 DbTransaction。" );
//#endregion -- 传入 DbConnection，没有传入 DbTrans，但需要事务：报错 --
#region -- 传入 DbConnection，没有传入 DbTrans，但需要事务：新建事务 --
IsolationLevel level;
//if( TS.DW.Setting.SettingDW.TransMode == 0 )
//{
// level = IsolationLevel.ReadCommitted;
//}
//else
//{
// level = IsolationLevel.ReadUncommitted;
//}
level = IsolationLevel.ReadUncommitted;
using (DbTransaction trans = args.DbConnection.BeginTransaction(level))
{
using (DbCommand cmd = Provider.CreateCommand())
{
int iResult = 0;
try
{
iResult = _ExecuteNonQuery(cmd, args.DbConnection, trans, cmdType, cmdText, cmdParms);
trans.Commit();
}
catch (Exception ex)
{
try
{
trans.Rollback();
}
catch
{
}
throw ex;
}
return(iResult);
}
}
#endregion -- 传入 DbConnection，没有传入 DbTrans，但需要事务：新建事务 --
}
else
{
#region -- 传入 DbConnection，没有传入 DbTrans，不需要事务 --
using (DbCommand cmd = Provider.CreateCommand())
{
return(_ExecuteNonQuery(cmd, args.DbConnection, null, cmdType, cmdText, cmdParms));
}
#endregion -- 传入 DbConnection，没有传入 DbTrans，不需要事务 --
}
}
private static int _ExecuteNonQuery(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, LBDbParameterCollection cmdParms)
{
PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);
int iResult = cmd.ExecuteNonQuery();
GetParameterOut(cmdParms, cmd.Parameters);
return(iResult);
}
private static int _ExecuteNonQuery(FactoryArgs args, DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, LBDbParameterCollection cmdParms)
{
bool bolExistsOut = false;
foreach (LBDbParameter parm in cmdParms)
{
if (parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Output)
{
bolExistsOut = true;
break;
}
}
int iResult;
if (bolExistsOut)
{
DataTable dt = ExecuteQuery(args, cmdType, cmdText, cmdParms, enQueryTransType.Non);
iResult = 0;
}
else
{
PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);
iResult = cmd.ExecuteNonQuery();
GetParameterOut(cmdParms, cmd.Parameters);
}
return(iResult);
}
