Having a c# code which doesn't close SQL Connection correctly (probably)

Question

I am facing an issue where a connection to our SQL Server is not closed properly. I can watch the open connections (sleeping) using sp_who2 or sp_who. I do some WEB Calls against the server and can see the number of connections increasing. At a specific number of connections I do run into a Timeout. The Website I call only has the following code (obfuscated) which opens the connection to the SQL Server:

    private string getsomedetails(string Name, string applicationConnectionString)
{
  string empty = string.Empty;
  SqlCommand sqlCommand = new SqlCommand();
  sqlCommand.Connection = new SqlConnection(applicationConnectionString);
  sqlCommand.CommandText = "SELECT xx FROM dbo.xxx WHERE Name = @xyz";
  sqlCommand.Parameters.AddWithValue("@xyz", (object) Name);
  sqlCommand.Connection.Open();
  return sqlCommand.ExecuteScalar().ToString();
    }

I understood that a connection is getting closed when it's in a block as above. But what happens for exceptions?

My connection string looks like:

      <connectionStrings>
<add name="AssetConnectionString"
     providerName="System.Data.SqlClient"
     connectionString= "server=SERVERNAME; 
                      database=DBNAME; 
                      uid=USERNAME; 
                      pwd=PASSWORD;
                      application name=APPNAME"
   />

My workaround right now it to recycle the app pool in IIS. After that the API will respond as usual.

Some Details: Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) IIS Version 6.2

My guess is that the code has a leak, any help is upvoted!

Answer 1

You need a "using" block to achieve the automatic connection closure. Something like this:

using (SqlConnection connection = new SqlConnection(myConnString))
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(fullSQLString, connection);
                    foreach (SqlParameter p in sqlParameters)
                        cmd.Parameters.Add(p);
                    cmd.ExecuteNonQuery();
                }

The connection doesn't close just because you left function scope. The key is that losing function scope doesn't automatically dispose of an object. The "using" command does, however, automatically dispose of the object when the blocked scope is complete. From Microsoft documentation:

The using statement defines a scope at the end of which an object will be disposed.

When the code block is executed and complete, the disposal process of the SQLConnection Object "connection" will result in a closed connection with the DB.

Try this format:

private string getsomedetails(string Name, string applicationConnectionString)
{
     string empty = string.Empty;
     string SQLStr = "SELECT xx FROM dbo.xxx WHERE Name = @xyz";


     using(SqlConnection connection = new SqlConnection(applicationConnectionString))
        {
          connection.Open();
          SqlCommand sqlCommand = new SqlCommand(SQLStr, connection);
          sqlCommand.Parameters.AddWithValue("@xyz", (object) Name);
          return sqlCommand.ExecuteScalar().ToString();
        }
}