Retrieving the stored procedure parameter list in ado.net

[Irishmaninusa]

http://www.15seconds.com/issue/010718.htm

I am current writing a data access layer for a project I am on, and one of
the things I want to be able to do is use stored procedures with optional
values. The stored procedure part is not an issue, what is the issue is how
I call the stored procedure using ado.net. Currently I get the list of
parameters for the sp and store them in an array.

The current way that I add them to the command object is as follow(s):

'Traverse through the array of parameters for the stored procedure

For iLoop = 0 To iArrPost - 1

..Parameters.Add(arrParamList(iLoop).sParamName, _

arrParamList(iLoop).enuDataType, _

arrParamList(iLoop).iSize).Value = arrParamList(iLoop).oValue

..Parameters(iLoop).Direction = arrParamList(iLoop).enuDirection

Next

The problem with this is if the stored proceudre is like this



param1

param2

param 3

param4

with param1 being required and the rest optional, then if pass in param1 and
param3, it is going to write out the contents of param3 to be sent to param2
and that is what I am trying to avoid. So the code I found about is from
ado, but I am not sure if this is possible to do in ado.net or if there is a
clearer and better way of doing it. So if any one has any insight into this
please let me know. Thanks


JD

 

[Val Mazur]

Hi,

You need to declare all the parameters anyway, but you can pass NULL value
to the optional on. See next KB with an example

http://support.microsoft.com/default.aspx?scid=kb;en-us;321902&Product=adonet

--
Val Mazur
Microsoft MVP


"Irishmaninusa"

 

[Irishmaninusa]

Just wanted to confirm this.

I tried the code listed on the url below and it worked like a charm. Thanks
for that. My question is as follows, this users the SQLClient, I am trying
to do this using OLEDB and it doesn't seem to support optional parameters,
even though the SQLClient does support it. Am correct in my assumption, or
is there something else I need to do in my code down below. Thanks in
advance.

'Declare and open Connection.

Dim cn As OleDbConnection = _

New OleDbConnection("Provider=SQLOLEDB;Data Source=SERVER;Initial
Catalog=northwind;user id=username;password=password;")

cn.Open()

'Declare Command.

Dim cmd As OleDbCommand = _

New OleDbCommand("usp_OptionalYear", cn)

cmd.CommandType = CommandType.StoredProcedure

'Declare three Parameters.

Dim prm As OleDbParameter = _

New OleDbParameter("@CustID", OleDbType.WChar, 5)

cmd.Parameters.Add(prm)

' prm = New SqlParameter("@Year", SqlDbType.Char, 4)

' cmd.Parameters.Add(prm)

prm = New OleDbParameter("@ShipCountry", OleDbType.VarWChar, 15)

cmd.Parameters.Add(prm)

'Set Parameter values.

With cmd

..Parameters("@CustID").Value = "ALFKI"

' .Parameters("@Year").Value = Nothing

..Parameters("@ShipCountry").Value = "Germany"

End With

'Execute stored procedure.

Dim odt As New OleDbDataAdapter

Dim ods As New DataSet

Dim odtb As DataTable

Try

odt.SelectCommand = cmd

odt.Fill(ods, "db")

odtb = ods.Tables("db")

MsgBox(odtb.Rows.Count)

Catch ex As Exception

MsgBox(ex.Message)

End Try

 

[Val Mazur]

Hi,

It might not work with the OLEDB provider. SqlClient was designed
specifically to work with SQL Server and should support more functionality,
specific to SQL Server. In a case of Oledb .NET Managed provider is not
oriented to the specific OLEDB provider and may have some limitations. So
you might expect this result

--
Val Mazur
Microsoft MVP


"Irishmaninusa"

 

[Irishmaninusa]

Thanks for your help, greatly appreciated

Hi,

It might not work with the OLEDB provider. SqlClient was designed
specifically to work with SQL Server and should support more functionality,
specific to SQL Server. In a case of Oledb .NET Managed provider is not
oriented to the specific OLEDB provider and may have some limitations. So
you might expect this result

--
Val Mazur
Microsoft MVP


"Irishmaninusa"