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 { /// /// SQL Server 数据库处理类 /// public class SQLDBHandler : IDBHandlerble, IDisposable { private Hashtable links; /// /// SqlCommand对象实例 /// private SqlCommand sqlCommand; /// /// 声明sql 的数据库连接对象 /// private SqlConnection sqlConnection; /// /// 取得整个系统的数据库都存放在一台服务器的数据库连接字符串 /// private static string sqlDefaultConnectionString = ConfigurationManager.AppSettings["DefaultConnectionString"].Trim(); /// /// 模块使用的字符串变量 /// private static string sqlModuleConnectionString = string.Empty; /// /// 声明事务对象 /// protected SqlTransaction sqlTransaction; /// /// 整个系统使用同一个数据库连接,通过web.config中DefultConnectionString中的连接字符串来连接数据库 /// /// 取得web.config或者app.config中AppSetting中sql_default中的连接字符串 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(); } /// /// 不同模块使用不同的数据库连接,通过web.config中DefultConnectionString中的连接字符串来连接数据库 /// /// 模块关键字 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(); } /// 添加参数 /// /// 参数名称 /// 参数类型 /// 参数方向 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); } /// /// 添加有长度的参数 /// /// 参数名称 /// 参数类型 /// 参数长度 /// 参数方向 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); } /// /// 重载-添加有长度的参数 /// /// 参数名称 /// 参数类型 /// 参数长度 /// 参数方向 /// 参数值 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 AddParameter(string paramName, DbType paramType, ParameterDirection direction, object pValue) { this.AddParameter(paramName, paramType, direction); this.ModifyParameter(paramName, pValue); } /// 添加参数 /// /// 参数名称 /// 参数类型 /// 参数方向 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); } /// /// 添加有长度的参数 /// /// 参数名称 /// 参数类型 /// 参数长度 /// 参数方向 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); } /// /// 重载-添加有长度的参数 /// /// 参数名称 /// 参数类型 /// 参数长度 /// 参数方向 /// 参数值 public void AddParameter(string paramName, SqlDbType paramType, int paramSize, ParameterDirection direction, object pValue) { this.AddParameter(paramName, paramType, paramSize, direction); this.ModifyParameter(paramName, pValue); } /// /// 重载-添加参数 /// /// 参数名称 /// 参数类型 /// 参数方向 /// 参数值 public void AddParameter(string paramName, SqlDbType paramType, ParameterDirection direction, object pValue) { this.AddParameter(paramName, paramType, direction); this.ModifyParameter(paramName, pValue); } /// /// 启动事务处理 /// /// 返回执行结果状态码 public void BeginTransaction() { this.ConnectionOpen(); if (null == this.sqlTransaction) { this.sqlTransaction = this.sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted); } this.sqlCommand.Transaction = this.sqlTransaction; } /// /// 提交事务处理 /// /// 返回执行结果状态码 public void CommitTransaction() { try { this.sqlTransaction.Commit(); } finally { this.ConnectionClose(); } } /// /// 关闭数据库连接 /// public void ConnectionClose() { if (ConnectionState.Closed != this.sqlConnection.State) { this.sqlConnection.Close(); this.sqlConnection.Dispose(); } } /// /// 打开数据库连接 /// public void ConnectionOpen() { if (ConnectionState.Closed == this.sqlConnection.State) { this.sqlConnection.Open(); } } /// /// 释放资源 /// public void Dispose() { if (this.sqlConnection != null) { this.sqlConnection.Dispose(); } if (this.sqlCommand != null) { this.sqlCommand.Dispose(); } if (this.sqlTransaction != null) { this.sqlTransaction.Dispose(); } } /// /// 返回数据适配器 /// /// 查询命令 /// 返回执行结果 public IDbDataAdapter ExecuteAdapter(string commandText) { return this.ExecuteAdapter(commandText, CommandType.Text); } /// /// 重载-返回数据适配器(适应存储过程调用需求) /// /// 查询命令 /// 查询命令类型 /// 返回执行结果 public IDbDataAdapter ExecuteAdapter(string commandText, CommandType commandType) { this.sqlCommand.CommandText = commandText; this.sqlCommand.CommandType = commandType; this.ConnectionOpen(); return new SqlDataAdapter(this.sqlCommand); } /// /// 返回数据集 /// /// 查询命令 /// 返回执行结果 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.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; } /// /// 返回数据表 /// /// 查询命令 /// 返回执行结果 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.sqlCommand.CommandText = commandText; this.sqlCommand.CommandType = commandType; this.ConnectionOpen(); new SqlDataAdapter(this.sqlCommand).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) { 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()); } /// /// 执行无返回类型数据查询(返回影响行数) /// /// 查询命令(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 row; this.sqlCommand.CommandText = commandText; this.sqlCommand.CommandType = commandType; try { this.ConnectionOpen(); row = this.sqlCommand.ExecuteNonQuery(); } finally { if (isCloseConnection) { this.ConnectionClose(); } } return row; } /// /// 返回向前只读数据集查询 /// /// 查询命令 /// 返回执行结果 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.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); } /// /// 返回第一行第一列结果的数据查询 /// /// 查询命令 /// 是否关闭数据连结,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.sqlCommand.CommandText = commandText; this.sqlCommand.CommandType = commandType; this.ConnectionOpen(); try { row = this.sqlCommand.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() { try { if (HttpContext.Current.Session["uLinks"] != null) { this.links = HttpContext.Current.Session["uLinks"] as Hashtable; if (this.links.Count == 0) { this.links = null; } } } catch { } } /// /// 提取参数值 /// /// 参数名称 /// 返回执行结果状态码 public object GetParameter(string paramName) { if (this.sqlCommand.Parameters.Contains(paramName)) { return this.sqlCommand.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.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; } } /// /// 移除所有的参数 /// public void RemoveAllParameters() { this.sqlCommand.Parameters.Clear(); } /// /// 移除参数 /// /// 参数名称 public void RemoveParameter(string paramName) { this.sqlCommand.Parameters.RemoveAt(paramName); } /// /// 回滚事务处理 /// /// 返回执行结果状态码 public void RollbackTransaction() { try { this.sqlTransaction.Rollback(); } finally { this.ConnectionClose(); } } } }