Chandru  velan Chandru velan - 4 months ago 14
C# Question

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

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;

Answer

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.