Delay to SQL Server throw an exception

Question

I have this Code to Get a table Data From SQL Server:

 public  static System.Data.Linq.Table<Equipment> GetEquipmentTable()
        {           
                DataClassesDataContext dc = new DataClassesDataContext();
                return dc.GetTable<Equipment>();            

        }

I Have a Button to call this Function :

  private void button_Click(object sender, RoutedEventArgs e)
    {

       MyListView.DataContext = GetEquipmentTable();
    }

My Problem is :When I Disable Communication Between my App and SQL Server Machine and then click this button, It takes a while to throw an Exception that Connect to Database is impossible!!!! My major problem is that my app freezed till this Exception accrued.

Did I missed something ?

Update 1 :

I used async and wait base on Rahul solution

public static async Task<System.Data.Linq.Table<Equipment>> GetEquipmentTable()
        {           
                DataClassesDataContext dc = new DataClassesDataContext();
                return dc.GetTable<Equipment>();            
        }
 private async void button_Click(object sender, RoutedEventArgs e)
    {

       MyListView.DataContext = await GetEquipmentTable();
    }

but it still wait for this line of code :

return dc.GetTable<Equipment>();

and UI freezes as well.

I think dc.gettable<> is not waitable or somthing else !!??

play around with connection timeout on your connectionstring. Default is 30 secs — apomene, Apr 27 '18 at 11:44
what do you mean by *"When I Disable Communication Between my App and SQL Server Machine"*? — Ricardo Pontual, Apr 27 '18 at 11:44
maybe this can help you: https://stackoverflow.com/questions/10108565/changing-sqlconnection-timeout — Ricardo Pontual, Apr 27 '18 at 11:46
I want test my app, turned off Server Machine or disconnect it from network or disconnect client machine from network — Mamad, Apr 27 '18 at 11:47

score 3 · Answer 1 · answered Apr 27 '18 at 11:48

When I Disable Communication Between my App and SQL Server Machine and then click this button

That's obvious right since it tries to connect to the machine (within the Timeout mentioned in connection string) and then throws the exception back once it finds that the server isn't reachable.

major problem is that my app freezed till this Exception accrued

Probably in that case make the method as async method like

 public static async Task<System.Data.Linq.Table<Equipment>> GetEquipmentTable()
        {           
                DataClassesDataContext dc = new DataClassesDataContext();
                return dc.GetTable<Equipment>();            
        }

Your event handler

 private async void button_Click(object sender, RoutedEventArgs e)
    {

       MyListView.DataContext = await GetEquipmentTable();
    }

I tried your solution but it still wait for this line of code : (return dc.GetTable();) and UI freezes as well . — Mamad, Apr 27 '18 at 18:22

Kyle B · Answer 2 · 2018-04-27T12:02:23.227

You have two problems, the app freezing is because you are not using asynchronous programming.
The delay before the connection exception is because the client side app waits until the connection timeout timer finishes (30 seconds default).

Try using async await on your database calls to free up the UI.

Try changing the connection timeout to 5 seconds in the connection string.

Change in connection string:

"Data Source=...;Connect Timeout=5"

paparazzo · Answer 3 · 2018-04-27T12:16:16.967

Use await as in the answer from Rahul. +1

You could also test a simple SqlConnection.Open

private static bool OpenSqlConnection(string connectionString)
{
    bool return = false;
    try 
    {
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        return = true;
    }
    catch (Exception ex)
    {
        return = false;
    }
    finally 
    {
        connection.Close();
    }
    return return;
}

Could use SqlConnection.OpenAsync.

        SqlConnection con = new SqlConnection("connection string");
        bool resp = false;
        try
        {
            con.OpenAsync();
            resp = true;
        }
        catch (SqlException ex)
        {
            //use the ex message
            resp = false;
        }
        catch (Exception ex)
        {
            resp = false;
        }
        finally
        {
            con.Close();
        }

Delay to SQL Server throw an exception

3 Answers3