class DbConn
{
//usingSystem.Data;
//usingSystem.Data.SqlClient;
private const int MaxPool=10;//×î´óÁ¬½ÓÊý
private const int MinPool=5;//×îСÁ¬½ÓÊý
private const bool Asyn_Process=true;//ÉèÖÃÒì²½·ÃÎÊÊý¾Ý¿â
//ÔÚµ¥¸öÁ¬½ÓÉϵõ½ºÍ¹ÜÀí¶à¸ö¡¢½öÏòÇ°ÒýÓúÍÖ»¶ÁµÄ½á¹û¼¯(ADO.NET2.0)
private const bool Mars=true;
private const int Conn_Timeout=15;//ÉèÖÃÁ¬½ÓµÈ´ýʱ¼ä
private const int Conn_Lifetime=15;//ÉèÖÃÁ¬½ÓµÄÉúÃüÖÜÆÚ
private string ConnString="";//Á¬½Ó×Ö·û´®
private SqlConnection SqlDrConn=null;//Á¬½Ó¶ÔÏó
public DbConn()//¹¹Ô캯Êý
{
ConnString=GetConnString();
SqlDrConn=new SqlConnection(ConnString);
}
private string GetConnString()
{
return"server=localhost;"
+"integratedsecurity=sspi;"
+"database=pubs;"
+"MaxPoolSize="+MaxPool+";"
+"MinPoolSize="+MinPool+";"
+"ConnectTimeout="+Conn_Timeout+";"
+"ConnectionLifetime="+Conn_Lifetime+";"
+"AsynchronousProcessing="+Asyn_Process+";";
//+"MultipleActiveResultSets="+Mars+";";
}
public DataTable GetDataReader(string StrSql)//Êý¾Ý²éѯ
{
//µ±Á¬½Ó´¦ÓÚ´ò¿ª×´Ì¬Ê±¹Ø±Õ,È»ºóÔÙ´ò¿ª,±ÜÃâÓÐʱºòÊý¾Ý²»Äܼ°Ê±¸üÐÂ
if(SqlDrConn.State==ConnectionState.Open)
{
SqlDrConn.Close();
}
try
{
SqlDrConn.Open();
SqlCommandSql Cmd=new SqlCommand(StrSql,SqlDrConn);
SqlDataReader SqlDr=SqlCmd.ExecuteReader();
if(SqlDr.HasRows)
{
DataTable dt=new DataTable();
//¶ÁÈ¡SqlDataReaderÀïµÄÄÚÈÝ
dt.Load(SqlDr);
//¹Ø±Õ¶ÔÏóºÍÁ¬½Ó
SqlDr.Close();
SqlDrConn.Close();
returndt;
}
returnnull;
}
catch(Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
return null;
}
finally
{
SqlDrConn.Close();
}
}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Timers;

namespace ObjectPoolSample
{
public abstract class ObjectPool
{
//Last Checkout time of any object from the pool.
private long lastCheckOut;

//Hashtable of the check-out objects.
private static Hashtable locked;

//Hashtable of available objects
private static Hashtable unlocked;

//Clean-Up interval
internal static long GARBAGE_INTERVAL = 90 * 1000; //90 seconds
static ObjectPool()
{
locked = Hashtable.Synchronized(new Hashtable());
unlocked = Hashtable.Synchronized(new Hashtable());
}

internal ObjectPool()
{
lastCheckOut = DateTime.Now.Ticks;

//Create a Time to track the expired objects for cleanup.
Timer aTimer = new Timer();
aTimer.Enabled = true;
aTimer.Interval = GARBAGE_INTERVAL;
aTimer.Elapsed += new ElapsedEventHandler(CollectGarbage);
}

protected abstract object Create();

protected abstract bool Validate(object o);

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;
unlocked.Remove(o);
if (Validate(o))
{
locked.Add(o, now);
return o;
}
else
{
Expire(o);
o = null;
}
}
}
catch (Exception) { }
o = Create();
locked.Add(o, now);
}
return o;
}

internal void ReturnObjectToPool(object o)
{
if (o != null)
{
lock (this)
{
locked.Remove(o);
unlocked.Add(o, DateTime.Now.Ticks);
}
}
}

private void CollectGarbage(object sender, 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.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ObjectPoolSample
{
public sealed class DBConnectionSingletion : ObjectPool
{
private DBConnectionSingletion() { }

public static readonly DBConnectionSingletion Instance =
new DBConnectionSingletion();

private static string connectionString =
@"server=(local);Trusted Connection=yes;database=northwind";

public static string ConnectionString
{
get
{
return connectionString;
}
set
{
connectionString = value;
}
}

protected override object Create()
{
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
return conn;
}

protected override bool Validate(object o)
{
try
{
SqlConnection conn = (SqlConnection)o;
return !conn.State.Equals(ConnectionState.Closed);
}
catch (SqlException)
{
return false;
}
}

protected override void Expire(object o)
{
try
{
SqlConnection conn = (SqlConnection)o;
conn.Close();
}
catch (SqlException) { }
}

public SqlConnection BorrowDBConnection()
{
try
{
return (SqlConnection)base.GetObjectFromPool();
}
catch (Exception e)
{
throw e;
}
}

public void ReturnDBConnection(SqlConnection conn)
{
base.ReturnObjectToPool(conn);
}
}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;

namespace ObjectPoolSample
{
class Program
{
static void Main(string[] args)
{
//Initialize the Pool
DBConnectionSingletion pool = DBConnection

Singletion.Instance;

//Set the ConnectionString of the Database

ConnectionPool
ConnectionStringSettingssettings = ConfigurationManager.ConnectionStrings

["NorthwindConnectionString"];
DBConnectionSingletion.ConnectionString = settings.ConnectionString;
//Borrow the SqlConnection object from the pool
SqlConnection conn = pool.BorrowDBConnection();

//Return the Connection to the pool after

using it
pool.ReturnDBConnection(conn);

Console.ReadLine();
}
}
}