13

I've got a table and a SqlDependency that is waiting for new inserts.

OnChange fires as I need, but I don't understand if it's possible to get the row which cause the databse change.

SqlDependency sql command: 

SqlCommand cmd = new SqlCommand("SELECT id FROM dbo.DataRequests", m_sqlConn);

OnChange code:

private void OnChange(object sender, SqlNotificationEventArgs e)
{
    SqlDependency dependency = sender as SqlDependency;

    dependency.OnChange -= OnChange;

    Console.WriteLine("Info:   " + e.Info.ToString());
    Console.WriteLine("Source: " + e.Source.ToString());
    Console.WriteLine("Type:   " + e.Type.ToString());


    Console.WriteLine(DateTime.Now);

    GetMessages();

}
kyrylomyr
  • 12,192
  • 8
  • 52
  • 79
Philipp
  • 355
  • 2
  • 4
  • 11
  • Your question was helpful for finding answer to my question that is how to find source and information of events!! – IT researcher Aug 31 '13 at 14:10
  • Since you are defining the SQL command that detects changes, structure it so that you know what changed. for example `SqlCommand cmd = new SqlCommand("SELECT id FROM dbo.DataRequests where changeAcknowledged = 0 ", m_sqlConn);` granted you may not have this level of control over the database. – Dave Kelly Jul 16 '19 at 15:45

7 Answers7

11

No information is available about the rows that caused the dependency to be fired.

I guess as a workaround you could always put a timestamp on your records and track when the event was last fired.

Rob West
  • 5,189
  • 1
  • 27
  • 42
  • How if I create a timepstamp on my record and then send a filter on timestamp with my SQL string to register a dependency? So that this way only the records that inserted after a specified time would be returned. – Khadim Ali Aug 14 '15 at 18:26
9

Take a look at this component: SqlTableDependency

For every change done on a SQL Server database table, the C# code receive an event containing a list of RECORDs changed.

Prashant Kumar
  • 25
  • 1
  • 1
  • 8
Christian Del Bianco
  • 1,008
  • 13
  • 13
3

Find a very ingenious solution here

Izmoto
  • 1,939
  • 2
  • 17
  • 21
2

According to this post, you can't: http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/07234067-73e1-4db5-a4e6-0f9f0bae22ae/

You can only narrow down the reason for the notification by using the properties

of the provided SqlNotificationEventArgs

Andre Loker
  • 8,368
  • 1
  • 23
  • 36
0

Just use a cross-platform .NET 3.5 compatible and open-source SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. This is a usage example:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

You can get change notifications as well as information which was changed. Please follow the link for details.

dyatchenko
  • 2,283
  • 3
  • 22
  • 32
-1

I hope this helps you:

     string commandString = string.Format("SELECT [Id] FROM [dbo].[Tech]");
     command = new SqlCommand(commandString, connection);

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        SqlDependency dependency = (SqlDependency)sender;
        dependency.OnChange -= dependency_OnChange;

        this.Dispatcher.Invoke((System.Action)(() =>
        {

            if (e.Info.ToString().ToLower().Trim() == "insert")
            {
                GetData();
                int NewTechID = TechIDs.Last();
            }

        }));
    }

    private void GetData()
    {
        command.Notification = null;
        SqlDependency dependency = new SqlDependency(command);
        dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

        command.Connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                TechIDs.add(int.Parse(reader.GetValue(0).ToString()));
            }
            reader.Close();
        }
        command.Connection.Close();
    }
Masoumeh Karvar
  • 791
  • 1
  • 14
  • 32
-1

You can use Temp tables. first of all you need to create a temp table with all the fields you need to keep under investigation. something like:

CREATE TABLE ##TempTab(
    [field1] [varchar](50) NULL,
    [field2] [varchar](50) NULL
}

Please note that kind of tables created within external cose are automatically dropped since the creator program quits so you don't need to drop it on formClosing... Now, after setting up sqlDepency stuffs you have to fill up you temp table, it's something like a snapshot of the starting scenario. Then, every time the onChange event is fired you just need to compare your temp table with updated situation. it could be something like:

    select * from ##temptable left outer join mytable
ON ##temptable.field1=myTable.field1 AND ##temptable.field2=myTable.field2
WHERE myTable.field2 is null

this will give you all rows has just been deleted (or chagend with old values). On the other side:

select * from mytable left outer join ##temptable
    ON ##temptable.field1=myTable.field1 AND ##temptable.field2=myTable.field2
    WHERE ##temptable.field2 is null

will give you all rows has just been added (or changed with new values). After this compare you just need to update your temp table with new values (faster way is to delete everything and insert all values) Of course, if your programm will be run simultaneously by different users, you'll need to handle userid within temp table.

Daniele Borini
  • 1