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.

If you have any questions or comments, please post them below. If you liked this post, you can share it with your followers or follow me on Twitter!