Enable. Net to use the database connection pool

* *************** Connection pool code ********************** *****

 

/**
* Connection pool implementation
*
*/

Using system;
Using system. collections;
Using system. Timers;

Namespace xulongclass
{
/// <Summary>
/// Abstract description of objectpool.
/// </Summary>
Public abstract class objectpool
{
Private long _ lastcheckout;
Private Static hashtable locked; // The object in use
Private Static hashtable unlocked; // idle object

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;
Timer atimer = new timer ();
Atimer. Enabled = true;
Atimer. interval = garbage_interval;
Atimer. elapsed + = new elapsedeventhandler (collectgarbage );
}

// The following three image extraction methods must inherit classes to implement

/// <Summary>
/// Generate an object
/// </Summary>
/// <Returns> </returns>
Protected abstract object create ();
/// <Summary>
/// Verification object
/// </Summary>
/// <Param name = "O"> </param>
/// <Returns> </returns>
Protected abstract bool validate (Object O );
/// <Summary>
/// Destroy the object
/// </Summary>
/// <Param name = "O"> </param>
Protected abstract void expire (Object O );

// Obtain the object
Internal object getobjectfrompool ()
{
Long Now = datetime. Now. ticks;
_ Lastcheckout = now;
Object o = NULL;

Lock (this)
{
Try
{
// Traverse idle tables
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>
/// Return the object to the pool.
/// </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>
/// Destroy useless objects
/// </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 ){}
}
}
}
}

********** *****************

/**
* Implement the database object pool by inheriting the object pool
*
*/

Using system;
Using system. Data. sqlclient;

Namespace xulongclass
{
/// <Summary>
/// Summary of dbobjectpool.
/// </Summary>
Public class dbobjectpool: objectpool
{
Public dbobjectpool ()
{

}

Public static readonly dbobjectpool instance = new dbobjectpool ();

Public static string _ connectionstring = new database (). myconnectionstring;

/// <Summary>
/// Database connection string
/// </Summary>
Public static string connectionstring
{
Set
{
_ Connectionstring = value;
}
Get
{
Return _ connectionstring;
}
}

/// <Summary>
/// Create an object
/// </Summary>
/// <Returns> </returns>
Protected override object create ()
{
Sqlconnection temp = new sqlconnection (_ connectionstring );
Temp. open ();
Return (temp );
}

/// <Summary>
/// Verify the object's validity
/// </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>
/// Destroy the object
/// </Summary>
/// <Param name = "O"> </param>
Protected override void expire (Object O)
{
Try
{
(Sqlconnection) O). Close ();
}
Catch (sqlexception)
{}
}

/// <Summary>
/// Obtain the object
/// </Summary>
/// <Returns> </returns>
Public sqlconnection borrowdbconnection ()
{
Try
{
Return (sqlconnection) base. getobjectfrompool ());
}
Catch (exception ex)
{
Throw ex;
}
}

/// <Summary>
/// Return object
/// </Summary>
/// <Param name = "E"> </param>
Public void returndbconnection (sqlconnection E)
{
Base. returnobjecttopool (E );
}

}
}

* ********************************** Application database connection pool *** ************************

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 to the pool.
// Return...