SQL Server: Rethrow exception with the original exception number
asp.net, c#, exception, sql, sql-server-2008
Solution
Thank you guys for your answers. Getting the error from the message of the re-thrown excetpion was something I had already done.
@gbn I also liked the gbn answer, but I will stick to the this answer as it is the one that works best and I am posting it here hoping it will also be useful for others.
The answer is using transactions in the application. If I don't catch the exception in the stored procedure I will get the original number in the SqlException object. After catching the original exception in the application, I write the following code
transaction.Rollback();
Otherwise:
transaction.Commit();
It's much simpler than I firstly expected!
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx
Problem
I am using a TRY CATCH block in a stored procedure where I have two INSERT instructions. If something goes wrong, the CATCH block takes care of rolling back all changes made and it works fine, except one thing! The exception caught by my ASP.NET application is a SqlException with number 50000. This is not the original number! (the number I was expecting was a 2627) In the Message property of the exception I can see the original exception number and message formated. How can I get the original exception number? ``` try { // ... code } catch (SqlException sqlException) { switch (sqlException.Number) { // Name already exists case 2627: throw new ItemTypeNameAlreadyExistsException(); // Some other error // As the exception number is 50000 it always ends here!!!!!! default: throw new ItemTypeException(); } } ``` Right now the return value is already being used. I guess that I could use an output parameter to get the exception number, but is that a good idea? What can I do to get the exception number? Thanks PS: This is needed because I have two INSERT instructions.