ADO.NET SqlClient Connection Timeout

[tjfitz68]

Hello,

In this situation, my application can connect to the MSSQL server in one of two ways; via a direct TCP/IP connection (WAN) (IP's filtered using RRAS in 2003 Server) or via a VPN connection.

The application will first try to open the connection using the WAN connection string and if it can't, then it tries using the VPN connection string.

My issue is, I set the connection timeout to 5 seconds for the first attempt but it's taking way longer than that to return an exception. The order in which I attempt to open the connection doesn't really matter.

I have looked at the ConnectionTimeout property after changing the connection string and it does indeed show 5 seconds. It appears that the connection timeout value is being ignored.

Everything works but the long delay, for VPN users, is just annoying.

Has anyone had this issue?

Thanks,

Tim

[sknake]

Can you post the full code (with connection strings) where you are creating and opening your connection to the SQL Server?

In my testing in the past the connection timeout in the connection string worked but wasn't exact. I believe I set it for 5 seconds and forced a connection to fail but it took 7 seconds to execute. Another solution would be to open the connection in another thread and kill the other thread after your desired timeout value if it hasn't sucessfully connection.

[tjfitz68]

Thanks for the response.

The connection string is pretty standard and it has been working just fine so I doubt that's the issue. I'm just using the standard .Open() [System.Date.SqlClient.SqlConnection]

Connection String:
Data Source=[Either the IP or domain name],[port number];Initial Catalog=[db];Persist Security Info=True;User ID=[user];Password=[pw];Encrypt=True;TrustServerCertificate=True

Everything enclosed in [] is an actual value. I just didn't put the data. Because the actual connection string is an encrypted entry in the app.config, I append [;Connection Timeout=5] after the string is decrypted. I know it's being added because I have checked the value of the .ConnectionTimout property after the fact.

If it's able to actually open a connection using the first connection string, the execution is near immediate. It's only if it can not and it's definately taking longer than 5 seconds. I think I even tried 1 second and it didn't matter.

Perhaps your suggestion, regarding a different thread, is the best way to go.

Tim

[sknake]

You are right. The connection timeout is next to useless. I have read on how it operates and its' not what you would expect. Evidently the connection timeout is where the ip/hostname is responding on the sql services port but is too busy to service the new connection. In the case of a wrong ip/host and no sql network connectivity the timer on the SqlConnection doesn't start ticking. Also you could have a DNS resolution failure that takes N seconds before a connection is attempted.

What behavior are you wanting in your application? Here is one example of implementing your own timeout and trying the next connection string. If you need error details then you would need to go about things a little differently:

Imports System.Data.SqlClient
Imports System.Runtime.Remoting.Messaging


Public Class frmSqlConn2

  Private Class ConnectionStateTracker
    Private _abandonConnection As Boolean
    Private _run As Boolean
    Private _connStr As String
    Private _sync As Object
    'ctors
    Private Sub New()
      _run = False
      _abandonConnection = False
      _sync = New Object()
    End Sub
    Public Sub New(ByVal connStr As String)
      Me.New()
      _connStr = connStr
    End Sub
    'public properties
    Private Property AbandonConnection() As Boolean
      Get
        Return VolatileRead(_abandonConnection)
      End Get
      Set(ByVal value As Boolean)
        VolatileWrite(_abandonConnection, value)
      End Set
    End Property
    'private methods
    Private Function VolatileRead(Of T)(ByRef Address As T) As T
      VolatileRead = Address
      Threading.Thread.MemoryBarrier()
    End Function
    Private Sub VolatileWrite(Of T)(ByRef Address As T, ByVal Value As T)
      Threading.Thread.MemoryBarrier()
      Address = Value
    End Sub
    Private Sub ConnectCallback(ByVal ar As IAsyncResult)
      'Clean up the connection if it was abandoned
      SyncLock _sync
        If Me.AbandonConnection Then
          Dim result As AsyncResult = CType(ar, AsyncResult)
          Dim conn As SqlConnection = CType(ar.AsyncState, SqlConnection)
          Dim del As Action = CType(result.AsyncDelegate, Action)
          Try
            del.EndInvoke(ar)
          Catch ex As Exception
            Console.WriteLine("Connection Error: " + ex.Message)
          End Try
          conn.Close()
          conn.Dispose()
        End If
      End SyncLock
    End Sub
    'public methods
    Public Function Connect(ByVal timeout As Integer) As SqlConnection
      If (_run) Then Throw New InvalidOperationException("Connect can only be called once.")
      _run = True

      SyncLock _sync
        Dim conn As New SqlConnection(_connStr)
        Dim del As Action = AddressOf conn.Open
        Dim ar As IAsyncResult = del.BeginInvoke(AddressOf ConnectCallback, conn)
        Dim finished As Boolean = ar.AsyncWaitHandle.WaitOne(timeout)
        If (Not finished) Or (conn.State <> ConnectionState.Open) Then
          Me.AbandonConnection = True
          Return Nothing
        Else
          Me.AbandonConnection = False
          del.EndInvoke(ar)
          Return conn
        End If
      End SyncLock

      Return Nothing
    End Function

  End Class


  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    'connStr1 is invalid, connStr2 is valid
    Const connStr1 As String = "Data Source=apexXXXX;Initial Catalog=Leather;Integrated Security=True;"
    Const connStr2 As String = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;"

    Dim connector As New ConnectionStateTracker(connStr1)
    Dim dtStart As DateTime = DateTime.Now
    Dim conn = connector.Connect(5000)
    If conn Is Nothing Then
      Dim dtStop As DateTime = DateTime.Now
      MessageBox.Show(String.Format("First sql connection failed in {0}s.", dtStop.Subtract(dtStart).TotalSeconds.ToString()))
    Else
      MessageBox.Show("Connect 1 suceeded")
    End If

    If (conn Is Nothing) Then 'try 2nd conn str
      connector = New ConnectionStateTracker(connStr2)
      dtStart = DateTime.Now
      conn = connector.Connect(5000)
      If conn Is Nothing Then
        Dim dtStop As DateTime = DateTime.Now
        MessageBox.Show(String.Format("Second sql connection failed in {0}s.", dtStop.Subtract(dtStart).TotalSeconds))
      Else
        MessageBox.Show("Connect 2 suceeded")
      End If
    End If

    If (Not conn Is Nothing) Then
      System.Diagnostics.Debugger.Break()
      conn.Close()
      conn.Dispose()
    End If

  End Sub
End Class

[tjfitz68]

Actually, I think your code example handles exactly what I'm trying to accomplish. I will implement that and let you know how it works out.

Thanks for time spent!

Tim

[sknake]

You're welcome

Please mark this thread as solved if you have found an answer to your question and good luck!

[tjfitz68]

You're welcome

Please mark this thread as solved if you have found an answer to your question and good luck!

Actually, I'm just now getting around to trying the code out. I'm receiving an error at lines 46 and 64 (I believe).

In ConnectCallBack function,
Dim del As Action = CType(result.AsyncDelegate, Action)
Is causing the error "To few Type Arguments to System.Action(Of T)

Same for,
Dim del As Action = AddressOf conn.Open
In the Connection function.

I tried to figure it out myself but I'm afraid I'm lost.

Thanks,

Tim