You know those tables in your systems that are almost static (never change) by are consulted thousands of times a day? Well, the other day at work some asked me if we could avoid all this trips to the database.

The solution I came up with creates a cache at our BLL (Business Logic Layer) avoiding DAL layer to be called. First of all our base architecture is based on the principles on the asp.net tutorial Creating a Business Logic Layer. This article basically creates something very similar to a proxy pattern before the DAL which gives me the perfect place to intercept any calls to the DAL and consult my cache.

To avoid the trip to the database, when the BLL is instantiated I do one query to the database which returns all the records in the table. The resulting DataTable is stored in my cache class which is declared as static in the class. This will make possible that the cache is populated only once. All subsequent queries will fetch the results from the Cache instead of making a trip to the database.

The cache table is very simple. It stores a DataTable and performs filters in it using LINQ syntax. I had other options but since I was starting to work with LINQ at the time it seemed like a good choice. For this code I use the Dynamic LINQ which I mentioned in my previous post.

In order to work with TypedDataSets (which was a requirement for me) I create the Cache class to accept the DataTable and DataRow types.

    public class CacheDataTable<T, S>  where T : DataTable where S : DataRow
    {
        private T cacheDataTable;

        //stores the DataTable that will be used for future consults
        public CacheDataTable(T dataTable)
        {
            this.cacheDataTable = dataTable;
        }

        //returns a DataTable filtered by the expression
        public T GetData(string expression, params object[] values)
        {
            IEnumerable<S> tmp = ((IEnumerable<S>)cacheDataTable).AsQueryable().Where(expression, values);
            return getDataTablePopulated(tmp);
        }

        //returns all the records
        public T GetData()
        {
            return getDataTablePopulated((IEnumerable<S>)cacheDataTable);
        }

        //takes an enumeration and creates a new DataTable
        private T getDataTablePopulated(IEnumerable<S> rows)
        {
            T dt = Activator.CreateInstance<T>();
            foreach (S row in rows)
            {
                dt.ImportRow(row);
            }
            return dt;
        }
    }

After having the Cache class defined you can use it in the BLL like this:

public class CountryBLL
{
        CountryDAL dal;

        //the cache is static so that it is shared among all instances from the CountryBLL class
        private static CacheDataTable<DataSet1.CountryDataTable, DataSet1.CountryRow> cache;

        public TBG_CountryBLL()
        {
            dal = new CountryDAL();
            //tests if the cache class has not been created yet. Only happens at the first access
            if (cache == null)
            {
                cache = new CacheDataTable<DataSet1.CountryDataTable, DataSet1.CountryRow>(dal.GetData());
            }
        }

        public DataSet1.CountryDataTable GetData()
        {
            //gets all records from the cache
            return cache.GetData();
        }

        public DataSet1.CountryDataTable GetDataByPK(decimal id)
        {
            return cache.GetData("id == @0", id);
        }
}

Coded like this the CountryBLL will only cause a database connection at its first instantiation. This approach should only be used with tables which are static (or almost). If one record is inserted in this table it would demand the application to be restarted so that the static cache variable would be unloaded and created again. There are ways around this but it is not the objective of this post.

Hope this will help someone :-)

LINQ is great; I’m having all kinds of use to it lately. The other day I needed to do something different, I needed to write a LINQ query dynamically, constructing the LINQ query as a String and then executing it (which is pretty common thing when you’re using SQL).

I was already convinced that I would need to spend a couple of weeks writing such functionality. Turns out that Microsoft has already written a LINQ Dynamic Query Library. It doesn’t come with the framework but you can download it and include in your project. Take a look at ScottGu’s Blog to see his post about it.

Here is an example:

Suppose I have a LocalDBDataSet with a mapping to the Table Person (Name, Sex, Phone).

If I wanted to get all the people in the table and perform a LINQ query to filter only the Males (M) I could do it like this:

PersonTableAdapter ta = new PersonTableAdapter();
DataTable dt = ta.GetData();
IEnumerable<LocalDBDataSet.PersonRow> drCollection = 
    ((IEnumerable<LocalDBDataSet.PersonRow>)dt).AsQueryable().Where("SEX == @0", "M");

I know this isn’t the most exciting example ever and I would need dynamic LINQ to do it but I just wanted to show you what you could accomplish. You get the idea, right?

With this kind of resource you could accomplish a lot of cool stuff. In the next post I’ll show how you can write a Cache class that can be queried with LINQ to avoid unnecessary trips to the database.

You can download the LINQ Dynamic Query Library here or here