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

سه شنبه ۶ آذر ۱۳۹۷
بازدید ۲,۵۰۱ نفر
سعید سیفی زاده
(88 امتیاز از 18 رای)
Loading...

تصویر sqldependency-training-in-wpf-csharp_4845 آموزش 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;

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 گزینه های زیر به بخش دیتابیس شما اضافه خواهد شد.

تصویر sqldependency-training-in-csharp_4845_3 آموزش SqlDependency در WPF (سی شارپ) برای آپدیت خودکار داده های جدول

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

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

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

تصویر sqldependency-training-in-csharp_4845_1 آموزش SqlDependency در WPF (سی شارپ) برای آپدیت خودکار داده های جدول

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

تصویر sqldependency-training-in-csharp_4845_2 آموزش SqlDependency در WPF (سی شارپ) برای آپدیت خودکار داده های جدول

محتوای فایل 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>

<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>

</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
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

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);

1	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>

/// برای اتصال یک شیء 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>

/// برای بررسی دسترسی کاربر استفاده می شود

/// </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();
}

/// <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);

1	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();
}

/// <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();

}

نکته! قبل از نام جدول در کوئری خود، حتما باید از dbo (برای مثال dbo.Customers) استفاده کنید. در غیر این صورت رویداد OnChange به صورت بی نهایت اجرا می شود و این موضوع باعث قفل شدن UI برنامه خواهد شد.