如何从C＃中的SQL存储过程中检索结果集和PRINT消息？

I have a stored procedure that can return both a PRINT message and regular query results from a SELECT statement. Using the elegant solution provided in this thread, I can easily capture my PRINT message when invoking SqlCommand.ExecuteNonQuery() in my C# code. The problem is, I also want to return my result set.

我有一个存储过程，可以从SELECT语句返回PRINT消息和常规查询结果。使用此线程中提供的优雅解决方案，我可以在我的C＃代码中调用SqlCommand.ExecuteNonQuery（）时轻松捕获我的PRINT消息。问题是，我也想返回我的结果集。

When I use a SqlDataReader to get back the query results from my stored proc using SqlCommand.ExecuteReader, the event handler that captures my PRINT message never fires like it does when I use SqlCommand.ExecuteNonquery.

当我使用SqlDataReader使用SqlCommand.ExecuteReader从我的存储过程中获取查询结果时，捕获我的PRINT消息的事件处理程序永远不会像我使用SqlCommand.ExecuteNonquery时那样触发。

It's beginning to seem like I can only have one or the other. Is there a way for me to capture both my PRINT message and my query results?

看起来我只能拥有一个或另一个。有没有办法捕获我的PRINT消息和查询结果？

Here's a snippet of my SQL stored procedure:

这是我的SQL存储过程的片段：

IF EXISTS
(
    SELECT MFG_PART_NUMBER, COUNT(*) AS RecordCount
    FROM [PartsManagement].[ManufacturerCatalogPart] 
    WHERE CatalogID = @CatalogID
    GROUP BY MFG_PART_NUMBER
    HAVING COUNT(*) > 1
)
    BEGIN

    SELECT MFG_PART_NUMBER, COUNT(*) AS RecordCount
    FROM [PartsManagement].[ManufacturerCatalogPart] 
    WHERE CatalogID = @CatalogID
    GROUP BY MFG_PART_NUMBER
    HAVING COUNT(*) > 1;

    PRINT 'The update was not executed because duplicate MFG_PART_NUMBERs were found in the catalog. Delete the duplicates and try again.';

END;

And here's a snippet from my code:

这是我代码中的一个片段：

//field
private string sqlPrintMessage = "";

//event handler for sql PRINT message
void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    sqlPrintMessage = e.Message;
}

public void SomeMethod()
{
    using (SqlConnection sqlcon = new SqlConnection(ConnectionManager.GetConnectionString()))
    {
        sqlcon.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);

        SqlCommand sqlcmd = new SqlCommand("[ManufacturerCatalogUpdate].[CheckCatalogForDuplicates]", sqlcon);
        sqlcmd.CommandType = CommandType.StoredProcedure;
        sqlcmd.Parameters.AddWithValue("@CatalogID", catalogID);
        sqlcon.Open();
        //SqlDataReader reader = sqlcmd.ExecuteReader();
        sqlcmd.ExecuteNonQuery();
        if (sqlPrintMessage == "") //then proceed with update
        {
            //do some logic
        }
        else
        {
            //System.Diagnostics.Debug.WriteLine(sqlPrintMessage);
            //List<DuplicatePartCount> duplicateParts = new List<DuplicatePartCount>();
            //while (reader.Read())
            //{
            //    DuplicatePartCount record = new DuplicatePartCount();
            //    record.MFG_PART_NUMBER = reader.GetString(0);
            //    record.count = reader.GetInt32(1);
            //    duplicateParts.Add(record);
            //}
        }
    }
}

2 个解决方案

#1

There are two way to do it based on selection of sql execution.

基于sql执行的选择，有两种方法可以做到这一点。

1) If you are using ExecuteNonQuery than use OUT param in sproc like below.

1）如果你使用的是ExecuteNonQuery而不是像下面那样在sproc中使用OUT param。

CREATE PROCEDURE GetEmployee
   @employeeID INT,
   @Message VarChar(100) OUTPUT
AS
BEGIN
   -- Here is your result set from select statement
   SELECT Emp_Name From Employee Where EmpId = @employeeID 

   -- Here is your message
   SELECT @Message = 'Your Message!'
END

Now to get it in your code pass @Message parameter with OUTlike below.

现在要在代码中传递它，使用下面的OUTlike传递@Message参数。

//Add the output parameter to the command object
SqlParameter outPutParameter = new SqlParameter();
outPutParameter.ParameterName = “@Message”;
outPutParameter.SqlDbType = System.Data.SqlDbType.Varchar;
outPutParameter.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(outPutParameter);

cmd.ExecuteNonQuery();

//Retrieve the value of the output parameter
string Message = outPutParameter.Value.ToString();

// You can get remaining result set as always

2) If you are using Reader than....

2）如果你使用的是阅读器而不是....

Set the message and use Select statement instead of print.Like below'

设置消息并使用Select语句而不是print.Like下面'

 SELECT Emp_Name From Employee Where EmpId = @employeeID 
  -- It should be set previously
  SELECT @Message

To get in your code just Use

要获取代码，请使用

using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                //Your result set or message
            }

            if(reader.NextResult())
            {
               while (reader.Read())
              {
                 //Your result set or message
              }
            }
        }

#2

So, I think we first need to rethink the operation here. You want to do a duplicate as a test to determine if the app code should proceed or not. The proc returns a result set and then prints a message if there are dupes, else returns neither. And you are inferring that there will be a result set based on the existence of a captured message. That type of indirect test is less than ideal and unnecessary.

所以，我认为我们首先需要重新考虑这里的操作。您希望将重复项作为测试来确定应用程序代码是否应该继续。 proc返回一个结果集，如果有dupes则打印一条消息，否则返回。并且您推断基于捕获的消息的存在将存在结果集。这种间接测试不太理想和不必要。

A direct and simpler method is available to test for the existence of a result set. Forget the message (especially since you aren't even using it) and instead just check the data reader:

可以使用直接且更简单的方法来测试结果集的存在。忘记消息（特别是因为你甚至没有使用它）而只是检查数据阅读器：

    if (reader.HasRows())
    {
        //System.Diagnostics.Debug.WriteLine(sqlPrintMessage);
        //List<DuplicatePartCount> duplicateParts = new List<DuplicatePartCount>();
        //while (reader.Read())
        //{
        //    DuplicatePartCount record = new DuplicatePartCount();
        //    record.MFG_PART_NUMBER = reader.GetString(0);
        //    record.count = reader.GetInt32(1);
        //    duplicateParts.Add(record);
        //}
    }
    else
    {
        //do some logic
    }

If you are still just curious about the PRINT message getting trapped, do these two tests:

如果您仍然只是对PRINT消息陷入困境感到好奇，请执行以下两项测试：

First, add a second System.Diagnostics.Debug.WriteLine(sqlPrintMessage); AFTER the while loop, and re-run the test
首先，添加第二个System.Diagnostics.Debug.WriteLine（sqlPrintMessage）;在while循环之后，重新运行测试
Second, without removing the 2nd debug line from #1, add a second PRINT command between the BEGIN and SELECT, with different text so that you can distinguish between them if necessary, and re-run the test
其次，在不从＃1中删除第二个调试行的情况下，在BEGIN和SELECT之间添加第二个PRINT命令，使用不同的文本，以便在必要时可以区分它们，并重新运行测试

Also, the following "Remarks" from the MSDN page for the SqlConnection.FireInfoMessageEventOnUserErrors property might shed some light on this behavior:

此外，来自MSDN页面的SqlConnection.FireInfoMessageEventOnUserErrors属性的以下“备注”可能会对此行为有所了解：

When you set FireInfoMessageEventOnUserErrors to true, errors that were previously treated as exceptions are now handled as InfoMessage events. All events fire immediately and are handled by the event handler. If is FireInfoMessageEventOnUserErrors is set to false, then InfoMessage events are handled at the end of the procedure.

将FireInfoMessageEventOnUserErrors设置为true时，以前视为异常的错误现在将作为InfoMessage事件处理。所有事件立即触发并由事件处理程序处理。如果FireInfoMessageEventOnUserErrors设置为false，则在过程结束时处理InfoMessage事件。

So, if you wanted / needed the messages as they happen, then you can set FireInfoMessageEventOnUserErrors to true, but that might alter the behavior of catching exceptions that might be more undesirable than getting the messages immediately.

因此，如果您希望/需要消息，那么您可以将FireInfoMessageEventOnUserErrors设置为true，但这可能会改变捕获异常的行为，这可能比立即获取消息更不可取。

#1