Subscribe RSS FeedThis 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:
will return 2
///</summary>
///<param name="a">The first number</param>
///<param name="b">The second number</param>
///<returns>The GCD of a and b</returns>
public static int FindGcd(int a, int b)
{
int t;
while (b != 0)
{
t = b;
b = a % b;
a = t;
}
return a;
}
}
}
