I can not figure out the issue of tracking changes in the database.

There is:

  • Application written in C #
  • SQL Server, with a database of 3 labels

In the program I do the following:

public Main_Form() { InitializeComponent(); SqlDependency.Stop(connectionString); SqlDependency.Start(connectionString); ScoutingSQL(); DataGridView_Load(); } // создаем dependency и подписываем его на событие // которое должно вызвать Service Broker private void ScoutingSQL() { SqlConnection connection = new SqlConnection(connectionString); connection.Open(); SqlCommand command = new SqlCommand("SELECT sentDate, verificationDate, verifiedTo", connection); SqlDependecy dependency = new SqlDependency(command); dependency.OnChange += new OnChangeEventHandler(OnDependencyChange); } // метод, вызванный событием void OnDependencyChange(object sender, SqlNotificationEventArgs e) { DataGridView_Load(); } // Загружаем данные из sql бд в наш грид public void DataGridView_Load() { while(dataGridView1.Rows.Count > 0) for (int i = 0; i < dataGridView1.Rows[i]); { dataGridView1.Rows.Remove(dataGridView1.Rows[i]); } string tableName = "[staff_106].[dbo].[staff]" string querry = ("SELECT * FROM" + tableName + ""); SqlConnection connection = new SqlConnection(connectionString); SqlDataAdapter dataAdapter = new SqlDataAdapter(querry, connection); // и тд... просто делаем Fill из dataSet, заполняя грид. }

The piece for SqlDependency wrote on this example

In the ALTER DATABASE [Database_name] SET ENABLE_BROKER; itself did ALTER DATABASE [Database_name] SET ENABLE_BROKER;

But ... for some reason, by PCM-> Properties-> Tracking changes in the Change tracking field hung False. Well, handles switched to true.

I start the application, change something in the database via SQL SMS and ... nothing happens. Checked by breakpoint, the event does not even call the method.

I had a number of questions during the study of the materiel.

1) Queue. How to find out the name of my default queue. How to configure it? Because, as I understand it, I have not quite the full code for SqlDependency, since required

 SqlDependency.Start(connectionString, queueName);

but queueName I don't know.

2) I just need to find out if there have been changes or not. What specifically, it does not matter to me, and it is not clear why this piece hangs in the example:

 // Execute the command. using (SqlDataReader reader = command.ExecuteReader()) { // Process the DataReader. }

More precisely, it is clear that in theory the correct way is to tighten those changes from Queue and work with them further, for example, fill the cell in the grid with them, and not re-fill the entire grid (but I have a small one and especially I don’t I lose from Fill), but, probably, it is something necessary, since I do not have a good time?

3) How in SQL SMS to check if Service Broker is working at all? I read about the messages in the database, which Service Broker supposedly had to answer, but I didn’t read anything concrete (what it should answer, in what form, where, how to look at this package in this case).

I understand that the questions are extremely stupid, and I hope for your patience. Thank you for any help and clarification on my question.

    1 answer 1

    Worn out in due time with the service broker, this code works (naturally, on the database side, everything must also be properly configured):

     public class ServiceBrokerWatcher { public ServiceBrokerWatcher(string conn, string query) { dependencyQuery = query; connectionString = conn; StartWatching(conn); ExecuteWatchingQuery(); } private readonly string dependencyQuery; private readonly string connectionString; public event EventHandler ContextChangedEvent; private SqlDependency sqlDependency; public static void StartWatching(string connectionString) { SqlDependency.Stop(connectionString); SqlDependency.Start(connectionString); } private void ExecuteWatchingQuery() { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand(dependencyQuery, connection)) { sqlDependency = new SqlDependency(command); sqlDependency.OnChange += OnDatabaseChange; command.Execute(); } } } private void OnDatabaseChange(object sender, SqlNotificationEventArgs args) { try { if (sqlDependency != null) { sqlDependency.OnChange -= OnDatabaseChange; } ExecuteWatchingQuery(); SqlNotificationInfo info = args.Info; if (SqlNotificationInfo.Insert.Equals(info) || SqlNotificationInfo.Update.Equals(info) || SqlNotificationInfo.Delete.Equals(info)) { AppLogger.Logger.Debug("Обновление данных!"); if (ContextChangedEvent != null) { ContextChangedEvent(this, new EventArgs()); } } } catch (Exception ex) { Logger.AppLogger.Logger.Error(ex.Message, ex.StackTrace); } } }

    Use something like this:

      internal void ServiceBrokerWatcherInit() { updateWatcher = new ServiceBrokerWatcher(MainForm.DBConnectString, @"select id from dbo.ProductHistory"); updateWatcher.ContextChangedEvent += updateWatcher_ContextChangedEvent; }
    • Thanks for the answer! And what, except for the inclusion of a broker, should be configured on the server side? - Alexandr
    • Here is described the inclusion of a broker on the side of the database blogs.msdn.microsoft.com/alexejs/2009/08/15/183 And by the code, its logic is as follows, we follow the dbo.ProductHistory table - as soon as a record appears in it, the service broker It sends an alert, it is received by the C # code, it jerks the event, and in its handler you can do anything. - Nova
    • read the article, thanks for your help! - Alexandr