如何在C#中显示SQL "PRINT"命令的输出？

Question

我有一个SQL存储过程，它总是返回"PRINT"命令，我想提取这个"PRINT"命令的输出，即在C#中如何处理该存储过程？以下是该存储过程：

ALTER PROC ResultsPoll
@pollid INT 
AS
DECLARE @count1 INT 
DECLARE @count2 INT 
DECLARE @count3 INT 
DECLARE @count4 INT 
DECLARE @count5 INT 
DECLARE @test VARCHAR(MAX)
DECLARE @value VARCHAR(MAX)
SELECT @count1 = COUNT(mem_id) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a1
SELECT @count2 = COUNT(mem_id) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a2
SELECT @count3 = COUNT(mem_id) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a3
SELECT @count4 = COUNT(mem_id) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a4
SELECT @count5 = COUNT(mem_id) FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a5

SELECT @test=Polls.a1 FROM Polls WHERE poll_id = @pollid
IF(@test IS NOT NULL)
BEGIN
PRINT ('Number of students who chose '+@test+' is:'+' '+CAST (@count1 AS VARCHAR(MAX)))
END
SELECT @test=Polls.a2 FROM Polls WHERE poll_id = @pollid
IF(@test IS NOT NULL)
BEGIN
PRINT ('Number of students who chose '+@test+' is:'+' '+CAST (@count2 AS VARCHAR(MAX)))
END
SELECT @test=Polls.a3 FROM Polls WHERE poll_id = @pollid
IF(@test IS NOT NULL)
BEGIN
PRINT ('Number of students who chose '+@test+' is:'+' '+CAST (@count3 AS VARCHAR(MAX)))
END
SELECT @test=Polls.a4 FROM Polls WHERE poll_id = @pollid
IF(@test IS NOT NULL)
BEGIN
PRINT ('Number of students who chose '+@test+' is:'+' '+CAST (@count4 AS VARCHAR(MAX)))
END
SELECT @test=Polls.a5 FROM Polls WHERE poll_id = @pollid
IF(@test IS NOT NULL)
BEGIN
PRINT ('Number of students who chose '+@test+' is:'+' '+CAST (@count5 AS VARCHAR(MAX)))
END

- Fady Kamal

---

可能是在.NET中捕获存储过程打印输出的重复问题。 - dsolimano

Answer 1

您需要订阅 SqlConnection.InfoMessage 事件。

MSDN 在这里提供了一些示例代码。

- Martin Smith

---

@BrijeshMishra - 没有专门针对SQL Server的SqlConnection。不确定除了Sybase之外的其他RDBMS是否有等效的PRINT... - Martin Smith

Answer 2

正确的方法是将值记录在输出参数中，然后在存储过程中打印输出参数的值。

例如：

ALTER PROC ResultsPoll
@pollid INT ,
@message varchar(max) OUTPUT
AS
SET @message = ''
...
IF(@test IS NOT NULL)
BEGIN
SET @message = 'Number of students who chose '+@test+' is:'+' '+CAST (@count1 AS VARCHAR(MAX))
END
...

PRINT(@message)

然后，在您的代码中，检索输出参数的值。

更新

上述建议仅在返回单个状态或错误消息时有效。经过仔细审查存储过程，我意识到这不是该存储过程的情况，因为打印语句用于将数据返回给调用应用程序。

现在我明白了这一点，如果可能的话，建议将存储过程重写如下：

ALTER PROC ResultsPoll
@pollid INT 
AS

SELECT result = 'Number of students who chose ' + MAX(Polls.a1) + ' is: ' + CAST(COUNT(1) AS NVARCHAR(MAX)) 
  FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
 WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a1

UNION

SELECT result = 'Number of students who chose ' + MAX(Polls.a2) + ' is: ' + CAST(COUNT(1) AS NVARCHAR(MAX)) 
  FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
 WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a2

UNION

SELECT result = 'Number of students who chose ' + MAX(Polls.a3) + ' is: ' + CAST(COUNT(1) AS NVARCHAR(MAX)) 
  FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
 WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a3

UNION

SELECT result = 'Number of students who chose ' + MAX(Polls.a4) + ' is: ' + CAST(COUNT(1) AS NVARCHAR(MAX)) 
  FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
 WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a4

UNION

SELECT result = 'Number of students who chose ' + MAX(Polls.a5) + ' is: ' + CAST(COUNT(1) AS NVARCHAR(MAX)) 
  FROM Students_answer_Polls INNER JOIN Polls ON Polls.poll_id = Students_answer_Polls.poll_id
 WHERE Students_answer_Polls.poll_id = @pollid AND Students_answer_Polls.answer = Polls.a5

通过这个方法，你只需要处理返回的行，它们只会有一个名为"result"的列。

- competent_tech

---

嗯，我还没有看过他们为什么想这样做。在我看来，“正确”的做法是返回一个简单的结果集，其中包含2列test,count。 - Martin Smith

@MartinSmith：如果他们的查询没有产生任何结果，或者将来更改查询以包括多个结果，则该方法可能存在问题。我发现在参数中“out of band”包含状态或错误消息要可靠得多，并且具有未来性。 - competent_tech

这不是状态消息。这是存储过程返回的唯一数据。 - Martin Smith

是的，你说得对，问题是我不是编写这些过程的人，所以我只专注于将它与ASP.NET连接起来。谢谢你们两个的答案，对我很有帮助。 - Fady Kamal

@MartinSmith：哇，我再次看了一下存储过程，意识到实际上正在发生什么。我从未见过这样的结构，现在意识到正在发生的事情。 - competent_tech

@MartinSmith：现在我明白了，我已经更新了答案，并提出了存储过程的建议重写以及对我先前答案的澄清。感谢您的敏锐眼力。 - competent_tech