Understanding Connection Pooling in .NET
page 2 of 5
by Joydip Kanjilal
Feedback
Average Rating: 
Views (Total / Last 10 Days): 54334/ 57

How does a Connection Pool work?

Connection pools are actually containers that contain open and reusable connections. Multiple pools can exist in the same application domain at the same point in time, but Connection Pools cannot be shared across application domains. Note that one pool is created per unique connection string. A Connection Pool is created the first time a request for a connection to the database comes in with a unique connection string. Note that if another request comes in with a different connection string for the database, another Connection Pool would be created. Hence, we have one Connection Pool per connection string and not per database. The following code listings below illustrate this.

Listing 1

// A new pool is created.
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = 
"Server=localhost;Database=test;User ID=joydip;Password=joydip;Trusted_Connection=False";
sqlConnection.Open();      

Listing 2

// A new pool is created as the connection strings differ.
SqlConnection conn = new SqlConnection();
sqlConnection.ConnectionString = 
"Server=localhost;Database=test;User ID=test;Password=test;Trusted_Connection=False";
sqlConnection.Open();   

Listing 3

// The connection string is the same as in Listing 1 so no new pool is created.
SqlConnection conn = new SqlConnection();
sqlConnection.ConnectionString = 
"Server=localhost;Database=test;User ID=joydip;Password=joydip;Trusted_Connection=False";
sqlConnection.Open();      

When a request for a new connection comes in the connection is served from the Connection Pool without creating a new one, i.e., the connections are re-used without creating new ones.  Therefore, it improves the performance and scalability of your applications. When your application closes an open connection, it is returned to the pool where it waits until a reconnect time out period expires. This is the period within which it waits to connect to the same database using the same credentials. If none comes in within this period, the connection to the database is closed and the connection instance is removed from the pool.

When a new Connection Pool is created, the connection objects are placed in the pool and the pool and the connections contained in it are made available for immediate use. The pool can house connections up to the maximum limit as specified in the connection string that was used to connect to the database. Connections are removed from the pool when they remain inactive for a long period of time or have outlived its specified life time or server connectivity.

A Connection Pool is maintained internally by the Connection Pool Manager. When a request for a subsequent connection comes in, the Connection Pool Manager searches the pool for the availability of a free connection and returns it to the application if one is available. The following points elaborate how the Connection Pool Manager works- the operations that it performs when a request for a new connection comes to it.

·         If any unused connection is available, it returns one.

·         If all connections are used up, a new connection is created and added to the pool.

·         If the number of connections reaches the maximum number of connections in the pool, the requests are queued until a connection becomes free for reuse.

Connection Pooling is controlled and the parameters passed to a connection string that basically comprises the following:

·         Connect Timeout

·         Min Pool Size

·         Max Pool Size

·         Pooling

In order to use Connection Pooling efficiently, remember to close the connections once you are done with it so that it returns to the pool.


View Entire Article

User Comments

Title: Author's Response   
Name: Joydip
Date: 2006-12-29 12:56:23 AM
Comment:
Hi Ritesh & Neha,

Thanks for your queries.

When the application closes the database connection by making a call to the Close method on the connection instance, the connection is returned to the pool. There it would wait for a specific amount of time, known as the reconnect time-out period, for a new open request from your application to the same database instance using the same security context used earlier to connect to this database. If no connection request comes in before the expiry of this "reconnect time-out period", the connection with the database instance is closed and the entry deleted from the connection pool.

Hope this helps.

-Joydip
Title: about reconnect time   
Name: Ritesh
Date: 2006-12-28 11:09:21 PM
Comment:
Hi Joydeep two similar queries are posted one by my self and other by Neha . I hope you could get some time to answer this
Title: Clarification Req.   
Name: Neha
Date: 2006-12-28 2:22:06 PM
Comment:
"it is returned to the pool where it waits until a reconnect time out period expires.." Here what do u mean by
'reconnect time out period' ? Can it be set from application?Is it in terms of minute or second? We offen see 'connection time out' message in application,'connection time out' and 'reconnect time out period' both are same?
Title: Good Work   
Name: Laloo
Date: 2006-12-28 2:12:55 PM
Comment:
Keep it up!
Title: Too bad   
Name: Haissam Abdul Malak
Date: 2006-12-27 6:09:13 PM
Comment:
Dont worry about the critizes, it will only make u trust yourself more coz i see jealousy in behind our two friends acts.. Keep up the good work and provide more information to serve this community
Title: Humm?   
Name: Nate
Date: 2006-12-27 12:17:38 PM
Comment:
Bob and Zen may be the same person.
This article was a decent intro for newbies which is his target audience. Most of us may already know about this but it is a source of published information for the community.
I do not understand why Bob is so hostile.
Title: about reconnect time   
Name: Ritesh
Date: 2006-12-27 6:29:41 AM
Comment:
You mention some thing about reconnect time where do we mention it. And also can you explain if my pool size is 20 and I am opening 15th connections so there is scope of 6 connections.
Out of 14 connections some them already released means at this moment they are free so will my 15th connection will create new instance and assigned to pool or will it used some unused connection?.;

I hope you are getting what I mean to say
Title: Trusted connection   
Name: Jeff
Date: 2006-12-27 2:21:00 AM
Comment:
What might be the reason connection pooling should be avoided on a trusted connection? Aren't we encouraged to use trusted connection whenever possible? Given the two, which should we strive for then?

Thanks,
Jeff
Title: Mr.   
Name: Maheswar
Date: 2006-12-26 11:58:45 PM
Comment:
Hi Joydip

i have a small doubt
using(SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
//Some Code
}

in this syntax if i get any Exception before executing the last braces It will close the connection or we need to take care for this?

Thanks
Maheswar
Title: Mr   
Name: Murry Gammash
Date: 2006-12-26 10:18:28 PM
Comment:
Thanks Joydip
Title: Give me a break, Bob and Zen   
Name: Francis
Date: 2006-12-26 8:03:21 PM
Comment:
Don't you guys have better things to do with your time than criticize articles? I concur with others - while this article may be 'beneath you', it certainly can benefit many other developers. If you have something constructive to share with the author, then please share it.

By the way, 'Bob Smith', your C# may or may not be impeccable, but your English grammar is lousy: Two of your three sentences are laden with very obvious incorrect grammar. My constructive criticism for you is to go back to high school for English lessons. Joydip, by the way, used impeccable English in his article.

And we must mention that "Intellectual Data Systems" comes up with only one hit on Google, which clearly has nothing to do with development. I highly doubt you are even a "Senior Architect" at all.

Good job Joydip, keep the articles coming.
Title: Good 1 for new comers   
Name: CKV
Date: 2006-12-26 3:38:22 PM
Comment:
Simple and clear.

To Bob and zen,
if you think this article is missing something, why don't you contact the author and advice him instead of making a nice comment.

If we (developers) make comment like that then no body will like to put an article and also will not like to serve community.

Peace.
Title: Give us a break.   
Name: Tom J
Date: 2006-12-26 11:29:57 AM
Comment:
To Bob and Zen
No need to make comment like that. Lots of people will benefit from a article even that it contains, based on your standard, little information.
Title: Author's Response   
Name: Joydip
Date: 2006-12-26 10:28:33 AM
Comment:
Hi Bob & Zen,

Thanks for the response. I do not feel that more coverage than what has been provided here on Connection Pooling is required for an application software developer who is new to .NET; I disagree that this is an incomplete article. There are plenty of code samples and discussions even more than what is necesary for a new comer to .NET

--Joydip
Title: Mr.   
Name: Zen
Date: 2006-12-26 9:10:10 AM
Comment:
@Bob Smith,

I agree with you. It is disappointing when people publish incomplete articles like this one.

Zen
Title: Author's Response   
Name: Joydip
Date: 2006-12-26 5:48:52 AM
Comment:
Hi Ali,

Thanks for your response.

When the end of the using block would be encountered, the Dispose() method will be immediately called on the instance. Note that when the Dispose() method is called on this connection instance, it checks to see if the connection is opened; if open it would close it implicitely prior to disposing off the instance.

--Joydip
Title: Closing connection   
Name: ali
Date: 2006-12-26 4:40:57 AM
Comment:
Hi,
Just want to ask from the below code
using(SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
//Some Code
}

How does the connection is closed? like in
finally
{
sqlConnection.Close();
}

Thanks
Title: Imp   
Name: Author's Response
Date: 2006-12-26 1:59:09 AM
Comment:
Hi Bob,

Please let me know what a Connection Pool is then. Please browse through this link:--

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp

Expecting your response.

-Joydip
Title: Mr.   
Name: Bob Smith
Date: 2006-12-25 12:39:22 AM
Comment:
Is this what do you call as the Connection Pool. Shame of you.


Be sure before publishing this kind of articles. It is very sad to see this kind of article in ASP Alliance.

Bob Smith,

Senior Architect,
Intellectual Data Systems.
Title: Pooling   
Name: Rudresh R Kaddipudi
Date: 2006-12-22 4:00:16 AM
Comment:
Hi Shawn de Wet,

http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx

All database connection requests raising at Host(where the connection requests are made) are trapped by the ADO.Net connection pool manager and served if one available in the pool. The pooling is just to avoid roundtrip information exchange to establish a database connection.
Title: Where does the pool live?   
Name: Shawn de Wet
Date: 2006-12-21 3:58:59 AM
Comment:
Where does the connection pool reside in memory...on the client or on the SQL Server? If I have a deployed application running on 20 client machines (all using the same connection string), does each client maintain its own connection pool, or does a single pool liver on the SQL Server for all clients to use?

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-04-19 5:54:21 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search