SqlParameterCollection

[Ice725]

To eliminate some code size in my programs, I would like to create a method that takes a parameter of SqlParameterCollection. Inside this method, I will do all the database work by creating a SqlCommand that holds all the parameters in the SqlParameterCollection that was passed in. I cannot figure out how to make this work.

 

Here is an example:

 

//This method creates the SqlParameterCollection

public void add()
{
    SqlCommand c = new SqlCommand();
    SqlParameterCollection p = c.Parameters;

    p.add("@fname",SqlDbType.Varchar,25);
    p.add("@lname",SqlDbType.Varchar,25);

    p[0].Direction = ParameterDirection.Input;
    p[1].Direction = ParameterDirection.Input;

    p[0].Value = "John";
    p[1].Value = "Doh";

    int rows = UpdateDatabase(param);
}


public int UpdateDatabase(SqlParameterCollection param)
{
    SqlCommand command = new SqlCommand();
    
    // ... Rest of code to update the database by the command

    //How do I set the parameters of the param passed in??
}

 

I tried to do something like:

 

command.Parameters.Add(param[0]);

 

... but I get an error saying that @fname already exist in another parameter collection.

 

I've tried simply printing the value of the parameters and I can see the correct values inside the method, so I know they are being passed correctly. I cannot figure out how to add them to the command, so when I do an ExecuteNonQuery, it will pass them to a stored procedure.

 

Any ideas or solutions?

 

Thanks

[Derek Stone]

You'll need to use an array of type SqlParameter[]. SqlParameterCollection does not expose a public constructor. By using an array over which you can enumerate, you can add the parameters one by one. This is an ugly method of doing it, but there aren't many other decent options (e.g. reflection).