Difference between initiating sql object inside "using" statement and decorating with using statement

Question

I've doubt what is the significant difference by applying the "Using" statement in same code block with different way , it would be good to know practice the best way for me sameple 1 code block

using (SqlConnection SqlConnection = new SqlConnection(dbConnectionString))
                 {
                     SqlConnection.Open();
                     using (var command = new SqlCommand(store_procName, SqlConnection))
                     {
                         command.Parameters.Add(Constants.PARAM_Value, SqlDbType.VarChar).Value = Id;
                         command.CommandType = CommandType.StoredProcedure;
                         using (var adp = new SqlDataAdapter(command))
                         {
                             adp.Fill(dtValid);
                         }
                     }
                 }
                 return dtValid;

sample code block 2

using (SqlConnection SqlConnection = new SqlConnection(dbConnectionString))
                 {
                     SqlConnection.Open();
                     SqlCommand command = new SqlCommand(store_procName, SqlConnection);                     
                     command.Parameters.Add(Constants.PARAM_Value, SqlDbType.VarChar).Value = Id;
                     command.CommandType = CommandType.StoredProcedure;
                     SqlDataAdapter adp = new SqlDataAdapter(command);
                     adp.Fill(dtValid);                       

                 }
                 return dtValid;

This is not `asp.net-mv`c specific or `sql-serve`r specific question. Just c#. Also, please spend an extra 10 seconds before posting to format the code for better readability. :) — Shyju, Aug 08 '16 at 14:33
Actually i'm working in MVC framework thought some one may come across such scenario — Chandru velan, Aug 08 '16 at 14:34
take a look http://stackoverflow.com/questions/3715126/what-is-meant-by-connection-dispose-in-c — Oğuzhan Soykan, Aug 08 '16 at 14:37
@soykan i'm aware of the using statement and its benefits i'm following the first sample code block 1 , suddenly when i saw the sample code block 2 i couldnt differentiate the different how the system will process and which is best one to follow and practice. — Chandru velan, Aug 08 '16 at 14:42
@Ehsan sajjad i'm aware of the using statement i was trying to ask the difference between two code blocks alone — Chandru velan, Aug 08 '16 at 14:49

score 1 · Accepted Answer · answered Aug 08 '16 at 14:42

The using statement is syntactical sugar to release resources (eg memory or handles) without having to write the code for that yourself. So a code snippet like

using (var adp = new SqlDataAdapter(command))
{
    adp.Fill(dtValid);
}

is converted into something like:

SqlAdapter adp = null;
try
{
    adp = new SqlDataAdapter(command);
    adp.Fill(dtValid);
}
finally
{
    if (adp != null) adp.Dispose();
    // or rather (adp as IDisposable)?.Dispose();
}

(this is just an example to give you the idea, not necessarily the exact code generated by the compiler).

So if you ommit the inner using statements in your code, the Dispose() methods of the instances will not be called at this point. Eventually the garbage collection will clean up those objects (which normally leads to calls to Dispose()).

The difference is relevant if you have a lot of calls to this method and read a lot of data so that the SqlCommand and the SqlDataAdapter will consume a lot of resources. If you want to release these resources as soon as possible, you should include the code in using statements.

You are asking for the best practice (which is often a matter of taste). In most cases the first snippet (with all the using statements) is preferable, because it releases all resources that are no longer needed immediatly.

Difference between initiating sql object inside "using" statement and decorating with using statement

1 Answers1