如何使 SqlDataReader.ReadAsync() 异步运行? [英] How do I make SqlDataReader.ReadAsync() run asynchronously?

问题描述

当调用实际执行需要时间的事情的 SQL Server 时，SqlDataReader.ReadAsync() 为我同步运行.有什么方法可以强制它异步运行还是我唯一的选择是在 Task.Run() 中调用它?

这是一个复制品.它使用 winforms 来证明调用阻塞了 GUI 线程.请注意，T-SQL 必须实际执行某些操作——这无法通过 WAITFOR DELAY '00:00:20' 重现.

使用系统；使用 System.Configuration；使用 System.Data.Common；使用 System.Data.SqlClient；使用 System.Threading.Tasks；使用 System.Windows.Forms；静态类 SqlDataReaderReadAsyncProgram{static async void Form_Shown(object sender, EventArgs e){var form = (Form)sender;//在 app.config 中声明你的连接字符串，比如//<connectionStrings><remove name="LocalSqlServer"/><add name="LocalSqlServer" connectionString="Data Source=localhostSQLEXPRESS;Integrated Security=true"/></connectionStrings>使用 (DbConnection 连接 = 新的 SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString)){form.Text = "正在连接...";等待连接.OpenAsync();form.Text = "已连接！";//安装一个存储过程.使用 (var command = connection.CreateCommand()){command.CommandText = "SET NOCOUNT ON"+选择'a'"+ " 声明 @t 日期时间 = SYSDATETIME()"+ " WHILE DATEDIFF(s, @t, SYSDATETIME()) < 20 BEGIN"+ " 选择 2 x INTO #y"+ " 删除表 #y"+结束"+ "选择'b'";form.Text = "正在执行...";使用 (var reader = await command.ExecuteReaderAsync()){form.Text = "正在阅读...";做{//在第二次调用时阻塞，直到 SQL Server 返回第二个结果集而(等待读者.ReadAsync()){}} 而(等待读者.NextResultAsync())；form.Text = "完成！";}}}等待 Task.Delay(TimeSpan.FromSeconds(5));form.Close();}[STA线程]静态无效主要(){Application.EnableVisualStyles();Application.SetCompatibleTextRenderingDefault(false);变种形式 = 新形式()；form.Shown += Form_Shown;应用程序.运行(窗体)；}}

当我运行它时，窗口在报告完成之前变为(未响应)"20 秒(请注意，在 VS 中调试时，(未响应)"文本不会出现，但它仍然冻结相同).如果我在 VS 中调试并在它冻结时将其中断，我会看到它与一个如下所示的调用堆栈坐在一起:

 [托管到本机转换]System.Data.dll!SNINativeMethodWrapper.SNIReadSyncOverAsync(System.Runtime.InteropServices.SafeHandle pConn, ref System.IntPtr packet, int timeout) 未知System.Data.dll!System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() 未知System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() 未知System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() 未知System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadByteArray(byte[] buff, int offset, int len, out int totalRead) 未知System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadInt64(out long value) 未知System.Data.dll!System.Data.SqlClient.TdsParser.TryProcessDone(System.Data.SqlClient.SqlCommand cmd，System.Data.SqlClient.SqlDataReader 阅读器，参考 System.Data.SqlClient.RunBehavior 运行，System.Data.SqlClient.TdsParserStateObject stateObj) 未知System.Data.dll!System.Data.SqlClient.TdsParser.TryRun(System.Data.SqlClient.RunBehavior runBehavior, System.Data.SqlClient.SqlCommand cmdHandler, System.Data.SqlClient.SqlDataReader dataStream, System.Data.SqlClient.BulkCopySimpleResultSetbulkCopyHandler, System.Data.SqlClient.TdsParserStateObject stateObj, out bool dataReady) 未知System.Data.dll!System.Data.SqlClient.SqlDataReader.TryHasMoreRows(out bool moreRows) 未知System.Data.dll!System.Data.SqlClient.SqlDataReader.TryReadInternal(bool setTimeout, out bool more) 未知System.Data.dll!System.Data.SqlClient.SqlDataReader.ReadAsync.AnonymousMethod__0(System.Threading.Tasks.Task t) 未知System.Data.dll!System.Data.SqlClient.SqlDataReader.InvokeRetryable<bool>(System.Func<System.Threading.Tasks.Task, System.Threading.Tasks.Task<bool>> moreFunc, System.Threading.Tasks.TaskCompletionSource 源，System.IDisposable objectToDispose) 未知System.Data.dll!System.Data.SqlClient.SqlDataReader.ReadAsync(System.Threading.CancellationToken cancelToken) 未知System.Data.dll!System.Data.Common.DbDataReader.ReadAsync() 未知>SqlDataReaderReadAsync.exe!SqlDataReaderReadAsyncProgram.Form_Shown(object sender, System.EventArgs e) 第 36 行 C#[恢复异步方法]

(为简洁起见进一步修剪).

整个 ReadSyncOverAsync 东西在我看来特别可疑.就像 SqlClient 假设同步读取不会阻塞一样，好像它不知道如何使用非阻塞 IO 什么的.然而，当查看参考源或使用 JustDecompile 进行反编译时，似乎应该支持异步，但它只是以某种方式启发式/回退地决定不使用它.

那么，如何让 SqlClient 中的 *Async() 内容真正异步?我认为这些方法应该使我能够编写无线程响应式 GUI 程序而无需使用 Task.Run() 因为在 Task.Run() 只是让它们异步是没有意义的开销……?

我正在使用 .net-4.7.02542.

我假设这是一个 .net 错误并已提交

您的应用程序必须以 .net-4.8 为目标才能获得修复(仅安装更新并不能修复已编译的应用程序).不幸的是，没有记录 <AppContextSwitchOverrides/> 用于此功能，因此如果您必须继续针对旧版本的 .net，则无法选择修复.(但是，您可以在编译时以 .net-4.8 为目标，编辑 «ProgramName».config 以更改 ，然后注意不要使用 .net 中在您定位的版本之后引入的任何 API).

When making calls to SQL Server that actually do things that take time, SqlDataReader.ReadAsync() runs synchronously for me. Is there any way to force it to run asynchronously or is my only option to call it in Task.Run()?

Here is a repro. It uses winforms to demonstrate that the call blocks the GUI thread. Note that the T-SQL has to actually do something—this is not reproducible with WAITFOR DELAY '00:00:20'.

using System;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Threading.Tasks;
using System.Windows.Forms;

static class SqlDataReaderReadAsyncProgram
{
    static async void Form_Shown(object sender, EventArgs e)
    {
        var form = (Form)sender;
        // Declare your connection string in app.config like
        // <connectionStrings><remove name="LocalSqlServer"/><add name="LocalSqlServer" connectionString="Data Source=localhostSQLEXPRESS;Integrated Security=true"/></connectionStrings>
        using (DbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString))
        {
            form.Text = "connecting…";
            await connection.OpenAsync();
            form.Text = "connected!";
            // Install a stored procedure.
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SET NOCOUNT ON"
                    + " SELECT 'a'"
                    + " DECLARE @t DATETIME = SYSDATETIME()"
                    + " WHILE DATEDIFF(s, @t, SYSDATETIME()) < 20 BEGIN"
                    + "   SELECT 2 x INTO #y"
                    + "   DROP TABLE #y"
                    + " END"
                    + " SELECT 'b'";
                form.Text = "executing…";
                using (var reader = await command.ExecuteReaderAsync())
                {
                    form.Text = "reading…";
                    do
                    {
                        // Blocks on the second call until the second resultset is returned by SQL Server
                        while (await reader.ReadAsync())
                        {
                        }
                    } while (await reader.NextResultAsync());
                    form.Text = "done!";
                }
            }
        }
        await Task.Delay(TimeSpan.FromSeconds(5));
        form.Close();
    }

    [STAThread]
    static void Main()
    {
        Application.EnableVisualStyles();
        Application.SetCompatibleTextRenderingDefault(false);
        var form = new Form();
        form.Shown += Form_Shown;
        Application.Run(form);
    }
}

When I run this, the window becomes "(Not Responding)" for 20 seconds before reporting it is done (note that when debugging in VS, "(Not Responding)" text does not appear but it still freezes the same). If I debug in VS and break it while it’s frozen, I see it sitting with a call stack that looks like this:

    [Managed to Native Transition]  
    System.Data.dll!SNINativeMethodWrapper.SNIReadSyncOverAsync(System.Runtime.InteropServices.SafeHandle pConn, ref System.IntPtr packet, int timeout) Unknown
    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()   Unknown
    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()   Unknown
    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()   Unknown
    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadByteArray(byte[] buff, int offset, int len, out int totalRead)    Unknown
    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadInt64(out long value) Unknown
    System.Data.dll!System.Data.SqlClient.TdsParser.TryProcessDone(System.Data.SqlClient.SqlCommand cmd, System.Data.SqlClient.SqlDataReader reader, ref System.Data.SqlClient.RunBehavior run, System.Data.SqlClient.TdsParserStateObject stateObj)    Unknown
    System.Data.dll!System.Data.SqlClient.TdsParser.TryRun(System.Data.SqlClient.RunBehavior runBehavior, System.Data.SqlClient.SqlCommand cmdHandler, System.Data.SqlClient.SqlDataReader dataStream, System.Data.SqlClient.BulkCopySimpleResultSet bulkCopyHandler, System.Data.SqlClient.TdsParserStateObject stateObj, out bool dataReady)  Unknown
    System.Data.dll!System.Data.SqlClient.SqlDataReader.TryHasMoreRows(out bool moreRows)   Unknown
    System.Data.dll!System.Data.SqlClient.SqlDataReader.TryReadInternal(bool setTimeout, out bool more) Unknown
    System.Data.dll!System.Data.SqlClient.SqlDataReader.ReadAsync.AnonymousMethod__0(System.Threading.Tasks.Task t) Unknown
    System.Data.dll!System.Data.SqlClient.SqlDataReader.InvokeRetryable<bool>(System.Func<System.Threading.Tasks.Task, System.Threading.Tasks.Task<bool>> moreFunc, System.Threading.Tasks.TaskCompletionSource<bool> source, System.IDisposable objectToDispose)   Unknown
    System.Data.dll!System.Data.SqlClient.SqlDataReader.ReadAsync(System.Threading.CancellationToken cancellationToken) Unknown
    System.Data.dll!System.Data.Common.DbDataReader.ReadAsync() Unknown
>   SqlDataReaderReadAsync.exe!SqlDataReaderReadAsyncProgram.Form_Shown(object sender, System.EventArgs e) Line 36  C#
    [Resuming Async Method] 

(further trimmed for brevity).

The whole ReadSyncOverAsync stuff looks particularly suspicious to me. It’s like the SqlClient is assuming a synchronous read will not block, as if it doesn’t know how to use non-blocking IO or something. Yet when viewing reference source or decompiling with JustDecompile, it looks like there’s supposed to be async support but it just somehow heuristically/fallbackedly decided not to use it.

So, how do I get the *Async() stuff in SqlClient to actually be async? I thought that these methods were supposed to enable me to write thread-free responsive GUI programs without needing to use Task.Run() because wrapping synchronous things in Task.Run() just to make them asynchronous is pointless overhead…?

I’m using .net-4.7.02542.

I’m assuming this is a .net bug and have submitted connect #3139210 (EDIT: connect is dead, I have a repro project at https://github.com/binki/connect3139210).

UPDATE: Microsoft acknowledges the bug and will fix it in .net-4.7.3. I used a "Technical Support" case from a VS subscription to report the bug and get this information.

解决方案

Microsoft released a fix for this issue in .net-4.8. I have tested and verified that it works. I have not seen a version of .net-4.7.3 yet, so I do not know if that actually will contain the fix.

The relevant SKUs from regedit for releaseKey=528040:

Your application must target .net-4.8 to get the fix (merely installing the update does not fix already-compiled applications). Unfortunately, there is no documented <AppContextSwitchOverrides/> for this feature, so you cannot opt into the fix if you must continue targeting an older version of .net. (However, you can target .net-4.8 at compile time, edit the «ProgramName».config to change the <supportedRuntime/>, and then be careful not to use any APIs introduced in .net after the version you’re targeting).

这篇关于如何使 SqlDataReader.ReadAsync() 异步运行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！