Show how many rows were deleted

Question

I use C# program and my database is in SQL server 2008.

When user deleted some rows from database, I want to show him/her in windows application how many rows deleted.

I want to know how I can send SQL message to C# and show it for user. For example when I deleted 4 rows from table, SQL show message like (4 row(s) affected). Now I want to send number 4 to my C# program. How can I do it? Thank you.

Answer 1

If you are using SqlCommand from your .NET application to perform your delete/update, the result of ExecuteNonQuery() returns the number of rows affected by the last statement of the command.

See http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx.

Answer 2

If you're using the System.Data.SqlClient.SqlCommand.ExecuteNonQuery method or System.Data.Common.DbCommand.ExecuteNonQuery method, then the return value should be the number of rows affected by your statement (the last statement in your command, I think).

There is a caveat to this...if you execute a batch or stored procedure that does SET NOCOUNT ON, then the number of rows affected by each statement is not reported and ExecuteNonQuery will return -1 instead.

in T-SQL, there is a @@rowcount variable that you can access in order to get the number of rows affected by the last statement. Obviously you would need to grab that immediately after your DELETE statement, but I believe you could do a return @@rowcount within your T-SQL if you are using SET NOCOUNT ON.

Alternatives would be to return the value as an OUTPUT parameter, especially if you have a batch of multiple statements and you'd like to know how many rows are affected by each. Some people like to use the T-SQL RETURN statement to report success/failure, so you may want to avoid returning "number of rows affected" for consistency's sake.