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

namespace ETD.Data
{
    using MySql.Data.MySqlClient;
    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Globalization;
    using System.Web;

    /// <summary>
    /// SQL Server 数据库处理类
    /// </summary>
    public class MySQLDBHandler : IDBHandlerble, IDisposable
    {
        private Hashtable links;
        /// <summary>
        /// MySqlCommand对象实例
        /// </summary>
        private MySqlCommand mySqlCommand;
        /// <summary>
        /// 声明sql 的数据库连接对象
        /// </summary>
        private MySqlConnection mySqlConnection;
        /// <summary>
        /// 声明事务对象
        /// </summary>
        protected MySqlTransaction mySqlTransaction;
        /// <summary>
        /// 取得整个系统的数据库都存放在一台服务器的数据库连接字符串
        /// </summary>
        private static string sqlDefaultConnectionString = ConfigurationManager.AppSettings["DefaultConnectionString"].Trim();
        /// <summary>
        /// 模块使用的字符串变量
        /// </summary>
        private static string sqlModuleConnectionString = string.Empty;

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

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

        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="p_objParamType">参数类型</param>
        /// <param name="direction">参数方向</param>
        public void AddParameter(string paramName, DbType paramType, ParameterDirection direction)
        {
            MySqlParameter mySqlParameter = new MySqlParameter();
            mySqlParameter.ParameterName = paramName;
            mySqlParameter.DbType = paramType;
            mySqlParameter.Direction = direction;
            this.mySqlCommand.Parameters.Add(mySqlParameter);
        }

        /// <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>
        /// 添加有长度的参数
        /// </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)
        {
            MySqlParameter mySqlParameter = new MySqlParameter();
            mySqlParameter.ParameterName = paramName;
            mySqlParameter.DbType = paramType;
            mySqlParameter.Size = paramSize;
            mySqlParameter.Direction = direction;
            this.mySqlCommand.Parameters.Add(mySqlParameter);
        }

        /// <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>
        /// <returns>返回执行结果状态码</returns>
        public void BeginTransaction()
        {
            this.ConnectionOpen();
            if (null == this.mySqlTransaction)
            {
                this.mySqlTransaction = this.mySqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            }
            this.mySqlCommand.Transaction = this.mySqlTransaction;
        }

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

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

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

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (this.mySqlConnection != null)
            {
                this.mySqlConnection.Dispose();
            }
            if (this.mySqlCommand != null)
            {
                this.mySqlCommand.Dispose();
            }
            if (this.mySqlTransaction != null)
            {
                this.mySqlTransaction.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.mySqlCommand.CommandText = commandText;
            this.mySqlCommand.CommandType = commandType;
            this.ConnectionOpen();
            return new MySqlDataAdapter(this.mySqlCommand);
        }

        /// <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.mySqlCommand.CommandText = commandText;
            this.mySqlCommand.CommandType = commandType;
            this.ConnectionOpen();
            if (tableName != string.Empty)
            {
                MySqlDataAdapter sqlAdapter = new MySqlDataAdapter(this.mySqlCommand);
                sqlAdapter.Fill(dstResult, tableName);
            }
            else
            {
                new MySqlDataAdapter(this.mySqlCommand).Fill(dstResult);
            }
            this.ConnectionClose();
            return dstResult;
        }

        /// <summary>
        /// 返回数据表
        /// </summary>
        /// <param name="p_strCommandText">查询命令</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="p_strCommandText">查询命令</param>
        /// <param name="p_objType">查询命令类型</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.mySqlCommand.CommandText = commandText;
            this.mySqlCommand.CommandType = commandType;
            this.ConnectionOpen();
            new MySqlDataAdapter(this.mySqlCommand).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)
        {
            return null;
        }

        /// <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 rows;
            this.mySqlCommand.CommandText = commandText;
            this.mySqlCommand.CommandType = commandType;
            try
            {
                this.ConnectionOpen();
                rows = this.mySqlCommand.ExecuteNonQuery();
            }
            finally
            {
                if (isCloseConnection)
                {
                    this.ConnectionClose();
                }
            }
            return rows;
        }

        /// <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.mySqlCommand.CommandText = commandText;
            this.mySqlCommand.CommandType = commandType;
            this.ConnectionOpen();
            try
            {
                row = this.mySqlCommand.ExecuteReader(commandBehavior);
            }
            catch (MySqlException 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.mySqlCommand.CommandText = commandText;
            this.mySqlCommand.CommandType = commandType;
            this.ConnectionOpen();
            try
            {
                row = this.mySqlCommand.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()
        {
            if (HttpContext.Current.Session["uLinks"] != null)
            {
                this.links = HttpContext.Current.Session["uLinks"] as Hashtable;
            }
        }

        /// <summary>
        /// 提取参数值
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <returns>返回执行结果状态码</returns>
        public object GetParameter(string paramName)
        {
            if (this.mySqlCommand.Parameters.Contains(paramName))
            {
                return this.mySqlCommand.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="paramName">参数名称</param>
        /// <param name="pValue">参数值</param>
        public void ModifyParameter(string paramName, object pValue)
        {
            if ((this.mySqlCommand.Parameters[paramName].MySqlDbType == MySqlDbType.Guid) && (pValue.GetType() == typeof(sbyte)))
            {
                pValue = new Guid(pValue.ToString());
            }
            if (-1 != this.mySqlCommand.Parameters.IndexOf(paramName))
            {
                this.mySqlCommand.Parameters[paramName].Value = pValue;
            }
        }

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

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

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