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 · Accepted Answer · 2017-10-11 16:31:21Z

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, 2017 at 16:31

answered Feb 20, 2014 at 13:31

Lukazoid

19.5k5 gold badges67 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AStopher Over a year ago

Should be dt.Rows.Count.

Jan Van Herck · Accepted Answer · 2014-02-20 13:49:13Z

5

+50

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, 2014 at 13:49

Jan Van Herck

2,28417 silver badges16 bronze badges

2 Comments

Gerard ONeill Over a year ago

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().

AStopher Over a year ago

As stupid as it sounds, this doesn't answer the question.

Sahil Ali · Accepted Answer · 2014-02-23 04:03:08Z

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......







                    }

Aaron McKeehan · Accepted Answer · 2017-09-22 17:25:41Z

1

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++;
}

answered Sep 22, 2017 at 17:25

Aaron McKeehan

4204 silver badges6 bronze badges

Comments

Julio Cesar · Accepted Answer · 2019-02-28 14:29:05Z

1

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();

edited Feb 28, 2019 at 14:29

answered Feb 28, 2019 at 14:20

Julio Cesar

212 bronze badges

Comments

FuuRe · Accepted Answer · 2014-02-17 19:21:10Z

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, 2014 at 19:21

FuuRe

1521 silver badge7 bronze badges

1 Comment

Boardy Over a year ago

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

Community · Accepted Answer · 2017-05-23 12:17:26Z

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, 2017 at 12:17

CommunityBot

11 silver badge

answered Dec 8, 2014 at 10:05

Sold Out

1,5181 gold badge20 silver badges44 bronze badges

1 Comment

Gerard ONeill Over a year ago

Just noting that for some reason, the * before the count works, whereas the * after the count fails -- although you can specify columns there instead.

Sudhakar Tillapudi · Accepted Answer · 2014-02-17 19:20:22Z

-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, 2014 at 19:20

Sudhakar Tillapudi

26.3k5 gold badges43 silver badges70 bronze badges

2 Comments

Boardy Over a year ago

Thanks I was trying to avoid doing multiple queries, I.e. a SELECT COUNT(*) then my actual SELECT

AStopher Over a year ago

That's not what they asked.

AzelMalaysia · Accepted Answer · 2014-02-21 10:32:31Z

-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, 2014 at 10:32

AzelMalaysia

452 bronze badges

2 Comments

Boardy Over a year ago

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

Gerard ONeill Over a year ago

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.

Collectives™ on Stack Overflow

Checking the number of rows returned from MySQL Data Reader

9 Answers 9

1 Comment

2 Comments

Comments

Comments

Comments

1 Comment

1 Comment

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

1 Comment

2 Comments

Comments

Comments

Comments

1 Comment

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related