Cache Stored Procedure SqlParameter Objects

Applies to

• ADO.NET 1.1

What to Do

Often, applications must run SQL commands multiple times, cache the Stored Procedure SqlParameter so that they can be reused later.

Why

Caching the SqlParameter object avoids recreating the them each time the Stored Procedure needs to be called. Thus improving the performance of the application.

When

This guideline should be followed whenever you have code repetitively calling stored procedure.

How

A good approach is to cache parameter arrays in a Hashtable object. Each parameter array contains the parameters that are required by a particular stored procedure that is used by a particular connection. The following code fragment shows this approach.

public static void CacheParameterSet(string connectionString,
                                    string commandText,
                                    params SqlParameter[] commandParameters)
{
 if( connectionString == null || connectionString.Length == 0 )
   throw new ArgumentNullException( "connectionString" );
 if( commandText == null || commandText.Length == 0 )
   throw new ArgumentNullException( "commandText" );

 string hashKey = connectionString + ":" + commandText;
 paramCache[hashKey] = commandParameters;
}

The following function shows the equivalent parameter retrieval function

public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
{
 if( connectionString == null || connectionString.Length == 0 )
   throw new ArgumentNullException( "connectionString" );
 if( commandText == null || commandText.Length == 0 )
   throw new ArgumentNullException( "commandText" );

 string hashKey = connectionString + ":" + commandText;

 SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
 if (cachedParameters == null)
 {
   return null;
 }
 else
 {
   return CloneParameters(cachedParameters);
 }
}

When parameters are retrieved from the cache, a cloned copy is created so that the client application can change parameter values, without affecting the cached parameters. The CloneParameters method is shown in the following code fragment.

private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
{
 SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

 for (int i = 0, j = originalParameters.Length; i < j; i++)
 {
   clonedParameters[i] =
     (SqlParameter)((ICloneable)originalParameters[i]).Clone();
 }
 return clonedParameters;
}

Right out of GuidanceExplorer.