namespace ETD.Data { using MySql.Data.MySqlClient; using System; using System.Collections; using System.Configuration; using System.Data; using System.Globalization; using System.Web; /// /// SQL Server 数据库处理类 /// public class MySQLDBHandler : IDBHandlerble, IDisposable { private Hashtable links; /// /// MySqlCommand对象实例 /// private MySqlCommand mySqlCommand; /// /// 声明sql 的数据库连接对象 /// private MySqlConnection mySqlConnection; /// /// 声明事务对象 /// protected MySqlTransaction mySqlTransaction; /// /// 取得整个系统的数据库都存放在一台服务器的数据库连接字符串 /// private static string sqlDefaultConnectionString = ConfigurationManager.AppSettings["DefaultConnectionString"].Trim(); /// /// 模块使用的字符串变量 /// private static string sqlModuleConnectionString = string.Empty; /// /// 整个系统使用同一个数据库连接,通过web.config中DefultConnectionString中的连接字符串来连接数据库 /// /// 取得web.config或者app.config中AppSetting中sql_default中的连接字符串 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(); } /// /// 不同模块使用不同的数据库连接,通过web.config中DefultConnectionString中的连接字符串来连接数据库 /// /// 模块关键字 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(); } /// /// 添加参数 /// /// 参数名称 /// 参数类型 /// 参数方向 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); } /// /// 重载-添加参数 /// /// 参数名称 /// 参数类型 /// 参数方向 /// 参数值 public void AddParameter(string paramName, DbType paramType, ParameterDirection direction, object pValue) { this.AddParameter(paramName, paramType, direction); this.ModifyParameter(paramName, pValue); } /// /// 添加有长度的参数 /// /// 参数名称 /// 参数类型 /// 参数长度 /// 参数方向 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); } /// /// 重载-添加有长度的参数 /// /// 参数名称 /// 参数类型 /// 参数长度 /// 参数方向 /// 参数值 public void AddParameter(string paramName, DbType paramType, int paramSize, ParameterDirection direction, object pValue) { this.AddParameter(paramName, paramType, paramSize, direction); this.ModifyParameter(paramName, pValue); } /// /// 启动事务处理 /// /// 返回执行结果状态码 public void BeginTransaction() { this.ConnectionOpen(); if (null == this.mySqlTransaction) { this.mySqlTransaction = this.mySqlConnection.BeginTransaction(IsolationLevel.ReadCommitted); } this.mySqlCommand.Transaction = this.mySqlTransaction; } /// /// 提交事务处理 /// /// 返回执行结果状态码 public void CommitTransaction() { try { this.mySqlTransaction.Commit(); } finally { this.ConnectionClose(); } } /// /// 关闭数据库连接 /// public void ConnectionClose() { if (ConnectionState.Closed != this.mySqlConnection.State) { this.mySqlConnection.Close(); } } /// /// 打开数据库连接 /// public void ConnectionOpen() { if (ConnectionState.Closed == this.mySqlConnection.State) { this.mySqlConnection.Open(); } } /// /// 释放资源 /// public void Dispose() { if (this.mySqlConnection != null) { this.mySqlConnection.Dispose(); } if (this.mySqlCommand != null) { this.mySqlCommand.Dispose(); } if (this.mySqlTransaction != null) { this.mySqlTransaction.Dispose(); } } /// /// 返回数据适配器 /// /// 查询命令 /// 返回执行结果 public IDbDataAdapter ExecuteAdapter(string commandText) { return this.ExecuteAdapter(commandText, CommandType.Text); } /// /// 重载-返回数据适配器(适应存储过程调用需求) /// /// 查询命令 /// 查询命令类型 /// 返回执行结果 public IDbDataAdapter ExecuteAdapter(string commandText, CommandType commandType) { this.mySqlCommand.CommandText = commandText; this.mySqlCommand.CommandType = commandType; this.ConnectionOpen(); return new MySqlDataAdapter(this.mySqlCommand); } /// /// 返回数据集 /// /// 查询命令 /// 返回执行结果 public DataSet ExecuteDataSet(string commandText) { return this.ExecuteDataSet(commandText, CommandType.Text); } /// /// 重载-返回数据集(适应存储过程调用需求) /// /// 查询命令 /// 查询命令类型 /// 返回执行结果 public DataSet ExecuteDataSet(string commandText, CommandType commandType) { return this.ExecuteDataSet(commandText, commandType, ""); } /// /// 重载-返回数据集(适应存储过程调用需求) /// /// 查询命令 /// 查询命令类型 /// 表名 /// 返回执行结果 public DataSet ExecuteDataSet(string commandText, CommandType commandType, string tableName) { return this.ExecuteDataSet("", commandText, commandType, ""); } /// /// 重载-返回数据集(适应存储过程调用需求) /// /// DataSet 名称 /// 查询命令 /// 查询命令类型 /// 表名 /// 返回执行结果 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; } /// /// 返回数据表 /// /// 查询命令 /// 返回执行结果 public DataTable ExecuteDataTable(string commandText) { return this.ExecuteDataTable(commandText, CommandType.Text); } /// /// 返回数据表 /// /// /// /// 返回执行结果 public DataTable ExecuteDataTable(string commandText, bool isCloseConnection) { return this.ExecuteDataTable(commandText, CommandType.Text, "", isCloseConnection); } /// /// 重载-返回数据表 /// /// 查询命令 /// 查询命令类型 /// 返回执行结果 public DataTable ExecuteDataTable(string commandText, CommandType commandType) { return this.ExecuteDataTable(commandText, commandType, ""); } /// /// 重载-返回数据表 /// /// 查询命令 /// 查询命令类型 /// 表名 /// 返回执行结果 public DataTable ExecuteDataTable(string commandText, CommandType commandType, string tableName) { return this.ExecuteDataTable(commandText, commandType, tableName, false); } /// /// 重载-返回数据表 /// /// 查询命令 /// 查询命令类型 /// 表名 /// 是否关闭数据库 /// 返回执行结果 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; } /// /// 返回数据表(分页) /// /// 当前页 /// 每页显示数 /// 总记录数 /// 需要查询的字段 /// 排序字段,例如: id asc,name desc 不带order by /// 查询表名称 /// public DataTable ExecuteDataTable(int page, int pageSize, ref int recordCount, string strFields, string strOrderBy, string strTableName) { return null; } /// /// 执行无返回类型数据查询(返回影响行数) /// /// 查询命令(SQL语句) /// 返回执行结果影响行数 public int ExecuteNonQuery(string commandText) { return this.ExecuteNonQuery(commandText, CommandType.Text); } /// /// 执行无返回类型数据查询(返回影响行数) /// /// 查询命令(SQL语句) /// 是否关闭数据连结,true: 关闭,false: 不关闭 /// 返回执行结果影响行数 public int ExecuteNonQuery(string commandText, bool isCloseConnection) { return this.ExecuteNonQuery(commandText, CommandType.Text, isCloseConnection); } /// /// 重载-无返回类型数据查询(适应存储过程调用需求且返回影响行数) /// /// 查询命令 /// 查询命令类型 /// 返回执行结果影响行数 public int ExecuteNonQuery(string commandText, CommandType commandType) { return this.ExecuteNonQuery(commandText, commandType, false); } /// /// 重载-无返回类型数据查询(适应存储过程调用需求且返回影响行数) /// /// 查询命令 /// 查询命令类型 /// 是否关闭数据连结,true: 关闭,false: 不关闭 /// 返回执行结果影响行数 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; } /// /// 返回向前只读数据集查询 /// /// 查询命令 /// 返回执行结果 public IDataReader ExecuteReader(string commandText) { return this.ExecuteReader(commandText, CommandType.Text); } /// /// 重载-返回向前只读数据集查询(适应存储过程调用需求) /// /// 查询命令 /// 查询命令类型 /// 返回执行结果 public IDataReader ExecuteReader(string commandText, CommandType commandType) { return this.ExecuteReader(commandText, commandType, CommandBehavior.CloseConnection); } /// /// 重载-返回向前只读数据集查询(适应存储过程调用需求) /// /// 查询命令 /// 查询命令类型 /// 提供对查询结果和查询对数据库的影响的说明 /// 返回执行结果 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); } /// /// 返回第一行第一列结果的数据查询 /// /// 查询命令 /// 是否关闭数据连结,true: 关闭,false: 不关闭 /// 返回查询结果 public object ExecuteScalar(string commandText, bool isCloseConnection) { return this.ExecuteScalar(commandText, CommandType.Text, isCloseConnection); } /// /// 重载-返回第一行第一列结果的数据查询(适应存储过程调用需求) /// /// 查询命令 /// 查询命令类型 /// 是否关闭数据连结,true: 关闭,false: 不关闭 /// 返回查询结果 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; } /// /// 获得数据库连接字符串 /// /// 模块名称 /// 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(); } /// /// /// private void GetLinkObject() { if (HttpContext.Current.Session["uLinks"] != null) { this.links = HttpContext.Current.Session["uLinks"] as Hashtable; } } /// /// 提取参数值 /// /// 参数名称 /// 返回执行结果状态码 public object GetParameter(string paramName) { if (this.mySqlCommand.Parameters.Contains(paramName)) { return this.mySqlCommand.Parameters[paramName].Value; } return null; } /// /// 将DataTable添加到DataSet中 /// /// DataSet对象 /// 表名数组 public void LoadDataSet(DataSet dataSet, string[] tableNames) { for (int i = 0; i < tableNames.Length; i++) { this.LoadDataSet(dataSet, tableNames[i]); } } /// /// 将DataTable添加到DataSet中 /// /// DataSet对象 /// 表名 public void LoadDataSet(DataSet dataSet, string tableName) { dataSet.Tables.Add(tableName); } /// /// 修改参数值 /// /// 参数名称 /// 参数值 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; } } /// /// 移除所有的参数 /// public void RemoveAllParameters() { this.mySqlCommand.Parameters.Clear(); } /// /// 移除参数 /// /// 参数名称 public void RemoveParameter(string paramName) { this.mySqlCommand.Parameters.RemoveAt(paramName); } /// /// 回滚事务处理 /// /// 返回执行结果状态码 public void RollbackTransaction() { try { this.mySqlTransaction.Rollback(); } finally { this.ConnectionClose(); } } } }