VB.NET : Recuperare i parametri di una stored procedure da SQL Server

La funzione proposta permette di ricavare l'elenco degli argomenti accettati da una stored procedure presente in un database sql server.

Per comodità, definiamo una classe che conterrà il singolo argomento:

Public Class DBParameterData
  Public Name As String ' Nome dell'argomento
  Public Direction As String ' Direzione dell'argomento (IN,OUT,INOUT)
  Public Type As String ' Tipo dell'argomento
  Public MaxLen As Integer ' Lunghezza massima nel caso di caratteri
  Public Precision As Integer ' Precisione
  Public Overrides Function ToString() As String
    Return Me.Name + " " + Me.Type + " " + Me.Precision.ToString() + " " + Me.MaxLen.ToString() + " " + Me.Direction
  End Function
End Class

La funzione vera e propria restituisce un array di oggetti DBParameterData :

Public Function GetStoredProcedureParameter    (ByVal spName As String, _
    ByVal serverName As String, _
    ByVal dbName As String) As ArrayList
  Dim retval As New ArrayList
  Dim dbConn As SqlConnection
  Dim connString As String = "Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=" + dbName + ";server=" + serverName

  dbConn = New SqlConnection(connString)
  dbConn.Open()


  Dim spReader As SqlDataReader = Nothing
  If Not dbConn Is Nothing Then
    Dim spSelectString = "select ordinal_position as Ordine, " + _
      "parameter_name as Name, parameter_mode as Direction, " + _
      "data_type as Type, character_maximum_length as MaxLen, " + _
      "numeric_precision as Prec from information_schema.parameters " + _
      "where specific_name='" + spName + "' order by ordinal_position"
    Dim spCommand As SqlCommand = New SqlCommand(spSelectString, dbConn)
    spReader = spCommand.ExecuteReader(CommandBehavior.CloseConnection)

    If (Not spReader Is Nothing) Then
      While spReader.Read()
        Dim pData As New DBParameterData
        pData.Name = spReader("Name")
        pData.Direction = spReader("Direction")
        If Convert.IsDBNull(spReader("MaxLen")) Then
          pData.MaxLen = -1
        Else
          pData.MaxLen = System.Convert.ToInt16(spReader("MaxLen"))
        End If
        If Convert.IsDBNull(spReader("Prec")) Then
          pData.Precision = -1
        Else
          pData.Precision = System.Convert.ToInt16(spReader("Prec"))
        End If
        pData.Type = spReader("Type")
        retval.Add(pData)
      End While
      spReader.Close()
    End If
    dbConn.Close()
  End If

  Return retval
End Function