How to check result of updating stored procedures in C #

Question

Looking at some source code that I inherited, there is a piece of code that calls the SQL stored procedure that does the update.

The stored procedure returns -1 if something goes wrong:

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

C # code looks 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 "); } } 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, using System.Data.DataSet () with the Fill method.
Is there a more elegant way to check if the return value is -1?
Can I use ExecuteReader in this case?

Answer 1

Have you tried using ExecuteScalar ? This is for queries that return a single value:

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

Answer 2

As John correctly pointed out, you are not actually using the return value of the SP, but actually getting the first value of the first line, which will be ExecuteScalar a simpler way.

However, to get the return value from SP (for example, from code like RETURN @i; in 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; 

Answer 3

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

Your question is not clear enough. You may need to do this with ReturnValue, as Lucero wrote.