Show how many rows were deleted

views:

answers:

+3 Q:

Show how many rows were deleted

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.

+3 A:

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.

kbrimington 2010-08-27 20:45:04

Note that SQL Server's NOCOUNT option interferes with the return value of ExecuteNonQuery if it is turned on. (http://petesbloggerama.blogspot.com/2006/10/note-to-self-set-nocount-on-not.html), (http://aspsoft.blogs.com/jonas/2006/10/executenonquery.html)

Dr. Wily's Apprentice 2010-08-27 21:00:46

Does it mean that executeNoneQuery() function returns int value and I can put it in int parameter?for example:int count = executeNoneQuery();

mahnaz 2010-08-27 21:19:14

thank you very much.I use this command and it worked.//-----------------------SqlDataAdapter>>>>>>>>>>>>sda..........int count= sda.DeleteCommand.ExecuteNonQuery();

mahnaz 2010-08-27 21:50:13

@Dr Wily: Thanks for the reminder. @mahnaz: Yes. If using this API, you can use `int count = myCommand.ExecuteNonQuery();`.

kbrimington 2010-08-27 21:50:36

I imagine you would want to do a select "count" on the delete statement before you issue the delete, then capture the number and manipulate it as needed.

Holman716 2010-08-27 20:45:24

This is a good idea. Note that it requires good transaction management to be reliable.

kbrimington 2010-08-27 20:46:08

There are ways build into SQL to avoid having to perform 2 queries

DaveShaw 2010-08-27 20:46:10

+1, as this is effectively the way I usually handle it using Linq-to-Sql.

kbrimington 2010-08-27 21:55:27

Use the @@RowCount SQL Environment variable. You can return it from a Stored Procedure if you are using them.

DaveShaw 2010-08-27 20:45:28

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.

Dr. Wily's Apprentice 2010-08-27 20:52:20

ansaurus

tags:

views:

answers:

Show how many rows were deleted

related questions