Home > DeveloperSection > Forums > SqlConnection SqlCommand SqlDataReader IDisposable
Ida B Wells
Ida B Wells

Total Post:30

Points:210
Posted on    June-19-2013 3:29 AM

 ADO.Net ADO.Net 
Ratings:


 1 Reply(s)
 1260  View(s)
Rate this:
Hi,

SqlConnection, SqlCommand and SqlDataReader all implement the IDisposable interface. I read about a best practice to always wrap IDisposables into a using block.

So, my common scenario for querying data would look something like this (in greater context of course a mapping tool like linq2sql would be suitable, but lets just assume we want to use this approach here):

using (SqlConnection cn = new SqlConnection("myConnectionstring"))
{
    using (SqlCommand cm = new SqlCommand("myQuery", cn))
    {
        // maybe add sql parameters
        using (SqlDataReader reader = cm.ExecuteReader())
        {
             // read values from reader object
             return myReadValues;
        }
    }
}


Is this the correct way or could that be considered overkill? I am a little unsure about this level of nesting using blocks, but of course i want to do it the correct way. Thanks!


Dev Patel

Total Post:26

Points:182
Posted on    June-19-2013 8:30 AM

Hi Ida,

Your way is correct. If a class leverages IDisposable it should be wrapped in a using statement to ensure that the Dispose() method is called. Further, communicating with an outside technology -unmanaged at that -like SQL Server shouldn't be taken lightly. The SqlCommand object implements IDisposable for a very good reason. The code below is the Dispose() method for the SqlCommand object:

protected override void Dispose(bool disposing)
{
    if (disposing)
    {
        this._cachedMetaData = null;
    }
    base.Dispose(disposing);
}

and as you can see, it's releasing a reference to the _cachedMetaData object so that it too can get cleaned up.


Don't want to miss updates? Please click the below button!

Follow MindStick