c# - c# asp.net 中的 SQL raiserror 输出

Question

我有一个存储过程，出于测试目的，它总是会引发错误。

UPDATE [Test]
    SET  [1.1] = 0
        ,[1.2] = 0
        ,[1.3] = 0
        ,[1.4] = 0

    WHERE Record_ID = 2 AND
          [1.1] = 0 AND
          [1.2] = 0 AND
          [1.3] = 0 AND
          [1.4] = 0
if(@@ROWCOUNT = 0)
    begin 
    raiserror('Error on purpose',16,1)
END

虽然错误在 SQL MGMT studio 中抛出，但它不会在 asp.net 中的 try/catch block 中触发。

我这样称呼SP:

try {
     dataContext.TESTProcedure();
}
catch(Exception ex)
{
    string error = ex.Message;
}

我已经单步执行了代码，但它从未进入 catch，即使它应该进入。

出了什么问题？

Answer 1

我会更深入地了解 dataContext 在做什么。如果在后台使用数据读取器，则可能必须前进到下一个结果集才能得到错误。

打电话

NextResult

在阅读器上。更多信息:

"So how do we get this to work? Well, when using a SqlDataReader it will only iterate over the first result set, which in this case will be the select in the stored procedure. This result set will be empty since there is no data for ID number 10. So nothing is displayed and the second result set is not accessed. What we have to do is to move into the next result set in order to get hold of the error.

One way to do this is to as follows, ie, this will keep going over all the returned result sets until the SqlDataReader is closed, which it will be when there are no more result sets returned."

http://blogs.msdn.com/b/spike/archive/2009/04/06/catch-a-sql-server-raiserror-at-client-side-using-sqldatareader.aspx