How can retrieve both a result set and a PRINT message from a SQL stored procedure in C#?

Question

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.

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.

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?

Here's a snippet of my SQL stored procedure:

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);
            //}
        }
    }
}

Answer 1

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

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

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.

//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....

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

 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
              }
            }
        }

Answer 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.

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:

1. First, add a second System.Diagnostics.Debug.WriteLine(sqlPrintMessage); AFTER the while loop, and re-run the test
2. 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

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

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.

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.