Browse DevX
Sign up for e-mail newsletters from DevX


Data Access with Microsoft Application Blocks : Page 3

Microsoft has created a set of libraries known as Application Blocks which help developers reduce the amount of code they must write—while using current best practices. One of the components, Data Access Application Block for .NET, addresses Microsoft SQL Server data access by wrapping up data access into a helper class.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

SqlHelperParameterCache Class
The SqlHelperParameterCache class is used in conjunction with the SqlHelper class to support the caching of SqlParameters, thus allowing you to reuse parameters without having to recreate them every time you need to run an SQL command. This parameter cache class uses a hash table to store parameter caches based on connection string and command text. You can set up a parameter cache in one of two ways: you can specify an array of SqlParameters yourself or you can specify a stored procedure and connection string, and it will build an array of SqlParameter objects using the SqlCommandBuilder class. To retrieve the SqlParameter array just use the same connection string and command text you used to store the array to retrieve it.

Table 3 describes the methods available for the SqlHelperParameterCache class.

Table 3:SqlHelperParameterCache class method definitions.






(string connectionString, string commandString, SqlParameter[] ParamArray)



(string connectionString, string commandString)



(string connectionString, string spName, bool includeReturnValueParameter)

There are some important things you should know about when using this class. First, the GetSpParameterSet will only work with stored procedures. Second, in order to retrieve the SqlParameter array the connection string must be syntactically and semantically correct, capitalization and all. Finally, remember that whenever you retrieve a SqlParameter array it is actually a clone of what is in the cache. You can use this to your advantage by presetting SqlParameter values and storing those preset SqlParameter objects in the cache. One last thing to note is that if you use the GetSpParameterSet more than one time for a particular connection string and command string, the method will try to locate the SqlParameters in cache first before attempting to utilize the SqlCommandBuilder class to determine the list of parameters from the stored procedure. If the GetSpParameterSet has to retrieve the parameters via the SqlCommandBuilder class, it will store them into the cache so that they can be used again later.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date