I have a situation in which I need to cache lookup data from a database tables that will be used in the BizTalk Mapper.  The part that makes it interesting is that it is not just key value pairs that I need to cache.  I need to return a value for a 4 part key.  The key needs to be the context (domain), the effective start and end date and the source key. 


The .NET framework does not contain an object that will allow you to have a key containing more than one part. 


So, how do we make this happen?  The first thing that we need to do is to create a class that represents the values that will be the key.  The second is to implement the IEqualityComparer<T> interface.


So, lets create the class that we will use as the key in the dictionary.


public class LookupCacheKey
{
    private string _sourceContext;
    private string _sourceKey;
    private DateTime _effectiveDate;
    private DateTime _effectiveStartDate;
    private DateTime _effectiveEndDate;
   
    public LookupCacheKey()
    {
    }

    public LookupCacheKey (string sourceContext, string sourceKey, DateTime effectiveDate)
    {
        this._sourceContext = sourceContext;
        this._sourceKey = sourceKey;
        this._effectiveDate = effectiveDate;
    }
}


In addition to this code, I also have a method (directly after the LookupCacheKey method) that sets the values in this class.  The setValues method takes all of the values that we received from the database query to create the collection.  The database contains values for the lookup data’s valid use dates through a start and end date field.  The value that comes from our application will have a date that we need to ensure falls between the start and end date or we can’t use the value (look at the Equals method below).  In our implementation, if we have a date and doesn’t have a valid return value then we throw an exception.  So, the code for our setValues method looks like:


     public setValues string sourceContext, string sourceKey, DateTime effectiveStartDate, DateTime effectiveEndDate)
     {
        this._sourceContext = sourceContext;
        this._sourceKey = sourceKey;
        this._effectiveStartDate = effectiveStartDate;
        this._effectiveEndDate = effectiveEndDate;
     }


Now what I have is a class that can be instantiated but I still need to add the IEqualityComparer interface.


The IEqualityComparer has two methods that you need to implement.  They are the Equals and GetHashCode methods. 


The Equals method provides the functionality necessary to compare the two object instances (comparing the values within the instance).  The method returns true if the classes are equal.


The GetHashCode method provides a hash code for object.  This is used within the dictionary to divide the dictionary items in the buckets – subgroups to provide the speed that we expect from the dictionary object.  For more information on creating good hash keys and more information on the performance and implementation options check out this on MSDN.


To implement the IEqualityComparer interface for our LookupCacheKey class we will add an additional class called EqualityComparer.  This implementation the EqualityComparer class will placed inside the LookupCacheKey class so that we have access to the private variables in the LookupCacheKey class from within the EqualityComparer class.  This class will implement the Equals method to compare each of the 4 variables to let the dictionary object know if there is a match.


public class LookupCacheKey
{
    private string _sourceContext;
    private string _sourceKey;
    private DateTime _effectiveStartDate;
    private DateTime _effectiveEndDate;
   
    public LookupCacheKey()
    {
    }

    public LookupCacheKey (……)
    {
        ……..
    }

    public class EqualityComparer: IEqualityComparer<LookupCacheKey>
    {
         public bool Equals(LookupCacheKey lc1, LookupCacheKey lc2)
         {
            return lc1._sourceContext == lc2._sourceContext &&
                   lc1._sourceKey == lc2._sourceKey &&
                   lc1._effectiveDate >= lc2._effectiveStartDate &&
                   lc1._effectiveDate <= lc2._effectiveEndDate;
         }
        
         public int GetHashCode(LookupCacheKey lc)
         {
            return 0;
         }
    }
}


We now have a complete custom key class for the dictionary.  The dictionary does not care what the key contains, only that it can compare the key values.  Lets create a dictionary object that uses the class we just created.


public class BizTalkCacheHelper
{
    private static Dictionary<LookupCacheKey, string> LookUpCacheDictionary =
                   new Dictionary<LookupCacheKey, string>(new LookupCacheKey.EqualityComparer());

    …..

}


One thing that will be evident very quickly is that if you don’t create an instance of the EqualityComparer then the dictionary will use its default method of comparing objects – which will not match even when the keys are the same.


Now we have two more steps to use the Dictionary.  We need to populate the Dictionary and then we need to call the the class to get our value.


To populate the Dictionary object we used the SQL Dependency functionality.  The BizTalkCacheHelper class also included a function to populate the Dictionary through a DataReader (along with the setValues method).  After the initial data load, the function then sets up the SQL Dependency subscription.  Take a look at the Query Notifications page on MSDN for samples and requirements.  So, once we loaded the Dictionary we then needed to create the SQLDependency object and set the OnChange event so that we could listen to the event which would tell us when a change occurred in the database.  In the OnChange event handler, we then repopulated the Dictionary object.  One thing to note about the SQL Dependency functionality is that you create a subscription based on a Select query and you get one notification per change.  What that means is that you need to setup your subscription again after each notification (which you can see at the bottom of the PopulateDictionary method).


The code within the BizTalkCacheHelper function looks like:


private void PopulateDictionary()
{
    SqlConnection conn = null;
    SqlCommand comm = null;
    SqlCommand commDependency = null;

    conn = new SqlConnection(connString);
    conn.Open();

    comm = new SqlCommand();

    ……
    SqlDataReader dataReader = comm.ExecuteReader();

    while (dataReader.Read())
   
{
        LookupCacheKey key = new LookupCacheKey();

        key.setValues(……);

        string value = dataReader[“TargetValue”].ToString();

        LookUpCacheDictionary.Add(key, value);
   
}

    dataReader.Close();

    //Now we set up the SQL Dependency Functionality
    commDependency = new SqlCommand();
    commDependency.Connection = conn;
    commDependency.CommandText = “…………”;
    commDependency.Notification = null;
    SqlDependency dependency = new SqlDependency(commDependency);
    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
    commDependency.ExecuteNonQuery();
}


and the OnChange event handler receives the event and calls the PopulateDictionary method to reload the cache as well as to setup the next subscriptions.  It looks like this.


private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    if (e.Info != SqlNotificationInfo.Invalid)
   
{
        PopulateDictionary();
   
}
}


Now we have a cache component that is populated with data and is setup to receive and handle notification whenever data in the database changes.  Lastly, we just need to call into the component and get back our value for the key parameters we pass it.  To do this we will add a method like the following:


public string GetValue(string sourceContextName, string domainName, string sourceKey, string effectiveDate)
{
    string lookupValue = string.Empty;
    try
    {
        LookupCacheKey lookupKey = new LookupCacheKey(sourceContextName, domainName, sourceKey, DateTime.Parse(effectiveDate));

        if (LookUpCacheDictionary.ContainsKey(lookupKey))
        {
            string lookupValue = LookUpCacheDictionary[lookupKey].TargetValue.ToString();
        }
    }

    ………
    if(lookupValue == string.Empty)
    {
        //throw exception
    }

    return lookupValue;
}


Now we can call directly from the Mapper (or any front end) into the GetValue function and we will finally have the value that is needed from the cached component.