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.

Chris Shain · Accepted Answer · 2012-06-22 15:15:47Z

0

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.

answered Jun 22, 2012 at 15:15

Chris Shain

51.5k7 gold badges95 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nibor · Accepted Answer · 2012-06-22 14:59:38Z

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, 2012 at 14:59

Nibor

1,1368 silver badges11 bronze badges

4 Comments

Chris Shain Over a year ago

Yes they do. They inherit MarshalByValueComponent and Component respectively, which are disposable.

Mark Over a year ago

the chaining is interesting and will remove many of the braces. Chris is right though, and they both have a Dispose() method in intellisense :-)

Nibor Over a year ago

good to know, thanks. I've very rarely seen DataTable wrapped in using (and very rarely done it myself) or disposed of after use.

Mark Over a year ago

@RobinHames if you're interested, there's a Code Analysis check to make sure Dispose() is called everywhere it is necessary. CA1063. msdn.microsoft.com/en-us/library/ms244737.aspx

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related