none
Sql dependencies in VB.NET RRS feed

  • Question

  • Hello

    I need to create an app in which different users will change data from a DataGridView. I was thinking to use SqlDependencies to automatically update changes.

    I have tried the example from link bellow but it seems that something is not working properly.

    http://msdn.microsoft.com/en-us/library/a52dhwx7(v=VS.80).aspx

    Can somebody help me?
    I want to mention that I am making this app in VB.NET


    Thanks in advance, Ciprian LUPU

    Tuesday, March 20, 2012 5:26 PM

Answers

  • Bellow is the code I have used together with sql dependencies and it works fine:

     

    'Option Strict On

    Option Explicit On

    Imports System.ComponentModel

    Imports System.Data.SqlClient

    Imports System.Security.Permissions

    Public Class Form1

        Public dataSource As String = "server"

        Public initialCatalog As String = "database"

        Public userID As String = "user"

        Public prgPassword As String = "password"

        Private Const tableName As String = "Inventory"

        Private Const statusMessage As String = _

          "{0} changes have occurred."

        ' The following objects are reused

        ' for the lifetime of the application.

        Private connection As SqlConnection = Nothing

        Private command As SqlCommand = Nothing

        Private dataToWatch As DataSet = Nothing

        Private changeCount As Integer

        Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed

            SqlDependency.Stop(GetConnectionString())

            If connection IsNot Nothing Then

                connection.Close()

            End If

        End Sub

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            Button1.Enabled = CanRequestNotifications()

        End Sub

        Private Function CanRequestNotifications() As Boolean

            ' In order to use the callback feature of the

            ' SqlDependency, the application must have

            ' the SqlClientPermission permission.

            Try

                Dim perm As New SqlClientPermission( _

                    PermissionState.Unrestricted)

                perm.Demand()

                Return True

            Catch ex As Exception

                Return False

            End Try

        End Function

        Private Function GetConnectionString() As String

            ' To avoid storing the connection string in your code,

            ' you can retrive it from a configuration file.

            'Return "Data Source=(local);Integrated Security=true;" & _

            ' "Initial Catalog=ISB_CL;"

            Return "Data Source=" & dataSource & _

                      ";Initial Catalog=" & initialCatalog & _

                      ";User Id=" & userID & ";Password=" & prgPassword ''& _

            '";Network Library=dbmssocn;"

        End Function

        Private Function GetSQL() As String

            Return "SELECT wu_req_id, wu_req_username, wu_req_date FROM dbo.wu_request_active_users"

        End Function

        Private Sub dependency_OnChange( _

        ByVal sender As Object, ByVal e As SqlNotificationEventArgs)

            ' 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

            Me.Label1.Text = String.Format(statusMessage, changeCount)

            ' Add information from the event arguments to the list box

            ' for debugging purposes only.

            With Me.ListBox1.Items

                .Clear()

                .Add("Info:   " & e.Info.ToString())

                .Add("Source: " & e.Source.ToString())

                .Add("Type:   " & e.Type.ToString())

            End With

            ' 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.

            dataToWatch.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(dataToWatch, tableName)

                Me.DataGridView1.DataSource = dataToWatch

                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

            changeCount = 0

            Me.Label1.Text = String.Format(statusMessage, changeCount)

            ' 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("@Quantity", SqlDbType.Int)

                'prm.Direction = ParameterDirection.Input

                'prm.DbType = DbType.Int32

                'prm.Value = 100

                'command.Parameters.Add(prm)

            End If

            If dataToWatch Is Nothing Then

                dataToWatch = New DataSet()

            End If

            GetData()

        End Sub

    End Class


    Ciprian LUPU

    Tuesday, March 27, 2012 11:09 AM

All replies

  • Ciprian,

    I could never successful implement this. Probably there are like you wrote "SQL dependencies". 

    Did you look at the bottom of that page, it is full of suggestions.

    But the first thing before you start to create all kind of code, check if the OnChange event ever fires. 

    After that the problem is of course solvable.

    If it does not fire, then you can contact better an SQL forum first for your type of Server (there are many you know and this service is AFAIK bound to the latest and I'm absolute not sure about that is even with Express)

    Be aware that the concept of watching other users changing data and then do that also for the current user leads mostly to something which gives only nervous end users. 


    Success
    Cor

    Wednesday, March 21, 2012 8:32 AM
  • Hi Ciprian,

    How about your issue now?

    Any updates?

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, March 26, 2012 2:23 PM
    Moderator
  • Hi thanks

    I solved the problem but a small issue still exist.

    When I receive a notification from SQL Server I still do not know to verufy what exactly has been changed... A new row or maybe a value was updated ... I do not know :(


    Ciprian LUPU

    Monday, March 26, 2012 6:28 PM
  • The ideea is that the solution is working but it has a problem. If you use * in query it run and run and it is unstopable

    Ciprian LUPU

    Monday, March 26, 2012 6:31 PM
  • Hi Ciprian,

    Would you like to share how you implement sqlDependency? Anyway, here is a codeproject to show how to use it: http://www.codeproject.com/Articles/12335/Using-SqlDependency-for-data-change-events 

    For your another question, I would like to suggest you to start a new thread on sql forum: http://social.msdn.microsoft.com/Forums/en-US/category/sqlserver 

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 27, 2012 3:32 AM
    Moderator
  • Bellow is the code I have used together with sql dependencies and it works fine:

     

    'Option Strict On

    Option Explicit On

    Imports System.ComponentModel

    Imports System.Data.SqlClient

    Imports System.Security.Permissions

    Public Class Form1

        Public dataSource As String = "server"

        Public initialCatalog As String = "database"

        Public userID As String = "user"

        Public prgPassword As String = "password"

        Private Const tableName As String = "Inventory"

        Private Const statusMessage As String = _

          "{0} changes have occurred."

        ' The following objects are reused

        ' for the lifetime of the application.

        Private connection As SqlConnection = Nothing

        Private command As SqlCommand = Nothing

        Private dataToWatch As DataSet = Nothing

        Private changeCount As Integer

        Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed

            SqlDependency.Stop(GetConnectionString())

            If connection IsNot Nothing Then

                connection.Close()

            End If

        End Sub

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            Button1.Enabled = CanRequestNotifications()

        End Sub

        Private Function CanRequestNotifications() As Boolean

            ' In order to use the callback feature of the

            ' SqlDependency, the application must have

            ' the SqlClientPermission permission.

            Try

                Dim perm As New SqlClientPermission( _

                    PermissionState.Unrestricted)

                perm.Demand()

                Return True

            Catch ex As Exception

                Return False

            End Try

        End Function

        Private Function GetConnectionString() As String

            ' To avoid storing the connection string in your code,

            ' you can retrive it from a configuration file.

            'Return "Data Source=(local);Integrated Security=true;" & _

            ' "Initial Catalog=ISB_CL;"

            Return "Data Source=" & dataSource & _

                      ";Initial Catalog=" & initialCatalog & _

                      ";User Id=" & userID & ";Password=" & prgPassword ''& _

            '";Network Library=dbmssocn;"

        End Function

        Private Function GetSQL() As String

            Return "SELECT wu_req_id, wu_req_username, wu_req_date FROM dbo.wu_request_active_users"

        End Function

        Private Sub dependency_OnChange( _

        ByVal sender As Object, ByVal e As SqlNotificationEventArgs)

            ' 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

            Me.Label1.Text = String.Format(statusMessage, changeCount)

            ' Add information from the event arguments to the list box

            ' for debugging purposes only.

            With Me.ListBox1.Items

                .Clear()

                .Add("Info:   " & e.Info.ToString())

                .Add("Source: " & e.Source.ToString())

                .Add("Type:   " & e.Type.ToString())

            End With

            ' 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.

            dataToWatch.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(dataToWatch, tableName)

                Me.DataGridView1.DataSource = dataToWatch

                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

            changeCount = 0

            Me.Label1.Text = String.Format(statusMessage, changeCount)

            ' 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("@Quantity", SqlDbType.Int)

                'prm.Direction = ParameterDirection.Input

                'prm.DbType = DbType.Int32

                'prm.Value = 100

                'command.Parameters.Add(prm)

            End If

            If dataToWatch Is Nothing Then

                dataToWatch = New DataSet()

            End If

            GetData()

        End Sub

    End Class


    Ciprian LUPU

    Tuesday, March 27, 2012 11:09 AM
  • Hi Ciprian,

    Thank you for sharing this solution here.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 28, 2012 2:10 AM
    Moderator