的SqlDependency +服务代理 [英] SQLDependency + Service Broker

问题描述

我使用的SqlDependency获得当一些表中的数据被更改的通知。

I'm using SqlDependency to get notification when data in some table are changed.

    private void subscribeBroker()
    {
        using (var conn = new SqlConnection(connString))
        {
            conn.Open();

            var cmd = new SqlCommand("SELECT text FROM dbo.Test");
            cmd.Connection = conn;

            var dependency = new SqlDependency(cmd);
            dependency.OnChange += dependency_OnChange;
            SqlDependency.Start(connString);

            cmd.ExecuteNonQuery();
        }
    }

    void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        //Do something...
        subscribeBroker();
    }

这是工作，但我有一些问题。

It is working but I have some questions.

1）我没有找到办法如何让这行被更改的信息。我需要读取整个表的所有数据，看看有什么不同。有没有办法如何得到这些信息？ （主ID，或东西）也许使用比的SqlDependency不同的方法？

1) I didn't find a way how to get information which row was changed. I need to read all data from entire table to see what is different. Is there a way how to get this information? (primary ID, or something) Maybe to use different approach than SqlDependency?

2）如果有人不断变化的数据非常快。这可能是某些变化不会被通知？ （我担心的通知，当我再次订阅它的时间之间的时间。

2) What if "somebody" changing data very fast. It is possible that some changes will not being notified? (I'm concerned about time between notification and time when I subscribe it again.

感谢您。

推荐答案

关于1查询通知，通知您一个事实，那东西被改变。如果你想获得自去年时间你很可能使用时间戳列发生了什么变化。

About 1- query notification informs you about the fact, that something is changed. If you want to get what was changed since last time- you could probably use timestamp column.

关于2 - 查询通知通知您的更改，然后被丢弃。然后你再重新订阅的通知。滴下和通知的创作之间的均值时间是当时在通知有关的变化不发送。

About 2- query notification informs you about changes and then is dropped. then you again subscribe for notification again. that mean- time between dropping and creation of notifications is that time in which notification about changes is not send.

查询通知是多为的情况下，当你的数据不会经常发生变化。对于一些示例 - 分类兑现价值。所谓你认购的变化一些表，等待的变化，并在它们发生的时候你会得到最新版本的数据。应该考虑到查询通知也使用服务器资源，所以如果你有巨大的表，并希望得到数据的一些小的子集，很多的变化查询可以在性能方面的影响（类似于索引视图）。

Query notifications is more for the situations, when your data is not changing frequently. For example- some cashed classification values. So- you subscribe for changes in some table, wait for changes and at the time they happen you get latest version of data. Should consider that query notification also uses server resources, so if you have huge table and want to get changes on some small subset of data, a lot of queries can be affected in terms of performance (something like indexed view).

如果您需要根据更改的数据采取一些行动，每一个变化是很重要的，那么我会猜触发+服务代理可能会更有效。或者，根据您的需要，变更数据捕获。

If you need to take some action based on changed data and each change is important, then i would guess that trigger + service broker could be more effective. Or, depending on your needs, Change Data Capture.

这篇关于的SqlDependency +服务代理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！