检测SQL数据库更改 [英] Detect SQL database changes

问题描述

请考虑以下示例:

INSERT INTO [Table] (column1)
SELECT value1

如果我要在SSMS中执行此命令，那么对于c#表单应用程序，我需要做些什么才能识别此事件?发生此事件时，与显示MessageBox的应用程序一样简单.我似乎无法解决这个问题，也找不到任何有用的数据.我尝试使用SqlDependency，但没有任何运气.如果那是我需要走的路，有人可以帮助我更好地理解这个概念吗?

If I were to execute this command in SSMS, in regards to a c# forms application, what would I need to do in order to recognize this event? Something as simple as the application displaying a MessageBox when this event occurs. I just can't seem to work this one out or find any helpful data on it. I have attempted to use SqlDependency but am not having any luck. If that is the path I need to go down, can anyone help me out with understanding the concept a little better?

推荐答案

如果您想检测更改而不仅仅是插入，则可以使用SQL依赖性.您已阅读并尝试了链接中的示例吗?

If you want to detect changes and not just inserts you could achieve this using SQL Dependency. Have you read and tried the example in the link?

这里有一个不错的教程/示例"并向您介绍基础知识.

这里是查询通知的很好概述.

• SqlNotificationRequest类提供了底层实现，该类公开了服务器端的功能，使您可以执行带有通知请求的命令.

• The low-level implementation is provided by the SqlNotificationRequest class that exposes server-side functionality, enabling you to execute a command with a notification request.

高级实现由SqlDependency类提供，该类提供了源应用程序和SQL Server之间的通知功能的高级抽象，使您能够使用依赖项来检测其中的更改.服务器.在大多数情况下，这是使用用于SQL Server的.NET Framework数据提供程序通过托管客户端应用程序利用SQL Server通知功能的最简单，最有效的方法.

The high-level implementation is provided by the SqlDependency class, which is a class that provides a high-level abstraction of notification functionality between the source application and SQL Server, enabling you to use a dependency to detect changes in the server. In most cases, this is the simplest and most effective way to leverage SQL Server notifications capability by managed client applications using the .NET Framework Data Provider for SQL Server.

此外，使用ASP.NET 2.0或更高版本构建的Web应用程序可以使用SqlCacheDependency帮助器类.

In addition, Web applications built using ASP.NET 2.0 or later can use the SqlCacheDependency helper classes.

它与将SqlDependency对象与SqlCommand关联以便检测查询结果何时不同于最初检索到的结果一样."

It is as basic as "A SqlDependency object can be associated with a SqlCommand in order to detect when query results differ from those originally retrieved."

您必须首先启用查询通知，然后按照创建通知查询

You must first Enable Query Notifications and follow Creating a Query for Notification

void Initialization()
{
    // Create a dependency connection.
    SqlDependency.Start(connectionString, queueName);
}

void SomeMethod()
{
    // Assume connection is an open SqlConnection.
    // Create a new SqlCommand object which directly references (no synonyms) the data you want to check for changes.
    using (SqlCommand command=new SqlCommand("SELECT value1 FROM [Table]", 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.
        using (SqlDataReader reader = command.ExecuteReader())
        {
            // Process the DataReader.
        }
    }
}

// Handler method
void OnDependencyChange(object sender, SqlNotificationEventArgs e )
{
  // Handle the event (for example, invalidate this cache entry).
}

void Termination()
{
    // Release the dependency.
    SqlDependency.Stop(connectionString, queueName);
}

这篇关于检测SQL数据库更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！