Cleaning Up Resources via Using Statement & Dispose for DataTable, SqlConnection, SqlCommand, & SqlDataAdapter

Question

I have to call a stored procedure and get the results. I know there are various ways of doing this (as with all programming), and that I should be cleaning up the resources by calling Dispose() and/or Close(). Reading this close or dispose question I'm thinking that I should be using the using statement and that should be enough. Below is how I'm making my call. My question is - am I over complicating this and will this clean up all of the resources?

private Int32 CallStoredProcedure(Int32 Id)
{
    using (var dt = new DataTable())
    {
        using (var conn = new SqlConnection(ConnectionString))
        {
            using (var sqlCmd = new SqlCommand("SEL_StoredProcedure", conn))
            {
                using (var sda = new SqlDataAdapter(sqlCmd))
                {
                    sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCmd.Parameters.AddWithValue("@ID", Id);
                    sqlCmd.Connection.Open();

                    sda.Fill(dt);
                }
            }
        }

        if (dt.Rows.Count == 1)
            return Convert.ToInt32(dt.Rows[0]["IDv2"]);
        else if (dt.Rows.Count > 1)
            throw new Exception("Multiple records were found with supplied ID; ID = " + studentId.ToString());
    }
    return 0;
}

P.S. - I know I could call ExecuteScalar, but that's not what I'm looking for here since I will be using the general format for non-scalar calls.

score 0 · Answer 1 · answered Jun 22 '12 at 14:59

0

AFAIK you do not need to wrap the DataTable or the SqlDataAdapter in using blocks, as they do not implement IDisposable.

You can "chain" the using statements toegether like this:

using(var conn = new SqlConnection(connectionString))
using(var cmd = new SqlCommand("SEL_storedProcedure", conn))
{

}

answered Jun 22 '12 at 14:59

Nibor

1,096
8
11

2

Yes they do. They inherit `MarshalByValueComponent` and `Component` respectively, which are disposable. – Chris Shain Jun 22 '12 at 15:01
the chaining is interesting and will remove many of the braces. Chris is right though, and they both have a `Dispose()` method in intellisense :-) – Mark Jun 22 '12 at 15:05
good to know, thanks. I've very rarely seen DataTable wrapped in using (and very rarely done it myself) or disposed of after use. – Nibor Jun 22 '12 at 15:37
1

@RobinHames if you're interested, there's a Code Analysis check to make sure `Dispose()` is called everywhere it is necessary. CA1063. http://msdn.microsoft.com/en-us/library/ms244737.aspx – Mark Jun 22 '12 at 16:36

score 0 · Accepted Answer · answered Jun 22 '12 at 15:15

The code that you wrote does appear to dispose all objects properly.

What you should be aware of is that disposing a DataTable makes that object unusable, which is not usually the intent with a DataTable. Typically if you are populating a DataTable you intend to keep the data around (cached) for a while, and not discard it within the query method.

Cleaning Up Resources via Using Statement & Dispose for DataTable, SqlConnection, SqlCommand, & SqlDataAdapter

2 Answers2

Linked