Query Notification: .NET SqlDependency klase

Rakstā par to, kā reaģēt uz datu izmaiņām datu bāzē no .NET koda. Zemāk neliels piemērs- konsoles aplikācija, kas, reaģējot uz izmaiņām datu bāzē, izvada uz ekrāna paziņojumu.

Vienkāršotais skatījums
Query Notification (msdn) var tik izmantots, lai reaģētu uz izmaiņām datos SQL Server datu bāzē. Apmēram tā: definē vaicājumu, kas atgriež moniotorējamo datu kopu (vaicājumam jābūt gana primitīvam- msdn), definē, ka vēlies reaģēt uz izmaiņām attiecīgajos datos un izpildi iepriekš definēto vaicājumu.
Tiks saņemts paziņojums, tiklīdz datu bāzē kāds pamainīs attiecīgos datus. Kodā var reaģēt uz šo notikumu, piemēram, izmetot paziņojumu uz ekrāna vai pārlasot attiecīgos datus (cache)..

To, cik tas ir vienkārši, var redzēt piemērā, raksta pēdējā nodaļā.

Mazliet dziļāks skatījums
Gluži kā Event Notifications (raksts: Event Notification: Strupsaķeres (deadlock) reģistrēšanai) arī Query Notification ir speciāls datu bāzes objekts, kas sūta ziņu SQL Server Service Broker servisam. Šajā gadījumā- reaģējot uz izmaiņām vaicājuma atgrieztajā datu kopā.

Tas nozīmē, ka izmantot šo klasi ir daudz efektīvāk, nekā visu laiku pārlasīt monitorējamo datu kopu lai pārliecinātos, vai tajā veiktas izmaiņas, jo izmaiņām līdzi seko SQL Server un ziņo, kad tādas ir notikušas.

.NET SqlDependency klase paslēpj visu, kas saistīts ar Service Broker servisiem, rindām, kontraktiem- objektu izveidi un izmantošanu. Tiek paslēpts arī tas, ka patiesībā tiek palaists jauns pavediens (thread), kas "klausās" Service Broker rindu un gaida, kad tajā parādīsies jauns ziņojums. Izmantojot SqlDependency klasi, tas kļūst svarīgi tikai tad, ja nepieciešams definēt detalizētas tiesības datu bāzē. Ir nepieciešamas tiesības Service Broker objektu izveidei un lietošanai (rinda, serviss), procedūras izveidei, ko izmanto lai "klausītos" rindu. Ja SqlDependency vēlas izmantot produkcijas vidē- tas ir svarīgi. Lai definētu tiesības, var noderēt šis raksts.

Piemērs
Priekšnosacījumi:
* Datu bāzē jābūt nodrošinātām lietotāja tiesībām- es slēdzos klāt ar db_owner tiesībām, bet vēlams būtu nodrošināt tieši tās tiesības, kas nepieciešams (šis raksts).
* Datu bāže jābūt atļautam Service Broker (Lai datu bāzē atļautu Service Broker jāizpilda "ALTER DATABASE DBNosaukums SET ENABLE_BROKER").

konkrētais piemērs ir visai triviāls, monitorēšu dbo.MyTable. Tās izveides skripts:

CREATE TABLE dbo.MyTable
(
    MyTableID INT not null PRIMARY KEY IDENTITY,
    SomeText nvarchar(50)
)

Un konsoles aplikācijas kods (jānomaina connection string: <MyServer>, <MyDB>, <user>,<pwd>)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace TestApp
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlDependency.Start("server=<MyServer>;database=<MyDB>;User ID=<user>;Password=<pwd>;Integrated Security=false;");
            Console.WriteLine("Started..");
            get_msg();
            Console.ReadLine();
            SqlDependency.Stop("server=<MyServer>;database=<MyDB>;User ID=<user>;Password=<pwd>;Integrated Security=false;");
        }
        private static void get_msg()
        {
            using (SqlConnection con =
                            new SqlConnection("server=<MyServer>;database=<MyDB>;User ID=<user>;Password=<pwd>;Integrated Security=false;"))
            {
                SqlCommand com = new SqlCommand("SELECT MyTableID, SomeText FROM dbo.MyTable ", con);
                SqlDependency dependency = new SqlDependency(com);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                con.Open();
                com.ExecuteNonQuery();
            }
        }
        static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            Console.WriteLine("dependency Info = {0}, time: {1}",e.Info, DateTime.Now);
            get_msg();
        }
    }
}

Un tad tests:
* Palaižu konsoles aplikāciju (un atstāju atvērtu):

* Management Studio izpildu:

INSERT INTO dbo.MyTable ( SomeText ) VALUES  ( N'Hi');
WaitFor DELAY '00:00:01';
go
UPDATE dbo.MyTable SET SomeText = 'Hi hi';
WaitFor DELAY '00:00:01';
go
DELETE dbo.MyTable;
WaitFor DELAY '00:00:01';
go
INSERT INTO dbo.MyTable ( SomeText ) VALUES  ( N'Hi');
WaitFor DELAY '00:00:01';
go
INSERT INTO dbo.MyTable ( SomeText ) VALUES  ( N'Hi');
WaitFor DELAY '00:00:01';

un rezultāts: