How send SP parameters to the function, which creates dataReader

Discussion in 'ASP .Net' started by simon, Apr 16, 2004.

  1. simon

    simon Guest

    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
     
    simon, Apr 16, 2004
    #1
    1. Advertising

  2. simon

    Jeremy Guest

    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, Apr 16, 2004
    #2
    1. Advertising

  3. simon

    Jeremy Guest

    Re: How send SP - Correction

    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
     
    Jeremy, Apr 16, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Shapper
    Replies:
    2
    Views:
    571
    Shapper
    Apr 22, 2005
  2. Vinay Sajip
    Replies:
    0
    Views:
    113
    Vinay Sajip
    Jan 30, 2013
  3. Ian Kelly
    Replies:
    0
    Views:
    136
    Ian Kelly
    Jan 30, 2013
  4. Vinay Sajip
    Replies:
    0
    Views:
    138
    Vinay Sajip
    Jan 30, 2013
  5. Philippe Ombredanne
    Replies:
    0
    Views:
    143
    Philippe Ombredanne
    Jan 31, 2013
Loading...

Share This Page