Пример #1
0
        /// <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;
        }
Пример #2
0
        /// <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;
        }
Пример #3
0
        /// <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;
        }
Пример #4
0
        /// <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;
        }