How to count rows in MySqlDataReader?

Question

I have successfully switched my project from odbc.datareader to mysql.datareader. The problem is that with the first one /odbc datareader), the AffectedRows property retrieves the number of rows correctly even when it was pure query. But it doesn work with mysql.datareader, its -1 then. So I cannot see the way how to retrieve the number of rows, i.e. "result views". EDIT: I know its forward only reader, but what I dont understand is following: If a place a breakpoint to the line DBreader=command.ExecuteRader(), I can see that DBreader has - in the result view - as many objects as rows should be. How it comes that its known just after running? Thank you

score 3 · Accepted Answer · answered Nov 14 '09 at 10:14

3

The reason DataReader doesn't contain the rowcount is that it can be very expensive to calculate. For example, say you execute a query that returns purchase orders that were entered this year and not deleted:

SELECT * FROM PurchaseOrders 
WHERE PurchaseDt > '2009-01-01'
AND IsDeleted = 0

And you use this query with a DataReader and read out the first 10 rows. SQL Server "streams" rows to the client as it requests them. Whenever you ask for another row, SQL Server will execute the next step of the query. So not even SQL Server knows about the total number of rows before you've actually read out all the rows.

answered Nov 14 '09 at 10:14

Andomar

232,371
49
380
404

How can I ask only for 10 rows? If I execute SELECT * .... I assume C# data reader do it all in one step, I mean the app will freeze until the query isnt completed? – Petr Nov 14 '09 at 10:18
One way to ask for 10 rows is to call Read() only 10 times. Another would be to append `LIMIT 10` at the end of the MySQL query – Andomar Nov 14 '09 at 10:49
Yes but I do not want to do that :) I was asking what if I send simple SELECT all query, so the db connection works with every Read and then its closed? – Petr Nov 14 '09 at 11:45
Not sure if that's what you're asking, but you can run multiple queries using the same connection. You've got to close the old DataReader before you can open a new one with ExecuteReader. – Andomar Nov 14 '09 at 13:50

score 2 · Answer 2 · edited Jun 16 '12 at 15:47

To count how many rows in a table (for instance the name is studentTable), firstly I use following SQL statement:

SELECT COUNT(*) FROM studentTable

I use that statement as the command text for the MySqlCommand object.

Then to know the value (how many rows) using an object of MySqlDataReader (for instance its name is reader) I use following code:

reader.GetString(0);

Below is the code that I use:

...            
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand command = conn.CreateCommand();
command.CommandText = "SELECT COUNT(*) FROM studentTable";
try
{
    conn.Open();
}
catch (Exception ex)
{
    label1.Content = ex.Message;
}

reader = command.ExecuteReader();
while (reader.Read())
{
    label1.Content = "";
    label1.Content = reader.GetString(0);
}

reader.Close();
conn.Close();

Guidex · Answer 3 · 2017-09-29T18:41:10.267

1

Es muy simple, el reader no tiene la opción de contar cuantas filas existe, el Datatable si tiene esa opción. Entonces lo que hacemos es pasar todos los datos del Reader al Datatable y trabajamos con este (se muestra como recuperar el total de filas, y como recuperar un registro especifico).

String consulta = "SELECT * FROM xxx";
conexion.Open();
comando.CommandText = consulta;

reader = command.ExecuteReader();

DataTable dt = new DataTable();
dt.Load(reader);

int nrofilas = dt.Rows.Count;

foreach (DataRow dr in dt.Rows)
{
    var value = dr["nameField"];
}

edited Sep 29 '17 at 18:41

answered Sep 27 '17 at 00:31

Guidex

11
2

Welcome to Stack Overflow. Please include some explanation with your code (what did you fix? where did the original question go wrong?) Answers with only uncommented code can be flagged for being low quality and deleted. – Graham Sep 27 '17 at 00:51

score 0 · Answer 4 · answered Mar 08 '11 at 16:46

Below is the function that I use. Feel free to adjust it to your needs.

    /// <summary>
    /// Counts the number of rows in a given table.
    /// </summary>
    /// <param name="tableName">The name of the table to query.</param>
    /// <param name="closeConnectionWhenDone">A flag indicating whether the connection should be closed once the query is executed.</param>
    /// <returns>The number of rows in the table.</returns>
    private static int GetNumRowsInTable(string tableName, bool closeConnectionWhenDone = false)
    {
        string selectQueryString = String.Format("select 1 from {0};", tableName);
        int numRows = 0;
        CommandBehavior behavior = closeConnectionWhenDone ? CommandBehavior.CloseConnection : CommandBehavior.Default;
        using (var command = new OdbcCommand(selectQueryString, ODBCHelper.Connection))
        using (var reader = command.ExecuteReader(behavior))
        {
            while (reader.Read())
            {
                numRows++;
            }
        }

        return numRows; 
    }

How to count rows in MySqlDataReader?

4 Answers4