SqlParameterCollection to IDataParameter[]

[tshad]

I am having trouble passing an SqlParamaterCollection to a

SqlParameterCollection myParams;

myParams = database.GetProcedureParameters("UpdateSurveyTestAnswer");
db.RunProcedure("UpdateSurveyTestAnswer", myParams, ref myRows);

the method is defined as:

public int RunProcedure(string storedProcName, IDataParameter[] parameters, ref int rowsAffected)

In my above code, I get an error:

Argument 2: cannot convert from 'System.Data.SqlClient.SqlParameterCollection' to 'System.Data.IDataParameter[]'

but SqlParameterCollection is derived from IDataParameter - so why won't this work?

Thanks,

Tom

 

[tshad]

I think I understand it a little better here.

SqlParameterCollection mySqlParamCol = null;
IDataParameterCollection myIDataPCol = null;
SqlParameter[] mySqlParam = null;
IDataParameter[] myIDataP = null;

// I can do the following 2

myIDataPCol = mySqlParamCol;
myIDataP = mySqlParam;

//I can't do any of the following ones

mySqlParamCol = myIDataPCol;
mySqlParam = myIDataP;
myIDataP = myIDataPCol;
myIDataPCol = myIDataP;
mySqlParam = mySqlParamCol;
mySqlParamCol = mySqlParam;

Apparently I can assign the SqlParameter[] to the IDataParameter[]
But not vice versa.

And I can assign the SqlParameterCollection to the IDataParameter[]
But also not vice versa. I assume this is because the SqlParameter is derived from the IDataParameter.

But you can't mix an match arrays with Collections and you can't assign IDataParameter anything to SqlParameter anything.

Not sure why.

But which is better to use? The SqlParameter arrays or SqlParameterCollection.

The SqlCommand object uses the SqlParameterCollection, I believe - so I would assume the SqlParameterCollection would be the best to use.

I had wanted to use the IDataParameterCollection and pass that back so I could use that in the SqlCommand object, but I can't assign the IDataParameterCollection object to the SqlParameterCollection object of the SqlCommand object.

I wanted to use the IDataParameterCollection as a generic class that could be used with anything that implements the class but I can't see how it would work with the SqlParameterCollection class of the SqlCommand object.

Thanks,

Tom