How can I receive SQL Messages in c#?

Question

I want to show these SQL messages in c#

My C# code:

SqlCommand cmd = new SqlCommand();
cmd = con.CreateCommand();
string ab = "";
con.FireInfoMessageEventOnUserErrors = true;
con.InfoMessage += delegate(object obj, SqlInfoMessageEventArgs err)
{
   ab = "\n " + err.Message;
   message_richTextBox.Text += (ab);
};
cmd.CommandText = @"execute my_sp ";
cmd.ExecuteNonQuery();
con.FireInfoMessageEventOnUserErrors = false;

Problem:
My problem is that I don't get all SQL messgaes as in SQL Server 2008 (image attached) in C#

Please, clarify this: "i dont get all SQl messgaes". What exactly you're not getting and want to get - affected records counts or messages? And how do you outputs these messages - by `print` or by `raiserror`? — Andrey Korneyev, Jan 27 '15 at 10:14
Use `SET @Message = 'Started stuff...' RAISERROR(@Message, 0, 0) WITH NOWAIT` to have output as info message — Scoregraphic, Jan 27 '15 at 10:18
Review this POST http://stackoverflow.com/questions/1880471/capture-stored-procedure-print-output-in-net — Y.S, Jan 28 '15 at 05:07

score 2 · Answer 1 · answered Jan 27 '15 at 10:34

Some of those are not 'messages' (ie. TDS ERRROR and INFO Messages) but instead are rowcounts. SSMS displays them in the output in a similar manner to Info messages, but they're unrelated.

You get them in SqlClient as return values from DML queries. In other words, the int returned by ExecuteNonQuery:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

The messages proper, like 'deleting previous signoff', you already know how to get them: SqlConnection.InfoMessage event, just as in your code.

How does this work if (say, due to execution of a trigger) a single UPDATE statement produces several "nnn row(s) affected" messages? Is there a way to retrieve all of these? — Kevin Martin, May 05 '23 at 14:43

score 0 · Answer 2 · answered Jan 27 '15 at 10:26

Declare the local variables and assign the messages and affected rowcount to the variables and make select at last.

Use the SQLDataReader to get the messages in C#

DECLARE @sMessage1 AS VARCHAR(100)
DECLARE @sMessage2 AS VARCHAR(100)
DECLARE @sMessage3 AS VARCHAR(100)
DECLARE @sAffectedRows1 AS VARCHAR(100)
DECLARE @sAffectedRows2 AS VARCHAR(100)
DECLARE @sAffectedRows3 AS VARCHAR(100)

SET @sMessage1  = 'something1'

First UPDATE query

SET @sAffectedRows1  = @@ROWCOUNT

SET @sMessage2  = 'something2'

Second UPDATE query

SET @sAffectedRows2  = @@ROWCOUNT

SET @sMessage3  = 'something3'

Third  UPDATE query

SET @sAffectedRows3  = @@ROWCOUNT


SELECT @sMessage1  AS Msg1, @sMessage2  AS Msg2, @sMessage3  AS Msg3, 
      @sAffectedRows1  AS Rows1, @sAffectedRows2  AS Rows2, @sAffectedRows3  AS Rows3

score 0 · Answer 3 · answered Jan 27 '15 at 10:38

I think SQLConnection will not help you to get those message that you see in SQL Server Management Studio.

To get that message you have to do following thing. ( Main SMO)

Use SQL Server Management Object ( You will find SQL server management object msi on Microsoft Site)

After that include reference of those DLL and use following code.

public static void ExecuteScript(string scriptContent)
{
SqlConnection conn = new SqlConnection(dbConnectionString);
conn.Open();
Server sqlServer = new Server(new Microsoft.SqlServer.Management.Common.ServerConnection(conn));
sqlServer.ConnectionContext.InfoMessage += new SqlInfoMessageEventHandler(ConnectionContext_InfoMessage);
sqlServer.ConnectionContext.ServerMessage += new Microsoft.SqlServer.Management.Common.ServerMessageEventHandler(ConnectionContext_ServerMessage);
sqlServer.ConnectionContext.ExecuteNonQuery(scriptContent);
}

static void ConnectionContext_ServerMessage(object sender, Microsoft.SqlServer.Management.Common.ServerMessageEventArgs e)
{

Console.WriteLine("Method : ConnectionContext_ServerMessage" + System.Environment.NewLine + e.Error.Message + System.Environment.NewLine);
}

static void ConnectionContext_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{                
Console.WriteLine(System.Environment.NewLine + e.Message + System.Environment.NewLine);
}

How can I receive SQL Messages in c#?

3 Answers3