Hi, 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
+1
A:
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.
Andomar
2009-11-14 10:14:29
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
2009-11-14 10:18:39
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
2009-11-14 10:49:25
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
2009-11-14 11:45:43
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
2009-11-14 13:50:24