数据库连接池

实现数据连接池,让系统有更高有执行效率

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Threading;

namespace HappyWood.DataAccess
{
 /** <summary>
 /// 内层对于共享工作的管理实体
 /// </summary>
 public class DataAccess
 {
  private static ArrayList list = new ArrayList();         // 共享实体集合
  private static ArrayList listTime = new ArrayList();  //共享实体最近使用时间

  private const int MaxInstances = 5;         // 最大的池化数量
  private const int RelaseInterval = 1000;    // 最长允许的闲置时间
  private const int DueTime = 1000;           // 时钟的预热时间
  private static System.Threading.Timer timer = new System.Threading.Timer(new TimerCallback(Release), null, DueTime, RelaseInterval);// 回收控制时钟

  static DataAccess()
  {
   //list = new ArrayList();
   //TimerCallback timerDelegate = new TimerCallback(Release);

   //timer = new System.Threading.Timer(timerDelegate, null, DueTime, RelaseInterval);

   //   timer = new System.Threading.Timer(Release(), null, DueTime, RelaseInterval);
  }

  /** <summary>
  /// 申请和动态创建共享实体的过程
  /// </summary>
  /// <returns></returns>
  internal static SqlConnection Create()
  {
   lock (list)
   {
    // 首先使用已有的实例
    if(list.Count > 0)
    {
     for(int i=0; i<list.Count; i++)
     {
      switch((list[i] as SqlConnection).State)
      {
       case System.Data.ConnectionState.Open:
       {
        listTime[i] = DateTime.Now.ToString();
        return list[i] as SqlConnection;
       }
       case System.Data.ConnectionState.Closed:
       {
        SqlConnection cn = (list[i] as SqlConnection);
        cn.Open();
        listTime[i] = DateTime.Now.ToString();
        return cn;
       }
       case System.Data.ConnectionState.Broken:
       {
        SqlConnection cn = (list[i] as SqlConnection);
        cn.Close();
        cn.Open();
        listTime[i] = DateTime.Now.ToString();
        return cn;
       }
      }
     }
    }

    // 如果可以创建新的实例
    if (list.Count < MaxInstances)
    {
     SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["strConn"]);
     cn.Open();
     list.Add(cn);
     listTime.Add(DateTime.Now.ToString());

     return cn;
    }

    // 不能继续申请
    return null;
   }
  }

  /** <summary>
  /// 获得实际运行的实体数量
  /// </summary>
  internal static int ActiveInstances
  {
   get
   {
    lock (list)
    {
     if (list.Count == 0)
      return 0;

     int count = 0;
     for (int i = 0; i < list.Count; i++)
     {
      if ((list[i] as SqlConnection).State != ConnectionState.Closed)
      {
       count++;
      }
     }

     return count;
    }
   }
  }

  /** <summary>
  /// 获得池中已经构造出的实体数量
  /// </summary>
  internal static int Instances
  {
   get
   {
    lock (list)
    {
     return list.Count;
    }
   }
  }

  /** <summary>
  /// 根据时钟的触发,动态释放引用实例的方法
  /// </summary>
  /// <param name="state"></param>
  private static void Release(object state)
  {
   lock (list)
   {
    if (list.Count <= 0)
     return;

    // 检查是否有需要销毁的实例,并把他们的序号保存在releaseList

    for (int i = 0; i < list.Count; i++)
    {
     if ((list[i] as SqlConnection).State == ConnectionState.Closed)
     {
      TimeSpan span = DateTime.Now - Convert.ToDateTime(listTime[i].ToString());
      if (span.TotalMilliseconds > RelaseInterval)
      {
                            (list[i] as SqlConnection).Close();
                            (list[i] as SqlConnection).Dispose();
       list.RemoveAt(i);
      }
     }
    }
   }
  }
 }
}