Checking the number of rows returned from MySQL Data Reader

Question

I am currently working on an C# project and I am trying to get the number of rows returned from MySQL Data Reader.

I know there is no direct function so I am trying to write my own. In the function, I pass it the MySQLDataReader object and then loop through the MySQL Data Reader and increment a counter and return the value of the counter.

This then seems to lock up the program, I guess because I am Reader.read() after I've got the count I'm already at the end. Instead I have tried creating a copy of the reader and then loop through the temp version but I get the same result.

Below is my code where I am executing the query and calling the function.

string query = "SELECT * FROM reports, software, platforms, versions "
                        + "WHERE EmailVerified = @verified AND reports.SoftwareID = software.id AND reports.PlatformID = platforms.id "
                        + "AND reports.VersionID = versions.id AND BugReportAcceptedNotificationSent = @notificationSent";
using (MySqlCommand cmd = new MySqlCommand(query, db.conn))
{
    cmd.Parameters.AddWithValue("@verified", "1");
    cmd.Parameters.AddWithValue("@notificationSent", "0");
    using (MySqlDataReader reader = cmd.ExecuteReader())
    {
        totalEmails = HelperClass.totalRowsInMySQLDataReader(reader);
        while (reader.Read())
        {
            currentEmailCount++;
            EmailNotifications emailNotification = new EmailNotifications(reader);
            emailNotification.sendNewBugReportAfterVerificationEmail(currentEmailCount, totalEmails);
        }
    }
}

Below is my function that gets the row count

public static int totalRowsInMySQLDataReader(MySqlDataReader reader)
{
    MySqlDataReader tempReader = reader;
    ILibraryInterface library = GeneralTasks.returnBitsLibrary(Configuration.databaseSettings, Configuration.engineConfig.logFile);
    string methodInfo = classDetails + MethodInfo.GetCurrentMethod().Name;
    try
    {
        int count = 0;
        while (tempReader.Read())
        {
            count++;
        }
        tempReader = null;
        return count;
    }
    catch (Exception ex)
    {
        string error = string.Format("Failed to get total rows in MySQL Database. Exception: {0}", ex.Message);
        library.logging(methodInfo, error);
        library.setAlarm(error, CommonTasks.AlarmStatus.Medium, methodInfo);
        return -1;
    }
}

Lukazoid · Answer 1 · 2017-10-11T16:31:21.330

7

Make use of a DataTable to load your results, e.g.

DataTable dt = new DataTable();
dt.Load(reader);
int numberOfResults = dt.Rows.Count;

You can then also iterate over the rows to read the values, e.g.

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

The other option is to issue two separate SQL statements, however you would need to ensure both statements were enclosed within a transaction with a Serializable isolation level, this is needed to make sure records aren't inserted between the execution of the two SQL statements.

edited Oct 11 '17 at 16:31

answered Feb 20 '14 at 13:31

Lukazoid

19,016
3
62
85

Should be `dt.Rows.Count`. – AStopher Oct 11 '17 at 16:21

score 5 · Accepted Answer · answered Feb 20 '14 at 13:49

5

To avoid multiple queries, how about including the total in the select itself?

SELECT COUNT(*) AS TotalNORows, * FROM reports, software, platforms, versions etc

answered Feb 20 '14 at 13:49

Jan Van Herck

2,254
17
15

This doesn't work with the *; you have to specify the columns. Even then, peter's point below is correct -- this will only return one row. -- EDIT: or place the * before the count(*). – Gerard ONeill May 13 '15 at 13:53
As stupid as it sounds, this doesn't answer the question. – AStopher Oct 11 '17 at 16:17

score 1 · Answer 3 · answered Feb 23 '14 at 04:03

i think without executing another command it's not possible...as there is no method available for count in reader class

you can try this... if it works..

string query = "SELECT * FROM reports, software, platforms, versions "
                    + "WHERE EmailVerified=@verified AND reports.SoftwareID=software.id AND reports.PlatformID=platforms.id "
                    + "AND reports.VersionID=versions.id AND BugReportAcceptedNotificationSent=@notificationSent";
                using (MySqlCommand cmd = new MySqlCommand(query, db.conn))
                {
                    cmd.Parameters.AddWithValue("@verified", "1");
                    cmd.Parameters.AddWithValue("@notificationSent", "0");


                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {


                  // create a new connection db.conn2 then  
                  MySqlCommand cmd2 = new MySqlCommand(query, db.conn2))
                   cmd2.Parameters.AddWithValue("@verified", "1");
                   cmd2.Parameters.AddWithValue("@notificationSent", "0");
                     MySqlDataReader reader2 = cmd2.ExecuteReader();
                     int numberofrow=0;
                    while(reader2.Read())
                     numberofrow++;


                         //your codes......







                    }

score 1 · Answer 4 · answered Sep 22 '17 at 17:25

Was working on the same problem. I hate having to iterate if a method is already available, but this is was the shortest bit I could come up with:

MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataReader reader = cmd.ExecuteReader();

int rowcount = 0;
while(reader.Read()){
 rowcount++;
}

Julio Cesar · Answer 5 · 2019-02-28T14:29:05.427

First, create this class:

public static class Extensions
{
    public static int Count(this MySqlDataReader dr)
    {
        int count = 0; 
        while(dr.Read())
            count++;

        return count;
    }
}

This will implement .Count () on MySqlDataReader.

int count = reader.Count();

Exemple:

string sql=  "SELECT * FROM TABLE";
MySqlCommand cmd = new MySqlCommand(sql, connection);
MySqlDataReader reader = cmd.ExecuteReader();
int count = reader.Count();

score 0 · Answer 6 · answered Feb 17 '14 at 19:21

0

Maybe you could look things the other way around

You could just do a select count(*) and get the row count

or

use a data adapter to fill a container (like a DataTable) and count the rows

answered Feb 17 '14 at 19:21

FuuRe

152
1
7

Thanks I was trying to avoid doing multiple queries, I.e. a SELECT COUNT(*) then my actual SELECT, I'll give the DataTable a try – Boardy Feb 17 '14 at 19:22

score 0 · Answer 7 · edited May 23 '17 at 12:17

0

Unfortunatelly solution from Jan Van @Herck will return one row only, so in case you are interested in getting all rows and their number in one select, this isn't what you need.

In that case I suggest uou to try this: select * , (select count(*) from my_table) AS numRow from my_table;

or read this: Getting total mysql results returned from query with a limit: FOUND_ROWS error

edited May 23 '17 at 12:17

Community

1
1

answered Dec 08 '14 at 10:05

Sold Out

1,321
14
34

Just noting that for some reason, the * before the count works, whereas the * after the count fails -- although you can specify columns there instead. – Gerard ONeill May 13 '15 at 13:57

score -1 · Answer 8 · answered Feb 17 '14 at 19:20

-1

You can use follwoing SQL Query to get the total rows Count.

SELECT COUNT(*) FROM [MYTABLE]

from the Code you can use ExecuteScalar() method to get the total number of rows returned by QUERY.

Try This:

int GetRowsCount(MySqlCommand command)
{    
int rowsCount=Convert.ToIn32(command.ExecuteScalar());
return rowsCount;    
}

Use above function as below:

MySqlCommand command=new MySlCommand("Select count(*) from MyTable",connectionObj);
int totalRows = GetRowsCount(command)

answered Feb 17 '14 at 19:20

Sudhakar Tillapudi

25,935
5
37
67

Thanks I was trying to avoid doing multiple queries, I.e. a SELECT COUNT(*) then my actual SELECT – Boardy Feb 17 '14 at 19:22
That's not what they asked. – AStopher Oct 11 '17 at 16:21

score -2 · Answer 9 · answered Feb 21 '14 at 10:32

-2

OleDbDataReader dbreader = new OleDbDataReader();
int intcount = 0;
if (dbreader.HasRows == true)
{
    if (dbreader.Read())
    {
    intcount = dbreader.RecordsAffected;
    }
}

"dbreader.RecordsAffected" will give you the number rows changed,inserted or deleted by the last statement of SQL

answered Feb 21 '14 at 10:32

AzelMalaysia

45
2

This doesn't answer my question, I'm doing a SELECT and need to get the number of rows returned, as you've said this is for database modification queries – Boardy Feb 21 '14 at 18:04
I think I saw that the RecordsAffected are only updated after the resultset has been closed -- a problem in addition to it only working on rows changed. – Gerard ONeill May 13 '15 at 14:10

Checking the number of rows returned from MySQL Data Reader

9 Answers9