using .RecordsAffected C#

Question

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() );

rocket · Answer

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?

rocket · Answer

ok now it will return 2 records affected whenever I update 1 row. Otherwise it will report accurately...

rocket · Answer

thanks so much i turned the SET NOCOUNT ON to SET NOCOUNT OFF and it works now

Ryan Alford · Answer

it will return 0 if no records are affected. In your stored procedure do you have SET NOCOUNT ON?

rocket · Answer

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.

Ryan Alford · Answer

With update and delete statements you will be using the ExecuteNonQuery() method with returns an integer. That integer is the number of effected rows.

rocket · Answer

The problem is that this will not work for UPDATE and DELETE statements only for SELECT statements.

Ryan Alford · Answer

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.

using .RecordsAffected, C#