SET NOCOUNT ON stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
a) How can you read these messages using C# and ADO.NET ( I assume C# code reading these messages is the same regardless of whether T-SQL statements were executed in a stored procedure, batch or… )?
b) Assuming stored procedure contains several statements, how can your C# code identify to what SQL statement does particular messages refer to?
Thank you
Best Answer
Informational messages (like the rows affected count info) are reported in ADO.Net through the SqlConnection.InfoMessage event. Add a delegate to the event and will be invoked whenever the server transmits an informational message (ie. any error message with severity bellow 10).
there is no way to associate informational messages like afffected count info with the source. You're going to have to do it based on knowledge of the logic and understand that the first message refers to the first update, the second message to the second update etc.
Relying on affected rows count in the client is generaly a bad practice. The many issues ORM layers like NHibernate and ADO.Net datasets have when SET NOCOUNT ON is turned on just shows how problematic this practice is.