Using SqlDependency in a Windows Application 

The following scenario shows how to use the SqlDependency class in a Windows Forms application. To demonstrate how notifications work, you will create two applications: a watcher to process query notifications, and an updater to modify the data that the watcher is monitoring.

Creating the Watcher Application

The watcher application is a Windows Forms application that loads a set of data into a DataSet from the SQL Server 2005 sample database AdventureWorks. The DataSet is filled using a SqlDataAdapter object that is bound to a DataGridView control. In order to receive notifications, a SqlDependency object is created and bound to the SqlCommand object used by the SqlDataAdapter. The SqlDependency object exposes a single event, OnChange. The handler that is registered to process the notifications performs the necessary actions to switch from the notifying thread, a thread pool thread, to the UI thread and then re-registers to receive notifications.

To create the watcher application

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

2. If you are using Visual Basic, add references to System.Data.dll and System.Xml.dll.

3. Select the default form in the Forms Designer. Change the Text property to Inventory Watcher.

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

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

6. Add a DataGridView below the ListBox control.

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

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

Imports System.ComponentModel

Imports System.Data

Imports System.Data.SqlClient

Imports System.Security.Permissions

[C#]

using System.Data.SqlClient;

using System.Security.Permissions;

1. 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;

1. 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;

}

}

1. 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]

Button1.Enabled = CanRequestNotifications()

[C#]

Button1.Enabled = CanRequestNotifications();

1. 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 Special Considerations When Using 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 using the

' System.Configuration.ConfigurationSettings.AppSettings property.

' In general, client applications don't need to incur the

' overhead of connection pooling.

Return "Data Source=localhost;Integrated Security=SSPI;" & _

"Initial Catalog=AdventureWorks;Pooling=False;"

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 using the

// System.Configuration.ConfigurationSettings.AppSettings property.

// In general client applications don't need to incur

// overhead of connection pooling.

return "Data Source=localhost;Integrated Security=SSPI;" +

"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;";

}

1. 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();

}

1. In order to receive notifications, the application needs to register a SqlDependency object with the SqlCommand object used to obtain the application's data. Add a method called GetData with the following body:

[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

OnChangedEventHandler(dependency_OnChange);

using (SqlDataAdapter adapter =

new SqlDataAdapter(command))

{

adapter.Fill(dataToWatch, tableName);

dataGridView1.DataSource = dataToWatch;

dataGridView1.DataMember = tableName;

}

}

1. 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]

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()

[C#]

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();

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

[Visual Basic]

SqlDependency.Stop(GetConnectionString())

If connection IsNot Nothing Then

connection.Close()

End If

[C#]

SqlDependency.Stop(GetConnectionString());

if (connection != null)

{

connection.Close();

}

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

Creating the Updater Application

The updater application provides a simple interface to modify the set of data that is being monitored by the watcher application. The updater in this example is a Windows Application also, but any type of application could cause the update to occur.

To create the updater application

1. Create a new Windows Application project called "Data Updater".

2. If you are using Visual Basic, add a reference to System.Data.dll.

3. Select the default form in the Forms Designer. In the property grid, change the Text property to Inventory Updater.

4. Add a Label control and a TextBox control to form. Change the Text property of the label to Product ID and the Name property of the text box to txtProductID.

5. Add another label and text box. Change the Text property of the label to Quantity and the Name property of the text box to txtQuantity.

6. Add a Button control and set its Name property to Update.

7. In the form's code module, add the following code to the top of the file:

[Visual Basic]

Option Strict On

Imports System.Data

Imports System.Data.SqlClient

[C#]

using System.Data.SqlClient;

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

[Visual Basic]

' The following objects are reused

' for the lifetime of the application.

Private connection As SqlConnection = Nothing

Private command As SqlCommand = Nothing

[C#]

// The following objects are reused

// for the lifetime of the application.

private SqlConnection connection = null;

private SqlCommand command = null;

1. Add two helper methods, GetConnectionString and GetSQL, to the form. They should look like the following:

[Visual Basic]

Private Function GetConnectionString() As String

' To avoid storing the connection string in your code,

' you can retrive it from a configuration file using the

' System.Configuration.ConfigurationSettings.AppSettings property.

' In general client applications don't need to incur

' overhead of connection pooling.

Return "Data Source=localhost;Integrated Security=SSPI;" & _

"Initial Catalog=AdventureWorks;Pooling=False;"

End Function

Private Function GetSQL() As String

Return "UPDATE Production.ProductInventory " & _

"SET Production.ProductInventory.Quantity = @Quantity " & _

"WHERE Production.ProductInventory.ProductID = @ProductID;"

End Function

[C#]

private string GetConnectionString()

{

// To avoid storing the connection string in your code,

// you can retrive it from a configuration file using the

// System.Configuration.ConfigurationSettings.AppSettings property.

// In general, client applications don't need to incur the

// overhead of connection pooling.

return "Data Source=localhost;Integrated Security=SSPI;" +

"Initial Catalog=AdventureWorks;Pooling=False;";

}

private string GetSQL()

{

return "UPDATE Production.ProductInventory " +

"SET Production.ProductInventory.Quantity = @Quantity " +

"WHERE Production.ProductInventory.ProductID = @ProductID;";

}

1. In the Click event of the Get Data button, add the following code:

[Visual Basic]

Dim productID As Integer = CInt(txtProductID.Text)

Dim quantity As Integer = CInt(txtQuantity.Text)

If connection Is Nothing Then

connection = New SqlConnection(GetConnectionString())

connection.Open()

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

command = New SqlCommand(GetSQL(), connection)

Dim prm As New SqlParameter("@Quantity", SqlDbType.Int)

prm.Direction = ParameterDirection.Input

prm.DbType = DbType.Int32

command.Parameters.Add(prm)

prm = New SqlParameter("@ProductID", SqlDbType.Int)

prm.Direction = ParameterDirection.Input

prm.DbType = DbType.Int32

command.Parameters.Add(prm)

End If

command.Parameters("@Quantity").Value = quantity

command.Parameters("@ProductID").Value = productID

Dim rowsAffected As Integer = command.ExecuteNonQuery()

MessageBox.Show(rowsAffected & " records updated.", "Update")

[C#]

int productID = Convert.ToInt32(txtProductID.Text);

int quantity = Convert.ToInt32(txtQuantity.Text);

if (connection == null)

{

connection = new SqlConnection(GetConnectionString());

connection.Open();

}

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;

command.Parameters.Add(prm);

prm = new SqlParameter("@ProductID", SqlDbType.Int);

prm.Direction = ParameterDirection.Input;

prm.DbType = DbType.Int32;

command.Parameters.Add(prm);

}

command.Parameters["@Quantity"].Value = quantity;

command.Parameters["@ProductID"].Value = productID;

int rowsAffected = command.ExecuteNonQuery();

MessageBox.Show(rowsAffected + " records updated.", "Update");

1. Finally, add the following code to the form's FormClosed event handler:

[Visual Basic]

If connection IsNot Nothing Then

connection.Close()

End If

[C#]

if (connection != null)

{

connection.Close();

}

1.

Compile and run both applications. Clicking Get Data on the watcher application should fill the application's grid with data and register a notification request. Examine the data in the grid, noting a product ID for a product whose inventory is zero. Then, using the updater application, enter the product ID and a new quantity of one, and click Update. You should see the watcher application automatically update itself moments after the updater changes the data on the server. Observe that the listbox control displays information about the update. Repeat the process for other quantities and products.

See Also

Other Resources

Using Query Notifications