Stored procedure parameter caching in VB.NET

One of the annoyances of Stored Procedures is having to pass the parameters and know the parameter datatypes, it's a bit of a pain and one I addressed when I started to use .NET all those years ago. I was cleaning up some code and noticed this little nugget and thought I'd share it with you since most (if not all examples) on the net show parameters being declared and passed through programmatically.

To overcome this annoyance I decided to retrieve the parameter definitions direct from the SQL Server using the DeriveParameters command which will retrieve the SPs parameter definition so you can use these when passing the values through instead of declaring them yourself. This has the distinct advantage of making sure that the most up to date definitions are available to you program and also allows you to tweak the stored proc without having to change the code in your VB app. The only issue is that it causes additional calls to the SQL Server before each stored procedure call!!!  To help get around this the parameter definitions are cached in a collection after the first call so we only need that additional call to be made once for each stored proc. So here you go :

Private Sub LoadParams(ByRef dbCommand As SqlClient.SqlCommand)
   Dim strProcName As String = dbCommand.CommandText
   Dim dbparameter As Collection = Nothing
   Dim parameter As SqlClient.SqlParameter = Nothing
   Static dicParams As Collection

   ' If this is the first time in then initialise the holding collection.
   If (dicParams Is Nothing) Then
      dicParams = New Collection
   End If

   ' Check to see if the stored procedure has been called before, if so then retrieve the cached parameter list
   If (dicParams.Contains(strProcName)) Then
      dbparameter = dicParams(strProcName)
      If (Not dbparameter Is Nothing) Then
         ' Go through the parameters we have stored and add them to the command parameter list.
         For Each parameter In dbparameter
            dbCommand.Parameters.Add(CType(parameter, ICloneable).Clone)
         Next
      End If
   Else
      ' OK this is a never seen before stored procedure, get the current parameter declarations from the SQL Server
      SqlClient.SqlCommandBuilder.DeriveParameters(dbCommand)
      ' Now that we have the list of paramteres we can record them
      For Each parameter In dbCommand.Parameters
         ' Create a new parameter collection, we do this in the loop since some commands come back with no parameters
         If (dbparameter Is Nothing) Then
            dbparameter = New Collection
         End If
         ' Create a copy of the parameter information by cloning it, we have to force the clone method here.
         dbparameter.Add(CType(parameter, ICloneable).Clone)
      Next
      ' Record these paramteres and stored procedure name
      dicParams.Add(dbparameter, strProcName)
   End If
End Sub

Having pasted the code here I can see I have no errorhanding, that's because it's handled in the calling procedure which I'll also post so you can see how to use it, this function opens a stored procedure with the given parameters and then returns a dataset.

'///////////////////////////////////////////////////////////////////////////////

' Open the dataset a stored procedure
Friend Function OpenDataSetFromSP(ByVal StoredProcName As String, ByRef ReturnValue As Object, ByVal ParamArray Args() As Object) As DataSet
   Dim dbData As SqlClient.SqlDataAdapter
   Dim dbCommand As SqlClient.SqlCommand
   Dim parameter As SqlClient.SqlParameter
   Dim lngCounter As Long
   Dim ds As DataSet

   OpenDataSetFromSP = Nothing
   Try
      ' Create the command for this stored procedure
      dbCommand = New SqlClient.SqlCommand(StoredProcName, SQLDatabase)
      dbCommand.CommandType = CommandType.StoredProcedure
      ' Load the parameters definitions for this stored procedure
      LoadParams(dbCommand)
      lngCounter = 0
      If (dbCommand.Parameters.Count > 0) Then
         For Each parameter In dbCommand.Parameters
            ' Ignore the return paramter if the stored procedure has one
            If (Not parameter.Direction = ParameterDirection.ReturnValue) Then
               ' Make sure that we don't push in more params than the stored procedure has
               If (Args.Length >= lngCounter) Then
                  parameter.Value = Args(lngCounter)
               End If
               lngCounter = lngCounter + 1
            End If
         Next
      End If

      ' Execute what we need
      dbData = New SqlClient.SqlDataAdapter(dbCommand)
      ds = New DataSet
      ' Shove the data into a dataSet
      dbData.Fill(ds)
      OpenDataSetFromSP = ds

      ' Retrieve the Return parameter?
      For Each parameter In dbCommand.Parameters
         If (parameter.Direction = ParameterDirection.ReturnValue) Then
            ReturnValue = parameter.Value
         End If
      Next

     ' Exit
   Catch ex As Exception
      DB.GenerateError("OpenDataSetFromSP : " + StoredProcName, ex)
      OpenDataSetFromSP = Nothing
   End Try

   ' Clean up
   dbCommand = Nothing
   dbData = Nothing
End Function

You'll need to make sure you have the SQLConnection object named as SQLDatabase and also you'll need to sort out a different errorhandler, but the classes will hopefully save you a lot of time and heartache.