c# - SQL Server 关闭时，SqlConnection OpenAsync 会阻止 UI

Question

我有一个带有一个按钮的简单 WPF 客户端应用程序 (.NET 4.6.2)。SqlConnection.OpenAsync 方法在完成以下步骤时阻塞 UI 线程:

1. SQL Server 已启动并可从客户端访问
2. 开始客户端应用
3. 单击按钮 => UI 未被阻止(如预期的那样)=> 消息框显示“确定”
4. 关闭 SQL Server
5. 单击按钮 => UI 被阻止 - OpenAsync 阻止 UI => 消息框“显示”错误
6. 再次点击按钮 => UI 不再被阻塞 => 消息框显示“错误”

只有从点击->成功到点击->错误 OpenAsync 才会阻塞 UI 线程。我错过了什么？

private async void Button_Click(object sender, RoutedEventArgs e)
{
    try
    {
        using (var conn = new SqlConnection("...."))
        {
            await conn.OpenAsync();
        }
    }
    catch (System.Exception)
    {
        MessageBox.Show("err");
        return;
    }
    MessageBox.Show("ok");
}

编辑:

在挂起期间暂停调试器时调用堆栈:

System.Data.dll!SNINativeMethodWrapper.SNIOpenSyncEx(SNINativeMethodWrapper.ConsumerInfo consumerInfo, string constring, ref System.IntPtr pConn, byte[] spnBuffer, byte[] instanceName, bool fOverrideCache, bool fSync, int timeout, bool fParallel, int transparentNetworkResolutionStateNo, int totalTimeout, bool isAzureSqlServerEndpoint)    Unknown
    System.Data.dll!System.Data.SqlClient.SNIHandle.SNIHandle(SNINativeMethodWrapper.ConsumerInfo myInfo, string serverName, byte[] spnBuffer, bool ignoreSniOpenTimeout, int timeout, out byte[] instanceName, bool flushCache, bool fSync, bool fParallel, System.Data.SqlClient.TransparentNetworkResolutionState transparentNetworkResolutionState, int totalTimeout)   Unknown
    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.CreatePhysicalSNIHandle(string serverName, bool ignoreSniOpenTimeout, long timerExpire, out byte[] instanceName, byte[] spnBuffer, bool flushCache, bool async, bool fParallel, System.Data.SqlClient.TransparentNetworkResolutionState transparentNetworkResolutionState, int totalTimeout) Unknown
    System.Data.dll!System.Data.SqlClient.TdsParser.Connect(System.Data.SqlClient.ServerInfo serverInfo, System.Data.SqlClient.SqlInternalConnectionTds connHandler, bool ignoreSniOpenTimeout, long timerExpire, bool encrypt, bool trustServerCert, bool integratedSecurity, bool withFailover, bool isFirstTransparentAttempt, System.Data.SqlClient.SqlAuthenticationMethod authType, bool disableTnir, System.Data.SqlClient.SqlAuthenticationProviderManager sqlAuthProviderManager)  Unknown
    System.Data.dll!System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(System.Data.SqlClient.ServerInfo serverInfo, string newPassword, System.Security.SecureString newSecurePassword, bool ignoreSniOpenTimeout, System.Data.ProviderBase.TimeoutTimer timeout, bool withFailover, bool isFirstTransparentAttempt, bool disableTnir)  Unknown
    System.Data.dll!System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(System.Data.SqlClient.ServerInfo serverInfo, string newPassword, System.Security.SecureString newSecurePassword, bool redirectedUserInstance, System.Data.SqlClient.SqlConnectionString connectionOptions, System.Data.SqlClient.SqlCredential credential, System.Data.ProviderBase.TimeoutTimer timeout)    Unknown
    System.Data.dll!System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(System.Data.ProviderBase.TimeoutTimer timeout, System.Data.SqlClient.SqlConnectionString connectionOptions, System.Data.SqlClient.SqlCredential credential, string newPassword, System.Security.SecureString newSecurePassword, bool redirectedUserInstance) Unknown
    System.Data.dll!System.Data.SqlClient.SqlInternalConnectionTds.SqlInternalConnectionTds(System.Data.ProviderBase.DbConnectionPoolIdentity identity, System.Data.SqlClient.SqlConnectionString connectionOptions, System.Data.SqlClient.SqlCredential credential, object providerInfo, string newPassword, System.Security.SecureString newSecurePassword, bool redirectedUserInstance, System.Data.SqlClient.SqlConnectionString userConnectionOptions, System.Data.SqlClient.SessionData reconnectSessionData, System.Data.ProviderBase.DbConnectionPool pool, string accessToken, bool applyTransientFaultHandling, System.Data.SqlClient.SqlAuthenticationProviderManager sqlAuthProviderManager)    Unknown
    System.Data.dll!System.Data.SqlClient.SqlConnectionFactory.CreateConnection(System.Data.Common.DbConnectionOptions options, System.Data.Common.DbConnectionPoolKey poolKey, object poolGroupProviderInfo, System.Data.ProviderBase.DbConnectionPool pool, System.Data.Common.DbConnection owningConnection, System.Data.Common.DbConnectionOptions userOptions) Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(System.Data.ProviderBase.DbConnectionPool pool, System.Data.Common.DbConnection owningObject, System.Data.Common.DbConnectionOptions options, System.Data.Common.DbConnectionPoolKey poolKey, System.Data.Common.DbConnectionOptions userOptions)   Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionPool.CreateObject(System.Data.Common.DbConnection owningObject, System.Data.Common.DbConnectionOptions userOptions, System.Data.ProviderBase.DbConnectionInternal oldConnection)   Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(System.Data.Common.DbConnection owningObject, System.Data.Common.DbConnectionOptions userOptions, System.Data.ProviderBase.DbConnectionInternal oldConnection)  Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionPool.TryGetConnection(System.Data.Common.DbConnection owningObject, uint waitForMultipleObjectsTimeout, bool allowCreate, bool onlyOneCheckConnection, System.Data.Common.DbConnectionOptions userOptions, out System.Data.ProviderBase.DbConnectionInternal connection)   Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionPool.TryGetConnection(System.Data.Common.DbConnection owningObject, System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry, System.Data.Common.DbConnectionOptions userOptions, out System.Data.ProviderBase.DbConnectionInternal connection)    Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(System.Data.Common.DbConnection owningConnection, System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry, System.Data.Common.DbConnectionOptions userOptions, System.Data.ProviderBase.DbConnectionInternal oldConnection, out System.Data.ProviderBase.DbConnectionInternal connection)    Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(System.Data.Common.DbConnection outerConnection, System.Data.ProviderBase.DbConnectionFactory connectionFactory, System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry, System.Data.Common.DbConnectionOptions userOptions)  Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(System.Data.Common.DbConnection outerConnection, System.Data.ProviderBase.DbConnectionFactory connectionFactory, System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry, System.Data.Common.DbConnectionOptions userOptions)    Unknown
    System.Data.dll!System.Data.SqlClient.SqlConnection.TryOpenInner(System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry)  Unknown
    System.Data.dll!System.Data.SqlClient.SqlConnection.TryOpen(System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry)   Unknown
    System.Data.dll!System.Data.SqlClient.SqlConnection.OpenAsync(System.Threading.CancellationToken cancellationToken) Unknown
    System.Data.dll!System.Data.Common.DbConnection.OpenAsync() Unknown
>   TestWpf.exe!TestWpf.UndoManagerTest.UndoManagerTestView.Button_Click(object sender, System.Windows.RoutedEventArgs e) Line 35   C#
    PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown
    PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised)    Unknown
    PresentationCore.dll!System.Windows.UIElement.RaiseEventImpl(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args)   Unknown
    PresentationCore.dll!System.Windows.UIElement.RaiseEvent(System.Windows.RoutedEventArgs e)  Unknown
    PresentationFramework.dll!System.Windows.Controls.Primitives.ButtonBase.OnClick()   Unknown
    PresentationFramework.dll!System.Windows.Controls.Button.OnClick()  Unknown
    PresentationFramework.dll!System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(System.Windows.Input.MouseButtonEventArgs e)    Unknown
    PresentationCore.dll!System.Windows.UIElement.OnMouseLeftButtonUpThunk(object sender, System.Windows.Input.MouseButtonEventArgs e)  Unknown
    PresentationCore.dll!System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(System.Delegate genericHandler, object genericTarget) Unknown
    PresentationCore.dll!System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate handler, object target)   Unknown
    PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown
    PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised)    Unknown
    PresentationCore.dll!System.Windows.UIElement.ReRaiseEventAs(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args, System.Windows.RoutedEvent newEvent)  Unknown
    PresentationCore.dll!System.Windows.UIElement.OnMouseUpThunk(object sender, System.Windows.Input.MouseButtonEventArgs e)    Unknown
    PresentationCore.dll!System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(System.Delegate genericHandler, object genericTarget) Unknown
    PresentationCore.dll!System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate handler, object target)   Unknown
    PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown
    PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised)    Unknown
    PresentationCore.dll!System.Windows.UIElement.RaiseEventImpl(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args)   Unknown
    PresentationCore.dll!System.Windows.UIElement.RaiseTrustedEvent(System.Windows.RoutedEventArgs args)    Unknown
    PresentationCore.dll!System.Windows.UIElement.RaiseEvent(System.Windows.RoutedEventArgs args, bool trusted) Unknown
    PresentationCore.dll!System.Windows.Input.InputManager.ProcessStagingArea() Unknown
    PresentationCore.dll!System.Windows.Input.InputManager.ProcessInput(System.Windows.Input.InputEventArgs input)  Unknown
    PresentationCore.dll!System.Windows.Input.InputProviderSite.ReportInput(System.Windows.Input.InputReport inputReport)   Unknown
    PresentationCore.dll!System.Windows.Interop.HwndMouseInputProvider.ReportInput(System.IntPtr hwnd, System.Windows.Input.InputMode mode, int timestamp, System.Windows.Input.RawMouseActions actions, int x, int y, int wheel)   Unknown
    PresentationCore.dll!System.Windows.Interop.HwndMouseInputProvider.FilterMessage(System.IntPtr hwnd, MS.Internal.Interop.WindowMessage msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled)   Unknown
    PresentationCore.dll!System.Windows.Interop.HwndSource.InputFilterMessage(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled)    Unknown
    WindowsBase.dll!MS.Win32.HwndWrapper.WndProc(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled) Unknown
    WindowsBase.dll!MS.Win32.HwndSubclass.DispatcherCallbackOperation(object o) Unknown
    WindowsBase.dll!System.Windows.Threading.ExceptionWrapper.InternalRealCall(System.Delegate callback, object args, int numArgs)  Unknown
    WindowsBase.dll!System.Windows.Threading.ExceptionWrapper.TryCatchWhen(object source, System.Delegate callback, object args, int numArgs, System.Delegate catchHandler) Unknown
    WindowsBase.dll!System.Windows.Threading.Dispatcher.LegacyInvokeImpl(System.Windows.Threading.DispatcherPriority priority, System.TimeSpan timeout, System.Delegate method, object args, int numArgs)   Unknown
    WindowsBase.dll!MS.Win32.HwndSubclass.SubclassWndProc(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam)  Unknown
    [Native to Managed Transition]  
    [Managed to Native Transition]  
    WindowsBase.dll!System.Windows.Threading.Dispatcher.PushFrameImpl(System.Windows.Threading.DispatcherFrame frame)   Unknown
    WindowsBase.dll!System.Windows.Threading.Dispatcher.PushFrame(System.Windows.Threading.DispatcherFrame frame)   Unknown
    PresentationFramework.dll!System.Windows.Application.RunDispatcher(object ignore)   Unknown
    PresentationFramework.dll!System.Windows.Application.RunInternal(System.Windows.Window window)  Unknown
    PresentationFramework.dll!System.Windows.Application.Run(System.Windows.Window window)  Unknown
    PresentationFramework.dll!System.Windows.Application.Run()  Unknown
    TestWpf.exe!TestWpf.App.Main() Line 53  C#

Answer 1

堆栈跟踪表明 ADO.NET 正在执行同步 IO，尽管您已请求异步 IO。这是 ADO.NET 中的一个错误，它可能发生在一些相当不常见的代码路径中，因此没有引起注意。

ADO.NET 最近进行了增量升级，以更好地支持异步 IO。在 .NET 的早期，异步数据库访问并不常见。

您可以尝试升级到更新的 .NET 版本。

在 GitHub 上的 corefx 存储库中报告此错误。

您可以通过在非 UI 线程上运行阻塞操作来解决此问题:

await Task.Run(async () => await conn.OpenAsync());