آموزش SqlDependency در WPF (سی شارپ) برای آپدیت خودکار داده های جدول

آموزش SqlDependency در WPF (سی شارپ)

گاهی اوقات در برنامه نویسی و کار با دیتابیس ممکن است وضعیتی پیش آید که نیاز باشد تا برنامه از تغییر اطلاعات موجود در جداول با خبر شود. در این مواقع یک راه این است که خودتان یک تایمر برای بررسی وضعیت داده های موجود ایجاد کنید تا در یک فاصله زمانی مشخص، جدول مورد نظر را بررسی کند. اما راه بهتری نیز وجود دارد و آن هم استفاده از قابلیت موجود در Sql Server  به نام SQL Dependency است. این ویژگی برنامه شما را قادر می سازد تا از تغییرات بوجود آمده در دیتابیس بدون استفاده از تایمر و یا حلقه، با خبر شود. در این مقاله نحوه استفاده از این ویژوگی SqlDependency در Sql Server برای آپدیت خودکار داده های جدول آموزش خواهیم داد.

نحوه استفاده SqlDependency

مراحل زیر نحوه تعریف یک dependency، اجرای یک Command و دریافت اعلان هنگام تغییر اطلاعات در دیتابیس را نشان می دهد:

• برقراری اتصال SqlDependency با دیتابیس
• ایجاد شیء SqlConnection و SqlCommand برای کار با دیتابیس
• اتصال شیء SqlDependency به یک SqlCommand
• ایجاد یک handler برای رویداد OnChange شیء SqlDependency
• اجرای Command با یکی از متدهای Execute موجود در شیء SqlCommand
• توقف اتصال SqlDependency به دیتابیس

مراحل زیر مربوط به نرم افزار Sql Server می باشد:

• ایجاد یک QUEUE
• ایجاد یک SERVICE
• فعال سازی Service Broker

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

USE SqlDependencyDemo;
CREATE QUEUE SqlDependencyQueue;
CREATE SERVICE SqlDependencyService ON QUEUE SqlDependencyQueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
ALTER AUTHORIZATION ON DATABASE::SqlDependencyDemo TO [sa]
ALTER DATABASE SqlDependencyDemo SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

در کد بالا به جای DatabaseName نام دیتابیس برنامه، به جای QueueName نام Queue، ServiceName نام Service و به جای YourUserName نام کاربری که با آن به دیتابیس متصل می شوید را وارد کنید. بعد از اجرای دستورات بالا در Query نرم افزار SQL Server گزینه های زیر به بخش دیتابیس شما اضافه خواهد شد.

بعد از پیاده سازی مراحل بالا، هر تغییراتی که در دیتابیس ایجاد شود، رویداد OnChange شیء SqlDependency فراخوانی می شود. در ادامه مراحل بالا را با یک مثال و فیلم توضیح خواهیم داد.

مثال (استفاده از SqlDependency در WPF) :

برای یاد گیری بهتر به نمونه برنامه با WPF توجه کنید. این مثال شامل دو عدد TextBox با نام های TxtFullName و TxtPhoneNumber، یک عدد Button با نام BtnInsert و یک عدد DataGrid با نام DgCustomers می باشد. در برنامه زیر اطلاعات (نام و شماره تلفن مشتری) را از کاربر می گیرد و در دیتابیس SQL Server ثبت می کند. سپس برنامه به وسیله SqlDependency به صورت خودکار متوجه تغییرات بوجود آمده می شود و متد BindCustomers را فراخوانی می کند تا DataGrid را آپدیت کند.

فایل های موجود در پروژه

محتوای فایل MainWindow.xaml

<Window x:Class="SqlDependencyDemo.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:SqlDependencyDemo"
        mc:Ignorable="d"
        Title="SourceSara.Com" 
        Height="350" 
        Width="525"
        Loaded="MainWindow_OnLoaded">
    <Grid>
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="*"/>
        </Grid.RowDefinitions>

        <StackPanel Grid.Row="0"
                    HorizontalAlignment="Center"
                    Orientation="Horizontal">
            <TextBox Name="TxtFullName"
                     Width="150"
                     Height="40"
                     Margin="8"
                     VerticalContentAlignment="Center"
                     HorizontalContentAlignment="Center">
                FullName
            </TextBox>

            <TextBox Name="TxtPhoneNumber"
                     Width="150"
                     Height="40"
                     Margin="8"
                     VerticalContentAlignment="Center"
                     HorizontalContentAlignment="Center">
                PhoneNumber
            </TextBox>
            
            <Button Name="BtnInsert"
                    Height="40"
                    Width="90"
                    Margin="8"
                    Click="BtnInsert_OnClick">
                INSERT
            </Button>
        </StackPanel>
        
        <DataGrid Name="DgCustomers"
                  Grid.Row="1"/>
    </Grid>
</Window>

محتوای فایل MainWindow.xaml.cs

using System;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Security.Permissions;
using System.Windows;
using System.Windows.Threading;

namespace SqlDependency
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow
    {
        #region Fields

        /// <summary>
        /// برای اتصال SqlDependency با دیتابیس
        /// </summary>
        private readonly SqlConnection _connection;

        /// <summary>
        /// برای اجرای دستور با SqlDependency
        /// </summary>
        private readonly SqlCommand _command;

        /// <summary>
        /// شیء SqlDependency
        /// </summary>
        private System.Data.SqlClient.SqlDependency _dependency;

        /// <summary>
        /// رشته اتصال به دیتابیس Sql Server
        /// </summary>
        private const string ConnectionString = "Server=(local);Database=SqlDependencyDemo;Trusted_Connection=True;";

        /// <summary>
        /// دستور انتخاب فیلد های جدول
        /// </summary>
        private const string SelectQuery = "SELECT FullName, PhoneNumber FROM dbo.Customers";

        #endregion

        #region Constructor

        public MainWindow()
        {
            InitializeComponent();

            _connection = new SqlConnection(ConnectionString);
            _command = new SqlCommand(SelectQuery, _connection);
        }

        #endregion

        #region UI Events

        private void MainWindow_OnLoaded(object sender, RoutedEventArgs e)
        {
            // راه اندازی SqlDependency برای بررسی تغییرات
            StartCustomersMonitoring();

            // بارگذاری داده های موجود
            BindCustomers();
        }

        private void BtnInsert_OnClick(object sender, RoutedEventArgs e)
        {
            Insert();
        }

        #endregion

        #region Overrides

        protected override void OnClosing(CancelEventArgs e)
        {
            // برای توقف SqlDependency استفاده می شود
            System.Data.SqlClient.SqlDependency.Stop(ConnectionString);
            base.OnClosing(e);
        }

        #endregion

        #region Methods

        /// <summary>
        /// برای بارگذاری داده ها استفاده می شود
        /// </summary>
        private void BindCustomers()
        {
            try
            {
                using (var connection = new SqlConnection(ConnectionString))
                using (var adaptor = new SqlDataAdapter(SelectQuery, connection))
                {
                    var dataTable = new DataTable();
                    adaptor.Fill(dataTable);
                    DgCustomers.ItemsSource = dataTable.DefaultView;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("BindCustomers - " + ex.Message);
            }
        }

        /// <summary>
        /// برای افزودن رکورد جدید استفاده می شود
        /// </summary>
        private void Insert()
        {
            try
            {
                var query =
                    $"INSERT INTO dbo.Customers (FullName, PhoneNumber) VALUES ('{TxtFullName.Text}', '{TxtPhoneNumber.Text}')";
                using (var connection = new SqlConnection(ConnectionString))
                using (var command = new SqlCommand(query, connection))
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Insert - " + ex.Message);
            }
        }
     
        /// <summary>
        /// برای راه اندازی SqlDependency استفاده می شود
        /// </summary>
        private void StartCustomersMonitoring()
        {
            try
            {
                // قبل از شروع یک بار آن متوقف کنید
                System.Data.SqlClient.SqlDependency.Stop(ConnectionString);
                System.Data.SqlClient.SqlDependency.Start(ConnectionString);

                RegisterSqlDependency();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        /// <summary>
        /// برای اتصال یک شیء SqlCommand به SqlDependency
        /// </summary>
        private void RegisterSqlDependency()
        {
            // بررسی دسترسی کاربر
            if (!DoesUserHavePermission())
                return;

            // بایند کردن command با شیء SqlDependency
            _command.Notification = null;
            _dependency = new System.Data.SqlClient.SqlDependency(_command);

            // ایجاد یک handler برای اعمال تغییرات بر روی دیتا گرید
            _dependency.OnChange += CustomersDependencyOnChange;

            // باز کردن اتصال دیتابیس در صورت نیاز
            if (_connection.State != ConnectionState.Open)
                _connection.Open();

            // اجرای Command با یکی از متدهای Execute موجود در شیء SqlCommand
            _command.ExecuteNonQuery();

            // بستن اتصال بعد از اجرای دستور
            _connection.Close();
        }

        /// <summary>
        /// ایجاد یک handler برای رویداد OnChange شیء SqlDependency
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void CustomersDependencyOnChange(object sender, SqlNotificationEventArgs e)
        {
            var dependency = (System.Data.SqlClient.SqlDependency)sender;
            dependency.OnChange -= CustomersDependencyOnChange;
            Dispatcher.BeginInvoke(DispatcherPriority.DataBind, new Action(BindCustomers));
            RegisterSqlDependency();
        }

        /// <summary>
        /// برای بررسی دسترسی کاربر استفاده می شود
        /// </summary>
        /// <returns></returns>
        private static bool DoesUserHavePermission()
        {
            try
            {
                var clientPermission = new SqlClientPermission(PermissionState.Unrestricted);
                clientPermission.Demand();
                return true;
            }
            catch
            {
                return false;
            }
        }

        #endregion
    }
}

توضیحات مثال

دستور زیر برای شروع اتصال SqlDependency با دیتابیس می باشد:

SqlDependency.Start(ConnectionString);

متد زیر برای اتصال شیء SqlDependency به یک SqlCommand استفاده می شود:

/// <summary>
/// برای اتصال یک شیء SqlCommand به SqlDependency
/// </summary>
private void RegisterSqlDependency()
{
	// بررسی دسترسی کاربر
	if (!DoesUserHavePermission())
		return;

	// بایند کردن command با شیء SqlDependency
	_command.Notification = null;
	_dependency = new System.Data.SqlClient.SqlDependency(_command);

	// ایجاد یک handler برای اعمال تغییرات بر روی دیتا گرید
	_dependency.OnChange += CustomersDependencyOnChange;

	// باز کردن اتصال دیتابیس در صورت نیاز
	if (_connection.State != ConnectionState.Open)
		_connection.Open();

	// اجرای Command با یکی از متدهای Execute موجود در شیء SqlCommand
	_command.ExecuteNonQuery();

	// بستن اتصال بعد از اجرای دستور
	_connection.Close();
}

متد زیر برای بررسی دسترسی برنامه استفاده می شود:

/// <summary>
/// برای بررسی دسترسی کاربر استفاده می شود
/// </summary>
/// <returns></returns>
private static bool DoesUserHavePermission()
{
	try
	{
		var clientPermission = new SqlClientPermission(PermissionState.Unrestricted);
		clientPermission.Demand();
		return true;
	}
	catch
	{
		return false;
	}
}

این رویداد زمانی که تغییری در داده های موجود بوجود آید، فراخوانی می شود:

/// <summary>
/// ایجاد یک handler برای رویداد OnChange شیء SqlDependency
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void CustomersDependencyOnChange(object sender, SqlNotificationEventArgs e)
{
	var dependency = (System.Data.SqlClient.SqlDependency)sender;
	dependency.OnChange -= CustomersDependencyOnChange;
	Dispatcher.BeginInvoke(DispatcherPriority.DataBind, new Action(BindCustomers));
	RegisterSqlDependency();
}

دستور زیر برای قطع اتصال SqlDependency با دیتابیس می باشد:

SqlDependency.Stop(ConnectionString);

استفاده از SqlDependency در سی شارپ :

برای استفاده از SQL Dependency در WinForms به زبان سی شارپ از  کدهای گفته شده در بخش بالای می توانید استفاده کنید فقط باید محتوای رویداد OnChange را به شکل زیر تغییر دهید:

/// <summary>
/// ایجاد یک handler برای رویداد OnChange شیء SqlDependency
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void CustomersDependencyOnChange(object sender, SqlNotificationEventArgs e)
{
   var dependency = (SqlDependency)sender;
   dependency.OnChange -= _dependency_OnChange;
   Invoke(new Action(BindCustomers));
   RegisterSqlDependency();
}

نوشته آموزش SqlDependency در WPF (سی شارپ) برای آپدیت خودکار داده های جدول اولین بار در سورس سرا - آموزش برنامه نویسی. پدیدار شد.