How To Use SqlParameterCache (Caching Sql Parameters)

This article describes how to cache your SqlParameters.  If you have code that is regularly executed, and that uses a stored procedure, you can benefit from this.  This information, as well as how to properly use this, is very hard to find, even on Google. (Don’t worry, you don’t need to spend all that time looking, the information is already here for you!)

There is a function that Microsoft built that will allow you to get the parameter information from the database stored procedure, and that doesn’t require you to re-build your parameter array every time.  The only thing you will need to do is set the values of the parameters.

What some people don’t realize, is that a similar approach can be taken with regular SQL queries that are not stored procedures!

The downside is that the first call will be slower, because it will need to cache the parameters, however, for each extra call, it will be faster because the parameters are already created.

How can you do this?  You first create your parameters, and store it in the call by calling SqlParameterCache.CacheParameterSet()

Then you call SqlParameterCache.GetCachedParameterSet() to get the cached result.  If the result is null, then you can store the parameters in the cache, otherwise you already have it cached!

Here is an example in VB.NET of how to use SqlParameterCache.CacheParameterSet()

Dim cachedParams as SqlParameter() = SqlHelperParameterCache.GetCachedParameterSet(Config.connstring,strSql)

if cachedParams is nothing then

Dim params as SqlParameter() = { _
    New SqlParameter("@userID", userID), _
    New SqlParameter("@timestamp", timestamp), _
    New SqlParameter("@email",email), _
    New SqlParameter("@timesLoaded",5), _
    New SqlParameter("@country",country), _
    New SqlParameter("@source",source) _
}

SqlHelperParameterCache.CacheParameterSet(Config.connstring,strSql,params)
    cachedParams = params
else
    cachedParams(0).Value = userID
    cachedParams(1).Value = timestamp
    cachedParams(2).Value = email
    cachedParams(3).Value = 5
    cachedParams(4).Value = country
    cachedParams(5).Value = source
End If

SqlHelper.ExecuteNonQuery(Config.connstring, CommandType.Text, strSql, cachedParams)

Now the down side to this, is you lose your ability to dynamically create your SQL parameters (i.e. it depends on the order of your SQL parameters).

Related articles:

• Using Dynamically Created SqlParameter Arrays
• Creating Maintainable Database Queries In C# (with source code)
• SqlHelper Source Code, Microsoft Application Blocks

Related Reading:

JavaScript: The Good Parts

Head First Java, 2nd Edition

Effective Java (2nd Edition)

Other Interesting Posts

• PHP VS. ASP.NET
• Creating SqlParameters Best Practices
• Creating Maintainable Database Queries In C# (with source code)