مانیتور کردن SQL Server و ساخت برنامه ی Real Time

بسم الله الرحمن الرحیم

در این آموزش قصد دارم شما را با یکی دیگر از ویژگی های SQL Server آشنا کنم. متاسفانه بسیاری از افراد فکر می کنند SQL Server فقط یک ابزار برای نگهداری اطلاعات است! و از بسیاری از سرویس های SQL Server بی خبر هستند.

آموزش مانیتورینگ SQL Server و ثبت تغییرات ایجاد شده

SqlDependency

کلاس SqlDependency و Query notifications ابزارهایی هستند که به شما اجازه می دهند SQL Server را مانیتور کنید این ابزار ها در SQL Server 2005 ارائه شدند.

Query Notifications

Query Notifications به برنامه شما اجازه می دهد در صورتی که داده ای در دیتابیس تغییر داده شد، از این تغییر اطلاع پیدا کند.
هدف این کلاس ذخیره کردن اطلاعاتی جدیدی است که مداوم در دیتابیس ایجاد می شود.

اگر شما بخواهید این کار را بدون استفاده از این سرویس انجام دهید باید یک تایمر داشته باشید که در بازه های زمانی معین اطلاعات را بررسی و در صورتی که تغییری ایجاد شد این اطلاعات را برای شما ارسال کند که تعیین این تغییر خود امری دشوار خواهد بود، اگر بخواهیم ساده بگوییم شما کاری را که SQL Server قبلا انجام داده و آن را بهینه کرده است می خواهید انجام دهید!

قبلا در مباحث دیگرSQL Server Broker را توضیح داده ام، در اینجا نیز برای استفاده از این سرویس باید از Service Broker و QUEUE استفاده کنید.

مثل همیشه آموزش را با مثال ادامه می دهیم. مثال بسیار ساده است، ما یک دیتابیس با جدولی به نام Users که شامل دو فیلد FirstName و LastNeme است ایجاد می کنیم. در این مثال ما دو فرم داریم یکی فرم نمایش اطلاعات و دیگری درج اطلاعات که پس از درج اطلاعات، فرم نمایش سریعا آن را نمایش خواهد داد.

SQL Server تمامی سرویس های خود را به صورت پیش فرض متوقف کرده است و شما برای استفاده از هر سرویس باید ابتدا آن را اجرا کنید و یا دسترسی های آن را نیز فعال کنید.

برای استفاده از سرویس های این مثال باید ابتدا یک Queue ایجاد و سپس Broker اسکیوال را برای دیتابیس خود فعال و در نهایت دسترسی به کاربر SQL Server خود بدهیم تا بتواند از این سرویس استفاده کند؛ کاربر شما همان کاربری است که در SqlConnection با نام userId آن را معرفی می کنید.

مثال:

<add name="Context" providerName="System.Data.SqlClient" connectionString="Data Source=DadehNegar.Blog.ir\SQL-ENG-Full2012;Initial Catalog=DadehNegar.Blog.ir; user ID=sqlUser; password=********;" />

حال این دستورات را در SQL Server اجرا کنید

USE YourDatabaseName;
     
CREATE QUEUE NameChangeQueue;
CREATE SERVICE NameChangeService ON QUEUE NameChangeQueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
     
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO YourUserName;
 
ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

به جای YourDatabaseName نام دیتابیس خود را قرار دهید و به جای YourUserName نام کاربر SQL Server خود را وارد نمایید.

پس از اجرای این دستورات باید یک صف و یک سرویس جدید را در دیتابیس خود مانند زیر مشاهده کنید:

حال یک پروژه Windows Application ایجاد کنید و سپس یک فرم جدید با نام InsertForm ایجاد و دو TextBox با نام های textBox_FirstName و textBox_LastName به همراه یک Button با نام button_Insert بر روی فرم قرار دهید.

حال بر روی Button مربوط به Insert دابل کلیک کنید و کد زیر را در آن قرار دهید:

try
     {
        using (SqlConnection cn = new SqlConnection(ShowData.connectionString))
         {
            using (SqlCommand cmd = cn.CreateCommand())
             {
                cmd.CommandText = "INSERT INTO Users VALUES (@FirstName, @LastName)";
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@FirstName", textBox_FirstName.Text);
                cmd.Parameters.AddWithValue("@LastName", textBox_LastName.Text);

                 cn.Open();

                 cmd.ExecuteNonQuery();
              }
            }
      }
 catch (Exception exception)
     {
       MessageBox.Show(exception.Message);
     }

در اینجا ما کد مربوط به درج اطلاعات را ثبت کردیم. حال به سراغ فرم مربوط به نمایش اطلاعات می رویم، فرم اصلی برنامه خود که Form1 نام دارد

انتخاب کنید و نام آن را به ShowData تغییر دهید.

بر روی فرم یک Button با نام button_ShowInsertForm برای نمایش فرم InsertForm قرار دهید و همچنین یک لیست باکس برای نمایش اطلاعات

ثبت شده با نام listBox_ShowData بر روی فرم قرار دهید.

بر روی Button ایجاد شده (button_ShowInsertForm) دابل کلیک کنید و کد زیر را در آن قرار دهید:

 private void button_ShowInsertForm_Click(object sender, EventArgs e)
        {

            try
            {
                InsertForm insertForm = new InsertForm();
                insertForm.Show();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
        }

کد زیر را در بخش اصلی کلاس فرم، (خارج از Event ها) در انتهای کد

public partial class ShowData : Form
    {

کد زیر را اضافه کنید:

 public static string connectionString = @"Data Source=DadehNegar.Blog.ir\SQL2012;Initial Catalog=SQlDepedency_Dadaehnegar; user ID=sa; password=******;";

حال باید کد مربوطه را بنویسیم، اولین چیزی که می نویسیم این است که چک کنیم که آیا این کاربری که شما برای متصل شدن به SQL Server معرفی کرده اید دسترسی به query notifications دارد یا خیر؟

در اینجا ما از کلاس SqlClientPermission برای اینکار استفاده می کنیم. بر روی فرم InsertForm کلیک راست کنید و گزینه View Code را انتخاب و در بخش کد نویسی این متد جدید را اضافه کنید:

private bool DoesUserHavePermission()
{
    try
        {
         SqlClientPermission clientPermission = new SqlClientPermission(PermissionState.Unrestricted);

          // will throw an error if user does not have permissions
            clientPermission.Demand();

             return true;
         }
     catch
         {
           return false;
         }
 }

حال متد مورد نظر خود را برای بدست آوردن اطلاعات از  می نویسیم

private void GetNames()
 {
   try
      {
       if (!DoesUserHavePermission())
          return;

        listBox_ShowData.Items.Clear();

        //  You must stop the dependency before starting a new one.
        //  You must start the dependency when creating a new one.
        SqlDependency.Stop(connectionString);
        SqlDependency.Start(connectionString);

         using (SqlConnection cn = new SqlConnection(connectionString))
          {
            using (SqlCommand cmd = cn.CreateCommand())
                {
                      
                 cmd.CommandType = CommandType.Text;
                 cmd.CommandText = "SELECT FirstName, LastName FROM dbo.[Users]";

                  cmd.Notification = null;

                  //  creates a new dependency for the SqlCommand
                  SqlDependency dep = new SqlDependency(cmd);
                  //  creates an event handler for the notification of data
                  //      changes in the database.
                  //  NOTE: the following code uses the normal .Net capitalization methods, though
                 //      the forum software seems to change it to lowercase letters
                 dep.OnChange += new OnChangeEventHandler(dep_onchange);

                 cn.Open();

                  using (SqlDataReader dr = cmd.ExecuteReader())
                  {
                   while (dr.Read())
                     {
                       listBox_ShowData.Items.Add(dr.GetString(0) + " " + dr.GetString(1));
                     }
                   }
               }
          }

     }
   catch (Exception exception)
      {
        throw new Exception(exception.Message);
      }

 }

چند نکته در ارتباط با SqlDependency

همانطور که می بینید من در Query خود از کارکتر * برای بدست اوردن تمام اطلاعات ستوان های جدول خود استفاده نکردم، زیرا در صورت استفاده از این کارکتر به جای قید کردن نام تمام ستون ها شما اطلاعات نادرست دریافت خواهید کرد.
در نتیجه باید ستون هایی را که نیاز دارید، تک تک قید کنید.
نکته بعد در مورد قید کردن نام جداول است در SqlDependency هیچوقت نام جداول را به همراه owner آن قید نکنید (owner.TableName) بلکه فقط نام جدول را به تنهایی قید کنید زیرا اگر با owner نام جدول مشخص شود، اطلاعات نادرست دریافت خواهید کرد.

این  event را نیز به برنامه خود اضافه کنید:

void dep_onchange(object sender, SqlNotificationEventArgs e)
        {
            // this event is run asynchronously so you will need to invoke to run on UI thread(if required).
            if (this.InvokeRequired)

                listBox_ShowData.BeginInvoke(new MethodInvoker(GetNames));
            else
                GetNames();

            // this will remove the event handler since the dependency is only for a single notification
            SqlDependency dep = sender as SqlDependency;

            //  NOTE: the following code uses the normal .Net capitalization methods, though
            //      the forum software seems to change it to lowercase letters
            dep.OnChange -= new OnChangeEventHandler(dep_onchange);
        }

کد مربوط به Form_Load را به صورت زیر تغییر دهید:

 private void ShowData_Load(object sender, EventArgs e)
        {
            try
            {
                GetNames();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
        }

شما باید SqlDependency در زمانی که می خواهید از برنامه خارج شوید متوقف کنید، پس به قسمت نمایش فرم بازگرید و در قسمت ساخت Event ها در پنجره Properties رویداد (Event) مربوط به FormClosing را انتخاب و برروی آن دابل کلیک کنید تا این رویداد ایجاد شود و سپس کد زیر را در آن قرار دهید:

   private void ShowData_FormClosing(object sender, FormClosingEventArgs e)
        {
            try
            {
            }

            finally
            {
                SqlDependency.Stop(connectionString);
            }
        }

حال برنامه را اجرا کنید اگر در فرم ثبت اطلاعات، رکوردی را در دیتابیس خود ثبت کنید، اطلاعات ثبت شده سریعا در فرم نمایش اطلاعات، نمایش داده خواهد شد. اگر شما یک رکورد را نیز به صورت مستقیم از طریق SQL Server ثبت کنید، فرم نمایش اطلاعات رکورد جدید را نمایش خواهد داد زیرا ما از سرویس SQL Server برای مشاهده تغییرات ایجاد شده استفاده می کنیم.

با استفاده از این آموزش ما در دوره آموزشی SignalR  می خواهیم برنامه های Real Time مانند چت و ... ایجاد کنیم.

اگر شما نمی توانید پروژه خود را به درستی پیاده سازی کنید با استفاده از کد پروژه قرار داده شده در زیر می توانید مشکل خود را برطرف کنید و از این مثال استفاده کنید:

دانلود پروژه SQLDependency

به دلیل اینکه ممکن است نتوانید SQL Server خود را به درستی برای این پروژع تنظیم کنید، از دیتابیس خود یک Script در لینک زیر برای دانلود قرارداده ام کافی است این فایل را در SQL Server باز کنید و آن را اجرا (Excute) کنید.

دانلود اسکریپت دیتابیس SqlDependency