How send SP parameters to the function, which creates dataReader

[simon]

Always when I need data reader in my programs, I simply have functions,
which creates it for me:

Dim rdr As SqlDataReader
dim sql as string

sql="myStoredProcedure"
rdr = createDataReader(sql, False)

And the functions are:

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False) As SqlDataReader
Dim oCmd As SqlCommand
Dim myReader As SqlDataReader

oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("appStrConnection"))
myConn.Open()
createConnection = myConn
End Function


It works fine, but what if my stored procedure has parameters. You don't
know how many and you don't know the type of them.
Does anybody know how to change this function, that it will except the x
parameters of x type?

I think that in program you create some array type, fill it with parameters
and then send this array to function and in function create as many
parameters as array size is or something similar. Any idea?

Thank you for your help,
Simon

 

[Jeremy]

Simon,

First off, this is an ADO.NET question, so future questions like this one
should be posted in the adonet group.

Does anybody know how to change this function, that it will except the x
parameters of x type?

Yes, you could change this function, but it would also be good for you to
check out the Data Access Application Block from Microsoft (link below),
they take care of this for you. Aslo, when you add params, the type is
based on the run-time type, so that is one less thing you need to worry
about.

Here is a quick & dirty solution:
---------------------

Function createDataReader(ByVal sqlStr As String, _
Optional ByVal type As Boolean =
False, _
Optional ParamNames( ) as String, _
Optional ParamValues( ) as Object, _
) As SqlDataReader
Dim oCmd As SqlCommand
Dim myReader As SqlDataReader

oCmd = New SqlCommand(sqlStr, createConnection)

If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
End If

'// Overloading the function, and removing the Optional values
'// can clear up this ugly logic:
if (Not ((ParamNames is Nothing) _
xor (ParamValues is Nothing))) _
OrElse (Ubound(ParamNames) _
<> UBound(ParamValues)) Then
'// Error, ParamNames was provided without ParamValues
'// or UBound does not match
Throw New Exception( )
ElseIf Not ParamNames Is Nothing Then
For I as Integer = 0 to Ubound(ParamValues)
oCmd.Parameters.Add ParamNaems(i), ParamValues(i)
Next
End If


myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
Return myReader
End Function


'// Client usage:
DataReader = createDataReader("SELECT * FROM TABLE WHERE x = @intVal", _
True, _
New String() {"@intVal"}, _
New Integer() {57})

DataReader = createDataReader("SELECT * FROM TABLE WHERE s = @strVal", _
True, _
New String() {"@strVal"}, _
New String() {"Some Value})

'// Mixed Values:
Dim ParamValues(1) as Object

ParamValues(0) = CObj(56)
ParamValeus(1) = CObj(Now)

DataReader = createDataReader("SELECT * FROM TABLE" & _
" WHERE longval = @longVal AND dt =
@dateVal", _
True, _
New String() {"@longVal",
"@dateVal"}, _
ParamValues)
--------------------------

I did not test that code, so you will need to debug it.

Links:
Data Access Application Block:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

Data Tier (HIGHLY Recomended):
http://msdn.microsoft.com/asp.net/u...spx?pull=/library/en-us/dnbda/html/boagag.asp


HTH,
Jeremy

 

[Jeremy]

Replace this :
if (Not ((ParamNames is Nothing) _
xor (ParamValues is Nothing))) _
OrElse (Ubound(ParamNames) _
<> UBound(ParamValues)) Then
'// Error, ParamNames was provided without ParamValues
'// or UBound does not match
Throw New Exception( )
ElseIf Not ParamNames Is Nothing Then
For I as Integer = 0 to Ubound(ParamValues)
oCmd.Parameters.Add ParamNaems(i), ParamValues(i)
Next
End If



With this:

if ((ParamNames is Nothing) _
xor (ParamValues is Nothing)) Then
'// Error, ParamNames was provided without ParamValues
'// or UBound does not match
Throw New Exception( )
ElseIf Not ParamNames Is Nothing Then

If Ubound(ParamNames) <> Ubound(ParamValues) Then
Throw New Exception( )

End If



For I as Integer = 0 to Ubound(ParamValues)
oCmd.Parameters.Add ParamNames(i), ParamValues(i)
Next
End If