How to count rows in MySqlDataReader?

c# mysql datareader
Petr picture Petr · Nov 14, 2009 · Viewed 15.5k times · Source

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

Answer

Andomar picture Andomar · Nov 14, 2009

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.

Related questions

Using MySQL Data Reader

I'm not familiar with using Data Reader, i need help with the following code, i want to retrieve a single data from the database. MySqlDataAdapter data = new MySqlDataAdapter(cmd); conn.Open(); DataTable dt = new DataTable(); data.Fill(dt); gridView1.DataSource = …

Read More
Invalid attempt to Read when reader is closed

I have a common database class for my application and in that class i have a function public MySqlDataReader getRecord(string query) { MySqlDataReader reader; using (var connection = new MySqlConnection(connectionString)) { connection.Open(); using (var cmd = new MySqlCommand(query, connection)) { reader = …

Read More
MySqlDataReader: DataTable.Fill(reader) throws ConstraintException

I have two tables orders and orderdetails table orders (PK = id, UNIQUE index on orderno) |id|orderno| | 1|1000 | | 2|1001 | table orderdetails (PK = id) |id|orderid|item|qty| | 1| 1|ABC | 3| | 2| 1|XYZ | 4| Now I want to query the data with: SELECT o.orderno, od.item, …

Read More