SQL Dependancy Connection Loss or Timeout Error Handling

Hi Guys,

I use the below code to capture changes to my SQL tables using SQL Dependancy. The code works very well 95% of the time however I have an issue when there is connection loss or the application has been idle for a very long time  i.e. 4 hours +

My companies environment is fairly stable hence why I decided to use SQL Dependancy over polling, triggers etc. Bascially my application has a hissy fit when the server has been rebooted and users have left the application overnight or when the backups are running on the server or lastly if someone knocks the LAN cable out of their machine:)

I would appreciate some help on how to better handle errors when this sort of thing happens, typically the errors I get are:

Timeout expired
Transport error (caused by connection loss)
Failure to set the notification request

Here is my code below:

Imports System.Data.SqlClient.SqlDependency
Imports System.Data.SqlClient
Imports System.Security.Permissions
Imports System.ComponentModel
Imports System.Net.NetworkInformation

Public Class Form1
    Public Sub New()

        ' This call is required by the designer.
        InitializeComponent()
        SqlDependency.Stop(My.Settings.MyConnectionString)
        SqlDependency.Start(My.Settings.MyConnectionString)
        ' Add any initialization after the InitializeComponent() call.

    End Sub

    Private Delegate Sub UICallback()

    Private Function CanRequestNotifications() As Boolean

        Try
            Dim perm As New SqlClientPermission( _
                PermissionState.Unrestricted)

            perm.Demand()
            Return True

        Catch ex As Exception
            Return False
        End Try

    End Function

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'KoalaDataSet.tblDashboards' table. You can move, or remove it, as needed.

        CanRequestNotifications()
        UpdateUserList()

        Me.TblUsersTableAdapter.Fill(Me.MyDataSet.tblUsers)

    End Sub

    Sub UpdateUserList()

        Try
            Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.MyConnectionString)
            Using command As New System.Data.SqlClient.SqlCommand( _
              "SELECT [user_ID],[user_name], [user_email], [user_tel], [user_status] FROM [MyDB].[dbo].[tblUsers]", _
              sqlConnection1)

                Dim dependency As New SqlDependency(command)

                AddHandler dependency.OnChange, AddressOf dependency_OnChange
                sqlConnection1.Open()
                SqlDependency.Start(My.Settings.MyConnectionString)
                Using reader = command.ExecuteReader()

                End Using
            End Using

        Catch Ex As Exception
ResumeConnection()
        End Try

    End Sub
    Private Sub dependency_OnChange( _
ByVal sender As Object, ByVal e As SqlNotificationEventArgs)

        Dim i As ISynchronizeInvoke = CType(Me, ISynchronizeInvoke)

        If i.InvokeRequired Then
            Me.Invoke(New UICallback(AddressOf FillGrid))

            Dim tempDelegate As New OnChangeEventHandler( _
                AddressOf dependency_OnChange)

            Dim args() As Object = {sender, e}


            i.BeginInvoke(tempDelegate, args)

            Return
        End If

        Dim dependency As SqlDependency = _
            CType(sender, SqlDependency)

        RemoveHandler dependency.OnChange, _
           AddressOf dependency_OnChange

        UpdateUserList()


    End Sub
    Private Sub FillGrid()


        Try

            Me.TblUsersTableAdapter.Fill(Me.MyDataSet.tblUsers)

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try


    End Sub


    Sub ResumeConnection()

        Try


            If System.Net.NetworkInformation.NetworkInterface.GetIsNetworkAvailable() = True Then

                If CheckServer() = False Then
                    SqlDependency.Stop(My.Settings.MyConnectionString)

                Else
                    SqlDependency.Stop(My.Settings.MyConnectionString)
                    SqlDependency.Start(My.Settings.MyConnectionString)
                    UpdateUserList()
                End If

            Else
                SqlDependency.Stop(My.Settings.MyConnectionString)
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try


    End Sub

    Public Function CheckServer() As Boolean
        Dim _ping As New Ping
        Dim _pingreply = _ping.Send("MYSQLSERVER", 1000)
        If _pingreply.Status = IPStatus.Success Then
            Return True

        Else
            Return False
        End If
    End Function


    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        SqlDependency.Stop(My.Settings.MyConnectionString)
    End Sub


End Class

Open in new window


I really appreciate anyone who can help me on this, please feel free to submit C# code if preferred.
databarracksAsked:
Who is Participating?
 
Vadim RappCommented:
Here's probably even better solution from my old application.  It monitors if the database is avialable, so if it is not, the main process can wait (in a loop, for instance) until it becomes available, and then continue. This is monitoring application that runs for days, uploading the data to the database, so this trick allows it to survive database server restart and such.

Public Class PingClass
    ' this runs as a separate thread and pings the database every <interval>, posting
    ' info to the mainform's variable DBUp. Monitors check DBUp before uploading the data, 
    ' so if the database is offline, monitors will know not to try to upload the data, so 
    ' they won't spend time on timeout

    Dim lDB As New SqlClient.SqlConnection, lMainPanel As frmMain, Interval As Int32
    Private EL As New EventLog
    Public Sub PingRun()
        Const Offline As String = "Offline "
        Do
            Dim t As SqlClient.SqlTransaction
            Try

                lDB.Open()
                t = lDB.BeginTransaction    ' w/o this, pooled connection may return "on" even if the server is off

                t.Rollback()
                If Not lMainPanel.DBUp Then
                    EL.Source = Application.ProductName
                    EL.WriteEntry("The database is back up")
                End If

                lMainPanel.DBUp = True

            Catch ex As System.Threading.ThreadAbortException
                ' that's ok

            Catch ex As Exception
                If lMainPanel.DBUp Then
                    EL.Source = Application.ProductName
                    EL.WriteEntry("The database is down")
                End If

                lMainPanel.DBUp = False

            Finally
                Threading.Thread.Sleep(Interval)
                lDB.Close()
            End Try
        Loop
    End Sub

    Public Sub New(ByVal DB As SqlClient.SqlConnection, ByRef MainPanel As frmMain, ByVal pInterval As Int32)
        lDB.ConnectionString = DB.ConnectionString
        lMainPanel = MainPanel
        EL.Source = Application.ProductName
        Interval = pInterval
    End Sub

End Class

Open in new window



This class is launched from the main form on load:
    Public DBUp As Boolean
    Dim PingThread As Threading.Thread, PingInstance As PingClass

        PingInstance = New PingClass(Database, Me, 3000)
        PingThread = New Threading.Thread(AddressOf PingInstance.PingRun)
        PingThread.Name = "Ping"
        PingThread.Start()

Open in new window



also, in form closing event:
        PingThread.Abort()
0
 
Vadim RappCommented:
Without detailed analysis of your code:

1. make the connection global variable (i.e. declare it at class level), and create asynchronous thread that will be checking it, say, every 10 seconds by issuing some trivial query. If it fails, close and reopen the connection.

2. handle specific exception that is thrown by reopening the connection and retrying the query.
0
 
databarracksAuthor Commented:
Hi Vadim,
That is what my code is trying to do on the service without the timer essentially. Would you happen to have a piece of code that explains you steps in more detail as my code seems 'fairly' complete without the error handling?
Thank you again
0
 
databarracksAuthor Commented:
Hi Vadim,

Your solution appears to be the right way of doing it and I really appreciate your help on this matter.
0
 
databarracksAuthor Commented:
First class code from Vadim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.