1. Create a new Windows Application project named "Data Watcher".

2. In Forms Designer, select the default form. Change the Text property to Inventory Watcher.

3. Add a Label control to the form. Dock the label control to the bottom of the form.

4. Add a ListBox control in the upper left of the form. Size it for approximately three lines of text.

5. Add a DataGridView below the ListBox control.

6. Add a Button control to the form and position it to the right of the ListBox control. Change its Text property to Get Data.

7. Open the form's class module and add the following code to the top of the file, above the class definition.

   Visual Basic

   Option Strict On
   Option Explicit On
   
   Imports System.ComponentModel
   Imports System.Data.SqlClient
   Imports System.Security.Permissions
   
   using System.Data.SqlClient;
   using System.Security.Permissions;

8. In the declaration section of the class, add the following items:

   Visual Basic

   Private changeCount As Integer = 0
   
   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

   C#

   private int changeCount = 0;
   
   private const string tableName = "Inventory";
   private const string statusMessage = "{0} changes have occurred.";
   
   // The following objects are reused
   // for the lifetime of the application.
   private DataSet dataToWatch = null;
   private SqlConnection connection = null;
   private SqlCommand command = null;

9. In the form, create a new method called CanRequestNotifications. This method will verify the application has permissions to request notifications from the server.

   Visual Basic

   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

   C#

   private bool CanRequestNotifications()
   {
       // In order to use the callback feature of the
       // SqlDependency, the application must have
       // the SqlClientPermission permission.
       try
       {
           SqlClientPermission perm =
               new SqlClientPermission(
               PermissionState.Unrestricted);
   
           perm.Demand();
   
           return true;
       }
       catch
       {
           return false;
       }
   }

10. In the form's Load event, use the return value from CanRequestNotifications to set the Enabled property of the form's only button.

    Visual Basic

    Private Sub Form1_Load(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load
    
        Button1.Enabled = CanRequestNotifications()
    End Sub

    C#

    private void Form1_Load(object sender, EventArgs e)
    {
        Button1.Enabled = CanRequestNotifications();
    }

11. Add two helper methods, GetConnectionString and GetSQL. The defined connection string uses integrated security. You will need to verify that the account you are using has the necessary database permissions and that the sample database, AdventureWorks, has notifications enabled. For more information, see Enabling Query Notifications.

    Visual Basic

    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=AdventureWorks;"
    End Function
    
    Private Function GetSQL() As String
        Return "SELECT Production.Product.ProductID, " & _
        "Production.Product.Name, Production.Location.Name AS Location, " & _
        "Production.ProductInventory.Quantity FROM Production.Product " & _
        "INNER JOIN Production.ProductInventory " & _
        "ON Production.Product.ProductID = " & _
        "Production.ProductInventory.ProductID " & _
        "INNER JOIN Production.Location " & _
        "ON Production.ProductInventory.LocationID = " & _
        "Production.Location.LocationID " & _
        "WHERE ( Production.ProductInventory.Quantity <= @Quantity ) " & _
        "ORDER BY Production.ProductInventory.Quantity, Production.Product.Name;"
    End Function

    C#

    private string GetConnectionString()
    {
        // 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=AdventureWorks;Pooling=False;";
    }
    
    private string GetSQL()
    {
        return "SELECT Production.Product.ProductID, " +
        "Production.Product.Name, Production.Location.Name AS Location, " +
        "Production.ProductInventory.Quantity FROM " +
        "Production.Product INNER JOIN Production.ProductInventory " +
        "ON Production.Product.ProductID = " +
        "Production.ProductInventory.ProductID " +
        "INNER JOIN Production.Location " +
        "ON Production.ProductInventory.LocationID = " +
        "Production.Location.LocationID " +
        "WHERE ( Production.ProductInventory.Quantity <= @Quantity ) " +
        "ORDER BY Production.ProductInventory.Quantity, " +
        "Production.Product.Name;";
    }

12. In order to receive notifications when data on the server changes, the application needs an event handler that matches the signature of the OnChangeEventHandler delegate. The procedure needs to catch the event and switch from the worker thread to the UI thread. Add the following code to the form's module:

    Visual Basic

    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

    C#

    private void dependency_OnChange(
       object sender, SqlNotificationEventArgs e)
    {
        // This event will occur on a thread pool thread.
        // Updating the UI from a worker thread is not permitted.
        // The following code checks to see if it is safe to
        // update the UI.
        ISynchronizeInvoke i = (ISynchronizeInvoke)this;
    
        // If InvokeRequired returns True, the code
        // is executing on a worker thread.
        if (i.InvokeRequired)
        {
            // Create a delegate to perform the thread switch.
            OnChangeEventHandler tempDelegate =
                new OnChangeEventHandler(dependency_OnChange);
    
            object[] args = { sender, e };
    
            // Marshal the data from the worker thread
            // to the UI thread.
            i.BeginInvoke(tempDelegate, args);
    
            return;
        }
    
        // Remove the handler, since it is only good
        // for a single notification.
        SqlDependency dependency =
            (SqlDependency)sender;
    
        dependency.OnChange -= dependency_OnChange;
    
        // At this point, the code is executing on the
        // UI thread, so it is safe to update the UI.
        ++changeCount;
        label1.Text = String.Format(statusMessage, changeCount);
    
        // Add information from the event arguments to the list box
        // for debugging purposes only.
        listBox1.Items.Clear();
        listBox1.Items.Add("Info:   " + e.Info.ToString());
        listBox1.Items.Add("Source: " + e.Source.ToString());
        listBox1.Items.Add("Type:   " + e.Type.ToString());
    
        // Reload the dataset that is bound to the grid.
        GetData();
    }

13. In order to receive notifications, the application must register a SqlDependency object with the SqlCommand object used to obtain the application's data. Add a method called GetData as follows:

    Visual Basic

    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

    C#

    private void GetData()
    {
        // Empty the dataset so that there is only
        // one batch of data displayed.
        dataToWatch.Clear();
    
        // Make sure the command object does not already have
        // a notification object associated with it.
        command.Notification = null;
    
        // Create and bind the SqlDependency object
        // to the command object.
        SqlDependency dependency =
            new SqlDependency(command);
        dependency.OnChange += new
            OnChangeEventHandler(dependency_OnChange);
    
        using (SqlDataAdapter adapter =
            new SqlDataAdapter(command))
        {
            adapter.Fill(dataToWatch, tableName);
    
            dataGridView1.DataSource = dataToWatch;
            dataGridView1.DataMember = tableName;
        }
    }

14. Add an event handler for the Click event in the form's only button, and put the following code in the handler body:

    Visual Basic

    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

    C#

    private void button1_Click(object sender, EventArgs e)
    {
        changeCount = 0;
        label1.Text = String.Format(statusMessage, changeCount);
    
        // Remove any existing dependency connection, then create a new one.
        SqlDependency.Stop(GetConnectionString());
        SqlDependency.Start(GetConnectionString());
    
        if (connection == null)
        {
            connection = new SqlConnection(GetConnectionString());
        }
    
        if (command == null)
        {
            // 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);
    
            SqlParameter prm =
                new SqlParameter("@Quantity", SqlDbType.Int);
            prm.Direction = ParameterDirection.Input;
            prm.DbType = DbType.Int32;
            prm.Value = 100;
            command.Parameters.Add(prm);
        }
        if (dataToWatch == null)
        {
            dataToWatch = new DataSet();
        }
    
        GetData();
    }

15. In the form's FormClosed event, add the following code to clean up the dependency and database connections:

    Visual Basic

    Private Sub Form1_FormClosed(ByVal sender As System.Object, _
        ByVal e As System.Windows.Forms.FormClosedEventArgs) _
        Handles MyBase.FormClosed
        SqlDependency.Stop(GetConnectionString())
        If connection IsNot Nothing Then
            connection.Close()
        End If
    End Sub

    C#

    private void Form1_FormClosed(object sender, FormClosedEventArgs e)
    {
        SqlDependency.Stop(GetConnectionString());
        if (connection != null)
        {
            connection.Close();
        }
    }

With the watcher application completed, you will need to create the updater application and then run them together.