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