Caching the Stored Procedure Parameters Discovered Using SqlCommandBuilder.DeriveParameters
by David Hayden ( Florida .NET Developer )
Filed: ADO.NET Tutorials and Examples
A couple days ago I talked about a DatabaseHelper Library of mine that mimics the functionality in the Enterprise Library 2.0 Data Access Application Block. To implement the following methods in the DatabaseHelper Library that are in the DAAB:
int ExecuteNonQuery(string storedProcedureName,
params object[] parameterValues);
IDataReader ExecuteReader(string storedProcedureName,
params object[] parameterValues);
object ExecuteScalar(string storedProcedureName,
params object[] parameterValues);
DataSet ExecuteDataSet(string storedProcedureName,
params object[] parameterValues);
I took advantage of the SqlCommandBuilder.DeriveParameters method that discovers the parameters on stored procedures. You can read the following article for more information: SqlCommandBuilder.DeriveParameters - Get Parameter Information for a Stored Procedure - ADO.NET Tutorials
Earlier this morning I took a few minutes to add a parameter cache to help with performance when using the parameter discovery functionality. I didn't want to do it, because it creates additional complexity as well as opens up thread safety issues on a library that wasn't meant for performance critical applications, but I figured what the heck :)
ParameterCache Class
I outsourced the caching of parameters to a separate caching service - ParameterCache Class. It is nothing but a thread-safe wrapper around a Dictionary Class. Enterprise Library 2.0 DAAB uses a Hashtable, but that was done before generics, etc. I won't bore you with the whole class, but the partial draft cut looks like this:
public class ParameterCache : IParameterCache
{
private Dictionary<string, DbParameter[]> dictionary =
new Dictionary<string, DbParameter[]>();
private object syncLock = new object();
public bool ContainsParameters(string connectionString,
string storedProcedureName)
{
return dictionary.ContainsKey(
GetCacheKey(connectionString, storedProcedureName));
}
public DbParameter[]
GetParameters(string connectionString,
string storedProcedure)
{
return CopyParameterArray(
dictionary[GetCacheKey(connectionString,
storedProcedure)]);
}
public void AddParameters(string connectionString,
string storedProcedureName,
DbParameterCollection parameterCollection)
{
if (!ContainsParameters(connectionString,
storedProcedureName))
{
lock(syncLock)
{
if (!ContainsParameters(connectionString,
storedProcedureName))
{
string cacheKey = GetCacheKey(connectionString,
storedProcedureName);
DbParameter[] parameters =
GetParameterArray(parameterCollection);
dictionary.Add(cacheKey, parameters);
}
}
}
}
}
All I am doing is delegating most of the work to the Dictionary Class. I added a little double-check locking on adding parameters for thread safety, but I have to make sure this is indeed thread safe by doing some testing. Thread safety really becomes an issue when enumerating a Dictionary Class, but we never do that.
Using the Parameter Cache
After a bit of refactoring, the code that I mentioned in the previous article has been cut down to this:
public IDataReader ExecuteReader(string storedProcedureName,
params object[] parameters)
{
SqlCommand command =
GetStoredProcCommand(storedProcedureName)
as SqlCommand;
DiscoverParameters(command);
SetParameterValues(command, parameters);
return ExecuteReader(command);
}
I need to add some argument checking, etc., but that is the gist.
The DiscoverParameters Method now checks the cache to see if the parameters are there before making a roundtrip to the database using SqlCommandBuilder.DeriveParameters.
public void DiscoverParameters(DbCommand command)
{
if (_cache.ContainsParameters(_connectionString,
command.CommandText))
{
DbParameter[] parameters = _cache.GetParameters
(_connectionString, command.CommandText);
command.Parameters.AddRange(parameters);
}
else
{
using (SqlConnection connection =
CreateConnection() as SqlConnection)
{
command.Connection = connection;
connection.Open();
SqlCommandBuilder.DeriveParameters(command
as SqlCommand);
}
_cache.AddParameters(_connectionString,
command.CommandText, command.Parameters);
}
}
Conclusion
Not bad for about an hour this morning. Unit and integration tests are passing, but I need to create ones that assure thread safety :)
by David Hayden ( Florida .NET Developer )
Filed: ADO.NET Tutorials and Examples