LINQ to Stored Procedure. Can I call the SP with a dynamic list of parameters instead of strongly typing them?

Question

I'm trying to make my code more dynamic. So I currently call the a stored procedure, with some strongly typed parameters like this

var results = datacontext.spReturnUsers(txtUserId.Text, txtUserFirstName.Text, txtUserLastName.Text);

Now what I would like to be able to do is loop through and add the parameters dynamically. So if tomorrow I add another search field, like User Address, or something to my panel then I can just loop through the controls and add the parameters in dynamically and not have to come back in and change the strongly typed Stored Procedure call.

Something like before LINQ you would do with a parameters.add() on your stored procedure.

Is there any way to accomplish this will still using LINQ to SQL?

Answer 1

You could have your sp take in an xml data type parameter and you could loop through and dynamically create the value of that parameter. Then the stored proc would know what to expect in the xml parameter....that is a way to pass in a variable length parameter into the database.

Answer 2

The ExecuteCommand and ExecuteQuery methods of the DataContext class take a simple parameter (params object[]) array. There are a few gotchas when it comes to those, but for the most part they work fine and are less code to write than populating the parameters of a SqlCommand.

context.ExecuteCommand("EXEC usp_UpdateProductName {0}, {1}",
    productID, productName);

That's all there really is to it.