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
– Mark Peters, Commented Jan 7, 2013 at 13:12

Vishal Suthar · Accepted Answer · 2013-08-01 05:52:32Z

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 1, 2013 at 5:52

answered Jan 7, 2013 at 13:17

Vishal Suthar

17.2k4 gold badges64 silver badges109 bronze badges

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

5 Comments

Ivan Akcheurov Over a year ago

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.

alexg Over a year ago

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

Johan Boulé Over a year ago

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.

PreguntonCojoneroCabrón Over a year ago

@JohanBoulé which is the good practice for you ? sample code ?

Johan Boulé Over a year ago

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

Chalky · Accepted Answer · 2013-11-28 22:37:52Z

6

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

answered Nov 28, 2013 at 22:37

Chalky

1,65218 silver badges21 bronze badges

Comments

Abbas · Accepted Answer · 2013-01-07 13:12:18Z

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 7, 2013 at 13:12

Abbas

14.5k6 gold badges43 silver badges77 bronze badges

Comments

TomTom · Accepted Answer · 2013-01-07 13:11:13Z

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 7, 2013 at 13:11

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Comments

ArieKanarie · Accepted Answer · 2014-11-20 13:22:34Z

-4

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

edited Nov 20, 2014 at 13:22

ArieKanarie

1,0234 gold badges18 silver badges32 bronze badges

answered Jun 14, 2013 at 6:24

adnan

1

1 Comment

Aluan Haddad Over a year ago

Declare variables at the site of first use, and please use using statements. Downvoted.

Collectives™ on Stack Overflow

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

5 Answers 5

5 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related