.Net SQL Server Database Monitoring - Insert, Update, Delete

Question

Does anyone know a way to track changes to a table entry in a SQL Server database (2005 or 2008) from a .Net application? It must support multiple clients at the same time. Each client will "subscribe" when it starts and "unsubscribe" when it leaves. Several users can immediately access the system, and I want to reflect their changes on another user client. Then, when the client processes the change event, it can update the local object representing this record. A view similar to how changes to update records that have been changed are reflected in each form that references it.

I know that microsoft has Microsoft.SqlServer libraries for interacting with SQL Server. But I'm not sure which concept refers to what I want to do (or what can be bent to apply to what I want to do). Those that sound like they can be useful are managerial or replicative.

In anticipation of someone asking, “Why don't you just demand that the desk sometimes look for new information?” I have a large number of tables that I want to control, and this will be a pain in the butt. Plus, if I'm looking for something more elegant.

I am open to suggestions ...

Answer 1

To track changes to an SQL table or record in SQL 2005+, you can use the SqlDependency class.

It is intended for use in ASP.NET or a mid-tier service where one server manages active subscriptions through a database, rather than several hundred clients managing subscriptions. Depending on the maximum number of clients you are accessing, you can create a pooling service that can manage anti-SQL notification subscriptions for your clients.

Internally, it uses the SqlNotificationRequest, which uses Service Broker, not Notification Services. Thus, this should continue in SQL Server 2008, etc.

MSDN for SqlDependency

void Initialization() { // Create a dependency connection. SqlDependency.Start(connectionString, queueName); } void SomeMethod() { // Assume connection is an open SqlConnection. // Create a new SqlCommand object. SqlCommand command=new SqlCommand( "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", connection); // Create a dependency and associate it with the SqlCommand. SqlDependency dependency=new SqlDependency(command); // Maintain the refence in a class member. // Subscribe to the SqlDependency event. dependency.OnChange+=new OnChangeEventHandler(OnDependencyChange); // Execute the command. command.ExecuteReader(); // Process the DataReader. } // Handler method void OnDependencyChange(object sender, SqlNotificationsEventArgs e ) { // Handle the event (for example, invalidate this cache entry). } void Termination() { // Release the dependency. SqlDependency.Stop(connectionString, queueName); } 

" Using and monitoring SQL Query Notifications ", which describes the steps to configure the code (using an example web application) along with the appropriate SQL permissions that are required to subscribe to Service Broker, etc.

the ASP.NET class (web caching) is also available for web scripting.

Answer 2

In SQL Server 2008, you have change tracking that you can track when an insert, update, or delete occurs. This will track that an event has occurred that is recorded and, possibly, which columns were changed, but not the values ​​(before and after). This is typically used to perform bulk processes with known modified data.

In change tracking, you set a retention period for storing changes, usually a period that ensures that any processing of the tracked tables is within the retention period.

In addition, you have (again, in SQL 2008) Change Data Capture, which is more verbose and records the actual changes. This, however, is only a function of the enterprise.

You can easily create a .NET application to run change tracking requests or change data .

Here is a link to more information on how

Change Tracking

Change data collection

For the most part, change tracking is likely to be the best option - plus it works on all versions of SQL Server 2008.

If you want to achieve this in 2005 or lower, you can basically create tables for each table that you want to track, and have a trigger to insert values ​​into the tracking table when you insert, update, delete. Then try to clear this. This is mainly change tracking.

Answer 3

I haven’t used it personally, but Notification Services seems to be worth a look.

More details

Answer 4

This script usually does not scale very well ... you can look at SQL notification services, but it has the same problems.

On one system, I do this by having triggers that write a brief summary of the changes to the (single) delta table, including the table, type of change, and row id. Each client supports "where I read", and simply asks for all delta records with id> this number. The problem is that you need to do some smart things to clear the table without going through any individual clients that change history ... that is, track exits, assume they are dead for a certain time, and log when they are fully updated .

Definitely doable, but a lot of work.

Answer 5

I think I misunderstood the question.

It looks like this is a standard multi-user lock control application. the user registers, then accesses the data on the server (subscribes). The user disconnects (unsubscribes).

Or is it a disabled application in which you want to synchronize changes?

For disabled applications, you can use syncservices for SQL Server.

Other than that, just use LINQ to Entities, and you can handle conflicts while saving changes to the object. To ensure that LINQ queries are executed, you can add a TIMESTAMP column to each table, but this is not required.