Recently we changed our DAC layer from using inline SQL to stored procedures in the database. On some of these SQL, we did record deletion (usually only 1 record), and we just execute it via IDBCommand.ExecuteNonQuery() and then check the return value to see how many records were affected (which should be 1) for verification that the query actually does something.
With the change to stored procedure, we just return 1 in the stored procedure if the delete is successful. However, the calling code then started to show these deletions as errors. Apparently ExecuteNonQuery only returns the number of affected rows on SELECT, INSERT and DELETE statements; for everything else it returns -1. So I tried to figure out how to get a return value from a stored procedure.
Let's assume a simplistic stored procedure as follows:
|
ALTER PROC ReturnOnly
AS
BEGIN
RETURN 5
END |
You can't use ExecuteScalar to get the returned value, and ExecuteNonQuery will always return -1. To get the value back, you need to add a return value parameter to the command. The name of the parameter is not important. The code to get the value returned by that procedure will be as follows:
|
private int ExecuteStoredProcedure(string storedProcedureName)
{
SqlConnection connection = new SqlConnection(connectionString);
// Command - specify as StoredProcedure
SqlCommand command = new SqlCommand(storedProcedureName, connection);
command.CommandType = CommandType.StoredProcedure;
// Return value as parameter
SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnValue);
// Execute the stored procedure
connection.Open();
command.ExecuteNonQuery();
connection.Close();
return Convert.ToInt32(returnValue.Value);
}
|