Geeks With Blogs
AzamSharp Some day I will know everything. I hope that day never comes.

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

 

Posted on Monday, October 17, 2005 5:12 PM | Back to top


Comments on this post: Using DataReader.RecordsAffected in ASP.NET 2.0

# re: Using DataReader.RecordsAffected in ASP.NET 2.0
Requesting Gravatar...
I'm not sure why someone would use that property. ExecuteNonQuery() also returns the number effected.
Left by Matt Watson on Oct 17, 2005 2:01 PM

# re: Using DataReader.RecordsAffected in ASP.NET 2.0
Requesting Gravatar...

There can be a situation where you need to fill a collection and send the collection back only when you have certain number of RowsAffected. Take a look at the code below:

<pre>
public ArrayList DataReaderCountTest()
{
ArrayList personList = new ArrayList();

string query = "INSERT INTO Person(LastName) VALUES('Johny Bravo')";
SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlCommand myCommand = new SqlCommand(query, myConnection);

SqlDataReader reader = null;
myConnection.Open();
reader = myCommand.ExecuteReader();

while (reader.Read())
{

Person p = new Person();
p.FirstName = reader["FirstName"] as String;
p.LastName = reader["LastName"] as String;

// Populate the collection
personList.Add(p);

// RecordsAffected will not print anything here since
// it does not know how many are effected at this point

}

if (reader.RecordsAffected > 0) // 0 can be any number depending on your criteria
{

// A record has been inserted
// Now I can return the collection
return personList;
}

// else just return null or something
return null;
}
</pre>
Left by Azamsharp on Oct 17, 2005 2:37 PM

Your comment:
 (will show your gravatar)


Copyright © Mohammad Azam | Powered by: GeeksWithBlogs.net