sqlServer2005查询通知

1.ALTER DATABASE AdventureWorks SET ENABLE_BROKER  //指定哪个数据库开启该服务
2.GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [YourComputerName\UserName] //哪个用户能使用该服务

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace SqlDependencyTest
{
    class Program
    {
        static string _connStr = "";
        static void Main(string[] args)
        {
            _connStr = @"Data Source=SPS-010\sps2005;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=123";
            SqlDependency.Start(_connStr);//传入连接字符串,启动基于数据库的监听
            UpdateGrid();

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

                    SqlDataReader sdr = command.ExecuteReader();
                    Console.WriteLine();
                    while (sdr.Read())
                    {
                        Console.WriteLine("Id:{0}\tUserId:{1}\tMessage:{2}", sdr["id"].ToString(), sdr["username"].ToString(),

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

        private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            UpdateGrid();
        }

    }
}