C# - Output SqlParameter uses different values then the ones given??

Question

I have a SqlCommand which runs a stored procedure that contains two integer output parameters. Right before the SqlCommand runs I can see that the output parameters are set to the correct values, however when the command actually executes, it uses a NULL for parameter1 and 0 for parameter2 (verified using SQL Profiler) regardless of what I set the parameters to.

A simplified version of the code is this:

foreach (KeyValuePair<string, object> parameter in outputParameters)
{
    SqlParameter param = new SqlParameter(parameter.Key, parameter.Value);
    param.Direction = ParameterDirection.Output;
    command.Parameters.Add(param);
}

command.ExecuteNonQuery();

I'm confused by two different things here:

1) Why isn't it using the values in the parameters? I can place a breakpoint right before command.ExecuteNonQuery() and see that the command.Parameters list has the output parameters set correctly, however the SQL profiler trace has different parameters when the query gets executed.

2) Both parameters are integers and defined the exact same way - Why is one set to NULL while the other set to 0??

Answer 1

They're output parameters - i.e. the value is meant to come from the stored procedure... logically they don't have values on input. If you want the values from the client to be used in the stored procedure, they should have a direction of InputOutput.

EDIT: Responding to your comment - Output means just output. Input means just input. InputOutput means both ways. It sounds like you want both ways, so use InputOutput.

As for why one is null and the other not... I don't know, to be honest.

Answer 2

You want to change the direction to InputOutput (per op edit) rather than output.

When you reference output parameters you are telling the code that the values should return from the actual stored procedure rather then from your code. Even if your code contains a value, your code actually doesn't care what those values are as you specified output parameters.

Here's what it should be:

foreach (KeyValuePair<string, object> parameter in outputParameters)
{
    SqlParameter param = new SqlParameter(parameter.Key, parameter.Value);
    param.Direction = ParameterDirection.InputOutput;
    command.Parameters.Add(param);
}

command.ExecuteNonQuery();