ExecuteReaderAsync() returns some rows at first but empty seconds later

Question

What I'm trying to do is to call a stored proc using ado.net and get its result. I have tried to execute the stored proc in ssms and its working fine.

But when I tried to run it through the code, the result is empty, so I tried to debug and here is my issue.

Below is my code which I'm trying to debug.

using (SqlConnection sql = new SqlConnection(_connString)) 
        {
            using (SqlCommand cmd = new SqlCommand("GetDdmhData", sql))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@requestDate", dateReport.Value.Date.ToString("yyyy-MM-dd")));
                
                await sql.OpenAsync();

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        
                        var test = "";
                    }
                }                    
            }
        }

I have kindda weird issue. I placed my breakpoint at the below statement

while (await reader.ReadAsync())

Once my breakpoint is hit, I i did a QuickWatch on the 'reader' value and it returns me some rows.

Then I close the QuickWatch window, and straight away open back the Quickwatch window on the 'reader', and suddenly, the result is empty.

Can someone explain to me why is that happening and how to resolve this issue?

Show us a screenshot of the quickwatch window. My guess is that the quickwatch is inadvertently moving to the next row of data. — mjwills, Feb 09 '21 at 09:22
Note what the "Value" column states. "Expanding the Results View will enumerate..." looks like you've enumerated and exhausted the reader and it doesn't reset itself — pinkfloydx33, Feb 09 '21 at 09:44
Also I just verified that neither SqlDataReader nor DbDataReader has a Debugger Proxy (which makes sense, but I wanted to be sure) so when you expand its enumerating the "real" thing — pinkfloydx33, Feb 09 '21 at 09:48
Yep - so the short answer is "don't expand those results in the quickwatch window". — mjwills, Feb 09 '21 at 09:48

pinkfloydx33 · Accepted Answer · 2021-02-09T10:00:27.650

Note what the value column says:

Expanding the results will enumerate the IEnumerable

You're causing the data reader to enumerate its contents. Most enumerators don't reset, so when you expand it again you've exhausted it and thus no results. SqlDataReader doesn't have a Debugger Proxy so you are iterating the actual reader and it's results.

Note that this isn't unique to the data reader. This can happen with any enumerable. It's also partly why some products (like ReSharper but maybe the VS IDE natively?) warn about multiple enumerations of IEnumerable. You might exhaust it, or worse you might cause multiple round trips to the source (for example a LINQ query). The data reader in this case exhausts itself; the DbEnumerator calls Read, advancing the reader until there's no more data and then the reader does not reset.

ExecuteReaderAsync() returns some rows at first but empty seconds later

1 Answers1

Linked