How can I display the output of the SQL command "PRINT" in C #?

Question

I have an SQL procedure that always returns the "PRINT" command, and I want to extract the result of this "PRINT" command. I am performing a procedure in C #, how can I do this? here 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 a sample code here .

Answer 2

The correct approach to this is to write the value to the output parameter, then print the value of the output parameter in the stored procedure.

For instance:

 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 the value of the output parameter is extracted.

Update

The above suggestion will only work if there is one status or error message that is returned. Upon closer inspection of the stored procedure, I realized that this does not apply to this stored procedure, since print statements are used to return data to the calling application.

Now that I understand this, I suggest that whenever 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 simply handle the returned rows, which will have one column named result.