ASP.NET Cache always returns null

Question

I am using SQLCacheDependency in my ASP.NET application with Query Notifications. I followed this article to set up my database with success.However whenever I am trying to store data in the cache object.It just does not hold value.It is always null .I am not getting any errors or exceptions. Here is my code

Global.asax

void Application_Start(object sender, EventArgs e)
    {
        // Code that runs on application startup
        System.Data.SqlClient.SqlDependency.
        Start(ConfigurationManager.ConnectionStrings["McdConn"].ToString());

    }

void Application_End(object sender, EventArgs e)
    {
        //  Code that runs on application shutdown
        System.Data.SqlClient.SqlDependency.
        Stop(ConfigurationManager.ConnectionStrings["McdConn"].ToString());
    }

public static class CacheManagement
{
    public static DataTable CreateCache(string cacheName, string tableName, string query)
    {
        DataTable dtResult = new DataTable();
        try
        {
            string connectionString = ConfigurationManager.ConnectionStrings["McdConn"].ToString();
            dtResult = HttpContext.Current.Cache[cacheName] as DataTable;

            if (dtResult == null)
            {
                dtResult = new DataTable();
                using (var cn = new SqlConnection(connectionString))
                {
                    cn.Open();
                    var cmd = new SqlCommand(query, cn);
                    cmd.Notification = null;
                    cmd.NotificationAutoEnlist = true;
                    SqlCacheDependencyAdmin.EnableNotifications(connectionString);
                    if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionString).Contains(tableName))
                    {
                        SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString,tableName);
                    }

                    var dependency = new SqlCacheDependency(cmd);
                    //SqlDataAdapter ad = new SqlDataAdapter(cmd);
                    //ad.Fill(dsResult);
                    SqlDataReader reader = cmd.ExecuteReader();
                    dtResult.Load(reader);
                    HttpContext.Current.Cache.Insert(cacheName, dtResult, dependency);

                }
            }
        }
        catch (Exception ex)
        {
            Exception_Log.ExceptionMethod("Web", "CacheManagement.cs", "CacheManagement", ex);
        }

        return dtResult = HttpContext.Current.Cache[cacheName] as DataTable;
    }

}

Code Behind

var dtCachedCategories = HttpContext.Current.Cache["tbl_CategoryMaster_Cached"] as DataTable;
if (dtCachedCategories == null)
{
 dtCachedCategories = CacheManagement.CreateCache("tbl_CategoryMaster_Cached","dbo.tbl_CategoryMaster_Languages", "Select * from dbo.tbl_CategoryMaster_Languages");

}

The above always returns null.

Can anyone help me in pointing out what could be missing?

Answer 1

Well there's a lot you can do to debug your code and arrive at a conclusion. It seems like your cached item is getting removed too frequently.

1.) Use CacheItemPriority.NotRemovable to Cache.Insert() to make sure ASP.NET doesn't removes your item whenever it feels so. use the Insert() method explained here. Check this MSDN article too.

2.) To find out the reason why your cached item is getting removed , log this removal action using CacheItemRemovedCallback delegate option of your Cache.Insert() method. Check this Insert method overload version and also this link.

3.) Make sure your dtresult as well as your reader is not null. Check the lines:

SqlDataReader reader = cmd.ExecuteReader(); & dtResult.Load(reader); , together with your logs.

4.) Check your application Pool recycle time. This link has everything related to App pool settings ( IIS 7 +).

5.) This link has a solution for App pool of IIS 6: http://bytes.com/topic/net/answers/717129-c-asp-net-page-cache-getting-removed-too-soon

Also, try using HttpRuntime.Cache method to see if it works.

System.Web.HttpRuntime.Cache.Insert(cacheName, dtResult, dependency);