2

Possible Duplicate:
SQL: Update a row and returning a column value with 1 query
Get affected rows on ExecuteNonQuery

I have a textbox where the users enter their SQL queries.

On example query could be:

UPDATE customer SET isnew = 1;

Running the above query updates 4 rows, how do I get the '4 rows affected' message that which be returned by SQL Server.

I've tried using InfoMessage but this only returns messages like 'Changed database context to 'databasenamehere' and not the actual rows affected.

Community
  • 1
  • 1
bluenose1970
  • 21
  • 1
  • 1
  • 6
  • 2
    I **HOPE** you're **sanitizing** the queries your users are inputting! Also: if you run a query using `command.ExecuteNonQuery()`; the return value is the number of rows affected by the SQL statement executed – marc_s Jan 24 '13 at 15:37
  • How are you running the queries in C#? – Arran Jan 24 '13 at 15:38
  • To run my SQL in C# i'm using the dataAdapter.Fill(dataSet) and then assigning the datasets to GridViews, so can't use the recordsAffected from the command.ExecuteNonQuery – bluenose1970 Jan 25 '13 at 13:54

2 Answers2

11

SqlCommand.ExecuteNonQuery returns the number of records affects as int:

var command = new SqlCommand("UPDATE customer SET isnew = 1;", conn);
int recordsAffected = command.ExecuteNonQuery();
Eren Ersönmez
  • 38,383
  • 7
  • 71
  • 92
  • See above, can't use this as I'm using dataAdapter.Fill(dataSet) to get the results from my sql queries... – bluenose1970 Jan 25 '13 at 13:56
  • How are you executing the UPDATE statement? `DataAdapter.Update` also returns the number of rows affected, for example. – JosephHirn Jan 25 '13 at 14:38
  • I'm using `dataAdapter.Fill(dataSet)` to fire my UPDATE statement(s), the sql statement could contains a couple of UPDATES & SELECTS, any SELECTS are assigned to a `GridView` – bluenose1970 Jan 25 '13 at 15:21
  • @bluenose1970 `Fill` uses the `SelectCommand` of the adapter, not the `UpdateCommand`. You cannot update the database using `Fill`. If you mean "refreshing" the dataset, then `Fill` also returns the number of records affected as an int: `int recordsAffected = dataAdapter.Fill(dataSet);`. – Eren Ersönmez Jan 25 '13 at 15:32
4

its not going to get the message you're after, but this will get the same count:

    declare @RC int
(your statement here)
SELECT @RC = @@ROWCOUNT

That will give you the rowcount of the statement.

Rich
  • 867
  • 7
  • 12