Asp.net 连接池使用

连接池类：

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
/// ConnectionPool 的摘要说明
/// </summary>
public class ConnectionPool
{
    private static ConnectionPool cpool = null;
    private static Object objlock = typeof(ConnectionPool);
    private int size = 10;
    private int useCount = 0;
    private ArrayList pool = null;
    private String ConnectionStr = "";

 private ConnectionPool()
 {
        ConnectionStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        size = 100;
        pool = new ArrayList();
 }

    public static ConnectionPool getPool()
    {
        lock (objlock)
        {
            if (cpool == null)
            {
                cpool = new ConnectionPool();
            }
            return cpool;
        }
    }

    public SqlConnection getConnection()
    {
        lock (pool)
        {
            SqlConnection tmp = null;
            if (pool.Count > 0)
            {
                tmp = (SqlConnection)pool[0];
                pool.RemoveAt(0);
                if (!isUserful(tmp))
                {
                    useCount--;
                    tmp = getConnection();
                }
            }
            else
            {
                if (useCount < size)
                {
                    try
                    {
                        SqlConnection conn = new SqlConnection(ConnectionStr);
                        conn.Open();                       
                        useCount++;
                        tmp = conn;
                    }
                    catch (Exception e)
                    {
                    }
                }
            }
            return tmp;
        }
    }

    public void closeConnection(SqlConnection con)
    {
        lock (pool)
        {
            if (con != null)
            {
                pool.Add(con);
            }
        }
    }

    private bool isUserful(SqlConnection con)
    {
        bool result = true;
        if (con != null)
        {
            string sql = "select 1 from IpSet";
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                cmd.ExecuteScalar().ToString();
            }
            catch
            {
                result = false;
            }

        }
        return result;
    }
}
调用方法：

 

 public static DataTable GetEditon(string Subject)
    {
        //string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        //SqlConnection conn = new SqlConnection(con);

        ConnectionPool cPool = ConnectionPool.getPool();
        SqlConnection conn = cPool.getConnection();
        DataTable dt = null;
        if (conn != null)
        {
            try
            {
               
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Proc_EODSourceDB_GetEditionBySubject";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Subject", SqlDbType.VarChar, 50);
                cmd.Parameters["@Subject"].Value = Subject;
                DateTime begintime = System.DateTime.UtcNow;
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DateTime endtime = System.DateTime.UtcNow;
                Console.Out.WriteLine("getediton time:%d", endtime.Ticks - begintime.Ticks); //本想看执行时间的，不知为何不输出。web里能用这个吗？
                dt = new DataTable();
                sda.Fill(dt);
               
            }
            catch
            {
            }
            finally
            {
                cPool.closeConnection(conn);
            }
        }        return dt;
    }

写得不好，呵呵，还是在别人的指导下完成的。更多关于连接池的，可参看：

http://www.cnblogs.com/chinhr/archive/2007/06/19/788996.html

http://www.testage.net/html/88/n-111988.html

当然高手路过了，还望指点一下，这里没用到事务，只是简单的处理并发事件时用的。