SqlTableDependency برای مانیتورینگ تغییرات رکوردهای جداول در Sql Server

برای مانیتورینگ تغییرات در سطح تغییرات رکورد در جداول در Sql Server راه حل های زیادی وجود دارد ، مثلا" برای یک وبسایت کوچک یا متوسط استفاده از یک ORM مثل Entity Framework می تونه به طور کامل نیاز شما را پوشش دهد ولی برای سیستم های Enterprise یا توزیع شده کار به همین سادگی نمی باشد و شما به یک راه حل کارآمدتر و مطمئن تر نیاز دارید ، برای پاسخ به همین نیاز مایکروسافت ابزار با نام SqlTableDependency را معرفی کرد.

SqlTableDependency بهینه ترین راه برای مانیتورینگ تغییرات در جداول در سیستم های بزرگ و پیچیده بدون نیاز به چک کردن مدام دیتابیس با کوئری. برای هر تغییری در جدول اعم از Delete و Update و Insert یک نوتیفیکشن که حاوی تغییرات رکورد بعلاوه نوع عملیات شامل حذف و ویرایش و ایجاد می باشد به برنامه که اینجا کد C# می باشد ارسال می شود.SqlTableDependency برای انجام این مهم در پشت صحنه از امکانات SQL Server مثل triggers و queues و service broker استفاده می کند.

برای راه اندازی این امکان چند مرحله ساده وجود دارد :

1 - چک کردن پیش نیاز ها دیتابیس و کد:

- برای سمت کد .NET Framewrok 4.5.1 یا ورژن های بالاتر و یا  .NET CORE 2.0 یا ورزن های بالاتر.

- برای سمت دیتابیس ، SqlTableDependency برای اجرا به حداقل SQL Server 2008 R2 نیاز دارد ، دقت بفرمایید که صرفا" ورژن خود SQL Server کفایت نمی کند و حتما" باید ورژن خود دیتابیس شما هم حداقل 2008 R2 باشد (ممکن است دیتابیس شما مثلا" با 2005 ساخته شده باشد و سپس به 2008 ارتقاء پیدا کرده باشد) برای چک کردن ورژن دیتابیس خود از دستور زیر با نام دیتابیس خود در SQL استفاده کنید :

USE <your db>

DECLARE @DBINFO TABLE ([ParentObject] VARCHAR(60),[Object] VARCHAR(60),[Field] VARCHAR(30),[VALUE] VARCHAR(4000))
INSERT INTO @DBINFO
EXECUTE sp_executesql N'DBCC DBINFO WITH TABLERESULTS'
SELECT [Field]
,[VALUE]
,CASE
WHEN [VALUE] = 515 THEN 'SQL 7'
WHEN [VALUE] = 539 THEN 'SQL 2000'
WHEN [VALUE] IN (611,612) THEN 'SQL 2005'
WHEN [VALUE] = 655 THEN 'SQL 2008'
WHEN [VALUE] = 661 THEN 'SQL 2008R2'
WHEN [VALUE] = 706 THEN 'SQL 2012'
WHEN [VALUE] = 782 THEN 'SQL 2014'
WHEN [VALUE] = 852 THEN 'SQL 2016'
WHEN [VALUE] > 852 THEN '> SQL 2016'
ELSE '?'
END [SQLVersion]
FROM @DBINFO
WHERE [Field] IN ('dbi_createversion','dbi_version')

نمونه خروجی این کوئری که نشان می دهد با اینکه سرور SQL Server 2008 R2 می باشد ولی دیتابیس مورد نظر ورزن قدیمی تر از SQL Server 2008 R2 می باشد :

و نمونه خروجی که حداقل ورژن را پشتیبانی می کند :

2 - فعال کردن Service Broker برای ارسال نوتیفکشن به C# :

برای این منظور دستور زیر با نام دیتابیس خود جرا نمایید :

ALTER DATABASE MyDatabase SET ENABLE_BROKER

3 - اعمال دسترسی های لازم به کاربر دیتابیس:

 در مواردی که کاربر ConnectionString شما Administrator یا db owner دیتابیس نمی باشد یا نقش db_owner را ندارد ، باید دسترسی های زیر را به کاربر ConnectionString بدهید :

• ALTER
• CONNECT
• CONTROL
• CREATE CONTRACT
• CREATE MESSAGE TYPE
• CREATE PROCEDURE
• CREATE QUEUE
• CREATE SERVICE
• EXECUTE
• SELECT
• SUBSCRIBE QUERY NOTIFICATIONS
• VIEW DATABASE STATE
• VIEW DEFINITION

4 - نصب SqlTableDependency از طریق Nuget در ویژوال استدیو

5 - ساخت مدل C# برای مپ ستون های جدول مورد نظر ، دقت بفرمایید که نیاز به مپ تمام ستون های جدول نیست و فقط آنهایی که ما علاقه مندیم تغییرات مقدارشان در دیتابیس را مشاهده کنیم ، همانند کلاس نمونه زیر:

public class Customer
{
 public int Id { get; set; }
 public string Name { get; set; }
 public string Surname { get; set; }
}

مدل ساخته شده حتما" نیاز نیست با جدول مورد نظر هم نام باشد.

6 - ساخت نمونه از SqlTableDependency با مقدار دهی پارامتر های ConnectionString و نام جدول در صورت غیر هم نام بودن با مدل C# و در نهایت ساخت یک event handler برای پاسخ به event با نام Changed مربوط به SqlTableDependency همانند نمونه کد زیر:

public class Program
{
 private static string _con = "data source=.; initial catalog=MyDB; integrated security=True";
   
 public static void Main()
 {
  // The mapper object is used to map model properties 
  // that do not have a corresponding table column name.
  // In case all properties of your model have same name 
  // of table columns, you can avoid to use the mapper.
  var mapper = new ModelToTableMapper<Customer>();
  mapper.AddMapping(c => c.Surname, "Second Name");
  mapper.AddMapping(c => c.Name, "First Name");

  // Here - as second parameter - we pass table name: 
  // this is necessary only if the model name is different from table name 
  // (in our case we have Customer vs Customers). 
  // If needed, you can also specifiy schema name.
  using (var dep = new SqlTableDependency<Customer>(_con, "Customers", mapper: mapper));
  {
   dep.OnChanged += Changed;
   dep.Start();

   Console.WriteLine("Press a key to exit");
   Console.ReadKey();

   dep.Stop();
  } 
 }

 public static void Changed(object sender, RecordChangedEventArgs<Customer> e)
 {
  var changedEntity = e.Entity;
      
  Console.WriteLine("DML operation: " + e.ChangeType);
  Console.WriteLine("ID: " + changedEntity.Id);
  Console.WriteLine("Name: " + changedEntity.Name);
  Console.WriteLine("Surname: " + changedEntity.Surname);
 }
}

بعد از اجرای برنامه موارد زیر به طور خودکار در دیتابیس شما ساخته می شوند :

- Message types

- Contract

- Queue

- Service Broker

- Trigger بر روی جدولی که قرار است مانیتور شود

- Stored procedure برای پاک کردن اشیایی در دیتابیس که بر اثر خروج ناگهانی برنامه dispose نشده اند

در شکل زیر نمونه ایی از این اشیا مشخص است:

7 - نکات :

- متد Start شروع به شنود برای دریافت نوتیفکشن های تغییرات در دیتابیس می کند ، این متد یک پارامتر با نام watchDogTimeOut دارد که مقدار پیش فرض آن 180 ثانیه می باشد ، در مواردی که برنامه به طور ناگهانی بسته می شود و متد Stop یا Dispose برای پاک کردن ، اشیا ساخته شده در دیتابیس صدا زده نمی شوند، اگر به مدت 180 ثانیه شنودی انجام نشود ، سیستم به طور خودکار اشیا ساخته شده را پاک میکند.

 -  همیشه نمونه گیریی از SqlTableDependency  را در داخل Using تعریف کنید تا در صورت بسته شدن اصطراری برنامه به صورت اتوماتیک دستور Dispose فراخوانی و اشیاء ساخته شده در دیتابیس پاک شوند.

 - در طول دیباگ برنامه ممکن است با استفاده از Breakpoint زمان watchDogTimeOut سپری شود و با خطای SqlTableDependency مواجه شوید ، لذا در زمان دیباگ حتما" زمان watchDogTimeOut را افزایش دهید.

 - اگر از در Windows service استفاده می کنید ، سرویس نباید به حالت SLEEP یا IDLE برود، در حالت SLEEP کدهای SqlTableDependency  بلاک می شوند و در نتیجه تمام اشیاء مربوط به SqlTableDependency   در دیتابیس پاک می شوند برای حل این مشکل به این مطلب مراجعه بفرمایید.

 - در صورت بازگردانی بک آپ در دیتابیس ، SqlTableDependency  از کار می افتد.

 - هنگام قطع و وصل Connection ، هیچ راهی برای re-connect مجدد SqlTableDependency به صف موجود در دیتابیس وجود ندارد و SqlTableDependency باید مجددا" نمونه گیری گردد.

 - SqlTableDependency متوجه تغییراتی مثل آپدیت رشته خالی با رشته با space مثل '' به ' ' نمی شود ، همین مورد برای Null و رشته خالی هم صادق است.

 - در صورت استفاده از sql bulk copy برای دریافت نوتیفیکشن ایجاد رکورد باید تنظیم SqlBulkCopyOptions.FireTriggers را قرار دهید.

 - ستون های از نوع زیر در SQL Server توسط SqlTableDependency  پشتیبانی نمی شوند :

XML

IMAGE

TEXT/NTEXT

STRUCTURED

GEOGRAPHY

GEOMETRY

HIERARCHYID

SQL_VARIANT

SqlTableDependency vs SqlDependency

SqlTableDependency خاص مشاهده تغییرات بر روی جدول است در حالی که SqlDependency بر روی یک کوئری هم کار می کند ، لیست کامل تفاوت ها در جدول زیر :

 8 - لینک های مفید:

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/detecting-changes-with-sqldependency

https://www.codeproject.com/Tips/5253254/Daily-reminder-using-SQL-Agent-and-SignalR

 Monitor table change with Blazor: نمونه استفاده شده با  .NET CORE 3.0 Blazor

Monitor table change with WPF and WCF: آپدیت یک DataGrid با تغییر رکوردها در دیتابیس.

Monitor table change with MVC, SignalR and jQuery: نمونه وبسایت برای آپدیت HTML table با تغییر رکوردها در دیتابیس.

Monitor table change with MVC, SignalR and Knockout JS: نمونه وبسایت رزرو بلیط هواپیما با SqlTableDependency .