Jon said:
Hello all.
Using C# and ADO.net, is it possible to return the parameters of a
Stored Procedure (name, type, direction, size etc) to my app? If it is
possible, what is the best way for them to be returned (as a
recordset?).
In a perfect world, I would like it to be able to look at any database
but this may be a little optimistic, so the major ones that I need
help with is Sql Server 2000 and Oracle.
Use DeriveParameters. This will attach the parameters to a command.
For SQLServer you can use the SQLCommandBuilder.DeriveParameters to query
the parameters for a stored procedure.
For oracle Microsoft's OracleClient use
OracleCommandBuilder.DeriveParameters.
For Oracle's Oracle Data Provicer for DotNet (ODP.NET), use the following:
Sub DeriveParameters(ByVal cmd As OracleCommand, ByVal con As
OracleConnection)
If cmd.CommandType <> CommandType.StoredProcedure Then
Throw New ArgumentException("Invalid Command Type")
End If
Dim r As OracleDataReader
Const sqlP As String = "select OBJECT_NAME, PACKAGE_NAME, ARGUMENT_NAME,
POSITION, DATA_TYPE, IN_OUT, DATA_LENGTH, DATA_PRECISION, DATA_SCALE from
user_arguments where UPPER(object_name) = UPPER
object_name) and
UPPER(package_name) = UPPER
package_name) order by position"
Const sqlNP As String = "select OBJECT_NAME, PACKAGE_NAME,
ARGUMENT_NAME, POSITION, DATA_TYPE, IN_OUT, DATA_LENGTH, DATA_PRECISION,
DATA_SCALE from user_arguments where UPPER(object_name) =
UPPER
object_name) and package_name is null order by position"
Dim objectName As String = cmd.CommandText
Dim packageName As String
If objectName.IndexOf("."c) > -1 Then
Dim np() As String = objectName.Split("."c)
If np.Length <> 2 Then
Throw New ArgumentException("Invalid object name")
End If
packageName = np(0)
objectName = np(1)
End If
Dim cmdDP As OracleCommand
If packageName Is Nothing Then
cmdDP = New OracleCommand(sqlNP, con)
cmdDP.Parameters.Add(New OracleParameter("object_name", objectName))
Else
cmdDP = New OracleCommand(sqlP, con)
cmdDP.Parameters.Add(New OracleParameter("object_name", objectName))
cmdDP.Parameters.Add(New OracleParameter("package_name", packageName))
End If
r = cmdDP.ExecuteReader
Dim params As New ArrayList(5)
Const OBJECT_NAME = 0, PACKAGE_NAME = 1, ARGUMENT_NAME = 2, POSITION =
3, DATA_TYPE = 4, IN_OUT = 5, DATA_LENGTH = 6, DATA_PRECISION = 7,
DATA_SCALE = 8
Try
Dim p As OracleParameter
Do While r.Read
p = New OracleParameter()
If r.IsDBNull(ARGUMENT_NAME) Then
p.ParameterName = "return_value"
Else
p.ParameterName = r.GetString(ARGUMENT_NAME)
End If
p.OracleDbType = GetOracleDBType(r.GetString(DATA_TYPE))
If Not r.IsDBNull(DATA_LENGTH) Then
p.Size = r.GetDecimal(DATA_LENGTH)
End If
If Not r.IsDBNull(DATA_PRECISION) Then
p.Precision = r.GetDecimal(DATA_PRECISION)
End If
If Not r.IsDBNull(DATA_SCALE) Then
p.Scale = r.GetDecimal(DATA_SCALE)
End If
Select Case r.GetString(IN_OUT)
Case "IN"
p.Direction = ParameterDirection.Input
Case "IN/OUT"
p.Direction = ParameterDirection.InputOutput
Case "OUT"
If r.IsDBNull(ARGUMENT_NAME) Then
p.Direction = ParameterDirection.ReturnValue
Else
p.Direction = ParameterDirection.Output
End If
End Select
If p.Direction = ParameterDirection.InputOutput _
Or p.Direction = ParameterDirection.Output _
Or p.Direction = ParameterDirection.ReturnValue Then
Select Case p.OracleDbType
Case OracleDbType.Char, OracleDbType.Varchar2,
OracleDbType.NChar, OracleDbType.NVarchar2
p.Size = 4000
Case OracleDbType.Raw
p.Size = 255
End Select
End If
params.Add(p)
Loop
cmd.Parameters.Clear()
For Each p In params
cmd.Parameters.Add(p)
Next
Finally
r.Close()
End Try
End Sub
Function GetOracleDBType(ByVal typeName As String) As OracleDbType
Dim s As String = String.Intern(typeName)
If s Is "CHAR" Then
Return OracleDbType.Char
ElseIf s Is "VARCHAR2" Then
Return OracleDbType.Varchar2
ElseIf s Is "NUMBER" Then
Return OracleDbType.Decimal
ElseIf s Is "DATE" Then
Return OracleDbType.Date
ElseIf s Is "BFILE" Then
Return OracleDbType.BFile
ElseIf s Is "BLOB" Then
Return OracleDbType.Blob
ElseIf s Is "CLOB" Then
Return OracleDbType.Clob
ElseIf s Is "FLOAT" Then
Return OracleDbType.Double
ElseIf s Is "INTERVAL DAY TO SECOND" Then
Return OracleDbType.IntervalDS
ElseIf s Is "INTERVAL YEAR TO MONTH" Then
Return OracleDbType.IntervalYM
ElseIf s Is "LONG RAW" Then
Return OracleDbType.LongRaw
ElseIf s Is "NCHAR" Then
Return OracleDbType.NChar
ElseIf s Is "NCLOB" Then
Return OracleDbType.NClob
ElseIf s Is "NVARCHAR2" Then
Return OracleDbType.NVarchar2
ElseIf s Is "RAW" Then
Return OracleDbType.Raw
ElseIf s Is "REF CURSOR" Then
Return OracleDbType.RefCursor
ElseIf s Is "TIME" Then
Throw New NotSupportedException(typeName)
ElseIf s Is "TIMESTAMP WITH LOCAL TIME ZONE" Then
Return OracleDbType.TimeStampLTZ
ElseIf s Is "TIMESTAMP WITH TIME ZONE" Then
Return OracleDbType.TimeStampTZ
ElseIf s Is "TIMESTAMP" Then
Return OracleDbType.TimeStamp
Else
Throw New NotSupportedException(typeName)
End If
End Function
