How to close db connections used by GridView control in asp.net

Question

Hello friends,

I have a website with pages having GridView in it. I am using SQL Data Source control to bind with the GridView. After running the site i am checking the no of active connections with SQL Server using -

SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame

I found that even after exiting my web application the connections remains open. So is there any way to close these connections. This is very important for me because as the number of users increases the no. of active connections are also increasing causing the sql server max pool issue. Although i have increased the user connections on sql server but i need the solution to explicitly close those connections as soon as user logs off.

Thanks for sharing your valuable time.

Answer 1

Have you tried calling the datasource's .Dispose() after you have finished with it?

Personally, in situations where the number of connections to a SQL Server need to be managed, I would stay away from the SqlDataSource. I instead would perform all operations in the code behind, wrapped in a 'Using' block. Especially when dealing with opening and closing connections (it is very easy to forget to close 1 connection when multiple are open).

'Using' provides a shortcut to the Dispose pattern - Using

Answer 2

Your connections remain "open" because the use of connection pooling. Turn off connection pooling, if that would be the desired behaviour, or limit the number of connections which can be opened at a time. These can be set from the connection string.