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!

tmptplayer · Accepted Answer · 2019-12-01 03:38:22Z

3

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();
        }
}

edited Dec 1, 2019 at 3:38

answered Dec 1, 2019 at 1:58

tmptplayer

5091 gold badge7 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

tmptplayer Over a year ago

The code above, when run, will result in a closed connection when the block is finished. An explicit connection.Close() function call is not required, because the connection is closed in the disposal process of the SQLConnection Variable.

tmptplayer Over a year ago

Right - because exiting a function doesn't dispose the object, which is a trigger to close the connection. You could also just finish the function with the line "connection.Close()"

RayofCommand Over a year ago

when i placed it right after "return sqlCommand.ExecuteScalar().ToString();" the issue persisted. and VS said that the code is "unreachable" i guess.

RayofCommand Over a year ago

you might edit your answer instead of the comment, and i will try it out and accept your answer, in case it works

tmptplayer Over a year ago

Also, if you added the C# tag, the code formatting may be nicer

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related