locked
Transaction rollback RRS feed

  • Question

  • I have sql stored procedures in which I included a begin try - begin tran - commit tran - end try - begin catch - rollback tran - end catch. In my c# winform I used to include a try catch in the ado.net and when it went to the catch I let the user know that the transaction had an error. Now though if the stored procedure properly executes the rollback on the error then it doesn't catch in c# since it finished successfully (through a rollback). So even when really there was an error and it rolled back the user doesn't know about it. How can I fix that? 

    Also, sometimes there is an error in a stored procedure before it even starts the begin tran. For example if the user inputs a number that is too high for an int 32 into a table valued parameter so then it does catch in c# and doesn't go through the rest of the procedure. But, I would like one uniform way of finding errors and letting the user know via c#. How can I do that?


    Debra has a question

    Monday, April 11, 2016 7:21 PM

Answers

All replies

  • You have 2 ways

    a) Transactions via C#

    C# Begin Transaction

    Another Example

    b) FROM SQL: USE Try catch with begin and end tran

    In the try catch on SQL use RAISEERROR, this would ensure an error is thrown from SQL,and should be returned as sql error to C#

    BEGIN TRAN
            BEGIN TRY

       

    PRINT'Commit Transaction Ran Successfully'
    COMMIT TRAN
    END TRY

    BEGIN CATCH
        PRINT 'Inside Catch'

    RAISERROR (N'The current database ID is:%d, the database name is: %s.', 10, -- Severity.---- Increase the severity 1, -- State. @DBID, -- First substitution argument. @DBNAME); -- Second substitution argument.

    --Use this inside message for meaning full error    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() as ErrorState,ERROR_PROCEDURE() as ErrorProcedure,ERROR_LINE() as ErrorLine,ERROR_MESSAGE() as ErrorMessage;


    ROLLBACK TRAN
    END CATCH       


    Abhinav http://bishtabhinav.wordpress.com/

    Monday, April 11, 2016 7:28 PM
  • In your begin catch /end catch statement simply throw the error back. This way your C# code will always get the SqlException back.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, April 11, 2016 8:03 PM
  • You need to re-raise the error in the CATCH block to let the application know that things went south.

    I have an article on my web site which gives you an introduction to error handling in SQL Server:
    http://www.sommarskog.se/error_handling/Part1.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, April 11, 2016 10:04 PM
  • How do you do that? Is it enough to just write THROW in the catch? I have sql 2008 and I saw that throw is for 2012 and up.

    Debra has a question



    Tuesday, April 12, 2016 3:51 PM
  • Tuesday, April 12, 2016 3:58 PM