-
创建一个称为“Data Watcher”的新 Windows 应用程序项目。
-
如果使用的是 Visual Basic，请添加对 System.Data.dll 和 System.Xml.dll 的引用。
-
从“窗体设计器”中选择默认窗体。将“文本”属性更改为“Inventory Watcher”。
-
在窗体上添加一个 Label 控件。将标签控件放在窗体的底部。
-
在窗体左上方添加 ListBox 控件。将其大小调整为大约三行文本。
-
将 DataGridView 添加到 ListBox 控件下方。
-
在窗体上添加一个 Button 控件，并将该控件放置在 ListBox 控件的右侧。将其“文本”属性更改为“Get Data”。
-
打开窗体的类模块，并在文件顶部类定义的上方添加以下代码：
[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;
-
在类的声明部分添加下列项：
[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;
-
在窗体中新建一个称为 CanRequestNotifications 方法。此方法将验证应用程序是否有权向服务器请求通知。
[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;
}
}
-
在窗体的 Load 事件中，使用 CanRequestNotifications 的返回值设置窗体的唯一一个按钮的 Enabled 属性。
[Visual Basic]
Button1.Enabled = CanRequestNotifications()
[C#]
Button1.Enabled = CanRequestNotifications();
-
添加两个帮助器方法 GetConnectionString 和 GetSQL。已定义的连接字符串使用集成安全性。您将需要验证所使用的帐户是否具有必要的数据库权限，并且示例数据库 AdventureWorks 是否启用了通知。有关更多信息，请参见使用查询通知时的特殊注意事项。
[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;";
}
-
为了在服务器上的数据更改时接收通知，应用程序需要使用事件处理程序来匹配 OnChangeEventHandler 委托的签名。该过程需要捕获事件并从工作线程切换到 UI 线程。在窗体的模块中添加以下代码：
[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();
}
-
为了接收通知，应用程序需要在用于获取应用程序数据的 SqlCommand 对象中注册 SqlDependency 对象。添加一个称为 GetData 的方法，包含以下正文：
[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;
}
}
-
为窗体上唯一一个按钮的 Click 事件添加一个事件处理程序，并将以下代码加入处理程序的正文：
[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();
-
在窗体的 FormClosed 事件中添加以下代码，以清理相关项和数据库连接：
[Visual Basic]
SqlDependency.Stop(GetConnectionString())
If connection IsNot Nothing Then
connection.Close()
End If
[C#]
SqlDependency.Stop(GetConnectionString());
if (connection != null)
{
connection.Close();
}