In-Depth

Enlist ADO.NET Connections in Pooled Objects Manually

You can boost performance in server-side apps by using COM+ pooled objects to cache resources. Be sure to enlist the connection manually each time the object pool picks up the object.

• By Enrico Sabbadin
• 05/01/2004

Technology Toolbox: C#, SQL Server 2000

Using COM+ pooled objects to cache and reuse valuable resources is one of the most effective ways to boost performance in server-side .NET and COM-based applications. Database connections are among the most precious server-side resources, so they've always been the number-one candidate for pooling. The OLE DB infrastructure offers database connection pooling features, but many performance tests have shown that caching database connections wrapped inside COM+ pooled objects manually outperforms OLE DB-based connection pooling.

Developing COM+ pooled objects was out of reach for VB6 developers, but implementing .NET COM+ pooled objects (using the Enterprise Services infrastructure) has become a snap. However, you must be aware of a big gotcha when caching connections in a pooled object when you're using Distributed Transaction Coordinator (DTC) based transactions. If a serviced component (one using Enterprise Services) runs inside a COM+ transactional context, database connections are enlisted automatically when they're opened or picked up from the database connection pool. This means that cached connections created and opened in the class constructor won't magically enlist into the active transactional context each time a call enters a pooled object's method.

You need to enlist the connection into the active transactional context manually each time the object is picked up from the object pool. You can hook into this event overriding the ServicedComponent Activate method. You extract the active transactional context and attach it to the database connection using the EnlistDistributedTransaction method that all connection classes (no matter what managed provider you're using) expose:

protected override void Activate() {
	// m_con is a DB connection that has been
	// opened in the class's constructor phase 
	m_con.EnlistDistributedTransaction 
		((ITransaction)ContextUtil.Transaction);
		base.Activate();
}

This is all you need to do, but it's good coding practice to set the Enlist=false parameter in the database connection string:

[Transaction(TransactionOption.Required), 
	ObjectPooling(true,3,30),
public class myclass : ServicedComponent {
	private SqlConnection m_con ;
	public myclass(){
		m_con = new  SqlConnection( 
			"Password=whatever;User ID=sa;Initial
			Catalog=pubs;Enlist=false")
		// the DB connection is opened when the object 
		// is created 
		m_con.Open ();
}

Un-enlist the connection from the transaction in the Deactivate event—that is, when the object is going to be put back in the pool:

protected override void Deactivate() {
	m_con.EnlistDistributedTransaction (null);
	base.Deactivate ();
}

Note, however, that these last two actions are not required at present.

One last thing: Each time the object is deactivated, the COM+ runtime calls the CanBePooled method of the ServicedComponent class to check if the object must be set back in the pool or destroyed. You must override this method in your class because the implementation of the base class always returns false, making your whole pooling strategy ineffective:

protected override bool CanBePooled() {
	// this object can always be pooled
	return true;
}

The connection pooling mechanism of the SqlClient managed provider in the .NET Framework has been rewritten internally using COM+ pooling features. You might not see significant improvements when caching SqlConnections, but if you're still using the OLE DB provider, you'd better roll your own connection caching mechanism using pooled objects if you want to squeeze every last drop of performance.