In-Out Parameter for SqlCommand

Question

I have the following parameter for SqlCommand. How do I make it to both in and out the paramter value for the Stored Procedure.

 SqlCommand mySqlCommand = new SqlCommand("aspInsertZipCode", mySqlConnection);
 mySqlCommand.CommandType = CommandType.StoredProcedure;
 mySqlCommand.Parameters.Add("@DataRows", dataStringToProcess.ToString());

Answer 1

SqlParameter has a Direction enumeration. Set this value.

Then use the SqlCommand.Parameters.Add that takes a SqlParameter.

Parameter direction:

http://msdn.microsoft.com/en-us/library/system.data.parameterdirection.aspx

You then pull the value out after having called ExecuteNonQuery (for example), by getting the Value from the parameter out of the command collection:

myCommand.Parameters["@paramName"].Value

Can't remember, but I think there is a string indexer on that.

Alternatively, there is this one liner:

myCommand.Parameters.AddWithValue("@paramName", value).Direction = ParameterDirection.InputOutput;

Answer 2

One of the attributes of a SQL Command Parameter is the Direction. You would want to use (going off of memory)

SqlCommand mySqlCommand = new SqlCommand("aspInsertZipCode", mySqlConnection);
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.Parameters.Add("@DataRows", dataStringToProcess.ToString());
mySqlCommand.Parameters("@DataRows").Direction = ParameterDirection.InputOutput;

Answer 3

var pInOut = mySqlCommand.Parameters.Add("@DataRows", dataStringToProcess.ToString());
pInOut.Direction = ParameterDirection.InputOutput;

And then to read the output value after you've executed the command:

// assumes that the parameter is a string and that it could possibly be null
string value = Convert.IsDBNull(pInOut.Value) ? null : (string)pInOut.Value;

Answer 4

SqlParameter DataRows = new SqlParameter("@DataRows", SqlDbType.Text) 
{ Value = dataStringToProcess.ToString(), Direction = ParameterDirection.InputOutput};
mySqlCommand.Parameters.Add(DataRows);