CODEWITHSUNDEEP
c#asp.netsql-serverwebforms
Nemanja
Nemanja

Reputation: 73

Sql Try Catch in asp.net

BEGIN TRY 
    INSERT INTO [something] ([something], [something])
        SELECT
            [ID] AS OrderNumber, [something], [something] 
        FROM 
            [something]
        WHERE 
            ID = @OrderNumber
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;
END CATCH

I catch some exception like this but I don't know how to show that error message in my web page. If I leave it like this code goes trough even if I get wrong data in purpose.

Upvotes: 1

Views: 678

Answers (2)

Scott Hannen
Scott Hannen

Reputation: 29222

If you don't need exception handling inside your stored procedure then you don't need a try/catch. You can just let your procedure throw its error.

In your application code you can catch the SqlException 

try
    // whatever
catch sqlException as SqlException
    // Now you have access to the properties of the SqlException
end try

Although I wouldn't show any of that detail to the user on the web page. Perhaps all they need is to know that there was an error. They probably don't want or need more than that. There's even a degree of security risk in telling users more than they need to know about what's behind the curtain.

Perhaps you do need the try/catch in your SQL so that you can roll back a transaction.

In SQL Server 2012 and after you can do this - just rethrow the original error.

begin catch
    -- Roll back transaction if needed
    throw
end catch

Before SQL Server 2012 you can do something like this, which isn't as good. Or you could set some output parameters. The trouble is that you can access details of the error but you can't rethrow the original error.

begin catch
    declare 
        @error_number int = error_number(),
        @error_severity int = error_severity(),
        @error_state int = error_state(),
        @error_procedure varchar(255) = error_procedure(),
        @error_line int = error_line(),
        @error_message varchar(1000) = error_message()

        -- Roll back transaction if needed
        -- Raise an error with the information from the original error.

    RAISERROR (N'Error number %d, severity %d, state %d, procedure %s, line %d, message: %s', @error_severity, @error_state,-- Message text.  
      @error_number, @error_severity, @error_state, @error_procedure, @error_line, @error_message); -- Second argument.  
end catch

Upvotes: 4

Jacob H
Jacob H

Reputation: 2505

Probably best to do the TRY CATCH in your C#, rather than in the procedure. If you do the latter you still have to handle the return values, which is more effort and no benefit unless you need to create specific return values from your proc.

https://learn.microsoft.com/en-us/dotnet/articles/csharp/language-reference/keywords/try-catch

Upvotes: 0

Related Questions