Too many SQL server connection for .Net SqlClient Data Provider application

[davidrobin]

I have written an intranet site with about 8 pages. Each page creates a new class (contained in a class library), in the class a connection to SQL server is created.
The class also has a public sub as follows Public Overridable Sub CloseConnection()
If conConnect.State <> ConnectionState.Closed Then conConnect.Close()
conConnect.Dispose()
End Subwhich can be called from class instance on each page.

The problem is after browsing each page once there are a list of 11 connections (Enterprise Manager > server > Management > Current Activity > Process Info) for .Net SqlClient Data Provider (application column) and I have to kill them.

Is there some way of ensuring connections get closed. We have 800ish staff and that is a lot of connections.

[nemaroller]

Are you sure you have 11 Open Connections, or are you just seeing 11 connections that are sleeping?

You need to refresh the Activity window, its just a snapshot at any one moment in time.

[davidrobin]

They are there even when I refresh.

Even now there are 4 processes.

Login Time
11:48:19
11:48:20
11:48:08
15:17:08

Current time 15:28

This is strange because I have not run my web application since 11:48 on purpose so I don't know where the 15:17 login came from.

[nemaroller]

Are they Open transactions though? My Sql guy says that the .Net framework pools the connections.... and uses a connection from the pool, as long as they are not still listed as Open transactions, you should be ok.

[davidrobin]

5 minutes of browsing and there are now 40 processes/connections in the list they are all sleeping and none are open transactions. My concern is what affect this will have when a couple hundred of staff are browsing the webapp.

Over the last couple of days we have had SQL server connection timeouts when trying to connect (obviously :rolleyes:). A part of the error said about max pool connections being reached. I am trying to avoid conributing to this problem.