asp.net数据连接池源码

 

using System;
using System.Data;
using System.Data.SqlClient ;
using System.Collections;
using System.Configuration;
using System.Web ;
using System.Xml;
using OPPO_ELMS;
using Microsoft.ApplicationBlocks.Data;

namespace OPPO_ELMS.ConnectionPool
{

/// <summary>
/// 执行类型
/// </summary>
public enum ExecuteType:int
{
/// <summary>
/// 仅连接
/// </summary>
OnlyConn,

/// <summary>
/// 事务
/// </summary>
Tran
}

/// <summary>
/// ISqlScope 的摘要说明。
/// 对数据库的更新操作请在您的方法中使用conn.BeginTransaction()-->conn.Commit()-->conn.Rollback()
/// 当用到嵌套的事务时，数据联接用 ref 传递，并且只在最外层加RollBack()
/// </summary>
public interface ISqlScope:IDisposable
{
new void Dispose();

SqlConnection Connection
{
get;
}

SqlTransaction Transaction
{
get;
}

string ConnString
{
get;
set;
}

void EnterQuery();
void LeaveQuery();

SqlCommand CreateCommand(string cmdtext);
SqlDataAdapter CreateAdapter(string cmdtext);

SqlDataReader ExecuteReader(string cmdtext);
void ExecuteNonQuery(string cmdtext);
object ExecuteScalar(string cmdtext);

object ExecuteTextScalar(string cmdtext,params SqlParameter[] commandParameters);
object ExecuteSpScalar(string spName,params SqlParameter[] commandParameters);

string ExecuteString(string cmdtext);
int ExecuteInt32(string cmdtext);
double ExecuteDouble(string cmdtext);
DateTime ExecuteDateTime(string cmdtext);

int RunSql(string strSql);
int RunSql(string strSql,string oldValue,string newValue);

int ExecuteTextNonQuery(string cmdtext,params SqlParameter[] commandParameters);
int ExecuteSpNonQuery(string spName,params SqlParameter[] commandParameters);

SqlDataReader ExecuteTextReader(string cmdtext,params SqlParameter[] commandParameters);
SqlDataReader ExecuteSpReader(string spName,params SqlParameter[] commandParameters);

DataSet ExecuteDataSet(string QueryString);
DataSet ExecuteTextDataSet(string cmdtext,params SqlParameter[] commandParameters);
DataSet ExcuteSpDataSet(string spName, params SqlParameter[] commandParameters);

void ExcuteSp( string spName, params SqlParameter[] commandParameters);

string GetDataConnectString();

#region 连接
/// <summary>
/// 开始连接
/// </summary>
void BeginConn();

/// <summary>
/// 结束连接
/// </summary>
void EndConn();

#endregion

#region 事务

/// <summary>
/// 开始事务
/// </summary>
void BeginTran();

/// <summary>
/// 提交事务
/// </summary>
void Commit();

/// <summary>
/// 回滚
/// </summary>
void RollBack();

#endregion
}

public class SqlScopeException:Exception
{
public SqlScopeException(string message)
:base(message)
{
}
public SqlScopeException(string message,Exception innerException)
:base(message,innerException)
{
}
}
public class SqlScopeProgrammingException:SqlScopeException
{
public SqlScopeProgrammingException(string message)
:base(message)
{
LogException();
}
public SqlScopeProgrammingException(string message,Exception innerException)
:base(message,innerException)
{
}
public void LogException()
{
}
}

public class OPPOConnection :ISqlScope
{
#region 私有变量
/// <summary>
/// 执行类型,默认为只链接
/// </summary>
private ExecuteType execType = ExecuteType.OnlyConn;

/// <summary>
/// SqlTransaction
/// </summary>
private SqlTransaction sqlTran = null;
#endregion

public static string mappath=string.Empty;
private static string m_strConnection=string.Empty;

private SqlTransaction objtrans = null;

public SqlTransaction ObjTrans
{
get { return objtrans ; }
set {objtrans = value ;}
}

private void SetTrans(SqlCommand cmd)
{
if(ObjTrans!=null)
cmd.Transaction = ObjTrans ;
}

public OPPOConnection()
{
connString=this.GetDataConnectString();
conn = new SqlConnection(connString) ;
}

public string ConnString
{
set{connString=value;}
get{return connString;}
}

// ~OPPOConnection()
// {
// DisposeConnection();
// }
public void Dispose()
{
DisposeConnection();
}

string connString;
SqlConnection conn=null;
SqlTransaction trans=null;
Stack stack=null;
//取得数据库联接字符串
public string GetDataConnectString()
{
if (m_strConnection!=string.Empty)
return m_strConnection;
else
{
string strConnectionString=string.Empty;
strConnectionString = Global.ConectString;//GetConfigValue.GetDBConnection();
return strConnectionString;
}
}
public SqlConnection GetConnection()
{
CheckConnection();
return conn;
}

public SqlTransaction GetTransaction()
{
CheckConnection();
return trans;
}

SqlConnection ISqlScope.Connection
{
get
{
return GetConnection();
}
}

SqlTransaction ISqlScope.Transaction
{
get
{
return GetTransaction();
}
}

public string ConnectionString
{
get
{
return connString;
}
}

void OpenConnection()
{
if(!IsClosed)
throw(new SqlScopeProgrammingException("Connection已经被打开!"));
try
{
stack=new Stack();
conn=new SqlConnection(connString);
conn.Open();

}
catch
{
DisposeConnection();
throw;
}
}

void CheckConnection()
{
if(IsClosed)
throw(new SqlScopeException("Connection没有打开或者已经被关闭"));
}

bool IsClosed
{
get
{
if (conn.State == System.Data.ConnectionState.Open)
return false ;
else
return true ;

}

}
private void DisposeConnection()
{

try
{
if(conn!=null)
conn.Dispose();
}
finally
{
conn=null;
}
}

public SqlCommand CreateCommand(string commandText)
{
if(commandText==null)
throw(new ArgumentNullException("commandText"));
CheckConnection();
SqlCommand cmd = new SqlCommand(commandText,conn) ;
SetTrans(cmd);
return cmd ;
}
public SqlDataAdapter CreateAdapter(string commandText)
{
if(commandText==null)
throw(new ArgumentNullException("commandText"));
CheckConnection();
return new SqlDataAdapter(CreateCommand(commandText));
}

public void ExecuteNonQuery(string cmdtext)
{
using(SqlCommand cmd=CreateCommand(cmdtext))
{
SetTrans(cmd);
cmd.ExecuteNonQuery();
}
}
public SqlDataReader ExecuteReader(string cmdtext)
{
using(SqlCommand cmd=CreateCommand(cmdtext))
{
SetTrans(cmd);
return cmd.ExecuteReader();
}
}
public object ExecuteScalar(string cmdtext)
{
// this.EnterQuery();
using(SqlCommand cmd=CreateCommand(cmdtext))
{
SetTrans(cmd);
return cmd.ExecuteScalar();
}
// this.LeaveQuery();
}
public String ExecuteString(string cmdtext)
{
return Convert.ToString(ExecuteScalar(cmdtext));
}
public int ExecuteInt32(string cmdtext)
{
return Convert.ToInt32(ExecuteScalar(cmdtext));
}
public double ExecuteDouble(string cmdtext)
{
return Convert.ToDouble(ExecuteScalar(cmdtext));
}
public DateTime ExecuteDateTime(string cmdtext)
{
return Convert.ToDateTime(ExecuteScalar(cmdtext));
}

/// <summary>
/// 打开连接
///
/// 和EnterQuery对应,和BeginTransaction不同之处，是它不会启动Transaction
/// 如果只进行SELECT时，即不需要COMMIT或ROLLBACK，可以用这个代替BeginTransaction
/// </summary>
public void EnterQuery()
{
if(IsClosed)
OpenConnection();
stack.Push(false);
}
/// <summary>
/// 关闭连接
/// </summary>
public void LeaveQuery()
{
CheckConnection();
bool isTrans=(bool)stack.Peek();
if(isTrans)
throw(new SqlScopeProgrammingException("不能使用LeaveQuery,因为对应的一次调用不是EnterQuery"));
stack.Pop();
if(stack.Count==0)
DisposeConnection();
}
/// <summary>
/// 运行SQL
/// </summary>
/// <param name="strSql">要执行的SQL</param>
/// <returns>受影响的行数</returns>
public int RunSql(string strSql)
{
try
{
SqlCommand cmd=this.CreateCommand(strSql);
SetTrans(cmd);
int row=cmd.ExecuteNonQuery();
return row;
}
catch (Exception e)
{
throw e;
}
}

/// <summary>
/// 重载RunSql方法，将字段为空的值替换成‘null’值
/// </summary>
/// <param name="strSql">传人的Sql语句</param>
/// <param name="oldValue">''</param>
/// <param name="newValue">null</param>
/// <returns></returns>
public int RunSql(string strSql,string oldValue,string newValue)
{
try
{
SqlCommand cmd=this.CreateCommand(strSql.Replace(oldValue,newValue));
SetTrans(cmd);
int row=cmd.ExecuteNonQuery();
return row;
}
catch (Exception e)
{
throw e;
}
}

public void ExcuteSpOutput( string spName, out string retvalue ,params SqlParameter[] commandParameters)
{
try
{
SqlCommand cmd=this.CreateCommand(spName);
cmd.CommandType=CommandType.StoredProcedure;
if (commandParameters != null)
{
AttachParameters(cmd,commandParameters);
}
cmd.ExecuteScalar();
retvalue = cmd.Parameters["@output"].Value.ToString() ;
cmd.Parameters.Clear();
}
catch (System.Exception e)
{
throw e;
}
}

public void ExcuteSp( string spName,params SqlParameter[] commandParameters)
{
try
{
SqlCommand cmd=this.CreateCommand(spName);
cmd.CommandType=CommandType.StoredProcedure;
if (commandParameters != null)
{
AttachParameters(cmd,commandParameters);
}
cmd.ExecuteNonQuery() ;
cmd.Parameters.Clear();
}
catch (System.Exception e)
{
throw e;
}
}

#region 执行参数传递方法

 

/// <summary>
/// 执行参数传递方法(不返回结果)
/// </summary>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public int ExecuteTextNonQuery(string commandText, params SqlParameter[] commandParameters)
{
if( execType == ExecuteType.Tran && sqlTran != null)
{
return ExecuteNonQuery(sqlTran,CommandType.Text,commandText,commandParameters);
}
return ExecuteNonQuery(conn,CommandType.Text,commandText,commandParameters);
}

 

/// <summary>
/// 无参数传递(不返回结果)
/// </summary>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <returns></returns>
public int ExecuteSpNonQuery( string commandText, params SqlParameter[] commandParameters)
{
if( execType == ExecuteType.Tran && sqlTran != null)
{
return ExecuteNonQuery(sqlTran,CommandType.StoredProcedure,commandText,commandParameters);
}
return ExecuteNonQuery(conn,CommandType.StoredProcedure,commandText,commandParameters);
}

 

/// <summary>
/// 执行参数传递方法(返回DataSet)
/// </summary>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public DataSet ExecuteDataset(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if( execType == ExecuteType.Tran && sqlTran != null)
{
return ExecuteDataset(sqlTran,commandType,commandText,commandParameters);
}
return ExecuteDataset(conn,commandType,commandText,commandParameters);
}

 

/// <summary>
/// 执行元参数传递方法(返回DataSet)
/// </summary>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public DataSet ExecuteTextDataSet(string commandText, params SqlParameter[] commandParameters)
{
if( execType == ExecuteType.Tran && sqlTran != null)
{
return ExecuteDataset(sqlTran,CommandType.Text,commandText,commandParameters);
}
return ExecuteDataset(conn,CommandType.Text,commandText,commandParameters);
}

 

/// <summary>
/// 执行SQL返回DataSet对象
/// </summary>
/// <param name="QueryString">SQL语句</param>
/// <returns></returns>
public DataSet ExecuteDataSet(string commandText)
{
if( execType == ExecuteType.Tran && sqlTran != null)
{
return ExecuteDataset(sqlTran,CommandType.Text,commandText,(SqlParameter[])null);
}
return ExecuteDataset(conn,CommandType.Text,commandText,(SqlParameter[])null);
}

 

/// <summary>
/// 执存储过程返回DataSet对象
/// </summary>
/// <param name="spName"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public DataSet ExcuteSpDataSet(string spName, params SqlParameter[] commandParameters)
{
if( execType == ExecuteType.Tran && sqlTran != null)
{
return ExecuteDataset(sqlTran,CommandType.StoredProcedure,spName,(SqlParameter[])null);
}
return ExecuteDataset(conn,CommandType.StoredProcedure,spName,(SqlParameter[])null);
}

 

/// <summary>
/// 执行参数传递方法(返回SqlDataReader)
/// </summary>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public SqlDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if( execType == ExecuteType.Tran && sqlTran != null)
{
return ExecuteReader(sqlTran.Connection,sqlTran,commandType,commandText,commandParameters);
}
return ExecuteReader(conn,(SqlTransaction)null,commandType,commandText,commandParameters);
}

 

/// <summary>
/// 执行参数传递方法(返回SqlDataReader)
/// </summary>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public SqlDataReader ExecuteReader(CommandType commandType, string commandText)
{
if( execType == ExecuteType.Tran && sqlTran != null)
{
return ExecuteReader(sqlTran.Connection,sqlTran,commandType,commandText,(SqlParameter[])null);
}
return ExecuteReader(conn,(SqlTransaction)null,commandType,commandText,(SqlParameter[])null);
}

 

/// <summary>
/// 执行命令文本,返回标量值
/// </summary>
/// <param name="cmdtext"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public object ExecuteTextScalar(string commandText,params SqlParameter[] commandParameters)
{
if( execType == ExecuteType.Tran && sqlTran != null)
{
return ExecuteScalar(sqlTran.Connection,sqlTran,CommandType.Text,commandText,commandParameters);
}
return ExecuteScalar(conn,(SqlTransaction)null,CommandType.Text,commandText,commandParameters);
}

 

/// <summary>
/// 执行存储过程,返回标量值
/// </summary>
/// <param name="spName"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public object ExecuteSpScalar(string spName,params SqlParameter[] commandParameters)
{
if( execType == ExecuteType.Tran && sqlTran != null)
{
return ExecuteScalar(sqlTran.Connection,sqlTran,CommandType.StoredProcedure,spName,commandParameters);
}
return ExecuteScalar(conn,(SqlTransaction)null,CommandType.StoredProcedure,spName,commandParameters);
}

 

/// <summary>
/// 执行命令文本,返回SqlDataReader
/// </summary>
/// <param name="cmdtext"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public SqlDataReader ExecuteTextReader(string commandText,params SqlParameter[] commandParameters)
{
if( execType == ExecuteType.Tran && sqlTran != null)
{
return ExecuteReader(sqlTran.Connection,sqlTran,CommandType.Text,commandText,commandParameters);
}
return ExecuteReader(conn,(SqlTransaction)null,CommandType.Text,commandText,commandParameters);
}

 

/// <summary>
/// 执行存储过程,返回SqlDataReader
/// </summary>
/// <param name="spName"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public SqlDataReader ExecuteSpReader(string spName,params SqlParameter[] commandParameters)
{
if( execType == ExecuteType.Tran && sqlTran != null)
{
return ExecuteReader(sqlTran.Connection,sqlTran,CommandType.StoredProcedure,spName,commandParameters);
}
return ExecuteReader(conn,(SqlTransaction)null,CommandType.StoredProcedure,spName,commandParameters);
}

#region 连接
  /// <summary>
  /// 开始连接
  /// </summary>
  public void BeginConn()
  {
   EnterQuery();
  }

  /// <summary>
  /// 结束连接
  /// </summary>
  public void EndConn()
  {
   LeaveQuery();
  }

  #endregion

  #region 事务

  /// <summary>
  /// 开始事务
  /// </summary>
  public void BeginTran()
  {
   sqlTran = conn.BeginTransaction();
   execType = ExecuteType.Tran;
  }

  /// <summary>
  /// 提交事务
  /// </summary>
  public void Commit()
  {
   if(sqlTran != null)
    sqlTran.Commit();
  }

  /// <summary>
  /// 回滚
  /// </summary>
  public void RollBack()
  {
   if(sqlTran != null)
    sqlTran.Rollback();
  }
  #endregion

  #region 私有方法

  /// <summary>
  /// 给Command对象赋值
  /// </summary>
  /// <param name="command">命令对象</param>
  /// <param name="connection">连接对象</param>
  /// <param name="transaction">事各对象,可以为空</param>
  /// <param name="commandType">命令对象类型(stored procedure, text, etc.)</param>
  /// <param name="commandText">命令文本,如果是存储过程,则为其名称</param>
  /// <param name="commandParameters">参数</param>
  private void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
  {
   // 如果连接未打开,则打开
   if (connection.State != ConnectionState.Open)
   {
    connection.Open();
   }

   command.Connection = connection;

   command.CommandText = commandText;

   // 如果事务不为空,则给命令对象传入事务
   if (transaction != null)
   {
    command.Transaction = transaction;
   }

   // 类型
   command.CommandType = commandType;

   // 参数赋值
   if (commandParameters != null)
   {
    AttachParameters(command, commandParameters);
   }

   return;
  }

  /// <summary>
  /// 将参数赋给命令对象
  /// </summary>
  /// <param name="command">命令对象</param>
  /// <param name="commandParameters">参数</param>
  private void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
  {
   foreach (SqlParameter p in commandParameters)
   {
    // 检查参数类型
    if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null))
    {
     p.Value = DBNull.Value;
    }
    
    command.Parameters.Add(p);
   }
  }

  /// <summary>
  /// 给参数赋值
  /// </summary>
  /// <param name="commandParameters">参数</param>
  /// <param name="parameterValues">参数值</param>
  private void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
  {
   if ((commandParameters == null) || (parameterValues == null))
   {
    return;
   }

   if (commandParameters.Length != parameterValues.Length)
   {
    throw new ArgumentException("Parameter count does not match Parameter Value count.");
   }

   for (int i = 0, j = commandParameters.Length; i < j; i++)
   {
    commandParameters.Value = parameterValues;
   }
  }

  /// <summary>
  /// 执行事务命令
  /// </summary>
  /// <param name="transaction">事务对象</param>
  /// <param name="commandType">类型</param>
  /// <param name="commandText">命令文本</param>
  /// <param name="commandParameters">参数</param>
  /// <returns>影响行数</returns>
  private int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  {
   // 建立命令对象
   SqlCommand cmd = new SqlCommand();
   PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
  
   // 执行查询
   int retval = cmd.ExecuteNonQuery();
  
   // 从集合中移除所有项
   cmd.Parameters.Clear();
   return retval;
  }

  /// <summary>
  /// 执行命令(无参数)
  /// </summary>
  /// <param name="connection">连接对象</param>
  /// <param name="commandType">类型</param>
  /// <param name="commandText">命令文本</param>
  /// <param name="commandParameters">参数</param>
  /// <returns>影响行数</returns>
  private int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  {
   // 创建命令对象
   SqlCommand cmd = new SqlCommand();
   PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
  
   // 执行
   int retval = cmd.ExecuteNonQuery();
  
   // 从集合中移除所有项
   cmd.Parameters.Clear();
   return retval;
  }

  /// <summary>
  /// 使用提供的参数,执行查询(返回DataSet)
  /// </summary>
  /// <param name="connection">连接对象</param>
  /// <param name="commandType">类型</param>
  /// <param name="commandText">命令文本</param>
  /// <param name="commandParameters">参数</param>
  /// <returns>数据集</returns>
  public DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  {
   // 新建命令对象
   SqlCommand cmd = new SqlCommand();
   PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
  
   // 新建数据适配器
   SqlDataAdapter da = new SqlDataAdapter(cmd);
   DataSet ds = new DataSet();

   // 填充数据集
   da.Fill(ds);
  
   // 清空
   cmd.Parameters.Clear();
  
   // 返回数据集
   return ds;      
  }

  /// <summary>
  /// 使用提供的参数,执行查询(返回DataSet)
  /// </summary>
  /// <param name="transaction">事务对象</param>
  /// <param name="commandType">类型</param>
  /// <param name="commandText">文本</param>
  /// <param name="commandParameters">参数</param>
  /// <returns>数据集</returns>
  private DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  {
   // 建立命令对象
   SqlCommand cmd = new SqlCommand();
   PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
  
   // 数据适配器
   SqlDataAdapter da = new SqlDataAdapter(cmd);
   DataSet ds = new DataSet();

   // 填充数据集
   da.Fill(ds);
  
   // 清空
   cmd.Parameters.Clear();
  
   // 返回数据集
   return ds;
  }

  /// <summary>
  /// 执行命令,返回DataReader对象
  /// </summary>
  /// <remarks>
  /// <param name="connection">连接对象</param>
  /// <param name="transaction">事务对象,可以为null</param>
  /// <param name="commandType">类型</param>
  /// <param name="commandText">文本</param>
  /// <param name="commandParameters">参数</param>
  /// <returns>SqlDataReader</returns>
  private SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText,params SqlParameter[] commandParameters)
  {
   // 创建命令对象
   SqlCommand cmd = new SqlCommand();
   PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
  
   // 创建Reader
   SqlDataReader dr;

   dr = cmd.ExecuteReader();
  
   // 清空参数
   cmd.Parameters.Clear();
  
   return dr;
  }

  /// <summary>
  /// 执行命令,返回标量值
  /// </summary>
  /// <param name="connection">连接对象</param>
  /// <param name="transaction">事务对象,可以为null</param>
  /// <param name="commandType">类型</param>
  /// <param name="commandText">文本</param>
  /// <param name="commandParameters">参数</param>
  /// <returns>标量对象</returns>
  private object ExecuteScalar(SqlConnection connection,SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  {
   // 创建命令对象
   SqlCommand cmd = new SqlCommand();
   PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
  
   // 执行命令
   object retval = cmd.ExecuteScalar();
  
   //  清空参数
   cmd.Parameters.Clear();
   return retval;
  }

  #endregion
}

}