What is the best way to read a sql server error return?

Question

How best to read an error return from sql server in a C# web form application?

I got the following..

No sql server;

END TRY
BEGIN CATCH
    Raiserror('Erro ao gerar os dados', 18, 1);
      return;
END CATCH; 

No c#

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "minhapro";
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Data", rdpDtMedicao.SelectedDate.ToString());
cmd.Parameters.AddWithValue("@Filial", Filial);
cmd.Parameters.AddWithValue("@UserId", cfg.UserID);
cmd.CommandTimeout = 1800;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
try
{
    conn.Open();
    var teste =  cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
    conn.Close();
}

I have seen that the Executenonquery returns a int , but I want it to be my mistake.

Answer 1

You’re hiding your mistake by doing this

catch (Exception ex)
{
}

Don’t do this. Hide the exceptions never is a good idea. Probably just take out this block catch will help you enough.

It is very common to think that "programming oriented to try-catch" is a good option, but in fact the exceptions should only be captured in specific cases, when you know what (and how) to do after capturing them.

Another important thing, this code

BEGIN CATCH
    Raiserror('Erro ao gerar os dados', 18, 1);
    return;
END CATCH; 

also does nothing very useful. You are hiding the actual error and putting a "beautiful message". This is not ideal.

Imagine that this procedure/function error in production environment, the error that the application will receive will be Erro ao gerar os dados, when in fact he should be telling you where and why the mistake broke.

About your comment

in the case of Raiserror('Error generating data', 18, 1); ... this will be shown to the user when he is generating the data of a demo in Textbox. I don’t want to blow up an msg of error that the guy won’t even know what it is.

Exceptions do not serve to inform something to the end user. They serve to show the developer that something is wrong. First of all it makes no difference whether the user knows what the error message is saying or not, it will still be an error message and he won’t be able to do anything without the help of a support or developer. Another important point is that if any Exception is being released because there is some error in the code and first thing you will want to do when you encounter an error is fix it, it is not?

I can understand your intention to not scare the user with giant error messages and code in the middle. The most indicated is you capture the exceptions generic on the "more top" layer of your application, something like, the main screen, the entry point or something similar.

---

Take a look at Best way to deal with Exceptions. Ali has good answers on this subject.

Answer 2

Create a parameter to receive the return of your SQL and add to the parameters of cmd, something like:

SqlParameter p = new SqlParameter("@Nome", DbType.String);
p.Direction = ParameterDirection.Output; 

cmd.Parameters.Add(p);

And then in your SQL code you set the desired return in this variable. After Executenonquery you just take back the contents of the variable.