高效地监视SQL表中的新插入数据的方法

Question

我有一个推送服务，它将传入的数据插入到SQL表中，我需要制作一个应用程序来监听此表以查看新的传入数据。

是否有一种方法可以监听此表而无需不断地选择数据库以获取新更改？如果没有使用这些数据，是否应该在读取后删除行，还是最好定期清理？

---

更新2

---

这是我的实际代码，它没有在表中插入任何通知，我做错了什么吗？

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading.Tasks;

namespace Navman_SPI_Service
{
    public partial class Service1 : ServiceBase
    {
        SqlConnection con = new SqlConnection();
        String passAvl = Properties.Settings.Default.AVLPass;
        String usuarioAvl = Properties.Settings.Default.AVLuser.ToString();
        String servidor = Properties.Settings.Default.Server.ToString();
        String DataB = Properties.Settings.Default.DB.ToString();
        String SQLuser = Properties.Settings.Default.DBuser.ToString();
        String SQLpass = Properties.Settings.Default.DBpassword.ToString();
        SqlCommand dependencyCommand = new SqlCommand();
        String connectionString = "";


        public Service1()
        {
            InitializeComponent();
        }
        public String getQuery()
        {
            return "SELECT MessageID FROM dbo.navman_ic_api_message WHERE Proccessed IS NULL";
        }

        public void OnDebug()
        {
            OnStart(null);
        }

        protected override void OnStart(string[] args)
        {
            dependencyCommand.CommandText = getQuery();
            connectionString = "Data Source=";
            connectionString += servidor + ";Initial Catalog=FOO;Persist Security Info=True;User ID=";
            connectionString += SQLuser + ";Password=";
            connectionString += SQLpass + ";Initial Catalog=" + DataB;

            con = new SqlConnection(connectionString);
            try
            {
                con.Open();
            }
            catch (Exception f)
            {
                var logging = new StringBuilder();
                var filePath = @"c:\temp\log.txt";
                String timestamp = DateTime.Now.ToString("[yyyy:MM:dd][HH:mm:ss]");
                logging.Append(timestamp + Environment.NewLine + Environment.NewLine + connectionString + Environment.NewLine + Environment.NewLine + f.ToString() + Environment.NewLine + Environment.NewLine);
                File.AppendAllText(filePath, logging.ToString());
            }
            if (con.State == ConnectionState.Open)
            {
                Initialization();
                dependencyStarter();
                var logging = new StringBuilder();
                var filePath = @"c:\temp\test.txt";
                String timestamp = DateTime.Now.ToString("[yyyy:MM:dd][HH:mm:ss]");
                logging.Append(timestamp + Environment.NewLine + Environment.NewLine + "SUCCESS" + Environment.NewLine + Environment.NewLine);
                File.AppendAllText(filePath, logging.ToString());
            }

        }

        protected override void OnStop()
        {
            Termination();
        }

        void dependencyStarter()
        {
            // Assume connection is an open SqlConnection.

            // Create a new SqlCommand object.
            using (SqlCommand command = new SqlCommand(getQuery(),con))
            {

                // Create a dependency and associate it with the SqlCommand.
                SqlDependency dependency = new SqlDependency(dependencyCommand);
                // 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.
                }
            }
        }
        void OnDependencyChange(object sender, SqlNotificationEventArgs e)
        {
            // Handle the event (for example, invalidate this cache entry).
            var logging = new StringBuilder();
            var filePath = @"c:\temp\test.txt";
            String timestamp = DateTime.Now.ToString("[yyyy:MM:dd][HH:mm:ss]");
            logging.Append(timestamp + Environment.NewLine + Environment.NewLine + sender.ToString() + Environment.NewLine + Environment.NewLine);
            File.AppendAllText(filePath, logging.ToString());
        }
        void Termination()
        {
            // Release the dependency.

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

感谢您的信件，

- Patricio Llaguno

---

你在使用哪种数据库引擎？SQL Server、MySQL、Oracle或PostgreSQL？ - Marnix van Valen

抱歉，我以为我说了，SQL服务器。 - Patricio Llaguno

你的关系型数据库管理系统是否支持触发器？ - PM 77-1

@PM77-1 我正在使用 Microsoft SQL Server Express V 11.0.5058.0，我想是这个版本，但我有点新手不知道它们如何工作。 - Patricio Llaguno

您可以创建一个触发器，以作用于INSERTS，并使其也向不同的表插入新行，该表的唯一目的是为了保存您的应用程序可以读取的新记录。然后，您的应用程序可以读取这些新记录，并在处理完成后从此表中删除它们。这样可以保持您的存储表干净，并且您的应用程序不会通过对大表运行查询来拖慢服务器。您的应用程序将基于计时器检查此表......这样，如果您的应用程序需要关闭，它总是可以从上次离开的位置恢复，因为数据将自行处理。 - Suing

Answer 1

尝试使用SqlDependency。使用SqlDependency检测更改

我在许多项目中都使用了这些依赖项，它们似乎运行得非常好。这使用了QueryNotifications，它是SQL Server Service Broker的一部分。

如果Broker尚未激活，则必须启用它。请让数据库管理员运行以下命令：

USE master;
GO

ALTER DATABASE MyDatabase SET ENABLE_BROKER;
GO

文章中的示例代码：

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

void SomeMethod()
{
    // Assume connection is an open SqlConnection.

    // Create a new SqlCommand object.
    using (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.
        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 Server 中设置一个回调，每当给定查询的结果更改时就会调用该回调。然后由您来处理相关操作。

您可能需要在表格上添加一列，以便您可以确定数据是否为新数据。例如“CreateDate”或“Processed”。将其从依赖项查询中排除。然后稍后可以使用它仅获取新内容。

我应该指出，如果表格经常更改，则最好定期轮询并作为单个批次抓取所有更改，而不是尝试逐个处理它们。SqlDependency 用于刷新缓存数据非常好。但对于用作事务队列的表格处理不太好。

您不应在完成后删除数据。只需在下一个查询中忽略它或将其移动到归档表中即可。删除信息通常是一个坏主意，以防以后需要进行故障排除。

- sarme

---

同意避免删除 - 最终你会后悔的。如果你有空间限制（SQL Express v8+ 仅限于10GB），你可以安排一个月度清理/归档作业。 - diegohb

@sarme，SqlDependency使用QueryNotifications而不是触发器来检测数据变化。QueryNotifications利用SQL Server Service Broker基础设施，在结果发生变化时传递一个变更通知消息。 - Dan Guzman

@sarme 只是为了澄清，在那个例子中，我只需要调用 SomeMethod 一次，对吗？每当我想将触发器绑定到一个 SQL 查询时，我理解得对吗？ - Patricio Llaguno

@sarme 在 OnDependecyChange 方法中，我应该将处理后的日期放在哪些行中？但是这不会再次触发 onDependencyChange 吗？因为我的查询将是所有具有该列上空值的行。还是我漏掉了什么？ - Patricio Llaguno

如果传递给SqlDependency的SqlCommand中不包含存储处理日期（“ProcessedDate”或其他名称）的列，则不应触发更改事件。@PatricioLlaguno - sarme