如何使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
{
静态异步void Form_Shown（object sender，EventArgs e）
{
var form =（Form）sender;
//在$ app.config中声明您的连接字符串，例如
// //< connectionStrings><删除名称= LocalSqlServer /><添加名称= LocalSqlServer connectionString = Data Source = localhost\SQLEXPRESS; Integrated Security = true />< / connectionStrings>
使用（DbConnection连接=新的SqlConnection（ConfigurationManager.ConnectionStrings [0] .ConnectionString））
{
form.Text = connecting…;
等待connection.OpenAsync（）;
form.Text =已连接！;
//安装存储过程。
使用（var命令= connection.CreateCommand（））
{
command.CommandText = SET NOCOUNT ON
+ SELECT'a'
+声明@t DATETIME = SYSDATETIME（）
+ WHILE DATEDIFF（s，@t，SYSDATETIME（））< 20 BEGIN
+ SELECT 2 x INTO #y
+ DROP TABLE #y
+ END
+ SELECT'b';
form.Text =正在执行…;
使用（var reader = await命令。ExecuteReaderAsync（））
{
form.Text = reading…;
do
{
//在第二次调用时阻塞，直到SQL Server返回第二个结果集为止
while（await reader.ReadAsync（））
{
}
} while（wait reader.NextResultAsync（））;
form.Text =完成！;
}
}
}
等待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）;
}
}
当我运行此命令时，窗口变为（报告完成前20秒钟（请注意，在VS中进行调试时，（无响应）文本不会出现，但仍会冻结）。如果我在VS中调试并在冻结时将其破坏，我会看到它坐在一个调用堆栈中，如下所示：
[ [托管到本机过渡]
System.Data.dll！SNINativeMethodWrapper.SNIReadSyncOverAsync（System.Runtime.InteropServices.SafeHandle pConn，ref System.IntPtr数据包，int超时）未知的
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偏移量，int len，out int totalRead）未知
System.Data.dll！ System.Data.SqlClient.TdsParserStateObject.TryReadInt64（超长值）未知
System.Data.dll！System.Data.SqlClient.TdsParser.TryProcessDone（System.Data.SqlClient.SqlCommand cmd，Sy stem.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.BulkCopySimpleResultSet bulkCopyHandler，System.Data.SqlClient.TdsParserStateObject stateObj，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.T任务< bool>> moreFunc，System.Threading.Tasks.TaskCompletionSource< bool>源，System.IDisposable objectToDispose）未知
System.Data.dll！System.Data.SqlClient.SqlDataReader.ReadAsync（System.Threading.CancellationToken cancelledToken）未知
System.Data.dll！System.Data。 Common.DbDataReader.ReadAsync（）未知
> SqlDataReaderReadAsync.exe！SqlDataReaderReadAsyncProgram.Form_Shown（对象发送者，System.EventArgs e）第36行C＃
[恢复异步方法]
（为简洁起见，对其进行了进一步修剪）。
整个 ReadSyncOverAsync 的内容似乎特别可疑，我。就像SqlClient假定同步读取不会阻塞一样，好像它不知道如何使用非阻塞IO一样。但是，当查看参考源或使用JustDecompile进行反编译时，似乎应该有异步支持，但是它以某种方式启发/后备决定不使用它。
因此，如何在SqlClient中获得* Async（）东西实际上是异步的吗？我认为这些方法使我无需使用 Task.Run（）就可以编写无线程响应式GUI程序，因为将同步内容包装在
我正在使用.net-4.7.02542。
我假设这是一个.net错误，并且已提交
应用程序必须以.net-4.8为目标才能获取此修复程序（仅安装更新不会修复已编译的应用程序）。不幸的是，没有记录在案的 < AppContextSwitchOverrides /> ，因此如果您必须继续定位较早版本的，则无法选择修复。净。 （不过，您可以在编译时定位.net-4.8，编辑«ProgramName».config 来更改< supportedRuntime /> ，请注意不要在目标版本之后使用.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=localhost\SQLEXPRESS;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屋！
