What is the best way to return the result of the execution of a SQL Server stored procedure back to the .NET code?
If I want to return the success or failure of a bunch of deletes in a transaction, should I 'Select' something, or maybe something like RETURN(1) or RETURN(0), or do I communicate number of rows affected?
Then will this be the return value for ExecuteNonQuery()?
Yes, if you use ExecuteNonQuery(), it will return the number of rows affected. If you want to specify a value to return in your stored procedure, you can use ExecuteScalary().
You can also use an output parameter to return values. In the SQL procedure, declare the parameter as an output parameter (ex. @ret int output). In the .NET code, set the direction of the parameter to output. After the execution line, you can check this value by reading the ".Value" property
ExecuteNonQuery always returns the number of rows affected for the LAST insert, update or delete done in your transaction. You don't need to RETURN(1) or RETURN(0).
If you're doing a bunch of deletes in a transaction and it fails then an SqlException would be thrown so there isn't really a need to return a result per se. Just catch the exception and handle it instead.
And to answer your question, if the result you wanted is specifically the number of affected records of the last executed statement then using ExecuteNonQuery is the best. If you wanted some other more specific result then the best might be to use an SqlCommand with a return SqlParameter and return the specific value in your return parameter.
You'll frequently see the return value used to indicate a problem with return 0 indicating that the SP completed with success. I generally use to return information about what has happened where the error itself wouldn't generate an exception. Across most of my apps I have a standard set of return codes that I re-use.
To provide more detailed output in specific scenarios you can then use the output parameters as well