An interesting problem

[Elliot M. Rodriguez]

This might be architecturally related, but I'm hoping there is an easy way
out that I am just missing. Its hard to explain,
so please bear with me.

I have a DataAccessLayer class. In it, I have a function that returns a
SqlParameterCollection. The idea
is I could pass in the name of a procedure and X number of parameters
(whether input or output), and return
back the SqlParameterCollection that is assigned to the DataAccessLayer
object's Command. I am using a ParamArray
of SqlParameter objects to accomplish this and it works fine.
Public Function ModifyDatabaseWithReturnParameters(ByVal ProcedureName As
String, ByVal ParamArray DataParameters() As SqlParameter) As
SqlParameterCollection

To make sure I clear out the local Command object parameters between calls,
I loop through it and remove each item before I perform
my next call. I *dont* do this in the above function, since that would mean
returning an object with 0 items in it.

So I now have a problem where I need to clear the Parameters assigned to the
local Command object, yet return the collection
from the function. Reason is, I am reusing SqlParameter objects, and when I
attempt to add the same SqlParameter object
to another method call without clearing the previous SqlParameterCollection,
I get errors (something like "An item named "@myparam"
is already a member of SqlParameterCollection").

I cant use ByRef (that would fix it I think) for my SqlParameter argument
because its a ParamArray. I could use CopyTo, but that would break my
object's implementation and cause me to rewrite some code to handle
returning an Array versus a SqlParameterCollection. As a last resort I'm not
going to reuse the parameters for this call, but it will make my code a lot
more verbose and difficult to follow (I intend to use the same parameters in
multiple calls).

Any ideas?