ASP.NET SQLClient connection pooling

[JustALittleGateway]

Hi,

We've got a connetion pooling problem with a few ASP.NET apps.

We comply to all demands described for enabling pooling (ie same
connectsting, open when needen, close when done etc..)

It seems that the app does not pool the connections, when a button is
clicked that opens a connection retrieves data and close the
connection 1 connection is created in SQL server. When the button is
clicked 10 times, 10 connections are created and so on. By setting the
connection life time to 10 seconds connections are closed in a
respectable time so we don't get any time out errors.

Question: what could cause ADO.NET to open 10 separate connections in
stead of pooling 1 connection and reusing it?

Hope anyone can provide some support.

Some background info on what we use.

Windows 2003 server standard
SQL Server 2000 SP3
Framework 1.1
..NET 2003
SqlClient for connecting

 

[David Browne]

Hi,

We've got a connetion pooling problem with a few ASP.NET apps.

We comply to all demands described for enabling pooling (ie same
connectsting, open when needen, close when done etc..)

It seems that the app does not pool the connections, when a button is
clicked that opens a connection retrieves data and close the
connection 1 connection is created in SQL server. When the button is
clicked 10 times, 10 connections are created and so on. By setting the
connection life time to 10 seconds connections are closed in a
respectable time so we don't get any time out errors.

Question: what could cause ADO.NET to open 10 separate connections in
stead of pooling 1 connection and reusing it?

Generally this is caused by not closing your connections. If you don't
close them, then they aren't returned to the connection pool until after
Garbage Collection runs.

David

 

[William \(Bill\) Vaughn]

I agree. See my article on connection pooling.
http://www.betav.com/sql_server_magazine.htm
The problem is, a connection cannot be reused until your program tells the
connection pooling mechanism that you're done with the connection--you do a
Db.Close. "Active" connections are held in the pool until you close them. If
you lose scope to the connection object, you lose the opportunity to close
the connection and it becomes hopelessly orphaned in the pool--until your
application ends or the GC kicks in (which might take hours).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________