Reset SqlParameters

Discussion in 'ASP .Net' started by tshad, Jun 17, 2005.

  1. tshad

    tshad Guest

    I have an SqlParameter array that I want to reuse after I have used it.

    For example, I have the following code that calls my generic db routines:
    ***********************************************
    Dim myDbObject as new DbObject()
    Dim DBReader As SqlDataReader

    Dim parameters As SqlParameter () = { _
    New SqlParameter("@ClientID",SqldbType.VarChar,20), _
    New SqlParameter("@UserName",SqlDbType.VarChar,20), _
    New SqlParameter("@Password",SqlDbType.VarChar,20) }

    parameters(0).value = session("ClientID")
    parameters(1).value = UserName.text
    parameters(2).value = Password.Text

    dbReader = myDbObject.RunProcedure("GetUserInfo", parameters)
    ******************************************************

    I now want to use the same parameters but this time I have only @ClientID
    and @Password:

    Dim parameters As SqlParameter () = { _
    New SqlParameter("@ClientID",SqldbType.VarChar,20) ,
    New SqlParameter("@Password",SqlDbType.VarChar,20) )

    Now in my old way I would clear the parameters of the SqlCommand object like
    so:

    Dim objCmd as New SqlCommand(CommandText,objConn)
    ....
    objCmd.Parameters.Clear()

    How do I do that with my SqlParameter array?

    Also, I pass my parameters to my routine as "ByVal parameters As
    IDataParameter()". If I have no parameters, how would I set up my Dim to
    show there are no parameters (I still need to send a parameter array).

    Thanks,

    Tom
    tshad, Jun 17, 2005
    #1
    1. Advertising

  2. tshad

    CodeMeister Guest

    A couple things here. I'm assuming you are using a new IDbCommand object for
    each database call. Whether you can reuse your exiting parameters or not
    depends on how you are attaching them to the command.If you are cloning the
    parameters and attaching the copy you can reuse the parameters. If you are
    attaching them directly to the command object you will not be able to reuse
    them. Parameters can only be attached to a single command. You would have to
    create a new parameter array for the second call.

    I would highly recommend looking at the Data Access Aplication Block in the
    Microsoft Application Blocks. You might be able to use the block as is and
    not have to "recreate the wheel" or use it as a starting point if you need
    to extend it. Here's the link to the v2.0 block:
    http://www.microsoft.com/downloads/...0A-9877-4A7B-88EC-0426B48DF275&displaylang=en

    IHTH

    Jon


    "tshad" <> wrote in message
    news:...
    >I have an SqlParameter array that I want to reuse after I have used it.
    >
    > For example, I have the following code that calls my generic db routines:
    > ***********************************************
    > Dim myDbObject as new DbObject()
    > Dim DBReader As SqlDataReader
    >
    > Dim parameters As SqlParameter () = { _
    > New SqlParameter("@ClientID",SqldbType.VarChar,20), _
    > New SqlParameter("@UserName",SqlDbType.VarChar,20), _
    > New SqlParameter("@Password",SqlDbType.VarChar,20) }
    >
    > parameters(0).value = session("ClientID")
    > parameters(1).value = UserName.text
    > parameters(2).value = Password.Text
    >
    > dbReader = myDbObject.RunProcedure("GetUserInfo", parameters)
    > ******************************************************
    >
    > I now want to use the same parameters but this time I have only @ClientID
    > and @Password:
    >
    > Dim parameters As SqlParameter () = { _
    > New SqlParameter("@ClientID",SqldbType.VarChar,20) ,
    > New SqlParameter("@Password",SqlDbType.VarChar,20) )
    >
    > Now in my old way I would clear the parameters of the SqlCommand object
    > like so:
    >
    > Dim objCmd as New SqlCommand(CommandText,objConn)
    > ...
    > objCmd.Parameters.Clear()
    >
    > How do I do that with my SqlParameter array?
    >
    > Also, I pass my parameters to my routine as "ByVal parameters As
    > IDataParameter()". If I have no parameters, how would I set up my Dim to
    > show there are no parameters (I still need to send a parameter array).
    >
    > Thanks,
    >
    > Tom
    >
    CodeMeister, Jun 17, 2005
    #2
    1. Advertising

  3. tshad

    tshad Guest

    "CodeMeister" <> wrote in message
    news:...
    >A couple things here. I'm assuming you are using a new IDbCommand object
    >for each database call. Whether you can reuse your exiting parameters or
    >not depends on how you are attaching them to the command.If you are cloning
    >the parameters and attaching the copy you can reuse the parameters. If you
    >are attaching them directly to the command object you will not be able to
    >reuse them. Parameters can only be attached to a single command. You would
    >have to create a new parameter array for the second call.


    I am just sending the Parameter array to my procedure (which calls another
    procedure) to build the SqlCommand object. Here is procedure:

    *******************************************************************************
    Private Function BuildQueryCommand( _
    ByVal storedProcName As String, _
    ByVal parameters As IDataParameter()) _
    As SqlCommand

    Dim command As New SqlCommand(storedProcName, myConnection)
    command.CommandType = CommandType.StoredProcedure

    Dim parameter As SqlParameter
    For Each parameter In parameters
    command.Parameters.Add(parameter)
    Next

    Return command

    End Function
    *********************************************************************************

    I pass the parameter list to this procedure which then goes through the list
    and adds it to the new SqlCommand object one by one.

    So I can keep using the parameter list. But I need to find out how to add,
    change or delete the SqlParameters from the array.

    Thanks,

    Tom
    >
    > I would highly recommend looking at the Data Access Aplication Block in
    > the Microsoft Application Blocks. You might be able to use the block as is
    > and not have to "recreate the wheel" or use it as a starting point if you
    > need to extend it. Here's the link to the v2.0 block:
    > http://www.microsoft.com/downloads/...0A-9877-4A7B-88EC-0426B48DF275&displaylang=en
    >
    > IHTH
    >
    > Jon
    >
    >
    > "tshad" <> wrote in message
    > news:...
    >>I have an SqlParameter array that I want to reuse after I have used it.
    >>
    >> For example, I have the following code that calls my generic db routines:
    >> ***********************************************
    >> Dim myDbObject as new DbObject()
    >> Dim DBReader As SqlDataReader
    >>
    >> Dim parameters As SqlParameter () = { _
    >> New SqlParameter("@ClientID",SqldbType.VarChar,20), _
    >> New SqlParameter("@UserName",SqlDbType.VarChar,20), _
    >> New SqlParameter("@Password",SqlDbType.VarChar,20) }
    >>
    >> parameters(0).value = session("ClientID")
    >> parameters(1).value = UserName.text
    >> parameters(2).value = Password.Text
    >>
    >> dbReader = myDbObject.RunProcedure("GetUserInfo", parameters)
    >> ******************************************************
    >>
    >> I now want to use the same parameters but this time I have only @ClientID
    >> and @Password:
    >>
    >> Dim parameters As SqlParameter () = { _
    >> New SqlParameter("@ClientID",SqldbType.VarChar,20) ,
    >> New SqlParameter("@Password",SqlDbType.VarChar,20) )
    >>
    >> Now in my old way I would clear the parameters of the SqlCommand object
    >> like so:
    >>
    >> Dim objCmd as New SqlCommand(CommandText,objConn)
    >> ...
    >> objCmd.Parameters.Clear()
    >>
    >> How do I do that with my SqlParameter array?
    >>
    >> Also, I pass my parameters to my routine as "ByVal parameters As
    >> IDataParameter()". If I have no parameters, how would I set up my Dim to
    >> show there are no parameters (I still need to send a parameter array).
    >>
    >> Thanks,
    >>
    >> Tom
    >>

    >
    >
    tshad, Jun 17, 2005
    #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. =?Utf-8?B?VGltOjouLg==?=

    Array with SQLParameters

    =?Utf-8?B?VGltOjouLg==?=, Apr 20, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    14,156
    =?Utf-8?B?QW5pbCBSYWphbg==?=
    Apr 20, 2005
  2. =?Utf-8?B?VGltOjouLg==?=

    SQLParameters and loops???

    =?Utf-8?B?VGltOjouLg==?=, Apr 25, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    665
    Kevin Spencer
    Apr 25, 2005
  3. Patrick Olurotimi Ige

    Sqlparameters and Stored Procedure

    Patrick Olurotimi Ige, May 24, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    424
  4. Patrick Olurotimi Ige

    SqlParameters SqlDbType.Char in VB Question

    Patrick Olurotimi Ige, Jun 11, 2005, in forum: ASP .Net
    Replies:
    10
    Views:
    1,076
    Teemu Keiski
    Jun 22, 2005
  5. Patrick.O.Ige

    Help me on this with SQLPARAMETERS

    Patrick.O.Ige, Aug 23, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    445
    Grant Merwitz
    Aug 25, 2005
Loading...

Share This Page