3

I have a ASP.net application in which I do a query on a SQL Server Database. When the query ends, I do the following :

reader.Close();
reader.Dispose();
conn.Close();
conn.Dispose();

And still when I go on SQL Server and does :

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NoOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

I can see the 'NoOfConnections' that keeps increasing while I have closed the connection and there is a moment where I'll get an error because I have over 100 connections opened. How do I close the connections properly?

Tim
  • 7,401
  • 13
  • 61
  • 102
Stan
  • 391
  • 2
  • 5
  • 21
  • 4
    Use the `using`-statement to dispose unmanaged resources. If you dispose/close a connection it is not closing the physical connection. That's up to the [connection-pool](https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx). Closing a connection when connection-pooling is enabled(default) just means that this connection is free to use now. – Tim Schmelter Jun 09 '15 at 10:01

2 Answers2

5

SQL Server connection pooling will cache connections. You can clear all connection pools with this:

conn.ClearAllPools();

For more info, see here.

To clear the connection pool for a specific connection, you can use the ClearPool method. For example:

conn.ClearPool();

For more info, see here.

You can configure the size of the connection pool through the connection string, for example:

Provider=SQLOLEDB;Data Source=ServerName;Integrated Security=SSPI;Min Pool Size=5; Max Pool Size=20;

You can turn off pooling in the connection string also, for example:

Provider=SQLOLEDB;Data Source=ServerName;Integrated Security=SSPI;Pooling=false;

Connection Pooling is on by default, the default size of the connection pool is 100. If the client connection string is the same, the pool will try and use a connection from the connection pool - if it is available. For more see here.

Donal
  • 31,121
  • 10
  • 63
  • 72
  • I am using IDbConnection so i can't use these methods, can it works if i cast my IDbConnection to SqlConnection and then call the method "ClearPool" ? – Stan Jun 09 '15 at 12:44
  • If you are using SQL Server, you should probably use SqlConnection throughout. It is not safe to cast from IDbConnection to Sql Connection - see here http://stackoverflow.com/questions/3647492/c-sharp-dbconnection-cast-to-sqlconnection – Donal Jun 09 '15 at 13:24
2

Instead of doing this

reader.Close();
reader.Dispose();
conn.Close();
conn.Dispose();

Why not use using statements? Example, for Connection:

using (var conn = new SqlConnection("YourConnectionString"))
{
    //your code here
}

This will close and dispose of the SqlConnection object for you.