CODEWITHSUNDEEP
c#ado.net
user184750
user184750

Reputation:

Release SqlConnection without "finally"

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

Upvotes: 2

Views: 784

Answers (9)

user285008
user285008

Reputation: 73

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

Upvotes: -2

AjmeraInfo
AjmeraInfo

Reputation: 505

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

Upvotes: 1

Michael Stum
Michael Stum

Reputation: 181134

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.

Upvotes: 1

Brian Rasmussen
Brian Rasmussen

Reputation: 116501

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.

Upvotes: 2

Lex Li
Lex Li

Reputation: 63295

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.

Upvotes: 0

Rob Kennedy
Rob Kennedy

Reputation: 163357

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.

Upvotes: 8

Dmytrii Nagirniak
Dmytrii Nagirniak

Reputation: 24118

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).

Upvotes: 4

abramlimpin
abramlimpin

Reputation: 5087

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

Upvotes: 0

micahtan
micahtan

Reputation: 19210

If

conn.Close();

throws an Exception.

Upvotes: 3

Related Questions