ExecuteReaderAsync() 首先返回一些行，但几秒钟后返回空 [英] ExecuteReaderAsync() returns some rows at first but empty seconds later

问题描述

我想要做的是使用 ado.net 调用存储过程并获取其结果.我尝试在 ssms 中执行存储过程并且它工作正常.

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())

一旦我的断点被击中，我就对 'reader' 值做了一个 QuickWatch，它返回了一些行.

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

然后我关闭 QuickWatch 窗口，并立即在阅读器"上重新打开 Quickwatch 窗口，结果突然变成空的.

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?

推荐答案

注意 value 列的内容:

Note what the value column says:

展开结果会枚举IEnumerable

Expanding the results will enumerate the IEnumerable

您导致数据读取器枚举其内容.大多数枚举器不会重置，所以当你再次扩展它时，你已经用完了它，因此没有结果.SqlDataReader 没有调试器代理，因此您正在迭代实际读取器及其结果.

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.

请注意，这不是数据阅读器独有的.这可能发生在任何可枚举项上.这也是某些产品(例如 ReSharper，但可能是 VS IDE 本身?)警告多个 IEnumerable 枚举的部分原因.您可能会耗尽它，或者更糟糕的是，您可能会导致多次往返源(例如 LINQ 查询).在这种情况下，数据读取器耗尽了自己；DbEnumerator 调用 Read，推进阅读器直到没有更多数据，然后阅读器不会重置.

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() 首先返回一些行，但几秒钟后返回空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！