Should I call SqlDataReader.HasRows if I am calling SqlReader.Read

Question

Trying to see if it is beneficial to add an if (dr.HasRows) before the while (dr.read()) function. I mean, technically if it doesn't have rows it isn't going to read, so would it matter if you checked this first?

using (SqlDataReader dr = cmd.ExecuteReader())
{
    if (dr.HasRows)
    {
        while (dr.Read())
        {
            ....do stuff here
        }
    }
}

or is this going to essentially do the exact same thing if you're just making sure it has values to provide...

using (SqlDataReader dr = cmd.ExecuteReader())
{
    while (dr.Read())
    {
        ....do stuff here
    }
}

It would be beneficial if there was an else clause on the if. Do you want special handling if there are no rows? — Mark Peters, Jan 07 '13 at 13:12

Vishal Suthar · Accepted Answer · 2013-08-01T05:52:32.580

20

No..It is not mandatory to check (dr.HasRows) if the DataReader contains any row or not.

Read() will return False if there are no more rows to fetch, but Reader.HasRows is much more telling as to what it does than Read() so it would be a good practice to use Reader.HasRows because you may accidentally do something other than Read() which may fall into exception.

edited Aug 01 '13 at 05:52

answered Jan 07 '13 at 13:17

Vishal Suthar

17,013
3
59
105

13

Be careful with HasRows as it may return false when Read() **would return SqlException** e.g. because there is an error in your SQL or there is a violation of data constraints on Insert, etc. Usually you need to know if there are really just no rows or there is an error. – Ivan Akcheurov Jan 16 '15 at 13:56
2

@IvanAkcheurov is correct. In addition to his examples, being selected as a deadlock victim will not be known by just using HasRows - SqlException is not thrown until Read() is invoked. – alexg Aug 29 '15 at 09:56
2

These two comments eventually change what ought to be the "good practise", i.e. don't short-circuit `Read()` with `HasRows()`. For this reason, i'm voting the answer down. – Johan Boulé Oct 22 '17 at 13:01
@JohanBoulé which is the good practice for you ? sample code ? – PreguntonCojoneroCabrón Nov 20 '17 at 12:28
@PreguntonCojoneroCabrón Ivan Akcheurov's comment is the answer: don't bother with HasRows. The original poster of the question provided a sample code which calls Read() directly, and I conclude this is the better practice. I also realise once again that the ADO.NET API has quite some bad design here and there. – Johan Boulé Nov 20 '17 at 12:45

score 6 · Answer 2 · answered Nov 28 '13 at 22:37

6

Be careful. HasRows() returns false for my CTE query, even though there are rows (437 rows actually).

answered Nov 28 '13 at 22:37

Chalky

1,624
18
20

score 2 · Answer 3 · answered Jan 07 '13 at 13:12

2

It's not mandatory to check if the DataReader has rows (dr.HasRows). The Read() method will return true if there is more data to read and false if there's no more data, thus breaking the while-loop.

answered Jan 07 '13 at 13:12

Abbas

14,186
6
41
72

score 1 · Answer 4 · answered Jan 07 '13 at 13:11

1

I think this is mostly for stored procedures which may or may not have data (one or more result sets) and it is "easier" to check first in case you also do other stuff than the while loop (i.e. initialize header/footer etc. when there is data).

answered Jan 07 '13 at 13:11

TomTom

61,059
10
88
148

score -4 · Answer 5 · edited Nov 20 '14 at 13:22

try

            string myconnection = "datasource= localhost;port=3306;username=root;password=root;";
            MySqlConnection myconn = new MySqlConnection(myconnection);

            //MySqlDataAdapter mydata = new MySqlDataAdapter();
            MySqlDataReader myreader;

            MySqlCommand SelectCommand = new MySqlCommand("select *from student_info.student_info where username= '" + textBox1.Text +" 'and password=' " + textBox2.Text +"';",myconn );


            myconn.Open();

            myreader = SelectCommand.ExecuteReader();
            int count = 0;
            if (myreader.HasRows) //returing false but i have 4 row
            {
                while (myreader.Read()) //returing false 
                {
                    MessageBox.Show("in button3");
                    count = count + 1;
                }
            }

your opinion required

Declare variables at the site of first use, and please use `using` statements. Downvoted. — Aluan Haddad, Apr 08 '17 at 01:06

Should I call SqlDataReader.HasRows if I am calling SqlReader.Read

5 Answers5

Linked