C# – SqlDependency İle Query Notification

Merhaba,

Üzerinde çalıştığımız projenin veritabanı kısmında neler olup bittiğini çalışma zamanında takip etmek istiyorsanız ve bu işlemi asenkron yapıya el sürmeden gerçekleştirmek istiyorsanız eğer işte bu içeriğimizde tamda bu konu üzerine konuşacağız.

Herhangi bir T zamanında veritabanımızda ne gibi değişim ve gelişim olduğunu, verilerin eklenme, silinme ve güncellenme durumlarını, tablolar üzerinde yapılacak fiziksel ayarları, SQL sunucusunun yeniden başlatılması yahut sunucu üzerinde oluşan hatalar nedeniyle sunucunun durdurulması vs. vs. gibi işlemleri SqlDependency sınıfı aracılığıyla kontrol edebilmekte ve bunu birazdan göreceğiniz üzere çok ama çok basit bir yapıyla gerçekleştirebilmekteyiz.

Öncelikle yukarıdaki forma özel aşağıdaki kod bloğunu inceleyiniz.

        private void btnPersonelleriGoster_Click(object sender, EventArgs e)
        {
            SqlConnection baglanti = new SqlConnection("Server=.;Database=Northwind;Trusted_Connection=True;");
            SqlCommand cmd = new SqlCommand("Select PersonelID, Adi, SoyAdi, Unvan from dbo.Personeller", baglanti);
            if (baglanti.State == ConnectionState.Closed)
                baglanti.Open();

            #region Dependency
            SqlDependency dependency = new SqlDependency(cmd);
            SqlDependency.Start(baglanti.ConnectionString);

            dependency.OnChange += Dependency_OnChange; 
            #endregion

            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            DataTable Personeller = new DataTable();
            Personeller.Load(dr);

            dgvPersoneller.DataSource = null;
            dgvPersoneller.DataSource = Personeller;

            baglanti.Dispose();
            cmd.Dispose();
        }

        private void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            MessageBox.Show("Veriler güncellenmiştir...");
        }

Gördüğünüz gibi, butona tıklandığı zaman Northwind veritabanına bağlantı kurulmakta ve Personeller tablosu çekilmektedir. Ama dikkat ederseniz arada SqlDependency sınıfı ile bir çalışma gerçekleştirilmiştir. Şimdi gelin bu çalışmada neler yapılmış, ne amaçla yapılmış tüm mantığıyla ortaya koyalım.

SqlDependency sınıfı, SqlCommand nesnesi ile çalışan bir özelliğe sahiptir. Verilen SqlCommand nesnesine özel bir Notification nesnesi üretir ve sunucuya bunu kaydeder. Bu işlemden sonra SqlCommand nesnesindeki sorgunun tablosunu belleğe taşır ve orada tutar. SQL Server Sunucusu belleğe alınan tablo ile fiziksel tablo arasında karşılaştırmayı yaparak olası değişikliklerde SqlDependency nesnesindeki OnChange olayını tetikler. Tabi bu sürecin yürütülmesi içinde SqlDependency.Start metodunun sorguların veritabanına gönderilmesinden önce çağrılmış olması gerekmektedir.

Burada dikkat etmeniz gereken iki husus vardır…
Birincisi;
Kullanılacak SqlCommand nesnesine

Select * from Bilmemne

gibi lalettayin bir sorgu veremezsiniz. Sorgunun kolon isimleri ve tablonunda şablon adı açık bir şekilde belirtilmiş olması gerekmektedir. Yukarıdaki örneğe dikkat ederseniz bu şartlara uygun bir select sorgusu belirtilmiştir.

Ayriyetten SqlCommand nesnesi tetikletilip veritabanına gönderilmelidir. Bu adımdan sonra SqlCommand’da ki sorguya özel tablo belleğe taşınacak ve o SqlCommand’a özel bir durum olacaktır. Buda ikincisidir.

Yukarıdaki örnek kod bloğunun çalıştırılmış hali aşağıdaki gibi olacaktır.


Gördüğünüz gibi veritabanında yapılan değişiklik sunucu tarafından anlık olarak bellekteki sorgu sonucu tabloyla kıyaslandı ve benzersizlik fark edilerek anında OnChange olayı tetiklenmiştir.

Bu işlemden sonra veritabanındaki başka bir veriyi güncellediğimiz zaman OnChange olayı tetiklenmemektedir. Eğer ki, ilk çalışmadan sonraki tüm adımlarda OnChange olayının tetiklenmesini istiyorsanız aşağıdaki gibi hareket edebilirsiniz.

        SqlConnection baglanti;
        SqlCommand cmd;
        SqlDependency dependency;

        void Dependency()
        {
            dependency = new SqlDependency(cmd);
            SqlDependency.Start(baglanti.ConnectionString);

            dependency.OnChange += Dependency_OnChange;
        }

Yukarıda gördüğünüz gibi öncelikle Dependency işlemini yapacak komutlarımızı ayrı bir metoda alalım.

        void PersonelleriGetir()
        {
            try
            {
                baglanti = new SqlConnection("Server=.;Database=Northwind;Trusted_Connection=True;");
                cmd = new SqlCommand("Select PersonelID, Adi, SoyAdi, Unvan from dbo.Personeller", baglanti);
                if (baglanti.State == ConnectionState.Closed)
                    baglanti.Open();

                Dependency();

                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                DataTable Personeller = new DataTable();
                Personeller.Load(dr);

                dgvPersoneller.DataSource = null;
                dgvPersoneller.DataSource = Personeller;

                baglanti.Dispose();
                cmd.Dispose();
            }
            catch
            {

            }
        }

Ardından veritabanı işlemlerimizi yapan komutlarımızıda yukarıdaki gibi farklı bir metoda alabilir, içerisinde uygun noktada Dependency metodunu çağırabiliriz.

        private void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            MessageBox.Show("Veritabanı güncellenmiştir. " + e.Info.ToString());
            dependency.OnChange -= Dependency_OnChange;
            PersonelleriGetir();
        }

OnChange olayımızı yukarıdaki gibi inşa edip, her tetiklenme neticesinde SqlDependency nesnesinin OnChange EventHandler’ından bağlı metodu çıkarıyor ardından PersonelleriGetir metodunu yeniden çağırıyoruz. Bu şekilde PersonelleriGetir metodu global olan cmd referansına yeni bir SqlCommand nesnesi bağlamakta ve ardından bu nesne ile içerisinde Dependency metodunuda tetikleyip dependency referansınada yeni SqlDependency üretip bağlanacaktır.

        private void btnPersonelleriGoster_Click(object sender, EventArgs e)
        {
            PersonelleriGetir();
        }

Olayımızıda bu şekilde kodlamamız yeterli olacaktır.

Yani yukarıda yaptığımız işlemin bütünsel halini aşağıya alırsak eğer

        SqlConnection baglanti;
        SqlCommand cmd;
        SqlDependency dependency;

        void Dependency()
        {
            dependency = new SqlDependency(cmd);
            SqlDependency.Start(baglanti.ConnectionString);

            dependency.OnChange += Dependency_OnChange;
        }
        void PersonelleriGetir()
        {
            try
            {
                baglanti = new SqlConnection("Server=.;Database=Northwind;Trusted_Connection=True;");
                cmd = new SqlCommand("Select PersonelID, Adi, SoyAdi, Unvan from dbo.Personeller", baglanti);
                if (baglanti.State == ConnectionState.Closed)
                    baglanti.Open();

                Dependency();


                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                DataTable Personeller = new DataTable();
                Personeller.Load(dr);

                dgvPersoneller.DataSource = null;
                dgvPersoneller.DataSource = Personeller;

                baglanti.Dispose();
                cmd.Dispose();
            }
            catch
            {

            }
        }
        private void btnPersonelleriGoster_Click(object sender, EventArgs e)
        {
            PersonelleriGetir();
        }
        private void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            MessageBox.Show("Veritabanı güncellenmiştir. " + e.Info.ToString());
            dependency.OnChange -= Dependency_OnChange;
            PersonelleriGetir();
        }

bu tarz bir çalışma gerçekleştirmeniz çoklu kullanım açısından yeterli olacaktır. Aslında temel amaç, yeni bir SqlCommand üretip, bir başka yeni SqlDependency nesnesine bağlayarak SQL Server sunucusuna göndermek ve süreci bu yeni hamlede tekrardan takip etmektir. Ve bunu her adımda yapmaktır…

Son olarak OnChange olayının SqlNotificationEventArgs parametresine değinerek makalemizi sonlandırırsak eğer bu parametre, veritabanında yapılan eylemin bilgisini bize getiren ve algoritmayı buna göre şekillendirmemizi sağlayan bir özelliğe sahiptir. Hemen üstteki kodlardan OnChange olayı içerisindeki çalışmamıza göz atarsanız eğer aşağıdaki görüntü o noktayla ilişkili bir örnek temsil etmektedir.

Ekranda da görmüş olduğunuz gibi veritabanında çalıştırılan insert sorgusu neticesinde fiziksel tablonun bellekteki karşılığı değişeceğinden dolayı OnChange metodu anında tetiklenecek ve bu tetiklenmenin sebebini Insert olarak bizlere getirecektir.

Okuduğunuz için teşekkür ederim…
Sonraki yazılarımda görüşmek üzere…
İyi çalışmalar…