使用ExecuteReader在SQL Server链接存储过程中的ErrorHandling [英] ErrorHandling in SQL Server chained stored proc with ExecuteReader
问题描述
SqlInfoMessageEventHandler 和代码，如 RAISERROR（'whatever'，10,1） WITH NOWAIT 在存储过程中向用户报告操作进度。为了做到这一点，我已经看到你不能使用cmd.ExecuteNonQuery（），而是使用cmd.ExecuteReader（）。我已经尝试过，似乎是这样的;我没有看到任何来自ExecuteNonQuery的消息。 我发现的问题是当我使用ExecuteReader，如果我的存储过程抛出任何错误，那么它们将被忽略。这意味着我的.NET应用程序从一个try块调用这个存储的proc，当存储的proc遇到错误（如SELECT 1/0）时，执行就不会进入catch块，而是提交我的事务。一个例子如下：
IF EXISTS（SELECT * FROM sys.objects WHERE object_id = OBJECT_ID（N'[dbo ] [TempTstTbl]'）AND键入（N'U'）
DROP TABLE [dbo]。[TempTstTbl]
CREATE TABLE [dbo]。[TempTstTbl]（
步骤INT，
Val VARCHAR（50）
）
如果EXISTS（SELECT * FROM sys.objects WHERE object_id = OBJECT_ID（N'[dbo]。[Child] '）并键入（N'P'，N'PC'）
DROP PROCEDURE [dbo]。[Child]
GO
CREATE PROCEDURE [dbo]。[孩子]
AS
BEGIN
BEGIN TRY
INSERT INTO [dbo]。[TempTstTbl]（Step，Val）VALUES（1，'FROM CHILD BEFORE FAULT'）
SELECT 1/0
--RAISERROR（'这应该真的失败'，16，2）
INSERT INTO [dbo]。[TempTstTbl]（Step，Val）VALUES（2，'FROM CHILD AFTER FAULT'）
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR（4000）;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE（），
@ErrorSeverity = ERROR_SEVERITY（），
@ErrorState = ERROR_STATE（）;
- 在CATCH块内使用RAISERROR返回错误
- 有关导致
的原始错误的信息 - 执行跳转到CATCH块。
RAISERROR（@ErrorMessage， - Message text。
@ErrorSeverity， - Severity。
@ErrorState - State。
）;
END CATCH;
END
GO
如果EXISTS（SELECT * FROM sys.objects WHERE object_id = OBJECT_ID（N'[dbo]。[Parent]'）AND键入（N'P '，N'PC'））
DROP PROCEDURE [dbo]。[Parent]
GO
CREATE PROCEDURE [dbo]。[Parent]
AS
BEGIN
BEGIN TRY
INSERT INTO [dbo]。[TempTstTbl]（Step，Val）VALUES（1，'FROM PARENT BEFORE CHILD'）
Exec [dbo]。[Child]
INSERT INTO [dbo]。[TempTstTbl]（Step，Val）VALUES（2，'FROM PARENT AFTER CHILD'）
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR 4000）;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE（），
@ErrorSeverity = ERROR_SEVERITY（），
@ErrorState = ERROR_STATE（）;
- 在CATCH块内使用RAISERROR返回错误
- 有关导致
的原始错误的信息 - 执行跳转到CATCH块。
RAISERROR（@ErrorMessage， - Message text。
@ErrorSeverity， - Severity。
@ErrorState - State。
）;
END CATCH;
END
GO
EXEC [dbo]。[父]
SELECT * FROM [dbo]。[TempTstTbl]
使用一些.NET代码，如;
private void button4_Click（object sender，EventArgs e）
{
using（SqlConnection conn = new SqlConnection（@Data Source = XPDEVVM\XPDEV; Initial Catalog = MyTest; Integrated Security = SSPI;） ）
{
conn.Open（）;
using（SqlTransaction trans = conn.BeginTransaction（））
{
using（SqlCommand cmd = conn.CreateCommand（））
{
cmd.Transaction = trans;
cmd.CommandText =[cfg]。[Parent];
cmd.CommandType = CommandType.StoredProcedure;
try
{
- cmd.ExecuteReader（）; - 使用这个而不是ExecuteNonQuery意味着在存储过程中除以0错误被忽略，并且一切都被提交:(
cmd.ExecuteNonQuery（）;
trans.Commit（）;
}
catch（Exception ex）
{
trans.Rollback（）;
}
}
}
}
}有没有人有任何想法，如何从我的存储过程中获取我的进度消息，但是仍然是抓取.NET异常，如果存储过程中出现错误？解决方案似乎这个问题源于DataReader的工作原理首先在Nelson Rothermel的一个提示中提到了一个单独的SO问题的这个答案。
然后，我进一步阅读有关这个线程 Richard McKinnon提供以下示例来解决问题（我的重点）;
尝试与严重度> 10（我使用11）相同，并将SELECT行添加到SP我使用NorthWind DB检查）。你会看到
，当你有一个SELECT检索数据时，你永远不会得到
异常。
[原始消息剪辑]
在这种情况下，ExecuteReader（）将开始处理第一组
结果，SELECT声明。要获得下一组结果，
RAISERROR，您需要调用NextResult（）。
所以要看看错误我会将我的代码从以前更改为
看起来像这样;
SqlDataReader dr = command.ExecuteReader（）; dr.NextResult（）;
dr.Close（）;
.NextResult（）到我的代码，它似乎已经解决了我的问题。
这样我就可以从我的存储过程中获取信息消息，而且还允许我捕获存储过程中引发的错误。
I am chaining together some stored procedures and have run into some issues in getting error handling working correctly. As some of these stored procedures are long running, I've made use of SqlInfoMessageEventHandler and code such as RAISERROR('whatever',10,1) WITH NOWAIT within the stored procedures to report the progress of the operation to the user. In order to do this, I've read that you can't use cmd.ExecuteNonQuery() and instead have to use cmd.ExecuteReader(). I've tried this, and it does seem to be the case; I don't see any messages from ExecuteNonQuery.
The problem I found though is that when I use ExecuteReader, if my stored proc throws any errors, then they are ignored. By this I mean my .NET Application that calls this stored proc from a try block and when the stored proc encounters an error (like SELECT 1/0), execution never enters the catch block, but instead commits my transaction. An example of this is as follows;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempTstTbl]') AND type in (N'U'))
DROP TABLE [dbo].[TempTstTbl]
CREATE TABLE [dbo].[TempTstTbl] (
Step INT,
Val VARCHAR(50)
)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Child]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Child]
GO
CREATE PROCEDURE [dbo].[Child]
AS
BEGIN
BEGIN TRY
INSERT INTO [dbo].[TempTstTbl] (Step, Val) VALUES (1, 'FROM CHILD BEFORE FAULT')
SELECT 1/0
--RAISERROR ('This should really fail', 16, 2)
INSERT INTO [dbo].[TempTstTbl] (Step, Val) VALUES (2, 'FROM CHILD AFTER FAULT')
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Parent]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Parent]
GO
CREATE PROCEDURE [dbo].[Parent]
AS
BEGIN
BEGIN TRY
INSERT INTO [dbo].[TempTstTbl] (Step, Val) VALUES (1, 'FROM PARENT BEFORE CHILD')
Exec [dbo].[Child]
INSERT INTO [dbo].[TempTstTbl] (Step, Val) VALUES (2, 'FROM PARENT AFTER CHILD')
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
END
GO
EXEC [dbo].[Parent]
SELECT * FROM [dbo].[TempTstTbl]
With some .NET code such as;
private void button4_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(@"Data Source=XPDEVVM\XPDEV;Initial Catalog=MyTest;Integrated Security=SSPI;"))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Transaction = trans;
cmd.CommandText = "[cfg].[Parent]";
cmd.CommandType = CommandType.StoredProcedure;
try
{
-- cmd.ExecuteReader(); -- Using this instead of ExecuteNonQuery means the divide by 0 error in the stored proc is ignored, and everything is committed :(
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
}
}
}
}
}
Does anyone have any ideas as to how I can get my progress messages from my stored proc, but still catch .NET exceptions if errors occur in the stored procedure?
解决方案 It seems that this issue stems from the way DataReader works. I first came across a hint in Nelson Rothermel comment in this answer to a separate SO question.
I then further read details of this issue described on this thread where Richard McKinnon gives the following example as a way to address the issue (my emphasis);
Try the same with severity > 10 (I am using 11) and add the SELECT line to the SP (I checked using the NorthWind DB). You'll see
that when you have a SELECT that retrieves data, you never get the
exception.
[Original message clipped]
In this case ExecuteReader() will start process the first set of
results, the SELECT statement. To get the next set of results, the
RAISERROR, you need to call NextResult().
So to just look at the errors I would change my code from before to
look like this;
SqlDataReader dr = command.ExecuteReader(); dr.NextResult();
dr.Close();
I tried adding dr.NextResult() to my code and it does seem to have fixed the issue for me.
This then allows me to get info messages from my stored proc, but also allows me to catch errors raised in the stored proc also.
这篇关于使用ExecuteReader在SQL Server链接存储过程中的ErrorHandling的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
