Release SqlConnection without "finally"

Question

I got a task to maintain a c# project, I found some code like this:

try{ 

    conn.Open(); 
    ... 
    conn.Close(); 
}catch(Exception e){ 

    conn.Close(); 
}

I know that the usual way to close SqlConnection is try-catch-finally or "using" keyword, but I found that I can't prove the code above is wrong, I can't find any situation that the Connection will be leak.

So could you give me some technical advises(not programming style) for it?Thanks

Answer 1

You can't find any situation? What about when that ... code contains a return statement? In a loop, continue and break can also fit the bill.

Your particular code apparently doesn't contain any control-flow statements, but that's beside the point. The point is that when you use the common resource-acquisition idioms, such as try-finally and using, you no longer need to worry about what the rest of the code is. The ... can be one line or a hundred lines; whatever resource you acquired at the top will get released at the bottom if you've done it right. Rather than spend time trying to think of other ways that might be right (and having to prove it), use the ways you know are right because they were designed to be right.

Besides, even if you can prove that the code never leaks a connection, it's still a lousy idea for conn.Close() to be the command that has to solve every possible exception your program could ever throw. Don't catch exceptions that you don't know how to resolve.

Answer 2

If

conn.Close();

throws an Exception.

Answer 3

    try
    {
        connection.Open();
        // other codes

    }
    catch (Exception Ex)
    {

        // Create a (useful) error message
        string ErrorMessage = "A error occurred while trying to connect to the server.";
        ErrorMessage += Environment.NewLine;
        ErrorMessage += Environment.NewLine;
        ErrorMessage += Ex.Message;

        // Show error message (this = the parent Form object)
        lblMessage.Text = "Error: " + ErrorMessage;

    }
    finally
    {
        // close connection
        if (connection != null)
        {
            connection.Close();
        }
    }

Answer 4

The connection is IDisposable, so wrap it with using statement:

using(conn) {
  ...
}

It should provide the best insurance for you. It should even close the connection if you will return from using block (as correctly mentioned, is often a hidden issue).

Answer 5

The above code is equivalent to

try{ 

    conn.Open(); 
    ... 
}catch(Exception e){     
}finally{
    conn.Close(); 
}

So there should be no leak. Personally I prefer the finally way.

Answer 6

I'll approach this question a little differently. I prefer using or try/finally over the above because it avoids the redundancy. In keeping with the DRY principle I would rather not have to remember to call Close() twice. Avoiding this makes the code easier to write and maintain. Apart from that the other answers have to good points to consider as well.

Answer 7

Kill the connection between the conn.Open() and the ExecuteNonQuery() call and I think it should throw an Exception and thus leak the connection object.

Answer 8

try { if(conn.State==ConnectionState.Closed) conn.Open(); .......... conn.Close(); } Catch{}

Answer 9

Perfect code is here...

            try
            {
                if (Conn.State == ConnectionState.Closed)
                    Conn.Open();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + " : dbOperation.ExecuteNonQuery()");
            }
            finally
            {
                 if (Conn.State == ConnectionState.Open)
                    Conn.Closed();
            }

Thank you