SqlCommand Parameter Caching

Last week, I wrote a short article about the SqlCommandBuilder.DeriveParameters method that can be used to automatically populate the parameters of a stored procedure command. One of the downsides to this approach is that each call to the method will result in a call to the database. Since stored procedure parameters aren’t typically changing very often, this can result in a lot of unnecessary chatter. So what’s the solution? Cache the parameter collection, of course!

Here’s a very simple way to do just that. I have an abstract class that maintains a static dictionary of SqlParameterCollections, indexed by SQL command. When the static DeriveParameters method is called from within derived classes, the dictionary is checked. If a matching parameter collection is found, it will be used to create a new parameter collection using the existing collection as a blueprint. If no collection is found, SqlCommandBuilder.DeriveParameters is used, and the resulting collection is cached.

public abstract class BaseDataProvider
{
    private static readonly Dictionary<string, SqlParameterCollection> _sqlParameterCollections;

    static BaseDataProvider()
    {
        _sqlParameterCollections = new Dictionary<string, SqlParameterCollection>();
    }

    private static void DeriveParameters(SqlCommand cmd)
    {
        if (_sqlParameterCollections.ContainsKey(cmd.CommandText))
        {
            var paramCollection = _sqlParameterCollections[cmd.CommandText];
            cmd.Parameters.AddRange(
                paramCollection.OfType<SqlParameter>()
                    .Select(x => new SqlParameter
                    {
                        ParameterName = x.ParameterName,
                        SqlDbType = x.SqlDbType,
                        Size = x.Size,
                        Direction = x.Direction
                    })
                    .ToArray()
                );
            return;
        }

        SqlCommandBuilder.DeriveParameters(cmd);
        _sqlParameterCollections.Add(cmd.CommandText, cmd.Parameters);
    }
}

Note that caching stored procedure parameters can have a negative side-effect: if the parameters DO change, your cache may need to be refreshed. This could be handled in a number of ways. You could have the cached parameters expire after a certain amount of time, you could have a manual trigger, or you could build logic into your application that automatically refreshes the cache if the appropriate error condition is detected.

Update 10/10/2012:

The SqlParameter class also implements ICloneable. I’ve updated my caching logic to take advantage of this. Note that you must cast the parameter to ICloneable in order to invoke its Clone method.

private static readonly Dictionary<string, SqlParameter[]> ParameterCache;

// retrieve from cache
if (ParameterCache.ContainsKey(cmd.CommandText))
{
	var paramCollection = ParameterCache[cmd.CommandText];
	cmd.Parameters.AddRange(
		paramCollection.OfType<SqlParameter>()
			.Select(x => ((ICloneable)x).Clone() as SqlParameter)
			.ToArray()
		);
	return;
}

// add to cache
var parameters = cmd.Parameters
	.OfType<SqlParameter>()
	.Select(x => ((ICloneable)x).Clone() as SqlParameter)
	.ToArray();
ParameterCache.Add(cmd.CommandText, parameters);

Author: Adam Prescott

I'm enthusiastic and passionate about creating intuitive, great-looking software. I strive to find the simplest solutions to complex problems, and I embrace agile principles and test-driven development. View all posts by Adam Prescott