How to get 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 message PRINTand the results of a regular query from an operator SELECT. Using the elegant solution presented in this thread , I can easily record my message PRINTwhen called SqlCommand.ExecuteNonQuery()in my C # code. The problem is that I also want to return my result set.

When I use SqlDataReaderto return query results from my saved process, using SqlCommand.ExecuteReader, the event handler that captures my message PRINTnever fires, as when using SqlCommand.ExecuteNonquery.

It starts to seem that I can only have one or the other. Is there a way to capture both my message PRINTand my query results?

Here is 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 is 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 ways to do this based on the choice of sql execution.

1) If you use ExecuteNonQuery, than use OUTparam in sproc, as shown 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 the code @Messagewith in your code OUT, as shown 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 use a reader than ....

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

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

To get into 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

, , . , , . Proc , , , else - . , , . .

. ( , ), :

    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
    }

, PRINT , :

• System.Diagnostics.Debug.WriteLine(sqlPrintMessage); while
• -, # 1, PRINT BEGIN SELECT, , , ,

, "" MSDN SqlConnection.FireInfoMessageEventOnUserErrors :

FireInfoMessageEventOnUserErrors true, , , InfoMessage. . FireInfoMessageEventOnUserErrors false, InfoMessage .

, / , FireInfoMessageEventOnUserErrors true, catching , , .