locked
Show in Textbox actual number of records from SQL Database - VB.net RRS feed

  • Question

  • Hi,

    I'm trying to use notification from SQL database to count actual records in table. I need this in real-time, because I would like to create InstantMessage popup informing particular employee about message for him in the database.

    On SQL I enabled broker,

    And on WPF window I'm using this code:

    Private Function DoesUserHavePermission() As Boolean
            Try
                Dim clientPermission As SqlClientPermission = New SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
                clientPermission.Demand()
                Return True
            Catch ex As Exception
                Return False
            End Try
            Return True
        End Function
        Public Sub GetNames()
            If Not DoesUserHavePermission() Then
                Return
            End If
            Dim enu As String = txtEmplID.Text
            'lbNames.Items.Clear()
    
            ' You must stop the dependency before starting a new one.
            ' You must start the dependency when creating a new one.
            SqlDependency.Stop("Data Source=c0040513.itcs.hp.com,443\SQLSERVER;User ID=Admonistrator;Password=Kakademona666;Initial Catalog=Prometheus")
            SqlDependency.Start("Data Source=c0040513.itcs.hp.com,443\SQLSERVER;User ID=Admonistrator;Password=Kakademona666;Initial Catalog=Prometheus")
    
            Using cn As SqlConnection = New SqlConnection("Data Source=c0040513.itcs.hp.com,443\SQLSERVER;User ID=Admonistrator;Password=Kakademona666;Initial Catalog=Prometheus")
    
                Using cmd As SqlCommand = cn.CreateCommand()
    
                    cmd.CommandType = CommandType.Text
                    cmd.CommandText = "SELECT COUNT([ID]) FROM dbo.tbl_InstantMessage where Enumber = '" & enu & "'"
    
                    cmd.Notification = Nothing
    
                    ' creates a new dependency for the SqlCommand
                    Dim dep As SqlDependency = New SqlDependency(cmd)
                    ' creates an event handler for the notification of data changes in the database
                    AddHandler dep.OnChange, AddressOf dep_onchange
    
                    cn.Open()
    
                    Dim result As Integer = Convert.ToInt32(cmd.ExecuteScalar)
                    btnNewMessage.Text= result
    
                End Using
    
            End Using
        End Sub
        Private Sub dep_onchange(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlNotificationEventArgs)
    
            If btnNewMessage.Dispatcher.CheckAccess Then
    
                btnNewMessage.Dispatcher.BeginInvoke(New Action(AddressOf GetNames))
    
            Else
                GetNames()
            End If
            Dim dep As SqlDependency = DirectCast(sender, SqlDependency)
            RemoveHandler dep.OnChange, AddressOf dep_onchange
    
        End Sub

    And I'm calling it on Window_Loaded. 

    When I turn on my app - it's working, but if I make some amendments in the database, it's not refreshing textbox.

    Thank you in advance


    Mathh

    Monday, April 7, 2014 10:58 AM

Answers