Calling stored procedure with return value
You need to add return parameter to the command:
using (SqlConnection conn = new SqlConnection(getConnectionString()))using (SqlCommand cmd = conn.CreateCommand()){ cmd.CommandText = parameterStatement.getQuery(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("SeqName", "SeqNameValue"); var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int); returnParameter.Direction = ParameterDirection.ReturnValue; conn.Open(); cmd.ExecuteNonQuery(); var result = returnParameter.Value;}
I know this is old, but i stumbled on it with Google.
If you have a return value in your stored procedure say "Return 1" - not using output parameters.
You can do the following - "@RETURN_VALUE" is silently added to every command object. NO NEED TO EXPLICITLY ADD
cmd.ExecuteNonQuery(); rtn = (int)cmd.Parameters["@RETURN_VALUE"].Value;
The version of EnterpriseLibrary on my machine had other parameters.This was working:
SqlParameter retval = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int); retval.Direction = System.Data.ParameterDirection.ReturnValue; cmd.Parameters.Add(retval); db.ExecuteNonQuery(cmd); object o = cmd.Parameters["@ReturnValue"].Value;