OpenAsync

OpenAsync

关注
发信
关注(28)粉丝(399)

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

我有一个按钮的简单WPF客户端应用程序（.NET 4.6.2）。
完成以下步骤后，SqlConnection.OpenAsync方法将阻止UI线程：


SQL Server已启动并可从客户端访问
启动客户端应用程序
单击按钮=> UI未被阻止（按预期方式）=>消息框显示“确定”
关闭SQL Server
单击按钮=> UI处于阻塞状态-OpenAsync块UI =>消息框“显示”错误
再次单击按钮=> 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#

最佳答案

简洁版本：

连接代码似乎在OpenAsync的同步部分中。

在这种情况下，似乎只有一个选项-将OpenAsync视为基本同步方法，并使用Task.Run执行它，以便在线程池线程上发生延迟

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




TLDR：

该问题很可能源于OpenAsync-https://github.com/microsoft/referencesource/blob/master/System.Data/System/Data/SqlClient/SqlConnection.cs#L1383的当前实现

乍一看无法确定，但到目前为止，似乎OpenAsync及其调用的其他方法中的ConfigureAwait(false)太少了。

这意味着搜索数据库主机并重试与关闭服务的连接的代码可能仅在当前（GUI）线程上执行，而实际上并没有允许任何人们可能期望的"async voidness"。

当您第二次调用它时，缓存的工作方式几乎可以立即返回。

因此，我建议您尝试在事件处理程序中添加ConfigureAwait(false)。

using (var conn = new SqlConnection("...."))
{
    await conn.OpenAsync().ConfigureAwait(false);
}


作为ConfigureAwait seems not to help，则可能意味着原始连接代码在OpenAsync的同步部分中。

在这种情况下，似乎只有一个选项-将OpenAsync视为基本同步方法，并使用Task.Run执行它，以便在线程池线程上发生延迟：

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

09-13 04:30
Powered by LMLPHP ©2024 OpenAsync 0.033665