How enterprise library caches the SP Signatures

I like to share few facts about how enterprise library data access block works on caching the SP signatures.

1. Every class of type ‘Database’ is related to another class called ‘ParameterCache’(relationship is Composition), and this class is mainly responsible for caching the SP signature. ‘SQLDatabase’ is derived from ‘Database’. We don’t need any configuration stuff to enable this, it automatically does.

2. ‘ParameterCache’ internally uses synchronized HashTable for storing the SP Singature’s with ‘ConnectionString:SP Name’ as keys.

3. ‘Database’ class instantiates this ‘ParameterCache’ as static member and this means it scope is limited to AppDomain.

public abstract class Database : IInstrumentationEventProvider
{
static readonly ParameterCache parameterCache = new ParameterCache();

No matter how many SQLDatabase objects we create, only one ParameterCache will exist.

4. Before we execute any SPs, we need to call a function called ‘GetStoredProcCommand’, this function will retrieve the SP Singatures if it is available in Cache else from DB and stores it in Cache.

public virtual DbCommand GetStoredProcCommand(string storedProcedureName,
params object[] parameterValues)
{
parameterCache.SetParameters(command, this);

And this is how I figured it

1. I ran SQL Profiler, and the code that retrieves SP Signature (in this case DataAccessHelper.AssignParameters(), this function internally calls GetStoredProcCommand), I am able to see an entry for ‘[sys].[sp_procedure_params_managed]’. But when I hit it next I am not able to, and it means Cache.

2. To make sure Cache is getting populated, I checked the hash table count before and after execution. It was 0 and 1 respectively.