none
SQL dependency

    Question

  • Sir

    I am testing a Vb.net application to have SQL dependency with SQL server database. Infact I copied the sample code given in MSDN and only changed the database connection string.

    I am getting the connection but the dependency_OnChange event gets fired repeatedly as in a loop.

    The args e.Type is "Subscribe"

    and e.info is Invalid.

    I checked the SQL statement for the format but could not find any mistake.

    Private Function GetConnectionString() As String Return "Server=OPS-TMIT\SQLEXPRESS;Database=FlightStatus;Integrated Security=true;" & _ "User ID=;Password=;Trusted_Connection=True;Encrypt=False;" & _ "Connection Timeout=30;Pooling=False;" End Function

    Private Function GetSQL() As String Return "Select [FlightStatus].[dbo].[Data].[Auto], " & _ "[FlightStatus].[dbo].[Data].[Arr Date] " & _ "FROM [FlightStatus].[dbo].[Data] " & _ "WHERE ([FlightStatus].[dbo].[Data].[Arr Date] = @Date1 );" End Function

    Private Sub dependency_OnChange( _ ByVal sender As Object, ByVal e As SqlNotificationEventArgs) Dim someType = e.Type Dim someInfo = e.Info Dim someScource = e.Source ' This event will occur on a thread pool thread. ' It is illegal to update the UI from a worker thread ' The following code checks to see if it is safe ' update the UI. Dim i As ISynchronizeInvoke = CType(Me, ISynchronizeInvoke) ' If InvokeRequired returns True, the code ' is executing on a worker thread. If i.InvokeRequired Then ' Create a delegate to perform the thread switch Dim tempDelegate As New OnChangeEventHandler( _ AddressOf dependency_OnChange) Dim args() As Object = {sender, e} ' Marshal the data from the worker thread ' to the UI thread. i.BeginInvoke(tempDelegate, args) Return End If ' Remove the handler since it's only good ' for a single notification Dim dependency As SqlDependency = _ CType(sender, SqlDependency) RemoveHandler dependency.OnChange, _ AddressOf dependency_OnChange ' At this point, the code is executing on the ' UI thread, so it is safe to update the UI. changeCount += 1 ' Add information from the event arguments to the list box ' for debugging purposes only. ' Reload the dataset that's bound to the grid. GetData() End Sub Private Sub GetData()

    ' Empty the dataset so that there is only ' one batch worth of data displayed. dtSet.Clear() ' Make sure the command object does not already have ' a notification object associated with it. command.Notification = Nothing ' Create and bind the SqlDependency object ' to the command object. Dim dependency As New SqlDependency(command) AddHandler dependency.OnChange, AddressOf dependency_OnChange Using adapter As New SqlDataAdapter(command) adapter.Fill(dtSet, tableName) Me.DataGridView1.DataSource = dtSet Me.DataGridView1.DataMember = tableName End Using End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Try ' Remove any existing dependency connection, then create a new one. SqlDependency.Stop(GetConnectionString()) SqlDependency.Start(GetConnectionString()) If connection Is Nothing Then connection = New SqlConnection(GetConnectionString()) End If If command Is Nothing Then ' GetSQL is a local procedure that returns ' a paramaterized SQL string. You might want ' to use a stored procedure in your application. command = New SqlCommand(GetSQL(), connection) Dim prm As New SqlParameter("@Date1", SqlDbType.DateTime2) prm.Direction = ParameterDirection.Input prm.DbType = DbType.DateTime2 prm.Value = "2013-11-18 00:00:00" command.Parameters.Add(prm) End If If dtSet Is Nothing Then dtSet = New DataSet() End If GetData() Catch ex As Exception MsgBox(ex.Message) End Try End Sub


    
    

    Please advice where I have gone wrong !!


    Manoj

    Friday, January 03, 2014 12:16 AM

Answers

  • I am getting the connection but the dependency_OnChange event gets fired repeatedly as in a loop.

    The args e.Type is "Subscribe"

    and e.info is Invalid.

    According to http://msdn.microsoft.com/en-us/library/z0fkxc6y(v=vs.110).aspx, "Subscribe" indicates "There was a failure to create a notification subscription. Use the SqlNotificationEventArgs object's SqlNotificationInfo item to determine the cause of the failure."

    I ran your code successfully, changing the connection string, query and adding missing Imports and variables.  So the problem looks to be environmental.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Friday, January 03, 2014 1:41 PM added info that the code works
    • Marked as answer by Manoj Joseph Sunday, January 05, 2014 11:39 PM
    Friday, January 03, 2014 2:35 AM