How to monitor SQL Server table changes by using c#?_问答_开发者

I have more than one application accessing the same DB and I need to get noti开发者_JAVA百科fied if one of these apps change anything (update, insert) in a certain table.

Database and apps are not in the same server.

In the interests of completeness there are a couple of other solutions which (in my opinion) are more orthodox than solutions relying on the SqlDependency (and SqlTableDependency) classes. SqlDependency was originally designed to make refreshing distributed webserver caches easier, and so was built to a different set of requirements than if it were designed as an event producer.

There are broadly four options, some of which have not been covered here already:

Change Tracking
CDC
Triggers to queues
CLR

Change tracking

Source: https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server

Change tracking is a lightweight notification mechanism in SQL server. Basically, a database-wide version number is incremented with every change to any data. The version number is then written to the change tracking tables with a bit mask including the names of the columns which were changed. Note, the actual change is not persisted. The notification only contains the information that a particular data entity has changed. Further, because the change table versioning is cumulative, change notifications on individual items are not preserved and are overwritten by newer notifications. This means that if an entity changes twice, change tracking will only know about the most recent change.

In order to capture these changes in c#, polling must be used. The change tracking tables can be polled and each change inspected to see if is of interest. If it is of interest, it is necessary to then go directly to the data to retrieve the current state.

Change Data Capture

Source: https://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

Change data capture (CDC) is more powerful but most costly than change tracking. Change data capture will track and notify changes based on monitoring the database log. Because of this CDC has access to the actual data which has been changed, and keeps a record of all individual changes.

Similarly to change tracking, in order to capture these changes in c#, polling must be used. However, in the case of CDC, the polled information will contain the change details, so it's not strictly necessary to go back to the data itself.

Triggers to queues - this method is obsolete

This technique depends on triggers on the tables from which notifications are required. Each change will fire a trigger, and the trigger will write this information to a service broker queue. The queue can then be connected to via C# using the Service Broker Message Processor (sample in the link above).

Unlike change tracking or CDC, triggers to queues do not rely on polling and thereby provides realtime eventing.

CLR

This is a technique I have seen used, but I would not recommend it. Any solution which relies on the CLR to communicate externally is a hack at best. The CLR was designed to make writing complex data processing code easier by leveraging C#. It was not designed to wire in external dependencies like messaging libraries. Furthermore, CLR bound operations can break in clustered environments in unpredictable ways.

This said, it is fairly straightforward to set up, as all you need to do is register the messaging assembly with CLR and then you can call away using triggers or SQL jobs.

In summary...

It has always been a source of amazement to me that Microsoft has steadfastly refused to address this problem space. Eventing from database to code should be a built-in feature of the database product. Considering that Oracle Advanced Queuing combined with the ODP.net MessageAvailable event provided reliable database eventing to C# more than 10 years ago, this is woeful from MS.

The upshot of this is that none of the solutions listed to this question are very nice. They all have technical drawbacks and have a significant setup cost. Microsoft if you're listening, please sort out this sorry state of affairs.

You can use the SqlDependency Class. Its intended use is mostly for ASP.NET pages (low number of client notifications).

ALTER DATABASE UrDb SET ENABLE_BROKER

Implement the OnChange event to get notified:

void OnChange(object sender, SqlNotificationEventArgs e)

And in code:

SqlCommand cmd = ...
cmd.Notification = null;

SqlDependency dependency = new SqlDependency(cmd);

dependency.OnChange += OnChange;

It uses the Service Broker (a message-based communication platform) to receive messages from the database engine.

Generally, you'd use Service Broker

That is trigger -> queue -> application(s)

Edit, after seeing other answers:

FYI: "Query Notifications" is built on Service broker

Edit2:

More links

Event Notification/Service broker
Service Broker team

Use SqlTableDependency. It is a c# component raising events when a record is changes. You can find others detail at: https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency

It is similat to .NET SqlDependency except that SqlTableDependency raise events containing modified / deleted or updated database table values:

string conString = "data source=.;initial catalog=myDB;integrated security=True";

using(var tableDependency = new SqlTableDependency<Customers>(conString))
{
    tableDependency.OnChanged += TableDependency_Changed;
    tableDependency.Start();

    Console.WriteLine("Waiting for receiving notifications...");
    Console.WriteLine("Press a key to stop");
    Console.ReadKey();
}
...
...
void TableDependency_Changed(object sender, RecordChangedEventArgs<Customers> e)
{
    if (e.ChangeType != ChangeType.None)
    {
        var changedEntity = e.Entity;
        Console.WriteLine("DML operation: " + e.ChangeType);
        Console.WriteLine("ID: " + changedEntity.Id);
        Console.WriteLine("Name: " + changedEntity.Name);
        Console.WriteLine("Surname: " + changedEntity.Surname);
    }
}

SqlDependency doesn't watch the database it watches the SqlCommand you specify so if you are trying to lets say insert values into the database in 1 project and capture that event in another project it won't work because the event was from the SqlCommand from the 1º project not the database because when you create an SqlDependency you link it to a SqlCommand and only when that command from that project is used does it create a Change event.

Be careful using SqlDependency class - it has problems with memory leaks.

Just use a cross-platform, .NET 3.5, .NET Core compatible and open source solution - SqlDependencyEx. You can get notifications as well as data that was changed (you can access it through properties in notification event object). You can also tack DELETE\UPDATE\INSERT operations separately or together.

Here is an example of how easy it is to use SqlDependencyEx:

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

Please follow the links for details. This component was tested in many enterprise-level applications and proven to be reliable. Hope this helps.

Since SQL Server 2005 you have the option of using Query Notifications, which can be leveraged by ADO.NET see http://msdn.microsoft.com/en-us/library/t9x04ed2.aspx

looks like bad architecture all the way. also you have not specified the type of app you need to notify to (web app / console app / winforms / service etc etc)

nevertheless, to answer your question, there are multiple ways of solving this. you could use:

1) timestamps if you were just interested in ensuring the next set of updates from the second app dont conflict with the updates from the first app

2) sql dependency object - see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx for more info

3) a custom push notification service which multiple clients (web / winform / service) can subscribe to and get notified on changes

in short, you need to use the simplest and easiest and cheapest (in terms of efforts) solution based on how complex your notification requirements are and for what purpose you need to use them. dont try to build an overly complex notification system if a simple data concurrency is your only requirement (in that case go for a simple timestamp based solution)

Another, very simple way of monitoring tables is table versioning. The system is proven working in constructions such as DNS synchronization. To make it work you create a table containing table names and table versions as decimal or bigint.In each table that you need monitored, create trigger on insert, update and delete that will increment appropriate table version in versioning table when executed. If you expect any of the monitored tables to be altered often, you need to provision for version reusing. Finally, in your application, every time you query monitored table, you also query its version and store it. When you go to alter the monitored table from your app, you first query its current version and process the change only if the version is unchanged. You can have stored proc on sql server do that work for you. This is extremely simple but proven solid solution. It has specific functional use (to ensure data consistency) and is light on resources (you do not raise brokered events that you would not watch for) but needs application to actively check for changes rather than passively wait for event to happen.

This isn't exactly a notification but in the title you say monitor and this can fit that scenario.

Using the SQL Server timestamp column can allow you to easily see any changes (that still persist) between queries.

The SQL Server timestamp column type is badly named in my opinion as it is not related to time at all, it's a database wide value that auto increments on any insert or update. You can select Max(timestamp) in a table you are after or return the timestamp from the row you just inserted then just select where timestamp > storedTimestamp, this will give you all the results that have been updated or inserted between those times.

As it's a database wide value too you can use your stored timestamp to check any table has had data written to it since you last checked/updated your stored timestamp.

1-create new Database to name of TestNotification

2-add new Table to name of Customers fields: Id , Name , Family

3-you should enable ServiceBroker

4-run this code in sql

ALTER DATABASE [TestNotification] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

5-creat new project c# consoleApp

6- install SqlTableDependency library in nuget

7- create class to name of Customer

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Family { get; set; }
}

8- in Program.cs write this code

        static void Main(string[] args)
        {
            var connectionString = "data source=.;initial catalog=TestNotification;integrated security=true;";
            using (var tableDependecy = new SqlTableDependency<Customer>(connectionString, "Customers"))
            {
                tableDependecy.OnChanged += TableDependency_Changed;
                tableDependecy.OnError += TableDependency_OnError;

                tableDependecy.Start();

                Console.WriteLine("Waiting");

                Console.ReadKey();
                tableDependecy.Stop();
            }
        }

        static void TableDependency_Changed(object sender, RecordChangedEventArgs<Customer> e)
        {
            Console.WriteLine(Environment.NewLine);
            if (e.ChangeType != ChangeType.None)
            {
                var changeEntity = e.Entity;
                Console.WriteLine("ChangeType: " + e.ChangeType);
                Console.WriteLine("Id: " + changeEntity.Id);
                Console.WriteLine("Name: " + changeEntity.Name);
                Console.WriteLine("Id: " + changeEntity.Family);
                Console.WriteLine(Environment.NewLine);
            }
        }

        static void TableDependency_OnError(object sender, ErrorEventArgs e)
        {
            Console.WriteLine(e.Message);
        }