SqlDependency无法监听数据库表

[wangtiantian23]

我想利用SqlDependency监听SQL Server数据库某一张表insert数据情况。

我照例子写了如下代码，疑问是当我插入一条数据的时候可以扑获到，当在插入数据的时候
程序就无法扑获到了，请问是为什么。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        private static string connectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
        static void Main(string[] args)
        {
            SqlDependency.Start(connectionString);//传入连接字符串,启动基于数据库的监听  
            UpdateGrid();
            Console.Read();
        }

        public static void UpdateGrid()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
                using (SqlCommand command = new SqlCommand("select ID,UserID,[Message] From [dbo].[Messages]", connection))
                {
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                    command.ExecuteNonQuery();
                   
                }
            }
        }

        private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            //UpdateGrid();
           //这里只能扑获到第一次插入数据，后面在插入的数据就无法扑获到了。
            string sql = "select TOP 1 ID,UserID,[Message] From [dbo].[Messages] order by ID desc";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        connection.Open();
                        SqlDataReader sdr = cmd.ExecuteReader();

                        Console.WriteLine();
                        while (sdr.Read())
                        {
                            Console.WriteLine("Id:{0}\tUserId:{1}\tMessage:{2}", sdr["ID"].ToString(), sdr["UserId"].ToString(),

            sdr["Message"].ToString());
                        }
                        sdr.Close();

                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }


        }
    }
}

CREATE TABLE [dbo].[Messages](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [Message] [nvarchar](256) COLLATE Chinese_PRC_CI_AS NOT NULL,
 CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 

ON [PRIMARY]
)

我的核心目的是扑获到数据库表中最新的插入数据。

[正怒月神]

哦，明白了。第二次插入，就没办法捕获了这个意思对吧？

[正怒月神]

疑问是当我插入一条数据的时候可以扑获到，当在插入数据的时候 程序就无法扑获到了 我没有看懂这句话的意思

[wangtiantian23]

是的。按照我的第一种做法，在插入第一条数据后，以后的insert的数据都不能扑获到了。

[大葡萄八块一斤]

SqlDependency的sql语句比较严格列名必须写，不能用*,不能用top，不能用函数，包括聚合函数，不能用子查询，包括where后的子查询，不能用外连接，自连接，不能用临时表，不能用变量，不能用视图，不能垮库，而且表名之前必须加类似dbo这样的前缀等等 你说的轮训根本不会，只有第一次的时候才会把全部读一下，之后只有更新的才能被读到，你不放心的话可以自己测试下，也可以自己家WHERE条件 ID大于多少多少这样。。。

[wangtiantian23]

while (sdr.Read())
                    {
                        
                        Console.WriteLine("Id:{0}\tUserId:{1}\tMessage:{2}", sdr["ID"].ToString(), sdr["UserId"].ToString(),
 
 sdr["Message"].ToString());
                        //break;//为了完成目的这样处理了一下。查一条就跳出。
                    }

@小疯纸纸 不是这样的啊！我试过了，我只要insert语句，它就会把表里所有数据都打印出来。

[正怒月神]

我测试了一下，winform是可以连续触发的。 而控制台应用程序，我测试了也不行。 sql users表只有一个uname字段。

insert into Users(uname)
select '2'

public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        string conStr = "server=.;database=movies;uid=sa;pwd=sasa;";
        private void Form1_Load(object sender, EventArgs e)
        {
            
            SqlDependency.Start(conStr);
            Update(conStr);
        }

        public void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            Update(conStr);
        }

        private void Update(string conStr)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                //此处 要注意 不能使用*  表名要加[dbo]  否则会出现一直调用执行 OnChange
                string sql = "select id from [dbo].[Users]";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    connection.Open();
                    command.CommandType = CommandType.Text;
                    var dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                    //必须要执行一下command
                    DataSet ds=new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter(command);
                    da.Fill(ds);
                    this.dataGridView1.Invoke(new Action(() => { this.dataGridView1.DataSource = ds.Tables[0]; }));
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {

            
        }

    }

[wangtiantian23]

@hanjun0612 按照您的方法是可以扑获到每一条insert数据，又有一个新问题。这样每次都是相当于select * 表了，这样 效率是不是会很大而且损耗会很多，随着数据的增多10万、100万那每一次select会很慢，其实我就想知道 insert哪条数据了。

[xuzuning]

感觉你的这个代码逻辑上有问题，搜索了一下，原来都是抄的某篇博文 你的 SqlDependency 对象是 Update 方法中的局部变量，自然是只能监听一次 你应该把 dependency 设计成全局的

[FainSheeg]

onchange事件里要重新调用updategrid

[wangtiantian23]

@xuzuning 我是从博文里摘抄的想研究其内容。 但是稍加改动发现有问题。我把dependency改成全局的并不能成功。 其实我的根本目的是监控某一张表，我只想得到这张表最新insert的数据。 这个是核心，但是现在我用一些方法可以得到这个数据，但是感觉每次用select来查这个表太消耗资源了。 未来10万、100万.....数据查询起来会很慢。

[非专业开发Five]

引用 12 楼 wangtiantian23 的回复:

@xuzuning 我是从博文里摘抄的想研究其内容。 但是稍加改动发现有问题。我把dependency改成全局的并不能成功。 其实我的根本目的是监控某一张表，我只想得到这张表最新insert的数据。 这个是核心，但是现在我用一些方法可以得到这个数据，但是感觉每次用select来查这个表太消耗资源了。 未来10万、100万.....数据查询起来会很慢。

dependency本身就应该是全局通知用的，之后的处理你应该在后面再做模块。 而不是在dependency的时候就做处理。 你这种建议用状态轮询。 就是一开始入库的时候插入一个你自己的数据像这样：

你的数据字段1  你的数据字段2  你的数据字段3  你的数据字段4  你的数据字段5    …………  你的数据字段n   状态字段（未处理为Start）

然后你就可以每隔一定时间轮询一下状态为 Start 的该表全部数据，在轮询到你的程序后将状态变更为 In Presses ，这样你下次轮询就不会找到这些数据。 后面你怎么处理那就是你的业务逻辑了。

[大葡萄八块一斤]

引用 7 楼 wangtiantian23 的回复:

while (sdr.Read())
                    {
                        
                        Console.WriteLine("Id:{0}\tUserId:{1}\tMessage:{2}", sdr["ID"].ToString(), sdr["UserId"].ToString(),
 
 sdr["Message"].ToString());
                        //break;//为了完成目的这样处理了一下。查一条就跳出。
                    }

@小疯纸纸 不是这样的啊！我试过了，我只要insert语句，它就会把表里所有数据都打印出来。

你跟我说的不是一个意思，我现在大概知道你想做的意思了，你这里command.ExecuteReader();而command的sql语句是查询全部，肯定会查到全部。。。你想实现只要最新插入的只能把最后一次读到的id保存下来，第二次打印的时候，这个语句你要改成select ID,UserID,[Message] From [dbo].[Messages] ID>最后的id order by ID asc这样就可以了

[正怒月神]

引用 9 楼 wangtiantian23 的回复:

@hanjun0612 按照您的方法是可以扑获到每一条insert数据，又有一个新问题。这样每次都是相当于select * 表了，这样 效率是不是会很大而且损耗会很多，随着数据的增多10万、100万那每一次select会很慢，其实我就想知道 insert哪条数据了。

引用 9 楼 wangtiantian23 的回复:

@hanjun0612 按照您的方法是可以扑获到每一条insert数据，又有一个新问题。这样每次都是相当于select * 表了，这样 效率是不是会很大而且损耗会很多，随着数据的增多10万、100万那每一次select会很慢，其实我就想知道 insert哪条数据了。

没什么好办法，因为sqldependency 是监视表的，而不监视行。 并且sqldependency也不支持复杂函数，比如top 聚合函数之类的。 所以你这个不太好解决单行新增的问题

[秋的红果实]

sqldependency只会告诉你：数据库有变动，并启动处理预案。具体改变了什么内容，它也不知道 我查了下官网https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency(v=vs.110).aspx，sqldependency类没有获取“已变动的内容”的功能 要想彻底将数据库和应用程序互动起来，需要研究下OMR，EF就是一种 lz可以在写入数据库前，将新内容保存到另一个表中，甚至是临时表；或者干脆保存到内存，更方便程序使用

[秋的红果实]

只有第一次有效，原因是你dependency_OnChange方法里，没有再调用UpdateGrid

[wangtiantian23]

@小疯纸纸 select ID,UserID,[Message] From [dbo].[Messages] ID>最后的id order by ID asc 我要知道最后的ID就好了，我就不用监控了。

[wangtiantian23]

@秋的红果实 OMR 是什么? EF是什么？ 都没听说请赐教。

[xuzuning]

如果只是担心 select 太多了 那么你可以通过触发器将被改变的记录送往监视用表

[吉普赛的歌]

http://blog.csdn.net/yenange/article/details/49636215 可以试着用 cdc ， 这个比触发器的影响小。 轮询 cdc 表， 如果有新记录则系统做相应的操作。 虽然没有SqlDependency那么高大上， 所谓的事件触发， 但绝对比SqlDependency稳定。

[秋的红果实]

引用 19 楼 wangtiantian23 的回复:

@秋的红果实 OMR 是什么? EF是什么？ 都没听说请赐教。

https://kb.cnblogs.com/page/73241/