ADO.NET Data Access Template

Here’s a basic ADO.NET data access template with a SqlCacheDependency. Just wanted to post this for my future reference.

public static DataSet GetData(long itemID)
{
    DataSet ds = new DataSet();
    string cacheKey = Helpers.GetCacheKey(itemID); //Get a cache key unique to this method.

    if (HttpContext.Current.Cache[cacheKey] != null)
    {
        ds = (DataSet)HttpContext.Current.Cache[cacheKey];
    }
    else
    {
        using (SqlConnection sqlConnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        {
            using (SqlCommand sqlCommand = new SqlCommand())
            {
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "sp_Items_GetItemByID";
                sqlCommand.Parameters.Add("@itemID", SqlDbType.BigInt).Value = itemID;
                sqlConnection.Open();

                using (SqlDataAdapter da = new SqlDataAdapter(sqlCommand))
                {
                    da.Fill(ds);
                }
            }
        }

        SqlCacheDependency sqlCacheDependency = new System.Web.Caching.SqlCacheDependency(WebConfigurationManager.AppSettings["DatabaseName"], "Items");
        HttpContext.Current.Cache.Insert(cacheKey, ds, sqlCacheDependency, System.Web.Caching.Cache.NoAbsoluteExpiration, System.Web.Caching.Cache.NoSlidingExpiration);
    }

    return ds;
}

Note that I’ve employed most of the recommended best practices like caching, “using” keywords, stored procedures and connection string inside web.config.