In-Out Parameter for SqlCommand

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());

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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;

Method 2

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;

Method 3

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;

Method 4

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0