SqlConnection timeout oddities

Question

I am experiencing a weird issue where the timeout remaining for a connection attempt seems to be persisting through multiple attempts.

I have a simple window where connection information is entered, with a connect and cancel button.

When the user hits connect, the following code executes:

 DisableControls();
 if((bool)AutoAuthenticated.IsChecked)
 {
     ((MainWindow)Owner).myConnection = new System.Data.SqlClient.SqlConnection
                                          ("server="+ServerName.Text + ";"+
                                          "Trusted_Connection=yes;"  +
                                          "database="+DatabaseName.Text + ";" +
                                          "connection timeout=3");
 }
 else
 {
     ((MainWindow)Owner).myConnection = new System.Data.SqlClient.SqlConnection
     ("user id="+UserName.Text+";" +
     "password="+Password.Password+";" +
     "server="+ServerName.Text+";" +
     "Trusted_Connection=no;" +
     "database="+DatabaseName.Text+";" +
     "connection timeout=3");
 }

 await ConnectToServer();

This is the ConnectToServer function:

private async Task ConnectToServer()
{
    //using (((MainWindow)Owner).myConnection)
    //{ 
        await ((MainWindow)Owner).myConnection.OpenAsync();
    //}
}

The timeout property is small right now for the purposes of testing.

Anyway, if the user hits the cancel button whilst connecting:

private void Cancel_Click(object sender, RoutedEventArgs e)
{
    if (((MainWindow)Owner).myConnection != null &&
        ((MainWindow)Owner).myConnection.State ==
            System.Data.ConnectionState.Connecting)
    {
        ((MainWindow)Owner).myConnection.Close();
        EnableControls();
    }
    else
    {
        this.Close();
    }
}

Now, if I enter in bogus details and leave it to timeout, then I catch the exception with 'The network path was not found.'.

If I try and connect again after this (or maybe the one after the next attempt depending on how quick I was to hit the cancel button), it times out almost instantly after hitting the connect button, without waiting the amount of time it was supposed to.

I'm not sure what I'm doing wrong here, I've tried looking in to resetting the SqlConnection object, but I thought the setting of it to a new SqlConnection each time I hit connect should be doing this already?

Answer 1

I found out what I was missing. Even when closing the connection, disposing of the object, keeping it all in a using statement, the issue persisted because there was another element that I was unaware of - the connection pool.

I still have to do some reading on this, but basically it reuses old connection information and so on to save on resources, etc.

There's more information here, but in case this helps someone else with similar issues, just call the ClearPool() function when you close the connection:

Example (in my exception handling code, and after I call Close()):

System.Data.SqlClient.SqlConnection.ClearPool(((MainWindow)Owner).myConnection);

Obviously from a resource usage perspective, it would probably be best to call that function only when you don't need to reuse the connection, but that said, I still need to read up on this so don't take my word for it!