HasRows property in MySqlDataReader C# always return true value?

Question

In my code when I debug using Visual Studio C# I noticed that although there are no rows in the query :

select MAX(idSalesJournal) as maxId from accountingsystemdb.salesjournal"

the dr.HasRows property always returns TRUE. When I continue debugging I noticed that the loop will run for 1 time before exiting from the loop. Now I have a big problem because although there are no data in the dataset how the loop will run 1 time. Then I printed the value I get in a messagebox and it shows nothing (blank). How could this happen and how can I avoid this thing ? I will put an image of the same query that I have executed in the mySql workbench which executed successfully and didn't show any rows in the result. It shows 0 rows in MySql. Same query executed in C# will return Hasrows property as True. I have put my complete code in below.

    public void salesJournal(string addOrRemove, string lorryNo, string invoiceNo, DateTime billDate, string source, DateTime paybackDate, string itemCode, double itemcost, string desc, double qty, double discount, double amount, string debtor)
    {

        if (conn.State.ToString() == "Open")
        {
            conn.Close();
            conn.Open();
        }

        int maxID = 0;

        if (conn.State.ToString() == "Closed")
        {
            conn.Open();
            MySqlCommand cmd = conn.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;

            //---------------------- Selecting Max ID ------------------------------------------
            cmd.CommandText = "select MAX(idSalesJournal) as maxId from accountingsystemdb.salesjournal";

            try
            {
                MySqlDataReader dr = cmd.ExecuteReader();

                if (dr.HasRows && dr != null)
                {

                    while (dr.Read())
                    {                            

                        maxID = Convert.ToInt32(dr["maxId"].ToString());

                    }
                }
                else
                {
                    maxID = 0;
                }
            }
            catch(Exception e)
            {
                MessageBox.Show(e.ToString());
            }

            conn.Close();
            //---------------------------------------------------------------------------------


            if (conn.State.ToString() == "Open")
            {
                conn.Close();
                conn.Open();
            }
            else
            {
                conn.Open();
            }

            try
            {
                cmd.CommandText = "insert into accountingsystemdb.salesjournal VALUES('" + maxID + 1 + "', STR_TO_DATE('" + billDate + "', '%m/%d/%Y %h:%i:%s %p'), '" + invoiceNo + "', '" + lorryNo + "', '" + source + "', '" + itemCode + "', '" + desc + "', '" + qty + "', '" + itemcost + "', '" + amount + "', '" + discount + "', '" + addOrRemove + "', STR_TO_DATE('" + paybackDate + "', '%m/%d/%Y %h:%i:%s %p'), '" + debtor + "', '" + Program.username + "')";
                cmd.ExecuteNonQuery();
            }
            catch(Exception e)
            {
                MessageBox.Show(e.ToString());
            }

            conn.Close();

        }


    }

Even there will be `0`, `MAX` does return a value in my opinion. What your command returns in your database manager? As you said your `while` loops 1 time, that means you **have** a row _actually_ and it is too normal `HasRows` returns `true`. — Soner Gönül, May 20 '15 at 11:58

score 1 · Answer 1 · answered May 20 '15 at 12:06

1

You're using the incorrect tool for the job. DataReader is supposed to be used to iterate over a data set and you expect only one value (or null) to be returned.

The correct method to use is MySqlCommand.ExecuteScalar

try
{
    var result = cmd.ExecuteScalar();

    if (result != null)
    {
        maxID = Convert.ToInt32(result);
    }
    else
    {
        maxID = 0;
    }
}
catch(Exception e)
{
    MessageBox.Show(e.ToString());
}

answered May 20 '15 at 12:06

Claudio Redi

67,454
15
130
155

I guess `ExecuteScalar` is used when a single value is returned. Check [reference](https://stackoverflow.com/questions/2974154/what-is-the-difference-between-executescalar-executereader-and-executenonquery#:~:text=ExecuteScalar%20is%20typically%20used%20when,col1%2C%20col2%20from%20sometable%20).) – Beats2019 Jul 12 '21 at 02:58

Ajay · Answer 2 · 2015-05-20T12:07:17.747

0

Instead of using ExecuteReader use ExecuteScalar

Try this

object countObj = cmd.ExecuteScalar();
int maxCount = 0;
if (countObj != null)
{
    int.TryParse(countObj.ToString(), out maxCount);
}

edited May 20 '15 at 12:07

answered May 20 '15 at 12:04

Ajay

6,418
18
79
130

1

How this answers to question? o.O – Soner Gönül May 20 '15 at 12:05

score 0 · Answer 3 · answered May 20 '15 at 12:12

Yes. it is because,if nothing is there the cell value will be null in SQL SERVER. so the text "NULL" is the result returned in SQLServer.

Better use dr.read() to check whether the reader HasRows

if(dr.read())  
{
 // code logic for HasRows
}
else
{
// if nothing is there
}

score 0 · Answer 4 · answered May 20 '15 at 12:24

A query to a MySql database can return TWO result sets. The first result set (aka table) is the number of rows returned/affected, the second, the results of your actual query.

So HasRows being set to true is accurate, because you're reading the first result set, which has 1 row containing the number of rows returned from your actual query.

Call dr.NextResult() to get the actual query results.

HasRows property in MySqlDataReader C# always return true value?

4 Answers4