Is there a bug in SqlDataReader.HasRows when running against SQL Server 2008?

Question

Take a look at these two queries:

-- #1
SELECT * FROM my_table
WHERE CONTAINS(my_column, 'monkey')

-- #2
SELECT * FROM my_table
WHERE CONTAINS(my_column, 'a OR monkey')  -- "a" is a noise word

Query #1 returns 20 rows when I run it in Management Studio.
Query #2 returns the same 20 rows, but I also see the following in the Messages tab:

Informational: The full-text search condition contained noise word(s).

So far, so boring - exactly what I'd expect to happen.

Now, take a look at this C# snippet:

using (SqlConnection conn = new SqlConnection(...))
{
    SqlCommand cmd = conn.CreateCommand();
    // setup the command object...

    conn.Open();
    using (SqlDataReader dr = cmd.ExecuteReader())
    {
        if (dr.HasRows)
        {
            // get column ordinals etc...

            while (dr.Read())
            {
                // do something useful...
            }
        }
    }
}

When I run this code against query #1 everything behaves as expected - the "do something useful" section gets hit for each of the 20 rows.

When I run it against query #2, nothing happens - the "do something useful" section is never reached.

Now here's where things get a bit more interesting...

If I remove the HasRows check then everything works as expected - the "do something useful" section gets hit for each of the 20 rows, regardless of which query is used.

It seems that the HasRows property isn't populated correctly if SQL Server generates a message. The results are returned and can be iterated through using the Read() method but the HasRows property will be false.

Is this a known bug in .NET and/or SQL Server, or have I missed something obvious?
I'm using VS2008SP1, .NET3.5SP1 and SQL2008.

EDIT: I appreciate that my question is very similar to this one, and it's almost certainly a manifestation of the same issue, but that question has been bogged down for three months with no definitive answer.

Answer 1

That certainly is strange behavior - but I am wondering why you need to check HasRows if you are going to simply iterate the result-set.

The HasRows property encapsulates a _hasRows field which gets set in to true or false inside SqlDataReader in many different places for many different reasons. Most of these places it is set to true if the private TdsParserStateObject's PeekByte method returns a number that indicates data is present.

Answer 2

As with Andrew I am not sure why you wouldn't just use the while loop, to avoid the performance hit of additional GetOrdinal calls. you could use a flag value to do the GetOrdinal calls on first row, then after that the code can be skipped.

I have noticed similar issues with HasRows in the past, and have gone to a pattern similar to mine listed above with minimal issues.

Answer 3

Sounds like HasRows is one of those properties which value is not guaranteed to be accurate...

I agree with the previous two posts (about going straight into while(dr.Read()) and get the ordinals in the first iteration). Also, why not get a dataset instead of a data reader? If in this case you're only dealing with 20 rows, getting the whole dataset at once might not be much of a performance hit compared to using a data reader. I know this doesn't really answer your question, but just a thought for a workaround.

Answer 4

Please could you try the following and report if it was successful or otherwise:

using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleResult))

Thanks

Answer 5

Hi, I'm the original poster of the refernced question (lost login) and never managed to figure it out. In the end I put it down to bad voodoo, sacrificed neatness and went with something like

bool readerHasRows=false;
while(reader.reader())
{
   readerHasRows=true;
   doStuffOverAndOver();
}
if (!readerHasRows)
{
   probablyBetterShowAnErrorMessageThen();
}

What was really weird was that it worked in one aspx page and not in a another despite the code blocks being almost identical bar the stored procedure used.

Needless to say I'm avoiding .HasRows from now on ;)

EDIT - Management Studio shows messages in the messages tab on the problem procedure in my project too. So that seems to be the cause of the problem. But why would it bugger up .HasRows??

EDIT2 - Confirmed, altered the query to avoid the warning messages and .hasrows is now true.

Answer 6

I'd suggest calling the NextResult method if multiple result sets are suspected to be causing this problem. Since the first result set is the one that appears to be empty, using CommandBehavior.SingleResult would not change the behavior, since the first (empty) result would still be returned. You could try that. Anyway, I have heard this was a bug, but I don't remember where I read it, and a quick Google search didn't yield any results.