Transaction rollback

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

Answer 1

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/

Answer 2

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

Answer 3

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, [e-mail]

Answer 4

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

Answer 5

Check out this article http://social.technet.microsoft.com/wiki/contents/articles/20002.structured-error-handling-mechanism-in-sql-server-2012.aspx

---

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


My blog

My TechNet articles