使.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);

mandType = mandType.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...