'SqlDependency' in einer Windows-Anwendung (ADO.NET)

  • Artikel

Im folgenden Szenario wird gezeigt, wie die SqlDependency-Klasse in einer Windows Forms-Anwendung verwendet wird. Zum Veranschaulichen der Funktionsweise von Benachrichtigungen erstellen Sie zwei Anwendungen: Eine Überwachungsanwendung zum Verarbeiten von Abfragebenachrichtigungen und eine Aktualisierungsanwendung zum Ändern der von der Überwachungsanwendung überwachten Daten.

Hinweis

Im Beispielcode wird davon ausgegangen, dass Sie durch Ausführen der Skripts in Aktivieren von Abfragebenachrichtigungen (ADO.NET) die Abfragebenachrichtigungen aktiviert haben.

Erstellen der Überwachungsanwendung

Bei der Überwachungsanwendung handelt es sich um eine Windows Forms-Anwendung, die Produktdaten aus der SQL Server 2005-AdventureWorks-Beispieldatenbank in ein DataSet lädt. Das DataSet wird mithilfe eines SqlDataAdapter-Objekts gefüllt, das an ein DataGridView-Steuerelement gebunden ist.Damit Benachrichtigungen empfangen werden können, wird ein SqlDependency-Objekt erstellt und an ein vom SqlDataAdapter verwendetes SqlCommand-Objekt gebunden. Das SqlDependency-Objekt macht ein einzelnes Ereignis, OnChange, verfügbar. Der Handler, der zum Verarbeiten der Benachrichtigungen registriert ist, führt die zum Wechseln vom benachrichtigenden Threadpoolthread zum Benutzeroberflächenthread erforderlichen Aktionen aus, und nach einer anschließenden Neuregistrierung können wieder Benachrichtigungen empfangen werden.

So erstellen Sie die Überwachungsanwendung

  1. Erstellen Sie ein neues Windows-Anwendungsprojekt mit dem Namen "Data Watcher".

  2. Wählen Sie im Forms-Designer das Standardformular aus. Ändern Sie die Eigenschaft Text in Inventory Watcher.

  3. Fügen Sie dem Formular ein Label-Steuerelement hinzu. Docken Sie das Label-Steuerelement am unteren Rand des Formulars an.

  4. Fügen Sie oben links im Formular ein ListBox-Steuerelement hinzu. Ändern Sie die Größe des Steuerelements so, dass es ungefähr drei Textzeilen fasst.

  5. Fügen Sie dem Formular unter dem ListBox-Steuerelement eine DataGridView hinzu.

  6. Fügen Sie dem Formular ein Button-Steuerelement hinzu, und positionieren Sie es rechts vom ListBox-Steuerelement. Ändern Sie dessen Text-Eigenschaft in Get Data.

  7. Öffnen Sie das Klassenmodul des Formulars, und fügen Sie am Anfang der Datei oberhalb der Klassendefinition folgenden Code hinzu.

    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. Fügen Sie im Deklarationsabschnitt der Klasse die folgenden Elemente hinzu:

    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

    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. Erstellen Sie im Formular folgende neue Methode: CanRequestNotifications. Diese Methode überprüft, ob die Anwendung über die Berechtigung zum Anfordern von Benachrichtigungen vom Server verfügt.

    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 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. Verwenden Sie im Load-Ereignis des Formulars den Rückgabewert von CanRequestNotifications, um die Enabled-Eigenschaft der einzigen Schaltfläche des Formulars festzulegen.

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

    Button1.Enabled = CanRequestNotifications()
End Sub

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

  11. Fügen Sie zwei Hilfsmethoden hinzu, GetConnectionString und GetSQL. Die definierte Verbindungszeichenfolge verwendet die integrierte Sicherheit. Sie müssen sicherstellen, dass das verwendete Konto über die erforderlichen Datenbankberechtigungen verfügt und dass für die Beispieldatenbank AdventureWorks Benachrichtigungen aktiviert sind. Weitere Informationen dazu finden Sie unter Aktivieren von Abfragebenachrichtigungen (ADO.NET).

    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

    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. Damit die Anwendung bei Änderungen an Daten auf dem Server benachrichtigt wird, benötigt sie einen Ereignishandler, der mit der Signatur des OnChangeEventHandler-Delegaten übereinstimmt. Die Prozedur muss das Ereignis abfangen und vom Arbeitsthread zum Benutzeroberflächenthread wechseln. Fügen Sie dem Modul des Formulars folgenden Code hinzu:

    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 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. Damit die Anwendung Benachrichtigungen empfangen kann, muss sie beim SqlCommand-Objekt ein SqlDependency-Objekt registrieren, mit dem die Daten der Anwendung abgerufen werden. Fügen Sie eine Methode mit dem Namen GetData hinzu. Gehen Sie dazu wie folgt vor:

    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 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. Fügen Sie in der einzigen Schaltfläche des Formulars einen Ereignishandler für das Click-Ereignis hinzu, und fügen Sie folgenden Code im Textkörper des Handlers ein:

    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

    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. Fügen Sie im FormClosed-Ereignis des Formulars den folgenden Code hinzu, um die Abhängigkeiten und Datenbankverbindungen zu bereinigen:

    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

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

Nachdem Sie den Erstellungsvorgang für die Überwachungsanwendung abgeschlossen haben, erstellen Sie die Aktualisierungsanwendung, und führen Sie dann beide Anwendungen gemeinsam aus.

Erstellen der Aktualisierungsanwendung

Die Aktualisierungsanwendung stellt eine einfache Schnittstelle zum Ändern der von der Überwachungsanwendung überwachten Datenmenge bereit. Bei der Aktualisierungsanwendung in diesem Beispiel handelt es sich ebenfalls um eine Windows-Anwendung. Zum Aktualisieren der Daten kann aber auch jede andere Art von Anwendung verwendet werden. Führen Sie zum Erstellen der Aktualisierungsanwendung die folgenden Schritte aus:

  1. Erstellen Sie ein neues Windows-Anwendungsprojekt mit dem Namen Data Updater.

  2. Wählen Sie im Forms-Designer das Standardformular aus. Ändern Sie die Text-Eigenschaft in "Inventory Updater".

  3. Fügen Sie dem Formular ein Label-Steuerelement und ein TextBox-Steuerelement hinzu. Ändern Sie die Text-Eigenschaft des Label-Steuerelements in "Product ID" und die Name-Eigenschaft des TextBox-Steuerelements in "txtProductID".

  4. Fügen Sie je ein weiteres Label-Steuerelement und ein weiteres TextBox-Steuerelement hinzu. Ändern Sie die Text-Eigenschaft des Label-Steuerelements in "Quantity" und die Name-Eigenschaft des TextBox-Steuerelements in "txtQuantity".

  5. Fügen Sie ein Button-Steuerelement hinzu, und legen Sie für dessen Text-Eigenschaft "Update" fest.

  6. Fügen Sie der Formularklasse die folgenden Direktiven hinzu:

    Option Strict On
Option Explicit On

Imports System.Data
Imports System.Data.SqlClient

using System.Data
using System.Data.SqlClient;

  7. Fügen Sie im Deklarationsabschnitt der Klasse die folgenden Elemente hinzu:

    ' The following objects are reused
' for the lifetime of the application.
Private connection As SqlConnection = Nothing
Private command As SqlCommand = Nothing

    // The following objects are reused
// for the lifetime of the application.
private SqlConnection connection = null;
private SqlCommand command = null;

  8. Fügen Sie dem Formular zwei Hilfsmethoden hinzu, GetConnectionString und GetSQL. Diese sollten wie folgt aussehen:

    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 "UPDATE Production.ProductInventory " & _
    "SET Production.ProductInventory.Quantity = @Quantity " & _
    "WHERE Production.ProductInventory.ProductID = @ProductID;"
End Function

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

private string GetSQL()
{
    return "UPDATE Production.ProductInventory " +
    "SET Production.ProductInventory.Quantity = @Quantity " +
    "WHERE Production.ProductInventory.ProductID = @ProductID;";
}

  9. Fügen Sie im Click-Ereignis der Get Data-Schaltfläche den folgenden Code hinzu:

    Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

    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 param As New SqlParameter("@Quantity", SqlDbType.Int)
        param.Direction = ParameterDirection.Input
        param.DbType = DbType.Int32
        command.Parameters.Add(param)

        param = New SqlParameter("@ProductID", SqlDbType.Int)
        param.Direction = ParameterDirection.Input
        param.DbType = DbType.Int32
        command.Parameters.Add(param)
    End If

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

    Dim rowsAffected As Integer = command.ExecuteNonQuery()

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

    private void button1_Click(object sender, EventArgs e)
{
    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 param = new
            SqlParameter("@Quantity", SqlDbType.Int);
        param.Direction = ParameterDirection.Input;
        param.DbType = DbType.Int32;
        command.Parameters.Add(param);

        param = new SqlParameter("@ProductID", SqlDbType.Int);
        param.Direction = ParameterDirection.Input;
        param.DbType = DbType.Int32;
        command.Parameters.Add(param);
    }

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

    int rowsAffected = command.ExecuteNonQuery();

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

  10. Fügen Sie abschließend dem FormClosed-Ereignishandler des Formulars den folgenden Code hinzu:

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

    If connection IsNot Nothing Then
        connection.Close()
    End If
End Sub

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

Testen der Anwendung

Kompilieren Sie beide Anwendungen, und führen Sie sie aus. Klicken Sie in der Überwachungsanwendung auf Get Data, um das Raster der Anwendung mit Daten zu füllen und eine Benachrichtigungsanforderung zu registrieren. Suchen Sie in den Daten im Raster nach einer Produkt-ID mit einer Bestandsmenge von null (0). Wechseln Sie zur Aktualisierungsanwendung, geben Sie die Produkt-ID und die neue Menge 1 ein, und klicken Sie auf Update.Die Überwachungsanwendung sollte sich daraufhin, kurz nachdem die Daten auf dem Server von der Aktualisierungsanwendung geändert wurden, selbst aktualisieren. Beachten Sie, dass das ListBox-Steuerelement Informationen zur Aktualisierung anzeigt.

Siehe auch

Konzepte

Abfragebenachrichtigungen in SQL Server (ADO.NET)