How to check Stored Procedures update result on C#

Looking on some source code I've inherited, there's a snippet of code that calls a SQL stored procedure making an Update.

The stored procedure returns -1 in case something goes wrong:

IF @@Error <> 0
    BEGIN
        ROLLBACK TRAN
        SELECT -1
        RETURN
    END
COMMIT TRAN
SELECT 0

The C# code is something like this:

    System.Data.SqlClient.SqlDataReader myReader;
    try{
        SqlDbConnection.Open();
        SqlDbCommand.Connection = SqlDbConnection;
        SqlDbCommand.CommandType = System.Data.CommandType.StoredProcedure;
        SqlDbCommand.CommandText = "StoredProcedured_UpdateFoo";
        SqlDbCommand.Parameters.Clear();
        SqlDbCommand.Parameters.Add("@FooData", SqlDbType.DateTime); SqlDbCommand.Parameters["@FooData"].Value = System.DateTime.Now.ToString("yyyy-MM-dd");
        myReader = SqlDbCommand.ExecuteReader();   
        if (myReader.Read())
           {
             if (int.Parse(myReader.GetValue(0).ToString()) == -1) throw new ErrorDataTxRx("Error FOO ");
     开发者_Go百科      }
    } finally {
      if (SqlDbConnection.State != ConnectionState.Closed){
           SqlDbConnection.Close();
      }

      if (myReader != null){              
          if (!myReader.IsClosed) myReader.Close();
      }
    }

I also see part of the same code, checking the same thing using System.Data.DataSet() with Fill method.

Is there a more elegant way to check if the returned value is -1?

Is it OK to use an ExecuteReader in this case?

Have you tried using ExecuteScalar? That's designed for queries which return a single value:

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored

As Jon correctly noted, you're actually not using the return value of the SP, but actually getting the first value of the first row, which is what ExecuteScalar would do in a simpler way.

However, in order to get the return value from the SP (e.g. from something like RETURN @i; in the SP SQL code), you need to add a new Parameter and set its direction to ReturnValue, something like this:

SqlParameter returnValueParam = new SqlParameter();
returnValueParam.DbType = DbType.Int32;
returnValueParam.IsNullable = false;
returnValueParam.Direction = ParameterDirection.ReturnValue;
SqlDbCommand.Parameters.Add(returnValueParam);
// execute SP here...
int returnValue = (int)returnValueParam.Value;

Use ExecuteNonQuery instead of the reader and you will get the number of rows affected.

Your question is not clear enough. You may need to do it with a ReturnValue like Lucero has written.