CODEWITHSUNDEEP
c#sqlsql-serverexception
Murtaza Mandvi
Murtaza Mandvi

Reputation: 10998

Passing SQL Server exception to .net

Stored procedure :

Procedure GetLocalConfig
(
    @ConfigId int
)
AS
SET NOCOUNT ON; 
BEGIN TRY
BEGIN TRANSACTION
SELECT 1/0
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    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;  


     IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
        RETURN 
END CATCH

.Net code calling the Stored Procedure :

try
{
return DatabaseFactory.CreateDatabase(ConnectionStringConfigName)
       .ExecuteSprocAccessor(DbCommands.GetLocalConfig, new LocalConfigDtoMapper(), configId)
       .Single();

}
catch (Exception ex)
{
   // Exception : Sequence contains no elements???
}

If I execute this SP from SSMS I get the actual exception :

ErrorNumber ErrorSeverity ErrorState  ErrorProcedure ErrorLine   ErrorMessage
8134        16            1           GetLocalConfig 54           Divide by zero error encountered.

I would like to see this exception in my try catch block for .Net however I keep getting Sequence contains no elements.

After some research I found I could RAISEERROR inside my stored procedure Try catch block :

DECLARE @errnum nchar(5), @errmsg nvarchar(2048);
SELECT
   @errnum = RIGHT('00000' + ERROR_NUMBER(), 5),
   @errmsg = @errnum + ' ' + ERROR_MESSAGE();
RAISERROR (@errmsg, 16, 1);

Now when I run the SP using SSMS I get :

Msg 50000, Level 16, State 1, Procedure GetLocalConfig, Line 70
8134 Divide by zero error encountered.

But still .Net is giving me the same exception, how do I get the exception to be passed over to .Net in this case?

Upvotes: 2

Views: 2437

Answers (2)

Jodrell
Jodrell

Reputation: 35746

I assume the error row you return from the GetLocalConfig SP does not map using the LocalConfigDtoMapper, its expecting a different schema.

Therfore you suppress the actual exeption, and return a row with the wrong schema. This results a zero length sequence or empty enumerable being returned to the caller. When the Single() check asserts that you have one row only, it throws the appropriate exception because you have none.

If you want to rollback on error look at these duplicates

How to rollback a transaction in TSQL when string data is truncated?

SQL Server 2008 Transaction, rollback required?

SQL Server - transactions roll back on error?

Essentialy

You can re raise the error with RAISERROR in your CATCH block. You can get the original "exception" details with the ERROR_... functions

or

SET XACT_ABORT ON

Upvotes: 1

Murtaza Mandvi
Murtaza Mandvi

Reputation: 10998

The TRY CATCH block inside the SP was suppressing the actual error message, removing the that I get the actual exception in my application.

Upvotes: 0

Related Questions