c# - Try...catch 在执行存储过程时不起作用
问题描述
在我的 web api 数据访问层中，Asp.net TRY CATCH 没有捕捉到存储过程的失败。注意：此代码模板用于丢弃我的 web api 数据访问层并且工作正常。只是在这种情况下不是。诡异的！！（我在底部包含了一个类似的工作示例）。
我希望存储过程会失败，因为没有符合条件的行。所以在这种情况下，我会引发一个错误 (RAISERROR)，它会在我的错误日志表中写入一个条目。
当我通过 SSMS 执行它或运行调用执行存储过程的 web api 的 web 应用程序时，会发生这种情况。
包含 2 个条目的错误日志表。1 通过 SSMS 运行存储过程，另一个来自运行我的 Web 应用程序。
问题是 web api 数据访问层代码执行没有捕获存储过程返回的错误。它不会进入 CATCH。
这是从我的 web api 控制器调用的 web api 数据访问层函数。它的 TRY CATCH 不能正常工作：
public List<BlogPublishedCategory>
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<BlogPublishedCategory> blogPublishedCategoryList = new
List<BlogPublishedCategory>();
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();
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"))
{
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
{
errorMessage = "Sql Exception Error in DataAccessLayer/GetBlogCategorysInBlogsPublishedList(). Using 'GetBlogCategorysInBlogsPublished' s/p. Error: " + sqlex.Message;
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;
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)
{
blogCategorysInBlogsDataReader.Close();
}
dbFunc.CloseDB();
}
}
GetBlogCategorysInBlogsPublished 存储过程（它失败，因为行数为 0）：
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.
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
这是一个类似的示例，我在存储过程中强制出错以显示类似（几乎相同）的 Web api 数据访问层函数确实捕获了存储过程错误。
带有条目的错误日志表。从运行我的网络 api。
捕获存储过程返回的错误的 web api 数据访问层代码执行。它确实进入了 CATCH 并进行了我期望的处理。
这是从我的 web api 控制器调用的 web api 数据访问层函数。它具有正常工作的 TRY CATCH：
public List<BlogCategory> 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<BlogCategory> blogCategoryList = new List<BlogCategory>();
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();
}
}
这是 GetBlogCategory 存储过程（它失败，因为我强制 rowcount = 0 ）：
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].
[GetBlogCategory]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].GetBlogCategory
END
GO
CREATE procedure [dbo].[GetBlogCategory]
@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
--SELECT BlogCategoryId as BlogCategoryId
-- ,BlogCategoryDescr as BlogCategoryDescr
--FROM dbo.BlogCategory
--ORDER BY BlogCategoryDescr asc
--SELECT @ReturnCode = @@ERROR,
-- @RowCount = @@ROWCOUNT
--IF @ReturnCode <> 0
-- BEGIN
-- SELECT @Message = 'Critical Error - procedure GetBlogCategory failed during the select.'
-- RAISERROR (@Message, 16, 1)
-- END
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
丹...我把我原来的 C# 循环代码放回去，我改变了失败的 s/p GetBlogCategorysInBlogsPublished 我完全删除了 SELECT DISTINCT 子句，只是通过设置 @Rowcount = 0 来强制 RAISERROR。Web api 函数中的 TRY CATCH捡起它并正确处理。
然后，我在 SSMS 中，使用了 SELECT DISTINCT 并运行了它。在结果选项卡中，我看到我得到 2 个结果，列标题和错误消息列。
2个结果可能是问题吗？
在消息选项卡中，我得到：消息 50000，级别 16，状态 1，第 26 行严重错误 - 过程 GetBlogCategorysInBlogsPublished 在选择期间失败。没有 BlogCategory 条目。
然后我在 SSMS 中，在没有 SELECT 的情况下强制出错。在结果选项卡中，我看到我只得到 1 列。
解决方案
确保使用数据访问层中的所有结果集以确保引发异常。使用示例模式NextResult()：
do {
while (blogCategoryDataReader.Read())
{
blogCategoryList.Add(new BlogCategory
{
BlogCategoryId = Convert.ToInt32(blogCategoryDataReader["BlogCategoryId"]),
BlogCategoryDescr = blogCategoryDataReader["BlogCategoryDescr"].ToString(),
});
}
} while blogCategoryDataReader.NextResult();
原因是生成的异常消息RAISERROR在底层表格数据流中的 SELECT 查询生成的结果集后面。这实际上变成了客户端 API 必须使用的多个结果集，因此使用所有结果以确保引发异常并且可以被客户端应用程序检测到，这一点很重要。
请注意，SSMS 使用FireInfoMessagesOnUserErrors连接属性而不是 try/catch 来显示用户错误以及其他上下文信息。
推荐阅读
- php - 在 laravel excel 中从 excel 导入列时更改列名
- three.js - arjs - aframe | 工作设备和网络摄像头
- javascript - 具有空字段名称的数组对象
- python - 从网站中抓取字符串中的单词
- azure - 如何根据 Kusto 中的状态汇总时间窗口
- c# - 随机显示 EF 核心错误“为 't' 指定了多次列 'Id'。\r\n”
- php - Laravel + Shopify 库存BulkAdjustQuantityAtLocation
- amazon-web-services - 为什么申请后无法连接到我的 ec2 实例？
- android - 带有 USB 设备的 Android Logcat
- python-3.x - 通过方法修改属性的值