Использование класса SqlDependency — это хороший способ повысить эффективность вашего приложения, управляемого данными (будь то веб-форма или Windows Forms), устраняя необходимость в постоянном повторном запросе базы данных для проверки изменений данных.
В целях этой статьи мы будем обсуждать использование SqlDependency с SQL 2005, но я уверен, что он работает так же или очень похож на SQL 2008. Использование SqlDependency для отслеживания измененных данных основывается на двух аспектах из SQL Server, Service Broker. , Компонент Service Broker позволяет отправлять асинхронные сообщения в вашу базу данных и из нее.
Второй пункт это опирается на очередиваш Service Broker будет использовать вашу очередь для создания слабой связи между отправителем и получателем. Отправитель может отправить свое сообщение в очередь и продолжить работу и полагается на компонент Service Broker, чтобы убедиться, что любое сообщение, помещенное в очередь, обрабатывается и ответ отправляется обратно отправителю (вашему приложению).
Для этой статьи я использую образец базы данных, которую я создал с именем SqlDependencyExample, и таблица с именем Employee whick имеет следующую структуру:
CREATE TABLE [dbo].[EmployeeList]( [EmployeeID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](25) NOT NULL, [LastName] [varchar](25) NOT NULL, [PhoneNumber] [varchar](13) NOT NULL) ON [PRIMARY]
Мы будем использовать хранимую процедуру для запросов, но это также можно сделать с помощью обычного текстового запроса в вашем коде. Вот хранимая процедура
CREATE PROCEDURE uspGetEmployeeInformationASBEGIN -- Insert statements for procedure here SELECT EmployeeID, FirstName, LastName, PhoneNumber FROM dbo.EmployeeListEND
ПРИМЕЧАНИЕ: обратите внимание, что в хранимой процедуре у нас есть имя dbo в имени таблицы, при использовании зависимости для отслеживания изменений данных у вас должна быть таблица в формате [владелец]. [Имя таблицы] ее может привести к нежелательным результатам, поэтому просто избегайте используйте указанный формат.
Первое, что нам нужно сделать, это создать вашего Service Broker и очередь, чтобы мы могли отправлять сообщения назад и вперед между нашей базой данных. В моем примере у меня есть простая таблица, которая содержит имена сотрудников и номера телефонов. Вот как мы создаем компонент Service Broker & Queue (имя моей базы данных — SqlDependencyExample, но меняем его вместе с именем вашей базы данных (нам также нужно дать нашему пользователю SQL разрешение на доступ к нему)
USING [SqlDependencyExample]CREATE QUEUE NewEmployeeAddedQueue;CREATE SERVICE NewEmployeeAddedService ON QUEUE NewEmployeeAddedQueue([http://schemas.microsoft.com/sql/notifications/postquerynotification]); GRANT SUBSCRIBE QUERY NOTIFICATIONS TO SqlDependencyExampleUser;
Теперь мы переходим к интересной части, коду (я знаю, это то, что вы ждете). Прежде чем это сможет работать, нам нужно проверить и убедиться, что у подключенного пользователя есть соответствующие разрешения для уведомлений. Мы можем сделать это с помощью создания простого метода CheckUserPermissions, который использует класс SqlClientPermissions для проверки прав доступа текущего подключенного пользователя. Итак, вот простой метод для достижения этой цели:
private bool CheckUserPermissions(){ try { SqlClientPermission permissions = new SqlClientPermission(PermissionState.Unrestricted); //if we cann Demand() it will throw an exception if the current user //doesnt have the proper permissions permissions.Demand(); return true; } catch { return false; }}
Следует знать одну вещь: SqlDependency полагается на делегат OnChangeEventHandler, который обрабатывает событие SqlDependency.OnChange , которое запускается при получении любого уведомления любой из команд для объекта SqlDependency.
Теперь для получения списка сотрудников, в этом методе мы запросим нашу таблицу EmployeeList, чтобы получить информацию о сотрудниках. Мы также установим событие OnChange нашего объекта SqlDependency, чтобы оно сообщало нам об изменении данных в нашей таблице и повторно заполняло их последним списком сотрудников. Прежде чем пытаться получить доступ к нашей базе данных, мы вызовем метод CheckUserPermissions, который мы создали ранее, чтобы убедиться, что текущий пользователь имеет соответствующие разрешения, если мы не отображаем сообщение, в противном случае мы переходим к получению списка сотрудников и заполнению ListView идентификатором, имя и фамилия сотрудника и его номер телефона.
Вот метод GetEmployeeList , который ожидает параметр типа ListView (который будет отображать наш список сотрудников)
/// <summary>/// method for querying our database to get an employee list/// </summary>/// <param name="lview">the ListView we want to display the employee list in</param>private void GetEmployeeList(ListView lview){ //the connection string to your database string connString = "YourConnectionString"; //the name of our stored procedure string proc = "uspGetEmployeeInformation"; //first we need to check that the current user has the proper permissions, //otherwise display the error if (!CheckUserPermissions()) MessageBox.Show("An error has occurred when checking permissions"); //clear our ListView so the data isnt doubled up lview.Items.Clear(); //in case we have dependency running we need to go a head and stop it, then //restart it SqlDependency.Stop(connString); SqlDependency.Start(connString); using (SqlConnection sqlConn = new SqlConnection(connString)) { using (SqlCommand sqlCmd = new SqlCommand()) { sqlCmd.Connection = sqlConn; sqlCmd.Connection.Open(); //tell our command object what to execute sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.CommandText = proc; sqlCmd.Notification = null; SqlDependency dependency = new SqlDependency(sqlCmd); dependency.OnChange += new OnChangeEventHandler(dependency_OnDataChangedDelegate); sqlConn.Open(); using (SqlDataReader reader = sqlCmd.ExecuteReader()) { while (reader.Read()) { ListViewItem lv = new ListViewItem(); lv.Text = reader.GetInt32(0).ToString(); lv.SubItems.Add(reader.GetString(1)); lv.SubItems.Add(reader.GetString(2)); lv.SubItems.Add(reader.GetString(3)); lview.Items.Add(lv); } } } }}
Обратите внимание, что мы устанавливаем OnChangeEventHandler нашего объекта SqlDependency в dependency_OnDataChangedDelegate , это метод, который будет повторно запрашивать нашу таблицу и отправлять уведомления, когда данные изменились в нашей таблице EmployeeList. В этом методе мы вызываем работу в главном потоке пользовательского интерфейса, это поможет нам избежать страшного межпотокового исключения, когда мы собираемся повторно заполнить элемент управления ListView, когда любые уведомления отправляются в наше приложение.
Поскольку нашему методу (GetEmployeeList) требовался параметр, мы не можем использовать стандартный делегат MethodInvoker (поскольку он не может принимать параметры). Итак, что мы сделаем, это создадим нашего собственного Делегата, который сможет принять наш параметр. Вот делегат (очень просто):
private delegate void getEmployeeListDelegate(ListView lv);
Нашу зависимость — OnDataChangedDelegate требует SqlNotificationEventArgs в подписи. Здесь мы проверяем используемый элемент управления, чтобы убедиться, что InvokeRequired имеет значение true; если нет, то мы используем Invoke для вызова работы в основном потоке пользовательского интерфейса для повторного заполнения нашего ListView, в противном случае мы просто вызываем наш метод для повторного запроса:
private void dependency_OnDataChangedDelegate(object sender, SqlNotificationEventArgs e){ //avoid a cross-thread exception (since this will run asynchronously) //we will invoke it onto the main UI thread if (listView1.InvokeRequired) listView1.Invoke(new getEmployeeListDelegate(GetEmployeeList), listView1); else GetEmployeeList(listView1); //this example is for only a single notification so now we remove the //event handler from the dependency object SqlDependency dependency = sender as SqlDependency; dependency.OnChange -= new OnChangeEventHandler(dependency_OnDataChangedDelegate);}
ПРИМЕЧАНИЕ. Следует помнить одну вещь: вам нужно запретить зависимостям запрашивать базу данных, когда ваша форма закрыта, чтобы сделать это, используйте событие FormClosing для остановки работы в форме, в которой вы используете работу с зависимостями.
Вот как вы используете класс SqlDependency для мониторинга изменений данных в вашей базе данных без необходимости использовать что-то вроде элемента управления таймером для повторного запроса через определенные промежутки времени. Спасибо за чтение и счастливое кодирование 🙂