This function:
Public Function RunSP(ByVal SP As String, Optional ByVal Params As Hashtable
= Nothing) As Object
Dim oConn As New SqlClient.SqlConnection(CONNECTION_STRING)
Dim oCmd As New SqlClient.SqlCommand(SP, oConn)
Dim SqlParameter As SqlClient.SqlParameter
oCmd.CommandType = CommandType.StoredProcedure
SqlClient.SqlCommandBuilder.DeriveParameters(oCmd)
If Not Params Is Nothing Then
For Each SqlParameter In oCmd.Parameters
If Params.ContainsKey(SqlParameter.ParameterName) Then
If IsDBNull(Params.Item(SqlParameter.ParameterName)) Then
SqlParameter.Value = DBNull.Value
Else
SqlParameter.Value = Params.Item(SqlParameter.ParameterName).ToString
End If
End If
Next
End If
Dim oDA As New SqlClient.SqlDataAdapter(oCmd)
Dim oDS As New Data.DataSet
oDA.Fill(oDS)
oDA.Dispose() : oDA = Nothing
RunSP = oDS
oCmd.Connection.Close()
oCmd.Dispose() : oCmd = Nothing
End Function
returns a dataset returned by the specified Stored Procedure with parameters
filled in from the supplied hashtable. However, if I try to call a stored
procedure from a stored procedure group (e.g. set SP to
"spCustomers_Select;2"), it bombs on the "DeriveParameters" line, saying the
stored procedure doesn't exist. Is there a fix for this, besides changing
this to construct an EXEC statement? (And would that even work?)