Will ASP.Net remove individual tables from a cached dataset to free up memory?

Question

I have a strange, sporadic issue.

I have stored procedure that returns back 5 small tables (e.g. IDs and Text Descriptions for Status drop down lists and such). The code calls this and places the returning dataset into ASP.Net cache. Separate methods are called to retrieve individual tables from the dataset to databind to controls throughout my web app.

Only in my QA server does one table disappear. The table will be there for one testing scenario; however, the next time the same scenario is ran, the one table is null. The table that goes MIA is always the same (Table #4 of 5 to be precise).

If the ASP.Net WP needs memory, can it remove an individual table from a cached dataset while keeping the data set's indexes in place?

Below is the caching code:

public static DataSet GetDropDownLists()
{
    DataSet ds = (DataSet)HttpContext.Current.Cache["DropDownListData"];

    if (null == ds)
    {
        // - Database Connection Information Here
        ds = db.ExecuteDataSet(CommandType.StoredProcedure, "Sel_DropDownListData");

        HttpContext.Current.Cache.Add("DropDownListData", ds, null, DateTime.Now.AddMinutes(20), TimeSpan.Zero, CacheItemPriority.Normal, null);
    }

    return ds;
}

Here's an example of the method that returns the null table:

public static DataTable GetStatusList()
{
    return GetDropDownLists().Tables[3]; 
}

Again, this only happens on my QA Server and not when my local code is wired up to the QA database or anything on a separate server for my own development testing.

Thanks

Answer 1

The ASP.NET Cache will only work directly on the objects you insert in it's IEnumerable list.

If you add a List<> of objects as one cache item, it will either keep the entire list or it will discard the entire list from cache. It will never go arround the list and remove single items from it. The same should be valid for tables in a dataset.

Something else must be messing up your dataset. Are you sure the table #4 is properly loaded on your QA server in the first place (ie. it's not empty to begin with ? )

Answer 2

The fact that the DataSet is cached is immaterial.

A table can't be removed from a DataSet other than explicitly.

You'll have to debug to find out what's going on, logically one of:

• the table's been explicitly removed from the DataSet

• the DataSet was removed from the cache and regenerated without the table

Answer 3

hi could u please tell me how do you store multiple tables in sqldependency i m facing some problem to do that. thanks in advance