C# SQL Connection try/catch vs using vs try/catch w/ using

Question

I am the third generation to work on a system within my organization and of course there are differences in programming styles. I was wondering what the correct way of connecting to a database is as there are two different styles being used within the system. So whats the "correct" way?

Method 1:

try
{
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        con.Open();

        using (SqlCommand command = new SqlCommand("StoredProcedure", con))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("foo", bar));
        }

        using (SqlDataReader reader = command.ExecuteReader())
        {   
            //do stuff
        }
    }
}
catch (Exception ex)
{
    //do stuff
}

Method 2:

// Variables
SqlCommand loComm;
SqlConnection loConn = null;
SqlDataReader loDR;

try
{
    // Init command
    loComm = new SqlCommand("foo");
    loComm.CommandType = CommandType.StoredProcedure;
    loComm.Parameters.AddWithValue("foo", bar);

    // Init conn
    loConn = new SqlConnection(String);
    loConn.Open();
    loComm.Connection = loConn;

    // Run command
    loDR = loComm.ExecuteReader();

    //do stuff           
}
catch (Exception ex)
{
    //do stuff
}

While both methods work I am not sure which one is the most appropriate to use. Is there a reason to use one over the other? The second method is cleaner and easier to understand to me, but it doesn't automatically run the iDispose() function when it is finished.

Edit: My question is different then the one suggested, because one approach uses the "using" statement while the other doesn't. So my question is directly related to whether or not to utilize the "using" statements when making a database connection.

Thanks, for all the responses.

Answer 1

Method 2 is simply incorrect and should be repaired.

When completed, you will be left with IDisposable instances that have not been disposed. Most likely, this will play havoc with the connection management that goes on behind the scenes with SqlConnection, especially if this code gets thrashed a lot before GC decides to step in.

If an instance is IDisposable, then it needs to be Disposed of, preferably in the smallest scope possible. Using using will ensure that this happens, even if the code malfunctions and exceptions are thrown.

A using statement:

using(var disposableInstance = new SomeDisposableClass())
{
    //use your disposable instance
}

is (give or take a few minor details) syntactic sugar for:

var disposableInstance = new SomeDisposableClass();
try
{
    //use your disposable instance
}
finally
{
    //this will definitely run, even if there are exceptions in the try block
    disposableInstance.Dispose();
}

Even if something goes wrong in the try block, you can be assured that the finally block will execute, thereby ensuring that your disposables are disposed, no matter what happens.

Answer 2

In the first example, if there is an exception thrown, the objects wrapped in the using blocks will be properly closed and disposed.

In the second, you will manually need to dispose of your objects.

One other thing worth mentioning is that if you were planning on disposing of your objects only when an exception is thrown, you will have objects that are not disposed of, so you would need to implement a try..catch..finally and dispose the objects within the finally block.

The first is the better option.

Answer 3

The first one will close the connection if an exception is thrown. The second one won't, so I'd say go with the first choice.

Answer 4

What the using block does is to warranty that the object Dispose method will always be invoked, no matter if an exception is thrown or not.

Dispose is a method used to clean up resources. In the case of a DB connection, the connection is released, which is really important.

So, the correct way in this case is using the using pattern.

The object included between the using parents mus be IDisposable whic means that it has a Dispose method that should be invoked when the object is no longer needed. If you don't invoke dispose, it will be disposde at any indeterminate time, when the garbage collector destroys the object. That's undesirable in case like a DB connection that must be closed as soon as possible.

The equivalen of usin is a try finally, which includes a call to Dispose within the finally block.

Answer 5

Reading here, it says:

As a rule, when you use an IDisposable object, you should declare and instantiate it in a using statement. The using statement calls the Dispose method on the object in the correct way, and (when you use it as shown earlier) it also causes the object itself to go out of scope as soon as Dispose is called. Within the using block, the object is read-only and cannot be modified or reassigned.

The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block; in fact, this is how the using statement is translated by the compiler.

That sounds like the correct way is method 1.