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); }