使.NET使用数据库连接池 .

****************连接池代码***************************

 

/**
 * 实现连接池
 *
 */

using System;
using System.Collections;
using System.Timers;

namespace XuLongClass
{
 /// <summary>
 /// ObjectPool 的摘要说明。
 /// </summary>
 public abstract class ObjectPool
 {
  private long _lastCheckOut;
  private static Hashtable locked;//正在使用的对象
  private static Hashtable unlocked;//闲置的对象

  internal static long GARBAGE_INTERVAL=90*1000;//90秒

  static ObjectPool()
  {
   locked =Hashtable.Synchronized(new Hashtable());
   unlocked=Hashtable.Synchronized(new Hashtable());
  }

  internal ObjectPool()
  {
   _lastCheckOut=DateTime.Now.Ticks;
   Timer aTimer=new Timer();
   aTimer.Enabled=true;
   aTimer.Interval=GARBAGE_INTERVAL;
   aTimer.Elapsed +=new ElapsedEventHandler(CollectGarbage);
  }

  //下面三个抽像方法需要继承类来实现

  /// <summary>
  /// 生成对象
  /// </summary>
  /// <returns></returns>
  protected abstract object Create();
  /// <summary>
  /// 验证对象
  /// </summary>
  /// <param name="o"></param>
  /// <returns></returns>
  protected abstract bool Validate(object o);
  /// <summary>
  /// 销毁对象
  /// </summary>
  /// <param name="o"></param>
  protected abstract void Expire(object o);

  //获取对象
  internal object GetObjectFromPool()
  {
   long now=DateTime.Now.Ticks;
   _lastCheckOut=now;
   object o=null;

   lock(this)
   {
    try
    {
     //遍历闲置表
     foreach(DictionaryEntry myEntry in unlocked)
     {
      o=myEntry.Key;
      if(Validate(o))
      {
       unlocked.Remove(o);
       locked.Add(o,now);
       return(o);
      }
      else
      {
       unlocked.Remove(o);
       Expire(o);
       o=null;
      }
     }
    }
    catch(Exception)
    {}
    o=Create();
    locked.Add(o,now);
   }
   return o;
  }

  /// <summary>
  /// 将对象返回到池中。
  /// </summary>
  /// <param name="o"></param>
  internal void ReturnObjectToPool(object o)
  {
   if(o!=null)
   {
    lock(this)
    {
     locked.Remove(o);
     unlocked.Add(o,DateTime.Now.Ticks);
    }
   }
  }

  /// <summary>
  /// 销毁无用对象
  /// </summary>
  /// <param name="sender"></param>
  /// <param name="ea"></param>
  private void CollectGarbage(object sender,System.Timers.ElapsedEventArgs ea)
  {
   lock(this)
   {
    object o;
    long now=DateTime.Now.Ticks;
    IDictionaryEnumerator e=unlocked.GetEnumerator();
    try
    {
     while(e.MoveNext())
     {
      o=e.Key;
      if((now-((long)unlocked[o])>GARBAGE_INTERVAL))
      {
       unlocked.Remove(o);
       Expire(o);
       o=null;
      }
     }
    }
    catch(Exception){}
   }
  }
 }
}

***************************实现数据库连接池***************************

/**
 * 通过继承对象池实现数据库对象池
 *
 */

using System;
using System.Data.SqlClient;

namespace XuLongClass
{
 /// <summary>
 /// DBObjectPool 的摘要说明。
 /// </summary>
 public class DBObjectPool:ObjectPool
 {
  public DBObjectPool()
  {

  }

  public static readonly DBObjectPool Instance=new DBObjectPool();

  public static string _connectionString=new DataBase().MyConnectionString;

  /// <summary>
  /// 数据库连接字符串
  /// </summary>
  public static string ConnectionString
  {
   set
   {
    _connectionString=value;
   }
   get
   {
    return _connectionString;
   }
  }

  /// <summary>
  /// 创建对象
  /// </summary>
  /// <returns></returns>
  protected override object Create()
  {
   SqlConnection temp=new SqlConnection(_connectionString);
   temp.Open();
   return (temp);
  }

  /// <summary>
  /// 验证对象的有效性
  /// </summary>
  /// <param name="o"></param>
  /// <returns></returns>
  protected override bool Validate(object o)
  {
   try
   {
    SqlConnection temp=(SqlConnection)o;
    return (!((temp.State.Equals(System.Data.ConnectionState.Closed))));
   }
   catch(SqlException)
   {
    return false;
   }
  }

  /// <summary>
  /// 销毁对象
  /// </summary>
  /// <param name="o"></param>
  protected override void Expire(object o)
  {
   try
   {
    ((SqlConnection) o).Close();
   }
   catch(SqlException)
   {}
  }

  /// <summary>
  /// 获取对象
  /// </summary>
  /// <returns></returns>
  public SqlConnection BorrowDBConnection()
  {
   try
   {
    return ((SqlConnection)base.GetObjectFromPool());
   }
   catch(Exception ex)
   {
    throw ex;
   }
  }

  /// <summary>
  /// 返回对象
  /// </summary>
  /// <param name="e"></param>
  public void ReturnDBConnection(SqlConnection e)
  {
   base.ReturnObjectToPool(e);
  }

 }
}

**********************************应用数据库连接池***************************

DBObjectPool dbPool=new DBObjectPool();
   SqlConnection connection=dbPool.BorrowDBConnection();
   // create a command...
   System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("UpdateVisitStat", connection);
   command.CommandType = System.Data.CommandType.StoredProcedure;
    // parameters...
    System.Data.SqlClient.SqlParameter companyIDParam = command.Parameters.Add("@companyID", System.Data.SqlDbType.Int);
    companyIDParam.Value = companyID;
    System.Data.SqlClient.SqlParameter viewURLParam = command.Parameters.Add("@viewURL", System.Data.SqlDbType.NVarChar, 200);
    viewURLParam.Value = viewURL;
    System.Data.SqlClient.SqlParameter depthParam = command.Parameters.Add("@depth", System.Data.SqlDbType.Int);
    depthParam.Value = depth;
    // execute...
    command.ExecuteNonQuery();
    command.Dispose();
    dbPool.ReturnDBConnection(connection);//返回到池中。
    // return...