Reset SqlParameters

[tshad]

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]

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]

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