C
C
Collin2018-06-28 09:28:52
SQL
Collin, 2018-06-28 09:28:52

How to track changes in MS SQL 2008 DB in C# through Service Broker?

Greetings. I can't figure out how to track changes in the database.
Available:

  • Application written in C#
  • SQL Server, with a 3-table database
I do the following in the program:
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, заполняя грид.
}

I wrote a piece for SqlDependency according to this example
In the database itself I did But ... for some reason, by RMB->Properties->Change Tracking in the Change Tracking field hung False. Well, manually switched to True. I start the application, change something in the database via SQL SMS and... nothing happens. I checked with a breakpoint, the method is not even called by the event. I had a number of questions in the course of studying the materiel. 1) Queue. How to find out the name of my queue by default. How to configure it? Because, as I understand it, I do not have a completely complete code for SqlDependency, because required but queueName I don't know. 2) I only need to know if there have been changes or not. Which ones specifically, it doesn’t matter to me, and it’s not clear why this piece hangs in the example:ALTER DATABASE [Database_name] SET ENABLE_BROKER;
SqlDependency.Start(connectionString, queueName);
// Execute the command.  
        using (SqlDataReader reader = command.ExecuteReader())  
        {  
            // Process the DataReader.  
        }

More precisely, it’s clear that, in theory, according to the correct idea, I should pull up those same changes from the Queue and work with them further, for example, fill a cell in the grid with them, and not refill the entire grid (but I have a small one and I don’t particularly I lose from Fill), but, probably, this is something you need, since it doesn’t work for me?
3) How can I check in SQL SMS if Service Broker is working at all? I read about messages in the database that Service Broker supposedly should respond to, but I didn’t deduct anything specific (what it should answer, in what form, where, how to look at this packet in this case).
I understand that the questions are extremely stupid, and I hope for your patience.
Thanks for any help and clarification on my issue.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question