Search code examples
c#.netado.netexecutereader

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

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.

enter image description here

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

enter image description here

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

Solution

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