MESProductionWeb/AntORM/Ant.Data/DataBaisc/SQLDBHandler.cs

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Text;
using System.Web;
namespace ETD.Data
{
    /// <summary>
    /// SQL Server 数据库处理类
    /// </summary>
    public class SQLDBHandler : IDBHandlerble, IDisposable
    {
        private Hashtable links;
        /// <summary>
        /// SqlCommand对象实例
        /// </summary>
        private SqlCommand sqlCommand;
        /// <summary>
        /// 声明sql 的数据库连接对象
        /// </summary>
        private SqlConnection sqlConnection;
        /// <summary>
        /// 取得整个系统的数据库都存放在一台服务器的数据库连接字符串
        /// </summary>
        private static string sqlDefaultConnectionString = ConfigurationManager.AppSettings["DefaultConnectionString"].Trim();
        /// <summary>
        /// 模块使用的字符串变量
        /// </summary>
        private static string sqlModuleConnectionString = string.Empty;
        /// <summary>
        /// 声明事务对象
        /// </summary>
        protected SqlTransaction sqlTransaction;

        /// <summary>
        /// 整个系统使用同一个数据库连接，通过web.config中DefultConnectionString中的连接字符串来连接数据库
        /// </summary>
        /// <remarks>取得web.config或者app.config中AppSetting中sql_default中的连接字符串</remarks>
        public SQLDBHandler()
        {
            this.links = null;
            this.GetLinkObject();
            string ConnectionString = this.GetDataLink("");
            if (ConfigurationManager.AppSettings["IsEncryptString"].Trim() == "1")
            {
                ConnectionString = new Security().Uncrypt(ConnectionString, "");
            }
            this.sqlConnection = new SqlConnection(ConnectionString);
            this.sqlCommand = this.sqlConnection.CreateCommand();
        }

        /// <summary>
        /// 不同模块使用不同的数据库连接，通过web.config中DefultConnectionString中的连接字符串来连接数据库
        /// </summary>
        /// <param name="module">模块关键字</param>
        public SQLDBHandler(string module)
        {
            string ConnectionString;
            Security security;
            this.links = null;
            this.GetLinkObject();
            string isSame = "0";
            if (this.links != null)
            {
                isSame = this.links["isSame"].ToString();
            }
            else
            {
                isSame = ConfigurationManager.AppSettings["IsSameDB"];
            }
            if (isSame.Trim() == "1")
            {
                ConnectionString = this.GetDataLink("");
                if (ConfigurationManager.AppSettings["IsEncryptString"].Trim() == "1")
                {
                    security = new Security();
                    ConnectionString = security.Uncrypt(ConnectionString, "");
                }
                this.sqlConnection = new SqlConnection(ConnectionString);
            }
            else if (module == string.Empty)
            {
                ConnectionString = this.GetDataLink("");
                if (ConfigurationManager.AppSettings["IsEncryptString"].Trim() == "1")
                {
                    security = new Security();
                    ConnectionString = security.Uncrypt(ConnectionString, "");
                }
                this.sqlConnection = new SqlConnection(ConnectionString);
            }
            else
            {
                sqlModuleConnectionString = this.GetDataLink(module);
                if (ConfigurationManager.AppSettings["IsEncryptString"].Trim() == "1")
                {
                    sqlModuleConnectionString = new Security().Uncrypt(sqlModuleConnectionString, "");
                }
                this.sqlConnection = new SqlConnection(sqlModuleConnectionString);
            }
            this.sqlCommand = this.sqlConnection.CreateCommand();
        }

        /// 添加参数
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="paramType">参数类型</param>
        /// <param name="direction">参数方向</param>
        public void AddParameter(string paramName, DbType paramType, ParameterDirection direction)
        {
            SqlParameter splParameter = new SqlParameter();
            splParameter.ParameterName = paramName;
            splParameter.DbType = paramType;
            splParameter.Direction = direction;
            this.sqlCommand.Parameters.Add(splParameter);
        }
        /// <summary>
        /// 添加有长度的参数
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="p_objParamType">参数类型</param>
        /// <param name="p_intParamSize">参数长度</param>
        /// <param name="p_objDirection">参数方向</param>
        public void AddParameter(string paramName, DbType paramType, int paramSize, ParameterDirection direction)
        {
            SqlParameter splParameter = new SqlParameter();
            splParameter.ParameterName = paramName;
            splParameter.DbType = paramType;
            splParameter.Size = paramSize;
            splParameter.Direction = direction;
            this.sqlCommand.Parameters.Add(splParameter);
        }

        /// <summary>
        /// 重载-添加有长度的参数
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="p_strParamType">参数类型</param>
        /// <param name="p_intParamSize">参数长度</param>
        /// <param name="p_objDirection">参数方向</param>
        /// <param name="pValue">参数值</param>
        public void AddParameter(string paramName, DbType paramType, int paramSize, ParameterDirection direction, object pValue)
        {
            this.AddParameter(paramName, paramType, paramSize, direction);
            this.ModifyParameter(paramName, pValue);
        }


        /// <summary>
        /// 重载-添加参数
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="p_strParamType">参数类型</param>
        /// <param name="direction">参数方向</param>
        /// <param name="pValue">参数值</param>
        public void AddParameter(string paramName, DbType paramType, ParameterDirection direction, object pValue)
        {
            this.AddParameter(paramName, paramType, direction);
            this.ModifyParameter(paramName, pValue);
        }
        




        /// 添加参数
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="paramType">参数类型</param>
        /// <param name="direction">参数方向</param>
        public void AddParameter(string paramName, SqlDbType paramType, ParameterDirection direction)
        {
            SqlParameter splParameter = new SqlParameter();
            splParameter.ParameterName = paramName;
            splParameter.SqlDbType = paramType;
            splParameter.Direction = direction;
            this.sqlCommand.Parameters.Add(splParameter);
        }
        /// <summary>
        /// 添加有长度的参数
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="p_objParamType">参数类型</param>
        /// <param name="p_intParamSize">参数长度</param>
        /// <param name="p_objDirection">参数方向</param>
        public void AddParameter(string paramName, SqlDbType paramType, int paramSize, ParameterDirection direction)
        {
            SqlParameter splParameter = new SqlParameter();
            splParameter.ParameterName = paramName;
            splParameter.SqlDbType = paramType;
            splParameter.Size = paramSize;
            splParameter.Direction = direction;
            this.sqlCommand.Parameters.Add(splParameter);
        }

        /// <summary>
        /// 重载-添加有长度的参数
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="p_strParamType">参数类型</param>
        /// <param name="p_intParamSize">参数长度</param>
        /// <param name="p_objDirection">参数方向</param>
        /// <param name="pValue">参数值</param>
        public void AddParameter(string paramName, SqlDbType paramType, int paramSize, ParameterDirection direction, object pValue)
        {
            this.AddParameter(paramName, paramType, paramSize, direction);
            this.ModifyParameter(paramName, pValue);
        }


        /// <summary>
        /// 重载-添加参数
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="p_strParamType">参数类型</param>
        /// <param name="direction">参数方向</param>
        /// <param name="pValue">参数值</param>
        public void AddParameter(string paramName, SqlDbType paramType, ParameterDirection direction, object pValue)
        {
            this.AddParameter(paramName, paramType, direction);
            this.ModifyParameter(paramName, pValue);
        }
      
        /// <summary>
        /// 启动事务处理
        /// </summary>
        /// <returns>返回执行结果状态码</returns>
        public void BeginTransaction()
        {
            this.ConnectionOpen();
            if (null == this.sqlTransaction)
            {
                this.sqlTransaction = this.sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            }
            this.sqlCommand.Transaction = this.sqlTransaction;
        }

        /// <summary>
        /// 提交事务处理
        /// </summary>
        /// <returns>返回执行结果状态码</returns>
        public void CommitTransaction()
        {
            try
            {
                this.sqlTransaction.Commit();
            }
            finally
            {
                this.ConnectionClose();
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void ConnectionClose()
        {
            if (ConnectionState.Closed != this.sqlConnection.State)
            {
                this.sqlConnection.Close();
                this.sqlConnection.Dispose();
            }
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void ConnectionOpen()
        {
            if (ConnectionState.Closed == this.sqlConnection.State)
            {
                this.sqlConnection.Open();
            }
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (this.sqlConnection != null)
            {
                this.sqlConnection.Dispose();
            }
            if (this.sqlCommand != null)
            {
                this.sqlCommand.Dispose();
            }
            if (this.sqlTransaction != null)
            {
                this.sqlTransaction.Dispose();
            }
        }

        /// <summary>
        /// 返回数据适配器
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <returns>返回执行结果</returns>
        public IDbDataAdapter ExecuteAdapter(string commandText)
        {
            return this.ExecuteAdapter(commandText, CommandType.Text);
        }

        /// <summary>
        /// 重载-返回数据适配器(适应存储过程调用需求)
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <param name="commandType">查询命令类型</param>
        /// <returns>返回执行结果</returns>
        public IDbDataAdapter ExecuteAdapter(string commandText, CommandType commandType)
        {
            this.sqlCommand.CommandText = commandText;
            this.sqlCommand.CommandType = commandType;
            this.ConnectionOpen();
            return new SqlDataAdapter(this.sqlCommand);
        }

        /// <summary>
        /// 返回数据集
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <returns>返回执行结果</returns>
        public DataSet ExecuteDataSet(string commandText)
        {
            return this.ExecuteDataSet(commandText, CommandType.Text);
        }

        /// <summary>
        /// 重载-返回数据集(适应存储过程调用需求)
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <param name="commandType">查询命令类型</param>
        /// <returns>返回执行结果</returns>
        public DataSet ExecuteDataSet(string commandText, CommandType commandType)
        {
            return this.ExecuteDataSet(commandText, commandType, "");
        }

        /// <summary>
        /// 重载-返回数据集(适应存储过程调用需求)
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <param name="commandType">查询命令类型</param>
        /// <param name="tableName">表名</param>
        /// <returns>返回执行结果</returns>
        public DataSet ExecuteDataSet(string commandText, CommandType commandType, string tableName)
        {
            return this.ExecuteDataSet("", commandText, commandType, "");
        }

        /// <summary>
        /// 重载-返回数据集(适应存储过程调用需求)
        /// </summary>
        /// <param name="dataSetName">DataSet 名称</param>
        /// <param name="commandText">查询命令</param>
        /// <param name="commandType">查询命令类型</param>
        /// <param name="tableName">表名</param>
        /// <returns>返回执行结果</returns>
        public DataSet ExecuteDataSet(string dataSetName, string commandText, CommandType commandType, string tableName)
        {
            DataSet dstResult = new DataSet();
            dstResult.Locale = CultureInfo.InvariantCulture;
            if (dataSetName != string.Empty)
            {
                dstResult.DataSetName = dataSetName;
            }
            this.sqlCommand.CommandText = commandText;
            this.sqlCommand.CommandType = commandType;
            this.ConnectionOpen();
            if (tableName != string.Empty)
            {
                SqlDataAdapter sqlAdapter = new SqlDataAdapter(this.sqlCommand);
                sqlAdapter.Fill(dstResult, tableName);
            }
            else
            {
                new SqlDataAdapter(this.sqlCommand).Fill(dstResult);
            }
            this.ConnectionClose();
            return dstResult;
        }

        /// <summary>
        /// 返回数据表
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <returns>返回执行结果</returns>
        public DataTable ExecuteDataTable(string commandText)
        {
            return this.ExecuteDataTable(commandText, CommandType.Text);
        }

        /// <summary>
        /// 返回数据表
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="isCloseConnection"></param>
        /// <returns>返回执行结果</returns>
        public DataTable ExecuteDataTable(string commandText, bool isCloseConnection)
        {
            return this.ExecuteDataTable(commandText, CommandType.Text, "", isCloseConnection);
        }

        /// <summary>
        /// 重载-返回数据表
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <param name="commandType">查询命令类型</param>
        /// <returns>返回执行结果</returns>
        public DataTable ExecuteDataTable(string commandText, CommandType commandType)
        {
            return this.ExecuteDataTable(commandText, commandType, "");
        }

        /// <summary>
        /// 重载-返回数据表
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <param name="commandType">查询命令类型</param>
        /// <param name="tableName">表名</param>
        /// <returns>返回执行结果</returns>
        public DataTable ExecuteDataTable(string commandText, CommandType commandType, string tableName)
        {
            return this.ExecuteDataTable(commandText, commandType, tableName, false);
        }

        /// <summary>
        /// 重载-返回数据表
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <param name="commandType">查询命令类型</param>
        /// <param name="tableName">表名</param>
        /// <param name="isCloseConnection">是否关闭数据库</param>
        /// <returns>返回执行结果</returns>
        public DataTable ExecuteDataTable(string commandText, CommandType commandType, string tableName, bool isCloseConnection)
        {
            DataTable dtblResult = new DataTable();
            dtblResult.Locale = CultureInfo.InvariantCulture;
            if (tableName != string.Empty)
            {
                dtblResult.TableName = tableName;
            }
            this.sqlCommand.CommandText = commandText;
            this.sqlCommand.CommandType = commandType;
            this.ConnectionOpen();
            new SqlDataAdapter(this.sqlCommand).Fill(dtblResult);
            if (isCloseConnection)
            {
                this.ConnectionClose();
            }
            return dtblResult;
        }

        /// <summary>
        /// 返回数据表(分页)
        /// </summary>
        /// <param name="page">当前页</param>
        /// <param name="pageSize">每页显示数</param>
        /// <param name="recordCount">总记录数</param>
        /// <param name="strFields">需要查询的字段</param>
        /// <param name="strOderBy">排序字段，例如： id asc,name desc 不带order by</param>
        /// <param name="strTableName">查询表名称</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(int page, int pageSize, ref int recordCount, string strFields, string strOrderBy, string strTableName)
        {
            recordCount = Convert.ToInt32(this.ExecuteScalar("select count(1) from " + strTableName + " as t"));
            StringBuilder sb = new StringBuilder("select " + strFields + " from (");
            sb.Append(string.Concat(new object[] { " select row_number() over( order by ", strOrderBy, " ) as rowrumber,* from ", strTableName, " as a) as t  where t.rowrumber between ", (page - 1) * pageSize, 1, " and ", page * pageSize, " order by t.rowrumber  " }));
            return this.ExecuteDataTable(sb.ToString());
        }

        /// <summary>
        /// 执行无返回类型数据查询(返回影响行数)
        /// </summary>
        /// <param name="commandText">查询命令(SQL语句)</param>
        /// <returns>返回执行结果影响行数</returns>
        public int ExecuteNonQuery(string commandText)
        {
            return this.ExecuteNonQuery(commandText, CommandType.Text);
        }

        /// <summary>
        /// 执行无返回类型数据查询(返回影响行数)
        /// </summary>
        /// <param name="commandText">查询命令(SQL语句)</param>
        /// <param name="isCloseConnection">是否关闭数据连结,true: 关闭,false: 不关闭</param>
        /// <returns>返回执行结果影响行数</returns>
        public int ExecuteNonQuery(string commandText, bool isCloseConnection)
        {
            return this.ExecuteNonQuery(commandText, CommandType.Text, isCloseConnection);
        }

        /// <summary>
        /// 重载-无返回类型数据查询(适应存储过程调用需求且返回影响行数)
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <param name="commandType">查询命令类型</param>
        /// <returns>返回执行结果影响行数</returns>
        public int ExecuteNonQuery(string commandText, CommandType commandType)
        {
            return this.ExecuteNonQuery(commandText, commandType, false);
        }

        /// <summary>
        /// 重载-无返回类型数据查询(适应存储过程调用需求且返回影响行数)
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <param name="commandType">查询命令类型</param>
        /// <param name="isCloseConnection">是否关闭数据连结,true: 关闭,false: 不关闭</param>
        /// <returns>返回执行结果影响行数</returns>
        public int ExecuteNonQuery(string commandText, CommandType commandType, bool isCloseConnection)
        {
            int row;
            this.sqlCommand.CommandText = commandText;
            this.sqlCommand.CommandType = commandType;
            try
            {
                this.ConnectionOpen();
                row = this.sqlCommand.ExecuteNonQuery();
            }
            finally
            {
                if (isCloseConnection)
                {
                    this.ConnectionClose();
                }
            }
            return row;
        }

        /// <summary>
        /// 返回向前只读数据集查询
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <returns>返回执行结果</returns>
        public IDataReader ExecuteReader(string commandText)
        {
            return this.ExecuteReader(commandText, CommandType.Text);
        }

        /// <summary>
        /// 重载-返回向前只读数据集查询(适应存储过程调用需求)
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <param name="commandType">查询命令类型</param>
        /// <returns>返回执行结果</returns>
        public IDataReader ExecuteReader(string commandText, CommandType commandType)
        {
            return this.ExecuteReader(commandText, commandType, CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 重载-返回向前只读数据集查询(适应存储过程调用需求)
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <param name="commandType">查询命令类型</param>
        /// <param name="commandBehavior">提供对查询结果和查询对数据库的影响的说明</param>
        /// <returns>返回执行结果</returns>
        public IDataReader ExecuteReader(string commandText, CommandType commandType, CommandBehavior commandBehavior)
        {
            IDataReader row;
            this.sqlCommand.CommandText = commandText;
            this.sqlCommand.CommandType = commandType;
            this.ConnectionOpen();
            try
            {
                row = this.sqlCommand.ExecuteReader(commandBehavior);
            }
            catch (SqlException ex)
            {
                this.ConnectionClose();
                throw ex;
            }
            return row;
        }

        public object ExecuteScalar(string commandText)
        {
            return this.ExecuteScalar(commandText, false);
        }

        /// <summary>
        /// 返回第一行第一列结果的数据查询
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <param name="isCloseConnection">是否关闭数据连结,true: 关闭,false: 不关闭</param>
        /// <returns>返回查询结果</returns>
        public object ExecuteScalar(string commandText, bool isCloseConnection)
        {
            return this.ExecuteScalar(commandText, CommandType.Text, isCloseConnection);
        }

        /// <summary>
        /// 重载-返回第一行第一列结果的数据查询(适应存储过程调用需求)
        /// </summary>
        /// <param name="commandText">查询命令</param>
        /// <param name="commandType">查询命令类型</param>
        /// <param name="isCloseConnection">是否关闭数据连结,true: 关闭,false: 不关闭</param>
        /// <returns>返回查询结果</returns>
        public object ExecuteScalar(string commandText, CommandType commandType, bool isCloseConnection)
        {
            object row;
            this.sqlCommand.CommandText = commandText;
            this.sqlCommand.CommandType = commandType;
            this.ConnectionOpen();
            try
            {
                row = this.sqlCommand.ExecuteScalar();
            }
            finally
            {
                if (isCloseConnection)
                {
                    this.ConnectionClose();
                }
            }
            return row;
        }

        /// <summary>
        /// 获得数据库连接字符串
        /// </summary>
        /// <param name="ModuleName">模块名称</param>
        /// <returns></returns>
        public string GetDataLink(string ModuleName)
        {
            if (this.links != null)
            {
                if (ModuleName == string.Empty)
                {
                    ModuleName = "default";
                }
                return this.links[ModuleName].ToString();
            }
            if (ModuleName == string.Empty)
            {
                return sqlDefaultConnectionString;
            }
            return ConfigurationManager.AppSettings[ModuleName + "ConnectionString"].Trim();
        }

        /// <summary>
        /// 
        /// </summary>
        private void GetLinkObject()
        {
            try
            {
                if (HttpContext.Current.Session["uLinks"] != null)
                {
                    this.links = HttpContext.Current.Session["uLinks"] as Hashtable;
                    if (this.links.Count == 0)
                    {
                        this.links = null;
                    }
                }
            }
            catch
            {
            }
        }

        /// <summary>
        /// 提取参数值
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <returns>返回执行结果状态码</returns>
        public object GetParameter(string paramName)
        {
            if (this.sqlCommand.Parameters.Contains(paramName))
            {
                return this.sqlCommand.Parameters[paramName].Value;
            }
            return null;
        }

        /// <summary>
        /// 将DataTable添加到DataSet中
        /// </summary>
        /// <param name="dataSet">DataSet对象</param>
        /// <param name="tableNames">表名数组</param>
        public void LoadDataSet(DataSet dataSet, string[] tableNames)
        {
            for (int i = 0; i < tableNames.Length; i++)
            {
                this.LoadDataSet(dataSet, tableNames[i]);
            }
        }

        /// <summary>
        /// 将DataTable添加到DataSet中
        /// </summary>
        /// <param name="dataSet">DataSet对象</param>
        /// <param name="tableName">表名</param>
        public void LoadDataSet(DataSet dataSet, string tableName)
        {
            dataSet.Tables.Add(tableName);
        }

        /// <summary>
        /// 修改参数值
        /// </summary>
        /// <param name="p_strParamName">参数名称</param>
        /// <param name="p_objValue">参数值</param>
        public void ModifyParameter(string paramName, object pValue)
        {
            if ((this.sqlCommand.Parameters[paramName].SqlDbType == SqlDbType.UniqueIdentifier) && (pValue.GetType() == typeof(sbyte)))
            {
                pValue = new Guid(pValue.ToString());
            }
            if (-1 != this.sqlCommand.Parameters.IndexOf(paramName))
            {
                this.sqlCommand.Parameters[paramName].Value = pValue;
            }
        }

        /// <summary>
        /// 移除所有的参数
        /// </summary>
        public void RemoveAllParameters()
        {
            this.sqlCommand.Parameters.Clear();
        }

        /// <summary>
        /// 移除参数
        /// </summary>
        /// <param name="p_strParamName">参数名称</param>
        public void RemoveParameter(string paramName)
        {
            this.sqlCommand.Parameters.RemoveAt(paramName);
        }

        /// <summary>
        /// 回滚事务处理
        /// </summary>
        /// <returns>返回执行结果状态码</returns>
        public void RollbackTransaction()
        {
            try
            {
                this.sqlTransaction.Rollback();
            }
            finally
            {
                this.ConnectionClose();
            }
        }
    }
}