{"id":20937,"date":"2021-02-28T04:57:17","date_gmt":"2021-02-28T04:57:17","guid":{"rendered":"https:\/\/www.gencayyildiz.com\/blog\/?p=20937"},"modified":"2021-02-28T04:57:17","modified_gmt":"2021-02-28T04:57:17","slug":"asp-net-core-5-0-sqltabledependency-kutuphanesi-ile-veritabanindaki-degisikleri-anlik-yakalama","status":"publish","type":"post","link":"https:\/\/www.gencayyildiz.com\/blog\/asp-net-core-5-0-sqltabledependency-kutuphanesi-ile-veritabanindaki-degisikleri-anlik-yakalama\/","title":{"rendered":"Asp.NET Core 5.0 &#8211; SqlTableDependency K\u00fct\u00fcphanesi \u0130le Veritaban\u0131ndaki De\u011fi\u015fikleri Anl\u0131k Yakalama"},"content":{"rendered":"<div id=\"fb-root\"><\/div>\n<p>Merhaba,<\/p>\n<p>Yakla\u015f\u0131k 1 ay \u00f6nce <a href=\"https:\/\/www.youtube.com\/Gen%C3%A7ayY%C4%B1ld%C4%B1z\" rel=\"noopener noreferrer\" target=\"_blank\">youtube kanal\u0131mda<\/a> &#8216;Veritaban\u0131ndaki De\u011fi\u015fiklikleri Anl\u0131k Yakalama Uygulamas\u0131&#8217; ba\u015fl\u0131kl\u0131 a\u015fa\u011f\u0131daki dersi y\u00fcklemi\u015ftim.<\/p>\n<div style=\"text-align:center;\">\n<iframe loading=\"lazy\" width=\"560\" height=\"315\" src=\"https:\/\/www.youtube.com\/embed\/v3bu9mmGzBI\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen><\/iframe>\n<\/div>\n<p>Bu i\u00e7eri\u011fimizde ise ilgili videonun i\u00e7eri\u011findeki SqlTableDependency k\u00fct\u00fcphanesi ile veritaban\u0131ndaki de\u011fi\u015fiklerin yakalanmas\u0131n\u0131 ele alacak ve konuya dair metinsel bir kaynak olu\u015fturaca\u011f\u0131z.<\/p>\n<h3>SqlTableDependency Nedir?<\/h3>\n<p>SQL Server&#8217;da ki tablolar \u00fczerinde yap\u0131lan de\u011fi\u015fikliklere dair denetimde ve izlenimde bulunmak i\u00e7in geli\u015ftirilmi\u015f y\u00fcksek seviyeli bir C# k\u00fct\u00fcphanesidir. \u0130lgili k\u00fct\u00fcphane ile SQL Server&#8217;da dinlenilen herhangi bir tablo \u00fczerinde bir de\u011fi\u015fiklik<span style=\"font-size:12px;\">(ekleme\/silme\/g\u00fcncelleme)<\/span> meydana geldi\u011fi taktirde bu duruma dair SQL Server&#8217;da ki Service Broker taraf\u0131ndan g\u00f6nderilen bildirimi yakalayabilmekte ve yap\u0131lan de\u011fi\u015fikli\u011fe dair runtime&#8217;da t\u00fcm detaylar\u0131yla haberdar olabilmekteyiz.<\/p>\n<h3>Service Broker Nedir?<\/h3>\n<p>SQL Server&#8217;da bulunan herhangi bir veritaban\u0131nda ki herhangi bir tabloya veri eklendi\u011finde, silindi\u011finde yahut g\u00fcncellendi\u011finde bizlere bilgilendirmede bulunacak olan bir servistir. Bu servis, kullan\u0131laca\u011f\u0131 veritaban\u0131nda aktifle\u015ftirilmi\u015f olmal\u0131d\u0131r. Bu durumu kontrol edebilmek i\u00e7in;<\/p>\n<div style=\"text-align:center;\"><code style=\"color:red; font-size:14px;\">SELECT NAME, is_broker_enabled FROM SYS.DATABASES<\/code><\/div>\n<p>sorgusunun sunucuda \u00e7al\u0131\u015ft\u0131r\u0131lmas\u0131 yeterlidir.<br \/>\n<div id=\"attachment_20947\" style=\"width: 564px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2021\/02\/Asp.NET-Core-5.0-\u2013-SqlTableDependency-Kutuphanesi-Ile-Veritabanindaki-Degisikleri-Anlik-Yakalama.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-20947\" src=\"https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2021\/02\/Asp.NET-Core-5.0-\u2013-SqlTableDependency-Kutuphanesi-Ile-Veritabanindaki-Degisikleri-Anlik-Yakalama.png\" alt=\"Asp.NET Core 5.0 \u2013 SqlTableDependency K\u00fct\u00fcphanesi \u0130le Veritaban\u0131ndaki De\u011fi\u015fikleri Anl\u0131k Yakalama\" width=\"554\" height=\"264\" class=\"size-full wp-image-20947\" srcset=\"https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2021\/02\/Asp.NET-Core-5.0-\u2013-SqlTableDependency-Kutuphanesi-Ile-Veritabanindaki-Degisikleri-Anlik-Yakalama.png 554w, https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2021\/02\/Asp.NET-Core-5.0-\u2013-SqlTableDependency-Kutuphanesi-Ile-Veritabanindaki-Degisikleri-Anlik-Yakalama-300x143.png 300w\" sizes=\"auto, (max-width: 554px) 100vw, 554px\" \/><\/a><p id=\"caption-attachment-20947\" class=\"wp-caption-text\">SELECT NAME, is_broker_enabled FROM SYS.DATABASES<\/p><\/div><br \/>\nG\u00f6r\u00fcld\u00fc\u011f\u00fc \u00fczere sorgu neticesinde sunucudaki veritabanlar\u0131 listelenmi\u015f ve service broker \u00f6zelli\u011fi aktif olanlarda &#8216;1&#8217; de\u011feri gelmi\u015ftir. Anl\u0131k de\u011fi\u015fikliklerin bilgisini istedi\u011finiz veritaban\u0131nda ilgili servis &#8216;0&#8217; yani pasif olarak geliyorsa e\u011fer;<\/p>\n<div style=\"text-align:center;\"><code style=\"color:red; font-size:14px;\">ALTER DATABASE <u>[TABLE NAME]<\/u> SET ENABLE_BROKER<\/code><\/div>\n<p>komutunu kullanabilirsiniz.<\/p>\n<h3>Uygulama Olu\u015fturma ve Temel Gereksinimler<\/h3>\n<p>SqlTableDependency&#8217;i \u00f6rneklendirebilmek i\u00e7in \u00f6ncelikle bir Asp.NET Core Web API yahut MVC uygulamas\u0131 olu\u015ftural\u0131m. Ard\u0131ndan se\u00e7ilen yakla\u015f\u0131ma dair temel konfig\u00fcrasyonlar\u0131 yapt\u0131ktan sonra uygulamaya <a href=\"https:\/\/www.nuget.org\/packages\/SqlTableDependency\/\" rel=\"noopener noreferrer\" target=\"_blank\">SqlTableDependency<\/a> k\u00fct\u00fcphanesini Nuget havuzundan y\u00fckleyelim.<\/p>\n<div style=\"text-align:center;\"><code font-size:14px;\">Install-Package SqlTableDependency -Version 8.5.8<\/code><\/div>\n<p>K\u00fct\u00fcphaneyi y\u00fckledikten sonra veritaban\u0131nda de\u011fi\u015fiklerin takip edilece\u011fi tablolar\u0131n modellerini olu\u015ftural\u0131m. Bu modeller ORM yap\u0131lanmas\u0131 olan Entity Framework Core&#8217;da ki entity class&#8217;lar da olabilir. Burada ama\u00e7, ilgili tabloda de\u011fi\u015fiklik oldu\u011fu vakit, service broker ile g\u00f6nderilen iletiyle uygulama aras\u0131nda kontrat g\u00f6revi g\u00f6recek bir model tasarlamakt\u0131r.<\/p>\n<p>Bizler misal olarak &#8216;Northwind&#8217; veritaban\u0131ndaki &#8216;Bolge&#8217; ve &#8216;Personeller&#8217; tablosunu dinleyece\u011fiz;<\/p>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\n    public class Bolge\r\n    {\r\n        public int BolgeId { get; set; }\r\n        public string BolgeTanimi { get; set; }\r\n    }\r\n<\/pre>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\n    public class Personeller\r\n    {\r\n        public string Adi { get; set; }\r\n        public string SoyAdi { get; set; }\r\n    }\r\n<\/pre>\n<p>Esas\u0131nda bu tan\u0131mlanan modeller, yukar\u0131da da bahsedildi\u011fi \u00fczere bir kontrat g\u00f6revi g\u00f6rece\u011fi i\u00e7in farkl\u0131 isim ve i\u00e7erikte tan\u0131mlanabilir. B\u00f6yle bir durumda ilgili modelle veritaban\u0131ndaki tablo aras\u0131nda mapping i\u015fleminin nas\u0131l yap\u0131laca\u011f\u0131n\u0131 yaz\u0131m\u0131z\u0131n devam\u0131nda inceliyor olaca\u011f\u0131z. Ayr\u0131ca tablo i\u00e7erisindeki t\u00fcm kolonlar\u0131 da belirtmek zorunda de\u011filiz. Sadece ilgilenilen kolonlar\u0131 belirtmek yeterli olacakt\u0131r.<\/p>\n<h3>Service Olu\u015fturma ve Uygulamaya Ekleme<\/h3>\n<p>Ard\u0131ndan tablo takip sorumlulu\u011funu \u00fcstlenmesi i\u00e7in &#8216;DatabaseSubscription&#8217; ismindeki generic s\u0131n\u0131f\u0131 olu\u015fturuyoruz.<\/p>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\n    public interface IDatabaseSubscription\r\n    {\r\n        void Configure(string tableName);\r\n    }\r\n<\/pre>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\n    public class DatabaseSubscription&lt;T&gt; : IDatabaseSubscription where T : class, new()\r\n    {\r\n        SqlTableDependency&lt;T&gt; _tableDependency;\r\n        readonly string connectionString;\r\n        public DatabaseSubscription() =&gt;\r\n            connectionString = &quot;Server=.;Database=Northwind;Trusted_Connection=True;&quot;;\r\n\r\n        public void Configure(string tableName)\r\n        {\r\n            _tableDependency = new SqlTableDependency&lt;T&gt;(connectionString, tableName);\r\n\r\n            \/\/Veritaban\u0131nda bir de\u011fi\u015fiklik oldu\u011fu vakit OnChanged event'i tetiklenecektir.\r\n            _tableDependency.OnChanged += (sender, e) =&gt;\r\n            {\r\n                var props = typeof(T).GetProperties();\r\n                foreach (var prop in props)\r\n                    Console.WriteLine($&quot;{prop.Name} : {prop.GetValue(e.Entity)}&quot;);\r\n            };\r\n\r\n            \/\/Olas\u0131 bir hata varsa OnError event'i tetiklenecektir.\r\n            _tableDependency.OnError += (sender, e) =&gt; Console.WriteLine(&quot;Hata :)&quot;);\r\n\r\n            \/\/Takibi ba\u015flat\u0131yoruz.\r\n            _tableDependency.Start();\r\n        }\r\n\r\n        ~DatabaseSubscription() =&gt; _tableDependency.Stop();\r\n    }\r\n<\/pre>\n<p>G\u00f6r\u00fcld\u00fc\u011f\u00fc \u00fczere &#8216;DatabaseSubscription&#8217; s\u0131n\u0131f\u0131 &#8216;Configure&#8217; metodunda, connection string&#8217;de belirtilen veritaban\u0131ndaki verilen &#8216;tableName&#8217;e kar\u015f\u0131l\u0131k tabloyu dinleyecek ve gerekli event&#8217;leri bizim i\u00e7in haz\u0131rlayacakt\u0131r.<\/p>\n<p>\u015eimdi yap\u0131lmas\u0131 gereken bu servisi uygulamaya dahil etmektir.<\/p>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\n    public class Startup\r\n    {\r\n        public void ConfigureServices(IServiceCollection services)\r\n        {\r\n            services.AddSingleton&lt;DatabaseSubscription&lt;Bolge&gt;&gt;();\r\n            services.AddSingleton&lt;DatabaseSubscription&lt;Personeller&gt;&gt;();\r\n            .\r\n            .\r\n            .\r\n        }\r\n\r\n        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)\r\n        .\r\n        .\r\n        .\r\n    }\r\n<\/pre>\n<p>&#8216;Startup.cs&#8217; dosyas\u0131nda &#8216;ConfigureServices&#8217; metodu i\u00e7erisinde ilgili servis istenilen tablolar\u0131 dinleyecek vaziyette uygulamaya eklenmi\u015ftir. Lakin daha eklenen bu servis nesneleri \u00e7al\u0131\u015ft\u0131r\u0131lmam\u0131\u015ft\u0131r. Bizim bu nesneleri aya\u011fa kald\u0131r\u0131p, ilgili tablolar\u0131 takip edilebilir hale getirmemiz gerekmektedir. Bu i\u015flem i\u00e7in en uygun yer &#8216;Configure&#8217; fonksiyonu olabilir. Nihayetinde bu fonksiyon, uygulama ilk aya\u011fa kald\u0131r\u0131lma s\u00fcrecinde tetiklenece\u011finden dolay\u0131 hemen hemen her \u015feyden \u00f6nce ilgili tablolar\u0131n takibini ba\u015flatmak i\u00e7in olduk\u00e7a uygundur.<\/p>\n<p>&#8216;Configure&#8217; metodunda ilgili takip nesnelerini aya\u011fa kald\u0131rabilmek i\u00e7in middleware mahiyetinde extension metotlardan istifade etmek gayet yerinde olacakt\u0131r.<\/p>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\n    static public class DatabaseSubscriptionMiddleware\r\n    {\r\n        public static void UseDatabaseSubscription&lt;T&gt;(this IApplicationBuilder builder, string tableName) where T : class, IDatabaseSubscription, new()\r\n        {\r\n            var subscription = (T)builder.ApplicationServices.GetService(typeof(T));\r\n            subscription.Configure(tableName);\r\n        }\r\n    }\r\n<\/pre>\n<p>Yukar\u0131daki kod blo\u011funu incelerseniz e\u011fer &#8216;UseDatabaseSubscription&#8217; isimli generic metot sayesinde uygulamada istenilen noktada ilgili takip nesnelerini kolayca devreye sokabiliriz. Tabi ki de bu metodu yukar\u0131da da bahsedildi\u011fi gibi &#8216;Configure&#8217; i\u00e7erisinde kullanaca\u011f\u0131z.<\/p>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\n    public class Startup\r\n    {\r\n        public void ConfigureServices(IServiceCollection services)\r\n        {\r\n            services.AddSingleton&lt;DatabaseSubscription&lt;Bolge&gt;&gt;();\r\n            services.AddSingleton&lt;DatabaseSubscription&lt;Personeller&gt;&gt;();\r\n            .\r\n        }\r\n\r\n        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)\r\n        {\r\n            .\r\n            app.UseDatabaseSubscription&lt;DatabaseSubscription&lt;Bolge&gt;&gt;(&quot;Bolge&quot;);\r\n            app.UseDatabaseSubscription&lt;DatabaseSubscription&lt;Personeller&gt;&gt;(&quot;Personeller&quot;);\r\n            .\r\n            .\r\n        }\r\n    }\r\n<\/pre>\n<p>Evet&#8230; B\u00f6ylece uygulama aya\u011fa kalkar kalkmaz ilgili tablolar takip edilir vaziyette olacakt\u0131r. \u015eimdi gelin bir test yapal\u0131m&#8230;<\/p>\n<h3>Test<\/h3>\n<p><a href=\"https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2021\/02\/Asp.NET-Core-5.0-SqlTableDependency-Kutuphanesi-Ile-Veritabanindaki-Degisikleri-Anlik-Yakalama.gif\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2021\/02\/Asp.NET-Core-5.0-SqlTableDependency-Kutuphanesi-Ile-Veritabanindaki-Degisikleri-Anlik-Yakalama.gif\" alt=\"Asp.NET Core 5.0 - SqlTableDependency K\u00fct\u00fcphanesi \u0130le Veritaban\u0131ndaki De\u011fi\u015fikleri Anl\u0131k Yakalama\" width=\"780\" height=\"626\" class=\"aligncenter size-full wp-image-20967\" \/><\/a><\/p>\n<h3>Model \u0130le Tablo Uyu\u015fmasayd\u0131?<\/h3>\n<p>Son olarak makale seyrinde de bahsetti\u011fimiz, olu\u015fturulan model ile takip edilen tablonun kolon bilgileri uyu\u015fmad\u0131\u011f\u0131 taktirde bu probleme kar\u015f\u0131 a\u015fa\u011f\u0131daki gibi &#8216;ModelToTableMapper&#8217; nesnesinin olu\u015fturulmas\u0131 ve ard\u0131ndan &#8216;SqlTableDependency&#8217; nesnesine &#8216;mapper&#8217; parametresinden verilmesi yeterli \u00e7\u00f6z\u00fcm olacakt\u0131r.<\/p>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\n            \/\/E\u011fer ki, model'\u0131n t\u00fcm propertyleri tablodaki kolon isimleriyle e\u015fle\u015fiyorsa \r\n            \/\/buradaki mapper'\u0131 kullanmayabilirsiniz.\r\n            var mapper = new ModelToTableMapper&lt;Bolge&gt;();\r\n            mapper.AddMapping(b =&gt; b.BolgeId, &quot;BolgeId&quot;);\r\n            mapper.AddMapping(b =&gt; b.BolgeTanimi, &quot;BolgeTanimi&quot;);\r\n\r\n            _tableDependency = new SqlTableDependency&lt;T&gt;(connectionString, tableName, mapper: mapper);\r\n<\/pre>\n<p>\u0130lgilenenlerin faydalanmas\u0131 dile\u011fiyle&#8230;<br \/>\nSonraki yaz\u0131lar\u0131mda g\u00f6r\u00fc\u015fmek \u00fczere&#8230;<br \/>\n\u0130yi \u00e7al\u0131\u015fmalar&#8230;<\/p>\n<p>Not : \u00d6rnek projeyi indirmek i\u00e7in <a href=\"https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2021\/02\/SqlTableDependencyExample.zip\">buraya<\/a> t\u0131klay\u0131n\u0131z.<\/p>\n<!-- AddThis Advanced Settings generic via filter on the_content --><!-- AddThis Share Buttons generic via filter on the_content -->","protected":false},"excerpt":{"rendered":"<p>Merhaba, Yakla\u015f\u0131k 1 ay \u00f6nce youtube kanal\u0131mda &#8216;Veritaban\u0131ndaki De\u011fi\u015fiklikleri Anl\u0131k Yakalama Uygulamas\u0131&#8217; ba\u015fl\u0131kl\u0131 a\u015fa\u011f\u0131daki dersi y\u00fcklemi\u015ftim. Bu i\u00e7eri\u011fimizde ise ilgili videonun i\u00e7eri\u011findeki SqlTableDependency k\u00fct\u00fcphanesi ile veritaban\u0131ndaki de\u011fi\u015fiklerin yakalanmas\u0131n\u0131 ele alacak ve konuya dair metinsel&#46;&#46;&#46;<!-- AddThis Advanced Settings generic via filter on get_the_excerpt --><!-- AddThis Share Buttons generic via filter on get_the_excerpt --><\/p>\n","protected":false},"author":1,"featured_media":20969,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2739,3102,4026],"tags":[4140,1274,4142,4138,4139,4141],"class_list":["post-20937","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-net-core","category-asp-net-core","category-asp-net-core-5-0","tag-asp-net-core-veritabanindaki-degisiklikleri-yakalama","tag-service-broker","tag-service-broker-nedir","tag-sqltabledependency","tag-sqltabledependency-kutuphanesi","tag-sqltabledependency-nedir"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/20937","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/comments?post=20937"}],"version-history":[{"count":35,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/20937\/revisions"}],"predecessor-version":[{"id":20976,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/20937\/revisions\/20976"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media\/20969"}],"wp:attachment":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media?parent=20937"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/categories?post=20937"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/tags?post=20937"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}