Using statement - is this more useful for the sql connection than the sql adapter?

Question

I know that the following statement is useful for garbage collection. Effectively it must be the same as Try/Catch/finally. If I use it for the sql connection should i also nest a further 'using' statement for a sql adapter? And then a further nested using for the DataSet?

using()
{
}

A further addition to the question is if I want to use the same connection across several methods then is it best practice to have 'Using()' in each of the methods, or can I just create this connection object once?

score 10 · Answer 1 · answered Mar 14 '12 at 09:13

Something else that is worth pointing out is that you can combine multiple using statements into one block like this:

using (SqlConnection connection = new SqlConnection("connectionString"))
using (SqlCommand command = new SqlCommand("SELECT * FROM Users", connection))
{
    // Do something here...
}

Once you exit this block (either by reaching the end of the block or an exception was thrown), both the SqlConnection and SqlCommand object would be disposed automatically.

Thorsten Dittmar · Answer 2 · 2012-03-14T09:15:03.050

using is not the same as try/catch/finally!! using makes sure that Dispose is automatically called on the IDisposable object in brackets, so while

using (SqlConnection conn = new SqlConnection(...))
{
}

is the same as

SqlConnection conn = new SqlConnection(...);
try
{
}
finally
{
    conn.Dispose();
}

there's no catch block involved at all an no errors are caught!

If you want to dispose of all the disposable objects you create timely, then yes, you should use a using block for each of them.

It may be easier to read sometimes to create everything outside the try block and then dispose of everything in one finally block (an example for this would be drawing in Windows Forms applications. Sometimes you need many brushes and pens. You could create them before the try, use them within the block and dispose of them in the finally block).

It's basically a matter of coding style.

score 4 · Accepted Answer · answered Mar 14 '12 at 08:59

4

Yes, you should use the using statement to dispose everything that implements IDisposable. The using statement is not really about garbage collection, it's about resources (other than memory). Both DataSet and SqlDataAdapter are instances of IDisposable.

The using block means that you guarantee that the resources that the object holds are disposed in a timely, deterministic manner. Without using, there is no guarantee when these resources will be freed. This can lead to resource leaks, such as running out of file handles.

answered Mar 14 '12 at 08:59

Jeff Foster

43,770
11
86
103

Thanks Jeff - I'll investigate IDisposable further. If I'm making the connection and filling the dataset in method X and then want to utilise the same connection/dataset later on in method Y then 'using' won't be the correct approach? – whytheq Mar 14 '12 at 11:43
@whytheq, that sounds right to me. The important thing is to understand the lifetimes of the objects. If you are sharing the connection, just be clear when the connection closes. – Jeff Foster Mar 14 '12 at 11:59

CSharpenter · Answer 4 · 2012-03-14T09:08:34.263

The using statment is meant to be used for every instance of a class that implements the IDisposable interface. Its relationship with the GC (garbage collector) is that your instances release their resources before the GC does that. This is very usefull for shared resources, and helps with concurrency.

As for your question, yes, you should use the using statement on Connection, and Adapter.

As for the try and catch block, there is no similarity with the using statement. The two of them are used for different purposes. One (using) is used for releasing resources early, the other is used for handling errors whether they are unexcpected or expected

Using statement - is this more useful for the sql connection than the sql adapter?

4 Answers4