Database Independent Development using ASP.NET 2.0: Dealing with Stored Procedures - Adding parameters to the cache
(Page 2 of 6 )
A stored procedure may have more than one parameter to be accepted. Before executing a stored procedure, we need to "cache" all those parameters in an object to serve the same while executing the stored procedure.
In this scenario, I would like to have the "cache" be determined as an "ArrayList" object. The declaration of that object is as follows:
Dim alSPParamCache As ArrayList = Nothing
I would like to have each of the parameters to be added to the above "cache" object using a separate method as follows:
Public Sub SPParameterAdd(ByVal ParamName As String, ByVal ParamValue As Object, Optional ByVal ParamDirection As ParameterDirection = ParameterDirection.Input, Optional ByVal ParamDataType As DbType = Nothing, Optional ByVal ParamSize As Integer = Nothing)
If alSPParamCache Is Nothing Then
alSPParamCache = New ArrayList
End If
Dim objParam As New SPParameter
With objParam
.ParameterName = ParamName
.ParameterValue = ParamValue
.ParameterDirectionUsed = ParamDirection
.ParameterDataType = ParamDataType
.ParameterSize = ParamSize
End With
alSPParamCache.Add(objParam)
End Sub
The entire parameter cache must be cleared after successful execution of a stored procedure (or even manually when necessary). The following is the method which is meant for that:
Public Sub SPParameterClearCache()
If Not alSPParamCache Is Nothing Then
alSPParamCache.Clear()
alSPParamCache = Nothing
End If
End Sub
Next: Executing a stored procedure as part of the Data Access Layer >>
More ASP.NET Articles
More By Jagadish Chaterjee
