Reputation: 383

Getting data from SqlDependency

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

}

Upvotes: 13

Answers (7)

dyatchenko

Reputation: 2343

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.

Upvotes: 0

Christian Del Bianco

Reputation: 1043

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.

Upvotes: 9

Izmoto

Reputation: 1959

Find a very ingenious solution here

Upvotes: 3

Daniele Borini

Reputation: 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.

Upvotes: -1

Masoumeh Karvar

Reputation: 801

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

Upvotes: -1