AzamSharp

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;

powered by IMHO