Leveraging C#'s "using" keyword for optimal data access
More and more specialized, niche market books have been published over the last 6 months, dealing with best practice programming for ASP.NET v.1.x. As such, many of the recommandations can get confusing, and at times, contradictory in laying out how to write good, clean, scalable, high-performance code. One of the major areas is data access.
One thing that people always ask me is how to properly leverage C#'s dual-purpose
using keyword when making data calls. Specifically, how to wrap code properly so that open or unused object references won't be left to the whim of the .NET garbage collector.
First, a bit of background:
using in C# can be used in two ways:
- as a shortcut to typing long namespaces used in code
- as a means of properly and automatically close and dispose any object references implementing the IDisposable interface
For the latter, here's an example, in which a private helper method returns a DataSet object. I typically write data access code wherein data is pulled from a SQL Server database through a stored procedure within a library like so:
using System.Data;
using System.Data.SqlClient;
private DataSet GetFreshData(string sprocName)
{
using ( SqlConnection conn = new SqlConnection() )
{
using ( SqlDataAdapter da = new SqlDataAdapter() )
{
da.SelectCommand = new SqlCommand();
da.SelectCommand.CommandText = sprocName;
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Connection = conn;
DataSet ds = new DataSet();
try
{
da.SelectCommand.Connection.Open();
da.Fill(ds);
da.SelectCommand.Connection.Close();
}
catch
{
return null;
}
finally
{
// do other things...calling Close() or Dispose()
// for SqlConnection or SqlDataAdapter objects not necessary
// as its taken care of in the nested "using" statements
}
return ds;
}
}
}