locked
Number of rows returned by a DataReader RRS feed

  • Question

  • User-408371289 posted
    Hi, Is there any way to know how many rows the data reader returns after executing a SQL statement? For now, I am doing the follwing: if datareader.hasrows then while datareader.read count += 1 end while end if But this method has its own problems. I think the "hasrows" property messes with the "read" method somehow, because if my datareader returns just 1 row, then it enters the if statement above, but doesn't go into the while statement!! Strange!!! please help if you can. Thanks a lot.
    Monday, March 22, 2004 3:49 PM

All replies

  • User-973072541 posted
    An easy way out is to use a query that returns the results PLUS the number of rows as another result set. Then you can use datareader.NextResult to go to the next result set and get the number of rows. Its not only easier but also faster because the database does the row counting.
    Monday, March 22, 2004 4:24 PM
  • User-408371289 posted
    Thanks a lot. Actually, I knew this solution but was wondering if there was an easy way!
    Monday, March 22, 2004 5:28 PM
  • User-2013806645 posted
    have you tried just using the COUNT(*) method in your SQL statement?... it returns the number of rows found... strSQL = " SELECT COUNT(*) AS numofrows FROM tblYourTable "; int intNumOfRows = 0; if (dtrYourDataReader.Read() == true) { intNumOfRows = Convert.ToInt32(dtrYourDataReader["numofrows"]); } //...
    Tuesday, March 23, 2004 3:59 AM
  • User-1754408712 posted
    You can get the number of records in a DataReader by looking at the property called RecordsAffected. This interger property will tell you the number of Rows currenlty inside your DataReader. Not only you do not need to iterate through the DataReader, but your connection can be Closed and you can still check for that property.
    Wednesday, April 14, 2004 1:21 PM
  • User-973072541 posted
    The problem with that property is that you first have to read all the rows and then close the data reader - only then does it return the number of records. Besides, it works only for queries that "affect" data i.e. insert, update or delete queries, it does not work for select queries.
    Thursday, April 15, 2004 10:38 AM
  • User-1754408712 posted
    To answer your Post on 03-22-2004 03:49 PM: I tried the OleDbDataReader against a DB2 database and surprisingly it comes back with the number of rows even if you are running just a SELECT statement and it is not only for CHANGED records and you do not need to iterate through the data in a loop at all. However, I tried the RecordsAffected property for both an SQL server and an Access database and unfortunately, I got values of 0 both times even though there were rows returned. In short, there are two ways: 1- iterate through the data: which is very quick as well as efficient with a datareader and have a counter 2- have a COUNT function to count the number of rows first, and then depending on what you do with that number, run the query again the second time. I do not suggest the latter suggestion because you are running TWO queries and that is costly. You are probably going to iterate through the data at some point anyways, so you might as well use the first suggestion. Now you can always put a condition to stop iterating when you get to a certain number. Two questions for you: a- what database are you goin against and b- why do you need a record count Code examples: ------------------ suggestion 1: ------------------ Dim intRows as int32=0 Dim maxRows as int32=100 'maximum rows you want to iterate through objCommand.CommandText="SELECT field0, field1 FROM table" objDataReader=objCommand.ExecuteReader() 'open your datareader with the data Do While objDataReader.Read() AndAlso intRows<maxRows ReDim Preserve arrValues(intRows) 'use GetString or GetInteger if you know what the data type is '0 is the column ordinal (first field in your SELECT statement) arrValues(intRows)=objDataReader.GetValue(0) intRows+=1 'add one to your counter Loop objDataReader.Close() '***intRows is equal to total rows*** ------------------ suggestion 2: ------------------ Dim totalRows as int32=0 objCommand.CommandText="SELECT COUNT(*) FROM table" objDataReader=objCommand.ExecuteReader() 'open your datareader with the data objDataReader.Read() totalRows=objDataReader.GetInteger(0) objDataReader.Close() '***totalRows is equal to total rows*** Redim arrValues(totalRows-1) then iterate through the data in the DataReader. Good luck :)
    Thursday, July 15, 2004 1:30 PM
  • User426001450 posted

    Use this code sample to find the number of records returned by Data Reader:

    con.Open()
     cmd = New SqlCommand("SELECT * ENTER YOUR SQL STATEMENT HERE )", con)
    
    'This part activate the con statistic
    con.StatisticsEnabled = True
    
    Dim reader As SqlDataReader = cmd.ExecuteReader()
    
    While reader.Read()
         'Do nothing something here .........
    End While
    
    'This part return the selected rows
    Dim stats As IDictionary = con.RetrieveStatistics
    SelectRows = CType(stats("SelectRows"), Int64)
    
    RowResultsLabel.Text = "The number of returned records is: " & SelectRows
    

    Monday, April 23, 2018 5:08 AM
  • User2034807014 posted

    Old post but I had this issue when implementing an EF core interceptor and ended up using

    result.Cast<object>().Count()

    where result is a

    DbDataReader result
    Thursday, June 24, 2021 10:24 AM