Asp.net TRY CATCH not working when surrounding stored procedure

First Last

5y1.5k.NET

In my web api data access layer, the Asp.net TRY CATCH is NOT catching the fail of the stored procedure. Note: this template of code is used threw out my web api data access layer and works fine. Just not in this case. Weird!!

I expect the stored procedure to fail as there are no rows that meet the criteria. So in that case, I raise an error (RAISERROR) and it writes an entry to my error log table.

This happens when I execute it via SSMS or running my web app that calls the web api that executes the stored procedure.

The error log table with 2 entries. 1 from running the stored procedure via SSMS and the other from running my web app.

The problem is the web api data access layer code execution does NOT catch the error returned by the stored procedure. It does NOT go into the CATCH.

Here is the web api data access layer function called from my web api controller. It has the TRY CATCH that is NOT working properly:

public List GetBlogCategorysInBlogsPublishedList(string userName, string ipAddress)
{
string userFriendlyMessage = "Unable to get the blog categorys in the blogs published list. We have been notified and are working to resolve this. Please do not continue.";
List blogPublishedCategoryList = new List();
SqlDataReader blogCategorysInBlogsDataReader = null;
try
{
dbFunc.OpenDB();
SqlCommand cmd = new SqlCommand("dbo.GetBlogCategorysInBlogsPublished", dbFunc.objConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@a_UserName", userName);
cmd.Parameters.AddWithValue("@a_IpAddress", ipAddress);
blogCategorysInBlogsDataReader = cmd.ExecuteReader();
// Loop thru the results returned.
while (blogCategorysInBlogsDataReader.Read())
{
// Add to the list of BlogPublishedCategory - creates a new row for the collection.
blogPublishedCategoryList.Add(new BlogPublishedCategory
{
BlogCategoryId = Convert.ToInt32(blogCategorysInBlogsDataReader["BlogCategoryId"]),
BlogCategoryDescr = blogCategorysInBlogsDataReader["BlogCategoryDescr"].ToString(),
});
}
// Return the blogPublishedCategoryList object.
return blogPublishedCategoryList;
}
catch (SqlException sqlex)
{
if (sqlex.Message.Contains("Critical"))
{
// A "critical" error coming from the stored procedure.
// So, send an alert email to a staff member (an Admin), but do NOT process the error log as it has been done already
// in the stored procedure.
currentDateTime = DateTime.Now;
sendAlertEmailResult = SendAlertEmailToStaff(currentDateTime, userName, ipAddress);
if (sendAlertEmailResult == "")
{
throw new Exception(userFriendlyMessage);
}
else
{
throw new Exception("In DataAccessLayer/GetBlogCategorysInBlogsPublishedList(). Sending an alert email for the initial sql exception error: " + sqlex.Message + ". Now getting this error: " + sendAlertEmailResult);
}
}
else
{
// Not coming from the stored procedure.
errorMessage = "Sql Exception Error in DataAccessLayer/GetBlogCategorysInBlogsPublishedList(). Using 'GetBlogCategorysInBlogsPublished' s/p. Error: " + sqlex.Message;
// Log the error and send an alert email.
currentDateTime = DateTime.Now;
processErrorLogAndSendAlertEmailResult = ProcessErrorLogAndSendAlertEmail(currentDateTime, userName, errorMessage, additionalInfoForLog, ipAddress);
if (processErrorLogAndSendAlertEmailResult != "")
{
throw new Exception("Error in DataAccessLayer/GetBlogCategorysInBlogsPublishedList(). Using 'GetBlogCategorysInBlogsPublished' s/p. Logging the initial sql exception error: " + sqlex.Message + ". Now getting this error: " + processErrorLogAndSendAlertEmailResult);
}
else
{
throw new Exception(userFriendlyMessage);
}
}
}
catch (Exception ex)
{
errorMessage = "Error in DataAccessLayer/GetBlogCategorysInBlogsPublishedList(). Using 'GetBlogCategorysInBlogsPublished' s/p. Error: " + ex.Message;
// Log the error and send an alert email.
currentDateTime = DateTime.Now;
processErrorLogAndSendAlertEmailResult = ProcessErrorLogAndSendAlertEmail(currentDateTime, userName, errorMessage, additionalInfoForLog, ipAddress);
if (processErrorLogAndSendAlertEmailResult != "")
{
throw new Exception("Error in DataAccessLayer/GetBlogCategorysInBlogsPublishedList(). Using 'GetBlogCategorysInBlogsPublished' s/p. Logging the initial error: " + ex.Message + ". Now getting this error: " + processErrorLogAndSendAlertEmailResult);
}
else
{
throw new Exception(userFriendlyMessage);
}
}
finally
{
if (blogCategorysInBlogsDataReader != null)
{
// Close the reader.
blogCategorysInBlogsDataReader.Close();
}
// Close the database.
dbFunc.CloseDB();
}
}

The GetBlogCategorysInBlogsPublished stored proc:

USE [DBGbngDev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].
[GetBlogCategorysInBlogsPublished]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].GetBlogCategorysInBlogsPublished
END
GO
CREATE procedure [dbo].[GetBlogCategorysInBlogsPublished]
@a_UserName varchar(250) = NULL,
@a_IpAddress varchar(250) = NULL
AS
BEGIN
DECLARE @RowCount int,
@ReturnCode int,
@CurrentDateTime datetime,
@Message varchar(max) = '',
@ApiMessageOut varchar(max),
@ApiAccessSwitchOut bit
DECLARE @ErrorLine AS INT;
DECLARE @ErrorMessage AS VARCHAR(2048);
DECLARE @ErrorNumber AS INT;
DECLARE @ErrorSeverity AS INT;
DECLARE @ErrorState AS INT;
DECLARE @DatabaseName AS VARCHAR(255);
DECLARE @ServerName AS VARCHAR(255);
DECLARE @ErrorDescription AS VARCHAR(MAX);
DECLARE @CRLF AS VARCHAR(2);
SELECT @CurrentDateTime = GETDATE()
BEGIN TRY
SET NOCOUNT ON;
IF ( ( @a_UserName = '' OR @a_UserName IS NULL )
OR ( @a_IpAddress = '' OR @a_IpAddress IS NULL ) )
BEGIN
SELECT @Message = 'Critical Error - procedure GetBlogCategorysInBlogsPublished - invalid
parameters. They cannot be null or empty.'
IF ( @a_UserName = '' OR @a_UserName IS NULL )
BEGIN
SET @a_UserName = 'No "user name" parameter provided.'
END
IF ( @a_IpAddress = '' OR @a_IpAddress IS NULL )
BEGIN
SET @a_IpAddress = 'No "ip address" parameter provided.'
END
RAISERROR (@Message, 16, 1)
END
ELSE
BEGIN
-- Do the API security check. If this user is valid, you can continue with further
processing.
SELECT @ReturnCode = -1
EXECUTE @ReturnCode = dbo.GetApiAccess
@CurrentDateTime,
@a_UserName,
@a_IpAddress,
@a_ApiAccessSwitchFromGet = @ApiAccessSwitchOut OUTPUT,
@a_ApiMessageFromGet = @ApiMessageOut OUTPUT
IF @ReturnCode = -1
BEGIN
RAISERROR ('Critical Error - procedure GetBlogCategorysInBlogsPublished failed during execute of procedure GetApiAccess.', 16, 1 )
END
-- Web api access was granted.
IF @ApiAccessSwitchOut = 1
BEGIN
SELECT DISTINCT (a.BlogCategoryId) as BlogCategoryId
,a.BlogCategoryDescr as BlogCategoryDescr
FROM dbo.BlogCategory a
JOIN dbo.Blog b On ( a.BlogCategoryId = b.BlogCategoryId )
WHERE ( b.PublishSwitch = 1 AND b.CanBeSeenSwitch = 1 )
ORDER BY a.BlogCategoryId asc
SELECT @ReturnCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF @ReturnCode <> 0
BEGIN
SELECT @Message = 'Critical Error - procedure GetBlogCategorysInBlogsPublished failed during the select.'
RAISERROR (@Message, 16, 1)
END
IF @RowCount = 0
BEGIN
SELECT @Message = 'Critical Error - procedure GetBlogCategorysInBlogsPublished failed during the select. There are no BlogCategory entries.'
RAISERROR (@Message, 16, 1)
END
END
ELSE
BEGIN
-- Web api access was NOT granted. The user did not have permission to use the web api or there is an error in the GetApiAccess procedure.
-- Pass the message returned from the GetApiAccess procedure.
RAISERROR (@ApiMessageOut, 16, 1 )
END
END
-- Returns success.
RETURN 0
END TRY
BEGIN CATCH
SELECT
@ErrorLine = ERROR_LINE()
-- ERROR_MESSAGE() contains the RAISERROR message raised above.
, @ErrorMessage = ERROR_MESSAGE()
, @ErrorNumber = ERROR_NUMBER()
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE()
, @DatabaseName = CAST(DB_NAME() AS VARCHAR)
, @ServerName = CAST(SERVERPROPERTY ( 'ServerName' ) AS VARCHAR)
, @CRLF = CHAR(13) + CHAR(10)
SET @ErrorDescription = 'From stored procedure: ' + ERROR_PROCEDURE()
+ '. Error Line: ' + CAST(@ErrorLine AS VARCHAR)
+ '. Error Message: ' + @ErrorMessage
+ ' Error Number: ' + CAST(@ErrorNumber AS VARCHAR)
+ '. Error Severity: ' + CAST(@ErrorSeverity AS VARCHAR)
+ '. Error State: ' + CAST(@ErrorState AS VARCHAR)
+ '. Database Name: ' + @DatabaseName
+ '. Server Name: ' + @ServerName
IF (XACT_STATE() <> 0)
BEGIN
ROLLBACK TRANSACTION
END
IF (@ErrorSeverity = 16) AND (@ErrorState = 2)
-- If it's a validation error (which we use 16/2 for).
BEGIN
-- Just send the validation message.
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END
ELSE
BEGIN
-- Log the critical error.
BEGIN TRY
EXEC dbo.InsertBlogErrorLog
@a_LogDateTime = @CurrentDateTime,
@a_UserName = @a_UserName,
@a_UserIpAddress = @a_IpAddress,
@a_ObjectID = @@PROCID,
@a_MessageType = 'S/P Critical Error',
@a_LogMessage = @ErrorDescription
END TRY
BEGIN CATCH
-- Stack the messages.
SELECT @Message = 'Critical Error - procedure InsertBlogErrorLog failed. A log entry cannot be made. Do not continue. Contact IT. Initial error message: ' + @ErrorMessage
RAISERROR(@Message, 16, 1)
END CATCH
SELECT @message = 'Critical Error - do not continue. Contact IT and provide this log date: ' + Convert(VARCHAR, @CurrentDateTime,21)
RAISERROR(@Message, 16, 1)
END
-- Returns failure.
RETURN 1
END CATCH
END

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar5y ago
Hi,
You will get sql exception in inner exception, you need catch inside generic exception but need to cas innerExcption as sqlException, remove first SqlException catch block and try below
catch (Exception ex)
          {
var sqlError = ex.InnerException as SqlException; //You need to cast inner exeption as sqlException
    if(sqlError != null)
{
if (sqlex.Message.Contains("Critical"))
              {
                  // A "critical" error coming from the stored procedure.
                  // So, send an alert email to a staff member (an Admin), but do NOT process the error log as it has been done already
                  // in the stored procedure.

                  currentDateTime = DateTime.Now;
                  sendAlertEmailResult = SendAlertEmailToStaff(currentDateTime, userName, ipAddress);

                  if (sendAlertEmailResult == "")
                  {
                      throw new Exception(userFriendlyMessage);
                  }
                  else
                  {
                      throw new Exception("In DataAccessLayer/GetBlogCategorysInBlogsPublishedList(). Sending an alert email for the initial sql exception error: " + sqlex.Message + ". Now getting this error: " + sendAlertEmailResult);
                  }
              }
              else
              {
                  // Not coming from the stored procedure.
                  errorMessage = "Sql Exception Error in DataAccessLayer/GetBlogCategorysInBlogsPublishedList(). Using 'GetBlogCategorysInBlogsPublished' s/p. Error: " + sqlex.Message;

                  // Log the error and send an alert email.
                  currentDateTime = DateTime.Now;
                  processErrorLogAndSendAlertEmailResult = ProcessErrorLogAndSendAlertEmail(currentDateTime, userName, errorMessage, additionalInfoForLog, ipAddress);

                  if (processErrorLogAndSendAlertEmailResult != "")
                  {
                      throw new Exception("Error in DataAccessLayer/GetBlogCategorysInBlogsPublishedList(). Using 'GetBlogCategorysInBlogsPublished' s/p. Logging the initial sql exception error: " + sqlex.Message + ". Now getting this error: " + processErrorLogAndSendAlertEmailResult);
                  }
                  else
                  {
                       throw new Exception(userFriendlyMessage);
                  }
              }
}
}
+1
Sachin Singh5y ago
yes , everything seems ok in your code
check if you have any unclosed if else or as begin and end. Remember after every if there must a begin that should be close with end otherwise your SP looks okay.
+1
First Last5y ago
Sachin, it does not get to that line as it does NOT enter the Catch. That's the problem.
Here is a similar example whereby I force an error in a stored procedure to show that a similar (almost identical) web api data access layer function DOES catch the stored procedure error.

The error log table with the entry. From running my web api.

The web api data access layer code execution that DOES catch the error returned by the stored procedure. It DOES go into the CATCH and does the processing I expect.

Here is the web api data access layer function called from my web api controller. It has the TRY CATCH that IS working properly:
public List GetBlogCategoryList(string userName, string ipAddress)
      {
          string userFriendlyMessage = "Unable to get blog categories. We have been notified and are working to resolve this. Please do not continue.";

          List blogCategoryList = new List();

          SqlDataReader blogCategoryDataReader = null;

          try
          {
              dbFunc.OpenDB();

              SqlCommand cmd = new SqlCommand("dbo.GetBlogCategory", dbFunc.objConn);
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Parameters.Clear();

              cmd.Parameters.AddWithValue("@a_UserName", userName);
              cmd.Parameters.AddWithValue("@a_IpAddress", ipAddress);

              blogCategoryDataReader = cmd.ExecuteReader();

                while (blogCategoryDataReader.Read())
              {
                  blogCategoryList.Add(new BlogCategory
                  {
                      BlogCategoryId = Convert.ToInt32(blogCategoryDataReader["BlogCategoryId"]),
                      BlogCategoryDescr = blogCategoryDataReader["BlogCategoryDescr"].ToString(),
                  });
              }

              return blogCategoryList;
          }
          catch (SqlException sqlex)
          {
              if (sqlex.Message.Contains("Critical"))
              {
                  // A "critical" error coming from the stored procedure.

                  // So, send an alert email to a staff member (an Admin), but do NOT process the error log as it has been done already
                  // in the stored procedure.

                  currentDateTime = DateTime.Now;
                  sendAlertEmailResult = SendAlertEmailToStaff(currentDateTime, userName, ipAddress);

                  if (sendAlertEmailResult == "")
                  {
                      throw new Exception(userFriendlyMessage);
                  }
                  else
                  {
                      throw new Exception("In DataAccessLayer/GetBlogCategoryList(). Sending an alert email for the initial sql exception error: " + sqlex.Message + ". Now getting this error: " + sendAlertEmailResult);
                  }
              }
              else
              {
                  // Not coming from the stored procedure. Like if the stored procedure above was not named properly, does not exist, parameter missing, etc.
                  errorMessage = "Sql Exception Error in DataAccessLayer/GetBlogCategoryList(). Using 'GetBlogCategory' s/p. Error: " + sqlex.Message;

                  // Log the error and send an alert email.
                  currentDateTime = DateTime.Now;
                  processErrorLogAndSendAlertEmailResult = ProcessErrorLogAndSendAlertEmail(currentDateTime, userName, errorMessage, additionalInfoForLog, ipAddress);

                  if (processErrorLogAndSendAlertEmailResult != "")
                  {
                      throw new Exception("Error in DataAccessLayer/GetBlogCategoryList(). Using 'GetBlogCategory' s/p. Logging the initial sql exception error: " + sqlex.Message + ". Now getting this error: " + processErrorLogAndSendAlertEmailResult);
                  }
                  else
                  {
                      throw new Exception(userFriendlyMessage);
                  }
              }
          }
          catch (Exception ex)
          {
              errorMessage = "Error in DataAccessLayer/GetBlogCategoryList(). Using 'GetBlogCategory' s/p. Error: " + ex.Message;

              currentDateTime = DateTime.Now;
              processErrorLogAndSendAlertEmailResult = ProcessErrorLogAndSendAlertEmail(currentDateTime, userName, errorMessage, additionalInfoForLog, ipAddress);

              if (processErrorLogAndSendAlertEmailResult != "")
              {
                  throw new Exception("Error in DataAccessLayer/GetBlogCategoryList(). Using 'GetBlogCategory' s/p. Logging the initial error: " + ex.Message + ". Now getting this error: " + processErrorLogAndSendAlertEmailResult);
              }
              else
              {
                  throw new Exception(userFriendlyMessage);
              }
          }
          finally
          {
              if (blogCategoryDataReader != null)
              {
                  blogCategoryDataReader.Close();
              }

              dbFunc.CloseDB();
          }
      }
Here's the GetBlogCategory stored procedure (It fails as I force a rowcount = 0 ):
@a_UserName                       varchar(250) = NULL,
@a_IpAddress                      varchar(250) = NULL

AS
BEGIN

    DECLARE @RowCount int,
              @ReturnCode                int,
              @CurrentDateTime           datetime,
              @Message                   varchar(max) = '',
              @ApiMessageOut             varchar(max),
              @ApiAccessSwitchOut        bit

    SELECT @CurrentDateTime = GETDATE()

    DECLARE @ErrorLine      AS INT;
    DECLARE @ErrorMessage       AS VARCHAR(2048);
    DECLARE @ErrorNumber        AS INT;
    DECLARE @ErrorSeverity      AS INT;
    DECLARE @ErrorState     AS INT;
    DECLARE @DatabaseName       AS VARCHAR(255);
    DECLARE @ServerName     AS VARCHAR(255);
    DECLARE @ErrorDescription   AS VARCHAR(MAX);
    DECLARE @CRLF           AS VARCHAR(2);

    BEGIN TRY

       SET NOCOUNT ON;
       IF ( ( @a_UserName  = '' OR @a_UserName  IS NULL ) OR ( @a_IpAddress = '' OR @a_IpAddress IS NULL ) )
          BEGIN
             SELECT @Message = 'Critical Error - procedure GetBlogCategory - invalid parameters. They cannot be null or empty.'

             IF ( @a_UserName = '' OR @a_UserName IS NULL )
                 BEGIN
                    SET @a_UserName = 'No "user name" parameter provided.'
                 END

             IF ( @a_IpAddress = '' OR @a_IpAddress IS NULL )
                 BEGIN
                    SET @a_IpAddress = 'No "ip address" parameter provided.'
                 END

            RAISERROR (@Message, 16, 1)
          END
       ELSE
          BEGIN
               -- Do the API security check. If this user is valid, you can continue with further processing.
               SELECT @ReturnCode = -1
               EXECUTE @ReturnCode = dbo.GetApiAccess
                                  @CurrentDateTime,
                                  @a_UserName,
                                  @a_IpAddress,
                                  @a_ApiAccessSwitchFromGet = @ApiAccessSwitchOut OUTPUT,
                                  @a_ApiMessageFromGet = @ApiMessageOut OUTPUT

               IF @ReturnCode = -1
                    BEGIN
                       RAISERROR ('Critical Error - procedure GetBlogCategory failed during execute of procedure GetApiAccess.', 16, 1 )
                    END

               -- Web api access was granted.
               IF @ApiAccessSwitchOut = 1
                   BEGIN

                       SET @RowCount = 0

                       IF @RowCount = 0
                           BEGIN
                               SELECT @Message =  'Critical Error - procedure GetBlogCategory failed during the select. There are no entries.'
                               RAISERROR (@Message, 16, 1)
                           END
                   END
               ELSE
                   BEGIN
                        -- Web api access was NOT granted. The user did not have permission to use the web api or there is an error in the GetApiAccess procedure.
                        -- Pass the message returned from the GetApiAccess procedure.
                        RAISERROR (@ApiMessageOut, 16, 1 )
                  END
          END

       -- Returns success.
       RETURN 0

    END TRY

    BEGIN CATCH

        SELECT
                @ErrorLine = ERROR_LINE()
            -- ERROR_MESSAGE() contains the RAISERROR message raised above.
            , @ErrorMessage = ERROR_MESSAGE()
            , @ErrorNumber = ERROR_NUMBER()
            , @ErrorSeverity = ERROR_SEVERITY()
            , @ErrorState = ERROR_STATE()
            , @DatabaseName = CAST(DB_NAME() AS VARCHAR)
            , @ServerName = CAST(SERVERPROPERTY ( 'ServerName' ) AS VARCHAR)
            , @CRLF  = CHAR(13) + CHAR(10)


            SET @ErrorDescription = 'From stored procedure: '  + ERROR_PROCEDURE()
                                    + '. Error Line: '  + CAST(@ErrorLine AS VARCHAR)
                                                + '. Error Message: ' + @ErrorMessage
                            + ' Error Number: ' + CAST(@ErrorNumber AS VARCHAR)
                        + '. Error Severity: ' + CAST(@ErrorSeverity AS VARCHAR)
                            + '. Error State: ' + CAST(@ErrorState AS VARCHAR)
                            + '. Database Name: '  + @DatabaseName
                        + '. Server Name: ' + @ServerName

        IF (XACT_STATE() <> 0)
            BEGIN
               ROLLBACK TRANSACTION
            END

        IF (@ErrorSeverity = 16) AND (@ErrorState = 2)
            BEGIN
               RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
            END
         ELSE
            BEGIN
               -- Log the critical error.
               BEGIN TRY
                  EXEC dbo.InsertBlogErrorLog
                    @a_LogDateTime = @CurrentDateTime,
                    @a_UserName = @a_UserName,
                    @a_UserIpAddress = @a_IpAddress,
                    @a_ObjectID = @@PROCID,
                    @a_MessageType = 'S/P Critical Error',
                    @a_LogMessage = @ErrorDescription
               END TRY

               BEGIN CATCH
                  -- Stack the messages.
                  SELECT @Message = 'Critical Error - procedure InsertBlogErrorLog failed. A log entry cannot be made. Do not continue. Contact IT. Initial error message: ' + @ErrorMessage
                  RAISERROR(@Message, 16, 1)
               END CATCH

               SELECT @message = 'Critical Error - do not continue. Contact IT and provide this log date: ' + Convert(VARCHAR, @CurrentDateTime,21)
               RAISERROR(@Message, 16, 1)
            END

         -- Returns failure.
         RETURN 1

    END CATCH

END
+1
Sachin Singh5y ago
Remove below if condtion and then test which type of SqlException you are getting and accordingly set the error type
if (sqlex.Message.Contains("Critical"))
             {
+1