I can't catch the SqlException using try-catch

0

I try to catch the SqlException using try-catch so I wrote this code:

public async Task Open()
    {
        try
        {
            if (sqlconnection.State != ConnectionState.Open)
            {
                await Task.Run(() => sqlconnection.OpenAsync()).ConfigureAwait(false);

            }
        }
        catch(SqlException ec)
        {
          XtraMessageBox.Show(ec.Message);
        }
    }
public async Task<DataTable> SelectData(string stored_procedure, SqlParameter[] param)
    {

        using (SqlCommand sqlcmd = new SqlCommand())
        {

            if (string.IsNullOrEmpty(stored_procedure))
            {
                return null;
            }

            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.CommandText = stored_procedure;
            sqlcmd.Connection = sqlconnection;

            if (param != null)
            {
                sqlcmd.Parameters.AddRange(param);
            }

            using (DataTable dt = new DataTable())
            {
                using (SqlDataAdapter da = new SqlDataAdapter(sqlcmd))
                {
                    try
                    {
                        await Task.Run(() => da.Fill(dt)).ConfigureAwait(false);
                    }
                    catch(SqlException ec)
                    {
                        XtraMessageBox.Show(ec.Message);
                    }
                }
                return dt;
            }
        }

    }

But unfortunately that did't work and always I get error on da.Fill(dt) with this message

Microsoft.Data.SqlClient.SqlException HResult=0x80131904 Message=Une erreur liée au réseau ou spécifique à l'instance s'est produite lors de l'établissement d'une connexion à SQL Server. Le serveur est introuvable ou n'est pas accessible. Vérifiez que le nom de l'instance est correct et que SQL Server est configuré pour autoriser les connexions distantes. (provider: Interfaces réseau SQL, error: 26 - Erreur lors de la localisation de Server/Instance spécifié) Source=Framework Microsoft SqlClient Data Provider StackTrace: at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, Boolean isFirstTransparentAttempt, SqlAuthenticationMethod authType, String certificate, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, Boolean useOriginalAddressInfo, Boolean disableTnir, SqlAuthenticationProviderManager sqlAuthProviderManager) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at Microsoft.Data.SqlClient.SqlConnection.Open() at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at Smart_Industrial_Management.DAL.DataAccessLayerV550.<>c__DisplayClass6_1.b__0() in C:\Users\MBoua\source\repos\SIM Windows7\Smart Industrial Management\DAL\DataAccessLayerV550.cs:line 116 at System.Threading.Tasks.Task`1.InnerInvoke() at System.Threading.Tasks.Task.Execute()

This exception was originally thrown at this call stack: Microsoft.Data.SqlClient.SqlInternalConnection.OnError(Microsoft.Data.SqlClient.SqlException, bool, System.Action) Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(Microsoft.Data.SqlClient.TdsParserStateObject, bool, bool) Microsoft.Data.SqlClient.TdsParser.Connect(Microsoft.Data.SqlClient.ServerInfo, Microsoft.Data.SqlClient.SqlInternalConnectionTds, bool, long, bool, bool, bool, bool, bool, Microsoft.Data.SqlClient.SqlAuthenticationMethod, string, Microsoft.Data.SqlClient.ServerCertificateValidationCallback, Microsoft.Data.SqlClient.ClientCertificateRetrievalCallback, bool, bool, Microsoft.Data.SqlClient.SqlAuthenticationProviderManager) Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(Microsoft.Data.SqlClient.ServerInfo, string, System.Security.SecureString, bool, Microsoft.Data.ProviderBase.TimeoutTimer, bool, bool, bool) Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(Microsoft.Data.SqlClient.ServerInfo, string, System.Security.SecureString, bool, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, Microsoft.Data.ProviderBase.TimeoutTimer) Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(Microsoft.Data.ProviderBase.TimeoutTimer, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, string, System.Security.SecureString, bool) Microsoft.Data.SqlClient.SqlInternalConnectionTds.SqlInternalConnectionTds(Microsoft.Data.ProviderBase.DbConnectionPoolIdentity, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, object, string, System.Security.SecureString, bool, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SessionData, Microsoft.Data.SqlClient.ServerCertificateValidationCallback, Microsoft.Data.SqlClient.ClientCertificateRetrievalCallback, Microsoft.Data.ProviderBase.DbConnectionPool, string, Microsoft.Data.SqlClient.SqlClientOriginalNetworkAddressInfo, bool, Microsoft.Data.SqlClient.SqlAuthenticationProviderManager) Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.Common.DbConnectionPoolKey, object, Microsoft.Data.ProviderBase.DbConnectionPool, System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions) Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(Microsoft.Data.ProviderBase.DbConnectionPool, System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.Common.DbConnectionPoolKey, Microsoft.Data.Common.DbConnectionOptions) Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal) ... [Call Stack Truncated]

I use it in this way
I have Created a class with this name CLS_VoortmanV550 and i put this code:

public async Task<DataTable> GetV550Batch()
    {
        DAL.DataAccessLayerV550 DAL = new DAL.DataAccessLayerV550();

        using (DataTable dt = await Task.Run(() => DAL.SelectData("GetV550Batch", null)).ConfigureAwait(false))
        {
            DAL.Close();
            return dt;
        }
    }

and from the form i call this method

 try
        {
            gridControl3.DataSource = await clsV550.GetV550Batch().ConfigureAwait(true);
        }
        catch
        {
            XtraMessageBox.Show(Resources.FailedConnectV550, Resources.error, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
c#
winforms
asked on Stack Overflow Nov 30, 2019 by M.Bouabdallah • edited Nov 30, 2019 by M.Bouabdallah

1 Answer

0

You should never have User-Interface Interaction in Background Tasks. And the Task class is designed to do so.

You are waiting for the completion of the Task. After the Task has ended you can check on

 Task.IsSuccessfullCompleted
 Task.IsCancelled

etc. In case of Exceptions you will find the exceptions here.

 Task.AggregateException

https://docs.microsoft.com/de-de/dotnet/api/system.threading.tasks.task.exception?view=netframework-4.8

This will contain information about occured exceptions, maybe multiple.

So this try-catch-pattern should generally not be used across threads.

If you do exception-handling inside your Task, log them somewhere, but do not come up with MessagesBoxes. Talking to the user is the job of the Foreground-Thread.

answered on Stack Overflow Nov 30, 2019 by Holger

User contributions licensed under CC BY-SA 3.0