Data Access Layer - static list objects and caching

Question

Hello,

i am devloping a site using .net MVC

i have a data access layer which basically consists of static list objects that are created from data within my database.

The method that rebuilds this data first clears all the list objects. Once they are empty it then add the data. Here is an example of one of the lists im using. its a method which generates all the UK postcodes. there are about 50 methods similar to this in my application that return all sorts of information, such as towns, regions, members, emails etc.

public static List<PostCode> AllPostCodes = new List<PostCode>();

1. when the rebuild method is called it first clears the list.

   ListPostCodes.AllPostCodes.Clear();

2. next it re-bulilds the data, by calling the GetAllPostCodes() method

   /// <summary>
   /// static method that returns all the UK postcodes
   /// </summary>
   public static void GetAllPostCodes()
   {
       using (fab_dataContextDataContext db = new fab_dataContextDataContext())
       {
           IQueryable AllPostcodeData = from data in db.PostCodeTables select data;
   
   
       IDbCommand cmd = db.GetCommand(AllPostcodeData);
       SqlDataAdapter adapter = new SqlDataAdapter();
       adapter.SelectCommand = (SqlCommand)cmd;
       DataSet dataSet = new DataSet();
   
   
       cmd.Connection.Open();
       adapter.FillSchema(dataSet, SchemaType.Source);
       adapter.Fill(dataSet);
       cmd.Connection.Close();
   
   
       // crete the objects
       foreach (DataRow row in dataSet.Tables[0].Rows)
       {
           PostCode postcode = new PostCode();
           postcode.ID = Convert.ToInt32(row["PostcodeID"]);
           postcode.Outcode = row["OutCode"].ToString();
           postcode.Latitude = Convert.ToDouble(row["Latitude"]);
           postcode.Longitude = Convert.ToDouble(row["Longitude"]);
           postcode.TownID = Convert.ToInt32(row["TownID"]);
   
   
           AllPostCodes.Add(postcode);
           postcode = null;
       }
   }
   
   
   }
   

The rebuild occurs every 1 hour. this ensures that every 1 hour the site will have fresh set of cached data.

the issue ive got is that occasionally if during a rebuild, the server will be hit by a request and an exception is thrown. The exception is "Index was outside the bounds of the array." it is due to when a list is being cleared.

ListPostCodes.AllPostCodes.Clear(); - // throws exception - although its not always in regard to this list.

Once this exception is thrown application dies, All users are affected. I have to restart the server to fix it.

i have 2 questions...

1. If i utilise caching instead of static objects would this help ?
2. Is there any way i can say "while the rebuild is taking place, wait for it to complete until accepting requests"

any help is most appricaiated ;)

truegilly

Answer 1

You have to make sure that your list is not modified by one thread while other threads are trying to use it. This would be a problem even if you used the ASP.NET cache since collections are just not thread-safe. One way you can do this is by using a SynchronizedCollection instead of a List. Then make sure to use code like the following when you access the collection:

lock (synchronizedCollection.SyncRoot) {
    synchronizedCollection.Clear();
    etc...
}

You will also have to use locking when you read the collection. If you are enumerating over it, you should probably make a copy before doing so as you don't want to lock for a long time. For example:

List<whatever> tempCollection;
lock (synchrnonizedCollection.SyncRoot) {
    tempCollection = new List<whatever>(synchronizedCollection);
}
//use temp collection to access cached data

The other option would be to create a ThreadSafeList class that uses locking internally to make the list object itself thread-safe.

Answer 2

1 If i utilise caching instead of static objects would this help ?

Yes, all the things you do are easier done by the caching functionality that is build into ASP.NET

Is there any way i can say "while the rebuild is taking place, wait for it to complete until accepting requests"

The common pattern goes like this:

1. You request data from the Data layer

2. If the Datlayer sees that there is data in the cache, then it serves the data from cache If no data is in the cache the data is requested from the db and put into cache. After that it is served to the client

There are rules (CacheDependency and Timeout) when the cache is to be cleared.

The easiest solution would be you stick to this pattern: This way the first request would hit the database and other requests get served from the cache. You trigger the refresh by implementing an SQLCacheDependency

Answer 3

I agree with Tom, you will have to do synchronization to make this work. One thing that would improve the performance is not clearing the list until you actually receive the new values from the database:

// Modify your function to return a new list instead of filling the existing one.
public static List<PostCode> GetAllPostCodes()
{
    List<PostCode> temp = new List<PostCode>();
    ...
    return temp;
}

And when you rebuild the data:

List<PostCode> temp = GetAllPostCodes();
AllPostCodes = temp;

This makes sure that your cached list is still valid while GetAllPostCodes() is executing. It also has the advantage that you can use a read-only list which makes the synchronization a bit easier.

Answer 4

In your case you need to refresh the data every one hour.

1) IT should use cache with absolute expiration set to 1 hour, so it expires after every 1 hour. Check the Cache before using it, by doing a NULL check.If its NULL get the data from DB and populate the Cache.

2) With above approach the disadvantage is that data can be stale by 1 hour. So if u want most updated data at all times, use SQLCacheDependency (PUSH). so whenever there is a change in the select command u r using, cache will be refreshed from the database with updated data.