none
Handling Sql Server errors in C# RRS feed

  • Question

  •  

    Hi.

     

    Dont know what I do wrong but I cant get this to work.

     

    We have a rather complex database schema where records are referensed and regulated by foreign keys, constraints, triggers and indexes all over the place. To deal with deletion of records we want to "test" the deletion first, when the user indicates that he wants to delete something and based on this test tell him if its possible to delete the record.

     

    What we want to do is just run the Delete procedure with the extra parameter @TestIfPossible and if it is set - do a rollback at the end of the procedure and that way, either get an error on any index/foreign key/constraint... violation or of no violation - get no error - and by that way we know that the user can do a delete.

     

    Not very difficult I guess - and a pretty simple way of avoiding lots of checking and testing in the application. And it will work just as well for Inserts and Updates.

     

    But - how can I catch the errors from the constraint violations occuring in the database? We call

    int returnValue = db.ExecuteNonQuery( ... ) to do the test but the only error message we get is that returnValue is -1. I want to retrieve the error message from the database. Ive added try catch but that catches nothing. Maybe its pretty obvious but I dont see it. What I want is this:

     

     "The DELETE statement conflicted with the REFERENCE constraint "FK_Xyz". The conflict occurred in database "Xyz123", table "dbo.Xyz", column 'myColumn'. The statement has been terminated."

     

    So I can analyse it in my C# code and give the user a proper error message.

     

    Thankyou!

     

    Monday, November 26, 2007 12:19 AM

All replies

  • I assume you are using

     

    catch (Exception ex)

    {

     

    Did you try using

     

    catch (SqlException ex)

    {

     

    You get a lot of options with this exception
    Monday, November 26, 2007 3:09 AM
  • Yes I tried all of these:

     

    catch (SqlException se)

    {

     

    }

    catch (System.InvalidOperationException ioe)

    {

     

    }

    catch (System.Exception ex)

    {

     

    }

     

    And none of them catches anything.

    Monday, November 26, 2007 9:13 AM
  • Instead of returning -1 in the stored proc then Raiseerror

     

    Monday, November 26, 2007 10:42 AM
  • But i dont return anything from the SP. And do I have to do Raiserror? I mean - there is an error in the sp (some violation of a unique index for instance) so the SP goes wrong - why would I have to do a Raiserror then? Thats what the SP does already isnt it?

     

    Monday, November 26, 2007 11:54 AM
  • Are you converting the SqlException to a string?

     

    string myException = se.ToString;

     

    There's no reason it shouldnt' work.

     

    Adam

    Monday, November 26, 2007 12:04 PM
  • No. I never get a chance to do anything with any exception since I dont receive any.

     

    Are you sure it should work? I mean - should I get an SqlException when running an Insert-statement in a

    ExecuteNonQuery that violates a Not Null constraint or anything like that? Because when I run this I dont get any SqlException - so what could be wrong then?

     

    Any help is appreciated.

     

     

     

    Monday, November 26, 2007 12:42 PM
  • My guess would be you have some kind of error handling in your procedure, then you rollback, and no error comes from the procedure.

     

    If catch exception doesn't catch anything then nothing is raised, at least that is my guess.

     

    Monday, November 26, 2007 2:09 PM
  • Yes it should be like that of course. But I have removed all rollbacks just because of that possibility. I read somewhere that sql server only throws errors to the calling application if the error is considered critical enough. I cant see how that could be the case here - what would be considered critical enough if not this thing that makes my insert abort.

     

    But it seems that you guys thinks it should work so I guess its me doing something wrong anyhow right :-)

    Monday, November 26, 2007 3:03 PM
  • If you don't show your source, there is less chance to get a complete solution. That error is for deleting record that is referenced by other record has severity level 16 which will be transfered to your application as error. Why you are not catching it is the problem of the code, t-sql or c# one. There are information errors(warnings) with lower severity then 10 which are not transfered to client applications by default, but this is not that case.

     

    Monday, November 26, 2007 10:02 PM
  • Well - my bad. Thats what you get for using somebody elses code without taking the time to find out how it works. The error thrown from Sql Server was dealt with in the database-function i was using and that function only returned -1 to me when something went wrong. Thanks anyway everyone that replied - its amazing to see all these helpful people.

    Tuesday, November 27, 2007 9:33 AM