Get SQL print messages from stored proc

Question

I have a stored proc that outputs print messages to the message tab. I want to grab these and display them back to the user once the proc has finished.

Here is what i'm trying but it's coming back empty. It's also not because i'm clearing the StringBuilder. I moved the clear into Open and still gives the same results.

private static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    sqlMessages.AppendLine(e.Message);
}

private static void Open()
{
    if (conn.State.Equals(ConnectionState.Closed))
    {
        conn.InfoMessage += conn_InfoMessage;
        conn.Open();
    }
}

private static void Close()
{
    if (conn.State.Equals(ConnectionState.Open))
    {
        conn.InfoMessage -= conn_InfoMessage;
        sqlMessages.Clear();
        conn.Close();
    }
}

public static string RemoveContacts(int customerCode)
    {
        try
        {
            Open();
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "s_RemoveAllCustomerContacts";
                cmd.Parameters.AddWithValue("@customerCode", customerCode);
                cmd.ExecuteNonQuery();
                return sqlMessages.ToString();
            }
        }
        catch (SqlException ex)
        {
            throw new ArgumentException(ex.Message);
        }
        finally
        {
            Close();
        }
    }

possible duplicate of [Capture Stored Procedure print output in .NET](http://stackoverflow.com/questions/1880471/capture-stored-procedure-print-output-in-net) — EkoostikMartin, May 27 '14 at 19:38
I don't see how this code even compiles, can you include the full class? — EkoostikMartin, May 27 '14 at 19:47

score 0 · Accepted Answer · answered May 27 '14 at 20:02

Ok so I figured out my answer and had to use the following

private static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
    {
        if (e.Errors != null)
        {
            for (int i = 0; i < e.Errors.Count; i++)
            {
                sqlMessages.AppendLine(e.Errors[i].Message);
            }
        }
    }

Get SQL print messages from stored proc

1 Answers1