The DataReader object in ASP.NET 2.0 provides you with a property RecordsAffected which can be used to find the number of rows updated, deleted or inserted. You can simply use it like this:
string updateQuery = "UPDATE Person SET LastName = 'Saif' WHERE PersonID IN(13,14)";
SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlCommand myCommand = new SqlCommand(updateQuery, myConnection);
SqlDataReader reader = null;
myConnection.Open();
reader = myCommand.ExecuteReader();
while (reader.Read())
{
// Perform some action like fill a custom collection or something
}
return reader.RecordsAffected;
Now this works for INSERT, UPDATE and DELETE. But what if I want to find out how many rows were selected. Offcourse I can create a query using the COUNT function to find the number of rows that are selected but if I want to do it using C# code than I can think of only incrementing the count like in the example below:
SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlCommand myCommand = new SqlCommand("SELECT * FROM Categories", myConnection);
SqlDataReader reader = null;
myConnection.Open();
reader = myCommand.ExecuteReader();
while (reader.Read())
{
// NOTE: This will only return the correct value for the SELECT statement
recordsAffected++;
}
return recordsAffected;