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(); } } } }