Call GetStoredProcCommand with variable number of arguments in C#?

Question

In my code I am repeating code blocks like this all the time:

var returnData = MyDb.ExecuteDataSet(
    MyDb.GetStoredProcCommand("MyTable_Insert", columnOne, columnTwo, columnThree),
    transaction
);

var returnId = (int)returnData.Tables[0].Rows[0]["INSERTED_ID"];

This seems to me to be a fairly large block of code to be repeating so many times, but I can't work out how to generalise it into a method because the number of arguments is different to each stored procedure.

The obvious solution (to me) is to pass my parameters through to the function in an array and then expand them to call GetStoredProcCommand, but I can't find a way to do this:

private int CallStoredProcedure(string procName, List<dynamic> parameterValues) {
    ..
    MyDb.GetStoredProcCommand(procName, expand parameterValues);
    // ERROR: Obviously "expand" doesn't exist
    ..
}

I could use the longer-winded way passing parameters using AddInParameter, but then I would need to pass type information along with the parameter values:

private int CallStoredProcedure(string procName, IDictionary<DbType, IDictionary<string, string>> paramsKeyValuesByType) {
    ..
    MyDb.AddInParameter(myProcCommand, param.Value.Key, param.Key, param.Value.Value);
    ..
}

But then I'd have to construct the IDictionary object before calling the method which would be even more long-winded than how I was originally calling the procedure.

Can anyone think of an elegant solution to this?

If you're using variable arguments in `MyDb.GetStoredProcCommand`, why wouldn't you just use variable arguments as well in `CallStoredProcedure`? Variable arguments are stored in an array which can be directly passed instead of an expanded list. — mellamokb, Oct 16 '12 at 13:37

score 4 · Accepted Answer · answered Oct 16 '12 at 13:37

4

You can use the params keyword:

private int CallStoredProcedure(string procName, params object[] parameterValues) {
    ..
    MyDb.GetStoredProcCommand(procName, parameterValues);
    ..
}

answered Oct 16 '12 at 13:37

Mark Byers

811,555
193
1,581
1,452

I don't think this helps me because how do I then pass those variable parameters through to `GetStoredProcCommand` - wouldn't that have to be defined using the `params` keyword? I couldn't get it to work that way. I'm happy to be corrected. – Robin Winslow Oct 16 '12 at 13:40
2

@RobinWinslow: You don't have to do anything. Check the [documentation](http://msdn.microsoft.com/en-us/library/bb748722(v=pandp.31).aspx). As you can see, `GetStoredProcCommand` uses `params` too, so it should just work. Why do you think it won't work? Did you try it? What error did you get? – Mark Byers Oct 16 '12 at 13:41
Good point. This whole `params` thing confused me a little, but now I understand it. – Robin Winslow Oct 22 '12 at 15:04

Call GetStoredProcCommand with variable number of arguments in C#?

1 Answers1