Asp.net TRY CATCH not working when surrounding stored procedure

Question

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