CODEWITHSUNDEEP
c#sqlconnection
Jaska
Jaska

Reputation: 1430

Disposing SqlConnection when exception is thrown

I'm wondering the following code pattern:

    static SqlConnection getcon()
    {
        SqlConnection con = new SqlConnection("data source=foobar..");
        con.Open();

        // is con Disposed automatically or will it leak and live 
        // forever when exception is thrown?

        throw new Exception("exception");
        return con;
    }

    static void Main(string[] args)
    {
        try 
        {
            using (var scope = new TransactionScope())
            using (var con = getcon())
            using (var cmd = new SqlCommand("UPDATE SomeTable SET Column1 = 'test'", con))
            {                   
                cmd.ExecuteNonQuery();

                scope.Complete();
            }
        }
        catch     
        {
        }
    }

Is this a safe way to use SqlConnection (getting a connection from the getcon() method)? Will it be disposed after the function exits when an exception is thrown or will it live forever?

The purpose why I want this GetCon() method is to shorten code and wrap the connection creation and opening in one line (using (var con = getcon())..)

Upvotes: 0

Views: 561

Answers (3)

IllusiveBrian
IllusiveBrian

Reputation: 3234

I think jaadooviewer's answer is correct, but it seems like you could avoid the question entirely by using a try/catch block in the getcon method.

try
{
    SQLConnection con = new SQLConnection("...");
    con.Open();
    if (/*condition*/)
        throw new Exception("Exception Condition Satisfied");
}
catch (Exception ex)
{
    con.Dispose();
    throw ex;
}
return con;

Upvotes: 1

Sriram Sakthivel
Sriram Sakthivel

Reputation: 73502

is con Disposed automatically or will it leak and live orever when exception is thrown?

throw new Exception("exception");
return con;//your code

Actually return con; line is unreachable. In other words it will never execute here. you're not returning con method actually exits by throwing an Exception. so your connection will not be cleaned here.

When the method exits(by exception) the local variable is out of Scope and you have no managed references to the same, so obviously your con is subject to Garbage Collection.

will it leak and live orever when exception is thrown?

Answer is no, Garbage collector will take care of reclaiming the memory used by Connection and your connection will be closed when Dispose(true) is called typically from Finalizer.

Edit

Assume your get con method doesn't throw any exception and returns a Connection and Exception is thrown as below

using (var scope = new TransactionScope())
using (var con = getcon())
using (var cmd = new SqlCommand("UPDATE SomeTable SET Column1 = 'test'", con))
{
    throw new Exception("Oops");//Throw excception somewhere here             
    cmd.ExecuteNonQuery();
    scope.Complete();
}

Above code will guarantee cleanup when an Exception is thrown since you wrapped con in using statement.

Hope this helps

Upvotes: 2

jaadooviewer
jaadooviewer

Reputation: 368

The way you have written you getcon method (I assume you did it specifically to test something), con will be freed the moment exception is thrown. Since `return con;' is after exception you are throwing, it will never be returned to the calling code and will be freed as soon as getcon exits (for going out of scope).

Upvotes: 2

Related Questions