using .RecordsAffected, C#

rocket

Hi, I am using a C# application that interacts with a TSQL stored procedure.

The problem is that my SqlDataReader property .RecordsAffected always returns -1 regardless of whatever kind of select, update, or delete query I run.

Here is my relevant code:

 
SqlDataReader myReader = null;

SqlCommand command = new SqlCommand("CSP_CT_233", myConnection);
myReader = command.ExecuteReader();

while (myReader.Read())
        {

           blah blah blah //this stuff works fine
        }

myReader.Close();
Console.Writeline(myReader.RecordsAffected.ToString());

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

rocket18y ago
The problem seems to be when my my update where field differs from my set field

(this is updating one record)

update table set field1=1 where field1=0 //returns 1
update table set field1=1 where field2=0 //returns 2

anyone seen this before?
+1
rocket18y ago
ok now it will return 2 records affected whenever I update 1 row.

Otherwise it will report accurately...
+1
rocket18y ago
thanks so much i turned the SET NOCOUNT ON to SET NOCOUNT OFF and it works now
+1
Ryan Alford18y ago
it will return 0 if no records are affected. In your stored procedure, do you have "SET NOCOUNT ON"?
+1
rocket18y ago
ok i am using this now

int affectedRowsInt = command.ExecuteNonQuery();
Console.WriteLine("Affected Rows" + affectedRowsInt .ToString());

and it is still returning -1, I know that it will return -1 when no records are affected but I am double checking that records are actually being affected with SQL server queries, so I know that this is not correct.
+1
Ryan Alford18y ago
With update and delete statements, you will be using the ExecuteNonQuery() method, with returns an integer. That integer is the number of effected rows.
+1
rocket18y ago
The problem is that this will not work for UPDATE and DELETE statements only for SELECT statements.
+1

Ryan Alford18y ago

I would probably do something like this.

SqlDataReader myReader = null;

SqlCommand command = new SqlCommand("CSP_CT_233", myConnection);
myReader = command.ExecuteReader();

int counter = 0;

while (myReader.Read())
{
      counter++;
      blah blah blah //this stuff works fine
}

myReader.Close();
Console.Writeline(counter);

or if you are loading the information from the datareader into some type of collection, you could use the count or length of the collection.