SqlDependency学习笔记

阅读更多
换一批

SqlDependency无法监听数据库表

12-29

我想利用SqlDependency监听SQL Server数据库某一张表insert数据情况。rnrn我照例子写了如下代码,疑问是当我插入一条数据的时候可以扑获到,当在插入数据的时候rn程序就无法扑获到了,请问是为什么。rnrn[code=csharp]rnusing System;rnusing System.Collections.Generic;rnusing System.ComponentModel;rnusing System.Data;rnusing System.Data.SqlClient;rnusing System.Drawing;rnusing System.Linq;rnusing System.Text;rnusing System.Threading.Tasks;rnrnnamespace ConsoleApplication1rnrn class Programrn rn private static string connectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();rn static void Main(string[] args)rn rn SqlDependency.Start(connectionString);//传入连接字符串,启动基于数据库的监听 rn UpdateGrid();rn Console.Read();rn rnrn public static void UpdateGrid()rn rn using (SqlConnection connection = new SqlConnection(connectionString))rn rn //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]rn using (SqlCommand command = new SqlCommand("select ID,UserID,[Message] From [dbo].[Messages]", connection))rn rn command.CommandType = CommandType.Text;rn connection.Open();rn SqlDependency dependency = new SqlDependency(command);rn dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);rnrn command.ExecuteNonQuery();rn rn rn rn rnrn private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)rn rn //UpdateGrid();rn //这里只能扑获到第一次插入数据,后面在插入的数据就无法扑获到了。rn string sql = "select TOP 1 ID,UserID,[Message] From [dbo].[Messages] order by ID desc";rn using (SqlConnection connection = new SqlConnection(connectionString))rn rn using (SqlCommand cmd = new SqlCommand(sql, connection))rn rn tryrn rn connection.Open();rn SqlDataReader sdr = cmd.ExecuteReader();rnrn Console.WriteLine();rn while (sdr.Read())rn rn Console.WriteLine("Id:0\tUserId:1\tMessage:2", sdr["ID"].ToString(), sdr["UserId"].ToString(),rnrn sdr["Message"].ToString());rn rn sdr.Close();rnrn rn catch (System.Data.SqlClient.SqlException E)rn rn connection.Close();rn throw new Exception(E.Message);rn rn rn rnrnrn rn rnrnrn[/code]rn[code=sql]rnCREATE TABLE [dbo].[Messages](rn [ID] [int] IDENTITY(1,1) NOT NULL,rn [UserID] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,rn [Message] [nvarchar](256) COLLATE Chinese_PRC_CI_AS NOT NULL,rn CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED rn(rn [ID] ASCrn)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) rnrnON [PRIMARY]rn)rn[/code]rn我的核心目的是扑获到数据库表中最新的插入数据。

没有更多推荐了,返回首页