2

Here is a short code snippet to execute a query using ADO.Net

using(SqlConnection objSqlConnection  = new SqlConnection(sConnectionString))
    {
    objSqlConnection.Open();

    using (SqlCommand objSqlCommand = new SqlCommand(sSQLQuery, objSqlConnection))
        {
        objSqlCommand.ExecuteNonQuery();
        }
    }

Where 'sConnectionString' is a standard SQL Server connection string and 'sSQLQuery' is a basic INSERT query

While executing the above code in a loop (once in a second), I found something very strange.

Handle count of the application is kept on increasing per execution.

Am I missing something?

Any thoughts will be greatly appreciated.

There are other similar questions here. One of the answers was to change the target framework to 3.5. But in my case, it is not possible - my target is 4.0.

Harish B
  • 21
  • 2
  • Maybe https://social.msdn.microsoft.com/Forums/vstudio/en-US/d013b5c5-7e17-4272-b7e6-7da803134aaa/sqlconnection-handle-leak?forum=netfxbcl or https://stackoverflow.com/questions/28961137/c-sharp-handles-count applies to your problem? – Freggar Jun 07 '18 at 06:39
  • Thanks, @Freggar and @ Aphelion for pointing out. I think I'm in the same boat. Let's make some changes in my code and let you know the result – Harish B Jun 07 '18 at 07:15

1 Answers1

0

With the help from @Freggar and @ Aphelion I have made some changes in the connection string Following is a sample code that I have used to form the connection string,

SqlConnectionStringBuilder objSqlConnectionStringBuilder = new SqlConnectionStringBuilder();
objSqlConnectionStringBuilder.DataSource                 = sMyDatabaseServer;
objSqlConnectionStringBuilder.IntegratedSecurity         = true;
objSqlConnectionStringBuilder.InitialCatalog             = sMyDatabaseName;
objSqlConnectionStringBuilder.ConnectTimeout             = 30;
objSqlConnectionStringBuilder.Pooling                    = false;


using(SqlConnection objSqlConnection  = new SqlConnection(objSqlConnectionStringBuilder.ConnectionString))
    {
    objSqlConnection.Open();

    using (SqlCommand objSqlCommand = new SqlCommand(sSQLQuery, objSqlConnection))
        {
        objSqlCommand.ExecuteNonQuery();
        }
    }

With this change significantly reduces the handle leak but not completely

Hope this helps someone going through the same situation

Harish B
  • 21
  • 2
  • Don't blindly turn off pooling, especially if you're opening and closing a connection for each query. You could alternatively limit the pool size instead (ie: set `MaxPoolSize = 8;`). The connection pool allows your application to reuse previously closed connections, which improves performance at the cost of keeping those resources. See https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling – C.Evenhuis Jun 07 '18 at 07:38
  • Thanks, @C.Evenhuis I will try that option too – Harish B Jun 07 '18 at 07:44
  • @C.Evenhuis, I have modified the code as you suggested but didn't work as I expected – Harish B Jun 07 '18 at 08:14
  • I don't know your expectations - I just commented to prevent people from turning off pooling "because SQL leaks resources", without knowing that these resources are held for a good reason. – C.Evenhuis Jun 07 '18 at 08:21