Ado. Net Connection pooling at a glance

Http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring (V = vs.80). aspx

Http://www.codeproject.com/Articles/17768/ADO-NET-Connection-Pooling-at-a-Glance

 

The following table lists the valid names for Connection Pooling values withinConnectionstring. For more information, see using Connection pooling.

 

Name	Default	Description
Connection lifetime	0	When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specifiedConnection lifetime. This is useful in clustered deployments to force load balancing between a running server and a server just brought online. A value of zero (0) causes pooled connections to have the maximum connection timeout.
Connection Reset	'True'	Determines whether the database connection is reset when being drawn from the pool. for SQL Server version 7.0, settingFalseAvoids making an additional server round trip when obtaining a connection, but you must realize that the connection state, such as database context, is not being reset. The connection pooler is not influenced by the changedatabase method as long you do not setConnection ResetToFalse. As the connection comes out of the pool the connection is reset with the server moving back to the login time database. There are no new connections created or reauthentications. If you setConnection ResetToFalse, Connections in the pool to different databases might result.
Enlist	'True'	WhenTrue, The pooler automatically enlists the connection in the creation thread's current transaction context. recognized values areTrue,False,Yes, AndNo.
Load Balance timeout	0	The minimum time, in seconds, for the connection to live in the connection pool before being destroyed.
Max pool size	100	The maximum number of connections allowed in the pool.
Min pool size	0	The minimum number of connections allowed in the pool.
Pooling	'True'	WhenTrue,SqlconnectionObject is drawn from the appropriate pool, or if it is required, is created and added to the appropriate pool. recognized values areTrue,False,Yes, AndNo.

When you are setting keyword or Connection Pooling values that require a Boolean value, you can use 'Yes' instead of 'true', and 'no' instead of 'false '. integer values are represented as strings.

 

Controlling connection pool through connection string

Connection string plays a vital role in Connection pooling. the handshake between ADO. net and database server happens on the basis of this connection string only. below is the table with important Connection Pooling specific keywords of the connection strings with their description.

Name	Default	Description
Connection lifetime	0	When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specifiedConnection lifetime. A value of zero (0) causes pooled connections to have the maximum connection timeout.
Connection timeout	15	Maximum time (in SECs) to wait for a free connection from the pool
Enlist	'True'	Whentrue, The pooler automatically enlists the connection in the creation thread's current transaction context. recognized values aretrue,false,yes, Andno. SetEnlist = "false"To ensure that connection is not context specific.
Max poolsize	100	The maximum number of connections allowed in the pool.
Min poolsize	0	The minimum number of connections allowed in the pool.
Pooling	'True'	Whentrue,SQLConnection Object is drawn from the appropriate pool, or if it is required, is created and added to the appropriate pool. recognized values aretrue,false,yes, Andno.
Incr poolsize	5	Controls the number of connections that are established when all the connections are used.
Decr poolsize	1	Controls the number of connections that are closed when an excessive amount of established connections are unused.

* Some table contents are extracted from Microsoft msdn library for reference.

Other than the above mentioned keywords, one important thing to note here.If you are using integrated security, then the connection pool is created for each user accessing the client system, whereas, when you use user ID and password in the connection string, single connection pool is maintained into SS for the application.In the later case, each user can use the connections of the pool created and then released to the pool by other users. Thus using user ID and password are recommended for better end user performance experience.

Sample connection string with pooling related keywords

The connection string with the pooling related keywords wowould look somewhat like this

Collapse | copy code

initial catalog=Northwind; Data Source=localhost; Connection Timeout=30; User Id=MYUSER; Password=PASSWORD; Min Pool Size=20; Max Pool Size=200; Incr Pool Size=10; Decr Pool Size=5;

Simple Ways to view connections in the pool created by ADO. net