Most of the major commands in the SqlClient class such as ExecuteNonQuery,
return an integer value which indicates the results of ROWCOUNT (if it hasn't
been set to "OFF") that reflects the number of records affected by the SQL
Statement.
Here is a code example of how to get the RETURN VALUE from a sproc call:
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "UpdatePrices";
SqlParameter paramReturnValue = new SqlParameter();
paramReturnValue.ParameterName = "@return_value";
paramReturnValue.SqlDbType = SqlDbType.Int;
paramReturnValue.Direction = ParameterDirection.ReturnValue;
SqlParameter paramPAF = new SqlParameter();
paramPAF.ParameterName = "@PriceMultiplier";
paramPAF.SqlDbType = SqlDbType.Decimal;
paramPAF.Direction = ParameterDirection.Input;
paramPAF.Value = textBoxPAF.Text;
SqlParameter paramNbrRows = new SqlParameter();
paramNbrRows.ParameterName = "@NbrRows";
paramNbrRows.SqlDbType = SqlDbType.Int;
paramNbrRows.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramReturnValue); // must be added first, parameter 0
cmd.Parameters.Add(paramPAF); // parameter 1
cmd.Parameters.Add(paramNbrRows); // parameter
After the stored procedure has been executed by calling the appropriate
execute method, the Parameters collection is used to retrieve data.
int returnValue = (int) cmd.Parameters["@return_value"].Value;
labelNbrPrices.Text = cmd.Parameters["@NbrRows"].Value.ToString() + "
prices updated";
Alternatively, ordinals could be used to retrieve values from the Parameters
collection.
int returnValue = (int) cmd.Parameters[0].Value; labelNbrPrices.Text =
cmd.Parameters[2].Value.ToString() + " prices updated";
Peter
