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

[vc_row][vc_column][vc_column_text]

مقدمه

كامپوننت‌ ها قطعاتی از کدهای اجرایی با فرمت ‌هایی نظیر DLL, OCX, EXE هستند. این کدها با رعایت یک سری استانداردهای مشخص، سرویس ‌های گوناگونی را به برنامه نویسان ارائه می ‌دهد. ضمنا می ‌توان از این کدها در برنامه نویسی FrontEnd سایر برنامه‌ ها نیز استفاده کرد. اغلب Component ها توابعی هستند که با یکدیگر ارتباط دارند و در انجام یک یا چند عملیات معین نقش دارند. استفاده از نرم افزار کامپوننت‌ ها باعث زیباسازی برنامه‌ها و اشتراک گذاری کدهای باینری می‌شود. SqlTableDependency نوعی کامپوننت در زبان C# است و اصلی‌ ترین استفاده از آن در مانیتورینگ تغییرات رکوردهای جداول در SQL Server است. در این مقاله قصد داریم که شما را با این کامپوننت جذاب و کاربردی آشنا کنیم.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

روش ‌های مانیتورینگ تغییرات رکوردهای جداول

برای انجام فرآیند مانیتورینگ تغییرات رکورد در جداول می ‌توان از طریق SQL Server راه حل‌ های بسیاری را یافت، در صورت استفاده از یک وبسایت ساده یا متوسط، استفاده از یک نقشه ارتباط موضوعی (ORM) مانند Entity Framework قابلیت رفع همه نیاز‌ها به طور کامل را دارد. در سیستم‌ های توزیع شده دیگر خبری از این سادگی نیست و به راه دیگری جهت پاسخگویی به نیاز های خود نیاز داریم. مایکروسافت به منظور رفع این مشکل نرم افزاری تحت عنوان SQLTableDependency را به علاقه‌ مندان معرفی کرده است..
SQLTableDependency تقریبا مناسب ‌ترین راه برای انجام مانیتورینگ تغییرات در جداول در سیستم‌ های بزرگ و پیچیده بدون نیاز به چک کردن مدام دیتابیس با کوئری است. یک نوتیفیکشن به ازای هر تغییری که در جدول ایجاد شود، برای شخص ارسال خواهد شد. این تغییر ممکن است شامل Delete و Update و Insert باشد که دستوراتی حاوی تغییرات رکورد بعلاوه نوع عملیات را مشخص می ‌کنند.
پس از رسیدن خبر تغییرات این دستورات به برنامه مورد نظرمان که اینجا بر اساس کد C# است ارسال می ‌شود. لازم به ذکر است که نرم افزار SQLTableDependency جهت انجام این ماموریت از امکانات SQL Server مانند Triggers و Queues و Service Broker استفاده می ‌کند.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

راه اندازی امکان مانیتورینگ تغییرات رکوردهای جداول

در ادامه قصد داریم که روش مانیتورینگ تغییرات در SQL را در 7 گام توضیح دهیم.

1 – ابتدا پیش نیاز دیتابیس و خود کد را بررسی کنید:

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

در قسمت دیتابیس، نرم افزار SQLTableDependency برای اجرا شدن حداقل به SQL Server 2008 R2 نیاز دارد، این نکته خیلی مهم است که ورژن SQL Server به تنهایی کافی نیست. شما لازم است که ورژن دیتابیس خود را نیز حداقل به 2008 R2 ارتقاء دهید. پیش از بررسی و مانیتورینگ تغییرات رکوردهای جداول ابتدا لازم است که ورژن دیتابیس خود را چک کنیم. به همین منظور و برای چک کردن ورژن دیتابیس می ‌توانید از دستور زیر در 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')
'

2 – فعال سازی Service Broker به منظور ارسال نوتیفیکیشن به سی شارپ

برای انجام این کار دستور زیر را در SQL server وارد می‌کنیم، توجه داشته باشید که به جای حروف ایتالیک نام دیتابیس خود را قرار دهد.

ALTER DATABASE MyDatabase SET ENABLE_BROKER

[/vc_column_text][vc_column_text]

3 – دسترسی دادن به کاربرانی که بر روی دیتابیس کار می‌ کنند:

در هنگامی که کاربر ConnectionString، به عنوانAdministrator یا db owner در دیتابیس معرفی نشده باشد. لازم است که دسترسی‌ های زیر را به این کاربر بدهیم:

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

4- نصب SQLTableDependency از طریق افزونه NuGet در ویژوال استدیو

کمپانی مایکروسافت در سال 2014 افزونه‌ ای جدید به اسم NuGet را برای نرم افزار ویژوال استودیو معرفی کرد. این افزونه می ‌تواند تمام سازنده ‌های منبع باز یا OSS موجود را پیدا کرده و به همراه سازنده ‌های وابسته به پروژه‌ های مدنظر ما اضافه کند. افزونه NuGet همچنین می ‌تواند تنظیمات مورد نظر را آپدیت یا حذف نماید. در اینجا نیز به منظور مانیتورینگ تغییرات رکوردهای جداول از این افزونه استفاده می ‌کنیم.

5 – ایجاد یک مدل #C برای مپ ستون ‌های مدنظرمان در جدول

لازم به ذکر است که در اینجا نیازی به ساخت مدل C# برای مپ تمام ستون‌ های جدول نیست. کافی است فقط برای آن ستون‌ هایی که می‌ خواهیم تغییر مقادیرشان را در دیتابیس مشاهده کنیم، مدل C# ایجاد شود. برای مثال در کلاس زیر شما فقط به بررسی ستون‌ های Id, Name, Surname نیاز دارید.

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

6 – ساخت نمونه از SQLTableDependency

حال با استفاده مقدار دهی پارامترهای ConnectionString و نام جدول نسبت به ساخت نمونه از SQLTableDependency اقدام می‌ کنیم. این کار به اجرای بی ‌نقص ‌تر مانیتورینگ تغییرات رکوردهای جداول کمک می‌ کند. در صورتی که مانند الگوی زیر عمل کنیم در نهایت یک Event Handler جهت پاسخ به Event با نام Changed خواهیم داشت:

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

در مراحل مانیتورینگ تغییرات رکوردهای جداول، همیشه نمونه‌ گیریی از SQLTableDependency را در قسمت Using تعریف کنید. اگر در این مواقع برنامه به صورت ناگهانی و اتوماتیک بسته شود، دستور Dispose فراخوانی شده و اشیاء ساخته شده در دیتابیس پاک نمی‌ شوند.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

نکات مهم در مانیتورینگ تغییرات رکوردهای جداول

دستور Start نوتیفیکیشن‌ های وقوع تغییرات در دیتابیس را شنود می‌ کند، یکی از پارامترهای متد استارت WatchDogTimeOut است. این پارامتر دارای مقدار پیش فرض 180 ثانیه‌ای است، وقتی برنامه به صورت ناگهانی بسته شده و متد Stop برای پاک کردن کاراکترهای ساخته شده فراخوانده نشود و این فرآیند به مدت 180 ثانیه طول بکشد، متد استارت وارد عمل شده و سیستم به صورت اتوماتیک کارکتر و اشیای ساخته شده را حذف می‌ کند.
در صورت استفاده از Breakpoint برای دیباگ کردن برنامه، ممکن است زمان 180 ثانیه‌ایWatchDogTimeOut سپری شود. در این هنگام با خطایی از جانب SQLTableDependency مواجه می ‌شویم، که این مورد می ‌تواند برای ما ایجاد مشکل کند. بنابراین در صورتی که قصد انجام دیباگ را دارید، حتما قبل از انجام آن زمان WatchDogTimeOut را از حالت 180 ثانیه ‌ای پیشفرض افزایش دهید.
در صورتی که از سرویس ویندوز استفاده می‌ کنید، هیچگاه اجازه ندهید که سرویس به حالت SLEEP یا IDLE برود. در حالت SLEEP کدهای ساخته شده‌ ی SQLTableDependencyمسدود می‌ شوند و در نتیجه تمامی اشیاء مربوط به SQLTableDependencyدر داخل دیتابیس حذف می ‌شوند. برای حل این مشکل می‌ توانید از تابع SETThreadExecutionState استفاده کنید. که به صورت زیر است:

public partial class MyWinForm: Window
{
    private uint fPreviousExecutionState;

    public Window1()
    {
        InitializeComponent();

        // Set new state to prevent system sleep
        fPreviousExecutionState = NativeMethods.SetThreadExecutionState(
            NativeMethods.ES_CONTINUOUS | NativeMethods.ES_SYSTEM_REQUIRED);
        if (fPreviousExecutionState == 0)
        {
            Console.WriteLine("SetThreadExecutionState failed. Do something here...");
            Close();
        }
    }

    protected override void OnClosed(System.EventArgs e)
    {
        base.OnClosed(e);

        // Restore previous state
        if (NativeMethods.SetThreadExecutionState(fPreviousExecutionState) == 0)
        {
            // No way to recover; already exiting
        }
    }
}

internal static class NativeMethods
{
    // Import SetThreadExecutionState Win32 API and necessary flags
    [DllImport("kernel32.dll")]
    public static extern uint SetThreadExecutionState(uint esFlags);
    public const uint ES_CONTINUOUS = 0x80000000;
    public const uint ES_SYSTEM_REQUIRED = 0x00000001;
}

توجه داشته باشید که در صورت بازگردانده شدن Backup در دیتابیس، نرم افزار SQLTableDependency از کار می ‌افتد. و نیاز به تنظیم مجدد دارد.

نرم افزار SQLTableDependency ستون ‌های زیر را پشتیبانی نمی ‌کند :