CODEWITHSUNDEEP
c#sql.netexception
SmashCode
SmashCode

Reputation: 4257

Closing a SQLConnection in catch statement of try/catch block

sc is a SQL connection passed in from the calling function I'm using one SQL connection throughout my entire program and having each subfunction open and close it as needed. (I read this was fine, because the SQLConnection class has it's own connection pooling functionality)

        string q = "this would be a query";
        using (SqlCommand cmd = new SqlCommand(q, sc))
        {
            cmd.Parameters.Add("@ID", SqlDbType.Int);
            cmd.Parameters["@ID"].Value = (an id that is passed in);
            try
            {
                sc.Open();
                using (var reader = cmd.ExecuteReader())
                {
                   if(reader.read())
                   { logic that might throw exception }
                   else
                   { logic that might throw exception }
                }
                sc.Close();
            }
            catch (Exception e)
            {
                alert user, program does not halt but current function returns
            }
        }

So the connection opens and the command attempts to execute and some logic is performed that might throw an exception. (I'm not expecting it to) This function is part of a larger program that is processing a large dataset. If it fails DUE TO AN EXCEPTION it needs to note it, but it also needs to attempt to keep running.

Other parts of the program are sharing this connection and opening and closing it as they need it and I've used this basic template for all queries in the program.

It just occurred to me that if an exception occurred, the connection might not be closed. Am I correct in thinking that? My original concern when writing this is that the sc.Open() would be the thing that would throw an exception, but as I've used this in multiple places I've realized that this was short sighted because the connection could open and then any number of steps between the close could throw an exception.

Do I need additional logic here to make sure this connection is closed and the program can attempt to continue running?

If my understanding is correct that the connection needs to be closed in the event of an exception between the open and close I think I have 2 options :

  1. try/catch between the open and close
  2. sc.Close() in the catch block (Bonus concern : If the sc.Open() throws an exception and catch block tries to sc.Close() will an additional exception be thrown?)

Upvotes: 1

Views: 2397

Answers (4)

Kemal Duran
Kemal Duran

Reputation: 1532

Its better that you close the connection in finally block

finally
{
   sc.Close(); 
}

After try block is completed, the finally block will work. In a case of an exception, finally block also works. So the connection will closed in any case.

Upvotes: 0

S. Rojak
S. Rojak

Reputation: 454

You have the State property of the SqlConnection that you can use to determine what state the last network operation left the connection in. This property is described here: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.state(v=vs.110).aspx

I would recommend reacting to exceptions in the SqlConnection (such as Open) and in the SqlCommand separately, because, in my experience, you will usually want to take different actions. Even with the overall approach you want to take, if you can't open your connection, there is no point in preparing the query, and you likely want to say something different about inability to open the connection than you would if the query failed. I would move the sc.Open() before the creation of the SqlCommand and try/catch around that.

Upvotes: 1

rory.ap
rory.ap

Reputation: 35338

You do not need to worry about closing the connection. The connection is closed automatically when it disposed by the using statement within which the connection is created.

In the event of an exception, the connection will still be disposed (and thus closed) because that's how a using statement is designed to work.

Upvotes: 3

C. Knight
C. Knight

Reputation: 749

Clarification on the above answer by @roryap - there is no need for sc.Close() if your connection is created in a using statement but from your question I guess it's not. In which case you should probably add a finally section to the try catch block and close the connection in there.

Upvotes: 0

Related Questions