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

چکیده

0 1

SqlTableDependency یک کامپوننت در C# می باشد که منظور مانیتورینگ ایجاد و ویرایش حذف رکوردهای جداول در Sql Server استفاده می شود.این مقاله به منظور آشنایی بیشتر با این کامپوننت می باشد.

پیشنهاد کارشناسان.نت :
ثبت نام هر دو کلاس حضوری مقدماتی و پیشرفته Asp.Net Core Blazor WebAssembly
تنها با 500 هزار تومان (هر دوره تکی 300 هزار تومان)

آموزش

SqlTableDependency-برای-مانیتورینگ-تغییرات-رکوردهای-جداول-در-Sql-ServerReviewed by کارشناسان.نت on July 22Rating:5

برای مانیتورینگ تغییرات در سطح تغییرات رکورد در جداول در 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 بر روی یک کوئری هم کار می کند ، لیست کامل تفاوت ها در جدول زیر :

SqlDependency	SqlTableDependecy	Functionality
		View
		Join multiple tables
		Where
		Generic
		Notification containing updated values
		Notification containing old values
		Notification only on insert
		Notification only on update
		Notification only on delete
		Notification only when specific column is changes