Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > How send SP parameters to the function, which creates dataReader

Reply
Thread Tools

How send SP parameters to the function, which creates dataReader

 
 
simon
Guest
Posts: n/a
 
      04-16-2004
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("a ppStrConnection"))
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


 
Reply With Quote
 
 
 
 
Jeremy
Guest
Posts: n/a
 
      04-16-2004
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/de...ml/daab-rm.asp

Data Tier (HIGHLY Recomended):
http://msdn.microsoft.com/asp.net/us...tml/boagag.asp


HTH,
Jeremy


 
Reply With Quote
 
 
 
 
Jeremy
Guest
Posts: n/a
 
      04-16-2004
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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: [Distutils] A new script which creates Python 3.3 venvs withDistribute and pip installed in them Philippe Ombredanne Python 0 01-31-2013 09:05 AM
Re: A new script which creates Python 3.3 venvs with Distributeand pip installed in them Vinay Sajip Python 0 01-30-2013 10:18 PM
Re: A new script which creates Python 3.3 venvs with Distribute andpip installed in them Ian Kelly Python 0 01-30-2013 08:42 PM
A new script which creates Python 3.3 venvs with Distribute and pipinstalled in them Vinay Sajip Python 0 01-30-2013 08:09 PM
DataSet versus DataReader. Which one to use? Thank You. Shapper ASP .Net 2 04-22-2005 12:29 AM



Advertisments