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
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.
Don't rely on it. Best practice is SET NOCOUNT ON (discussed with my question here)
- When you load your datatable, use .Count.
- Use an OUTPUT parameter to pass @@ROWCOUNT back (or as a dataset)
Take a look at this question and answers. You can't do (b) above without adding some code in your TSQL that captures the @@rowcount and outputs it in some manner (like a resultset that you could read from).
One option is in your stored procedure is to include variables that you will pass back statement counts. You can do by creating your procedure with the needed OUTPUT parameters.
FIRST SQL HERE
@FirstSQLCount = @@ROWCOUNT
SECOND SQL HERE
@SecondSQLCount = @@ROWCOUNT
