How Can i display the output of SQL "PRINT" Command in C#?

Question

i am having a SQL Procedure that always returns "PRINT" Command and i want to extract the output of this "PRINT" Command i.e the procedure in C# how can i do that ? here is the PROCEDURE

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

Answer 1

You need to subscribe to the SqlConnection.InfoMessage Event.

MSDN has some example code here.

Answer 2

The proper approach to this is to record the value in an output parameter, then in the stored procedure, print the output parameter value.

For example:

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)

Then, in your code, retrieve the value of the output parameter.

Update

The above suggestion will only work if there is a single status or error message that is being returned. On closer review of the stored procedure, I realized that this is not the case with this stored procedure since the print statements are used to return data to the calling application.

Now that I understand this, I suggest that, if possible, the stored procedure be rewritten as follows:

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

With this, you can just process the returned rows, which will have a single column called result.