/// <summary> /// SqlServer 执行插入数据的查询,如果执行成功,受影响的行数只会返回1 /// </summary> /// <param name="SQL">插入数据的SQL</param> /// <param name="commandType">命令类型</param> /// <param name="parameters">参数数组</param> /// <param name="ID">要传出的本次操作的新插入数据行的主键ID值</param> /// <returns>本次查询受影响的行数</returns> public override int ExecuteInsertQuery(string SQL, CommandType commandType, IDataParameter[] parameters, ref object ID) { IDbConnection conn = GetConnection(); IDbCommand cmd = conn.CreateCommand(); CompleteCommand(cmd, ref SQL, ref commandType, ref parameters); CommandLog cmdLog = new CommandLog(true); bool inner = false; int result = -1; ID = 0; try { if (cmd.Transaction == null) { inner = true; cmd.Transaction = conn.BeginTransaction(); } cmd.CommandText = SQL + " ;SELECT SCOPE_IDENTITY();"; ID = cmd.ExecuteScalar(); //如果在内部开启了事务则提交事务,否则外部调用者决定何时提交事务 result = 1; if (inner) { cmd.Transaction.Commit(); cmd.Transaction = null; } } catch (Exception ex) { ErrorMessage = ex.Message; bool inTransaction = cmd.Transaction == null ? false : true; if (cmd.Transaction != null) cmd.Transaction.Rollback(); if (inner) cmd.Transaction = null; cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage); if (OnErrorThrow) { throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString); } } finally { if (cmd.Transaction == null && conn.State == ConnectionState.Open) conn.Close(); } long _elapsedMilliseconds; cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds); base.ElapsedMilliseconds = _elapsedMilliseconds; return result; }
/// <summary> /// 执行返回单一值得查询 /// </summary> /// <param name="SQL">SQL</param> /// <param name="commandType">命令类型</param> /// <param name="parameters">参数数组</param> /// <returns>查询结果</returns> public virtual object ExecuteScalar(string SQL, CommandType commandType, IDataParameter[] parameters) { IDbConnection conn = GetConnection(); IDbCommand cmd = conn.CreateCommand(); CompleteCommand(cmd, ref SQL, ref commandType, ref parameters); CommandLog cmdLog = new CommandLog(true); object result = null; try { result = cmd.ExecuteScalar(); //如果开启事务,则由上层调用者决定何时提交事务 } catch (Exception ex) { ErrorMessage = ex.Message; //如果开启事务,那么此处应该回退事务 //if(cmd.Transaction!=null) // cmd.Transaction.Rollback (); bool inTransaction = cmd.Transaction == null ? false : true; cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage); if (OnErrorThrow) { throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString); } } finally { //if (cmd.Transaction == null && conn.State == ConnectionState.Open) // conn.Close(); CloseConnection(conn, cmd); } cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds); return result; }
/// <summary> /// 执行查询,并以指定的(具有数据架构的)数据集来填充数据 /// </summary> /// <param name="SQL">查询语句</param> /// <param name="commandType">命令类型</param> /// <param name="parameters">查询参数</param> /// <param name="schemaDataSet">指定的(具有数据架构的)数据集</param> /// <returns>具有数据的数据集</returns> public virtual DataSet ExecuteDataSetWithSchema(string SQL, CommandType commandType, IDataParameter[] parameters, DataSet schemaDataSet) { IDbConnection conn = GetConnection(); IDbCommand cmd = conn.CreateCommand(); CompleteCommand(cmd, ref SQL, ref commandType, ref parameters); IDataAdapter ada = GetDataAdapter(cmd); CommandLog cmdLog = new CommandLog(true); try { ada.Fill(schemaDataSet);//FillSchema(ds,SchemaType.Mapped ) } catch (Exception ex) { ErrorMessage = ex.Message; bool inTransaction = cmd.Transaction == null ? false : true; cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage); if (OnErrorThrow) { throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString); } } finally { //if (cmd.Transaction == null && conn.State == ConnectionState.Open) // conn.Close(); CloseConnection(conn, cmd); } cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds); return schemaDataSet; }
/// <summary> /// 根据查询返回数据阅读器对象 /// </summary> /// <param name="SQL">SQL</param> /// <param name="commandType">命令类型</param> /// <param name="cmdBehavior">对查询和返回结果有影响的说明</param> /// <param name="parameters">参数数组</param> /// <returns>数据阅读器</returns> protected virtual IDataReader ExecuteDataReader(ref string SQL, CommandType commandType, CommandBehavior cmdBehavior, ref IDataParameter[] parameters) { IDbConnection conn = GetConnection(); IDbCommand cmd = conn.CreateCommand(); CompleteCommand(cmd, ref SQL, ref commandType, ref parameters); CommandLog cmdLog = new CommandLog(true); IDataReader reader = null; try { //如果命令对象的事务对象为空,那么强制在读取完数据后关闭阅读器的数据库连接 2008.3.20 if (cmd.Transaction == null && cmdBehavior == CommandBehavior.Default) cmdBehavior = CommandBehavior.CloseConnection; reader = cmd.ExecuteReader(cmdBehavior); } catch (Exception ex) { ErrorMessage = ex.Message; //只有出现了错误而且没有开启事务,可以关闭连结 //if (cmd.Transaction == null && conn.State == ConnectionState.Open) // conn.Close(); CloseConnection(conn, cmd); bool inTransaction = cmd.Transaction == null ? false : true; cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage); if (OnErrorThrow) { throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString); } } cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds); return reader; }