How to Get Number of Rows Using Sqldatareader in C#

How to get number of rows using SqlDataReader in C#

There are only two options:

• Find out by reading all rows (and then you might as well store them)

• run a specialized SELECT COUNT(*) query beforehand.

Going twice through the DataReader loop is really expensive, you would have to re-execute the query.

And (thanks to Pete OHanlon) the second option is only concurrency-safe when you use a transaction with a Snapshot isolation level.

Since you want to end up storing all rows in memory anyway the only sensible option is to read all rows in a flexible storage (List<> or DataTable) and then copy the data to any format you want. The in-memory operation will always be much more efficient.

SQLDataReader Row Count

SQLDataReaders are forward-only. You're essentially doing this:

count++;  // initially 1
.DataBind(); //consuming all the records

//next iteration on
.Read()
//we've now come to end of resultset, thanks to the DataBind()
//count is still 1 

You could do this instead:

if (reader.HasRows)
{
    rep.DataSource = reader;
    rep.DataBind();
}
int count = rep.Items.Count; //somehow count the num rows/items `rep` has.

How to get the number of records in a Data Reader?

After looping through the results of your query you can use RecordsAffected:

mybtnreader1 = command.ExecuteReader();

 while(mybtnreader1.Read())
        {

          ///do your stuff
        }

mybtnreader1 .Close();
MessageBox.Show(mybtnreader1 .RecordsAffected.ToString());

How to read rows count using SqlDataReader

try

int rows = 0;
if(rdr1.Read()) {
    rows = (int) rdr1["Rows"];
}

How to read SQL Server COUNT from SqlDataReader

You have to read it:

if (mySqlDataReader.Read()) {
  count = mySqlDataReader.GetInt32(0);
}

Alternatively, you can just use ExecuteScalar:

int count = (int)cmd.ExecuteScalar();

which is defined as:

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

How can I figure out the number of rows a Database has using MySqlDataReader?

You're only reading one row of your result set.

Instead of

if (mdr.Read())

you should use

while (mdr.Read())

You should change your SQL to say SELECT appointmentId FROM U05lUM.appointment;. It's wasteful and slow to read all the columns (with SELECT *) from the table when all you want is the single column.

mdr.HasRows will always be true inside if (mdr.Read()) or while (mdr.Read()) blocks. You just read a row, so the resultset definitely has rows.

Your foreach(int id in mdr.GetInt32("appointmentId").ToString()) does not do what you want. It generates a value for each character in the string you made from the appointmentId.

And, the SqlDataReader object called mdr needs to be cleaned up (Disposed) after use. That happens automatically with using(){}.

So I suggest this code.

    ...
    string Query = "SELECT appointmentId FROM U05lUM.appointment;";
    MySqlCommand command = new MySqlCommand(Query, Conn);
    using (SqlDataReader resultSet = command.ExecuteReader()) 
    {
        while (resultSet.Read())
        {
            int id = resultSet.GetInt32("appointmentId")
            AppointmentIDList.Add(id);
        }
    }

This won't be fast if your table is large, because your query must scan through every row of the table. The whole point of SQL is to let you handle datasets orders of magnitude larger than your RAM. You should consider designing your program so it doesn't have to read every row unless absolutely necessary.

If all you want is an exact count of the number of rows, you can use COUNT(*) . This gets you the count of rows in your table

    ...
    int rowCount;
    string Query = "SELECT COUNT(*) rowCount FROM U05lUM.appointment;";
    MySqlCommand command = new MySqlCommand(Query, Conn);
    using (SqlDataReader resultSet = command.ExecuteReader()) 
    {
        if (resultSet.Read())   // THIS result set has only one row.
        {
            rowCount = resultSet.GetInt32("rowCount")
        }
    }

Your DBMS is smart about how it handles COUNT(*). MAX(appointmentId) might also work. It could be different from the row count if there are some missing appointmentId values in your table. That could happen if some rows were deleted.

How to get number of rows from NpgsqlDataReader

From a bit of googling the NpgsqlDataReader would seem to be very similar to the sqldatareader. The sqldatareader does not have a built in way to get the row count. It would appear you would have to loop through and perform your own count if you wanted to get the rowcount.

DataReader does not return data but the number of rows

You need to read the reader

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader#:~:text=To%20retrieve%20data%20using%20a,rows%20from%20a%20data%20source.

SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
    while (reader.Read())
    {
           // 
    }
}

---

Related Topics