学步园

系列目录：

SqlDependency缓存用法

Asp.net使用SqlDependency

SqlCacheDependency使用命令通知使缓存无效

CacheDependency用法

AggregateCacheDependency 用法

SqlCacheDependency使用轮流检测技术(轮询)使缓存无效

----------------------------------------------------------------------------------------------

 

 

    在Microsoft SQL Server数据库中的数据发生变化时，SqlDependency类能够提供一种方法，将这种变化通知应用程序。可以使用这种通知来终止缓存和重新加载数据库服务器中的数据。
    通知是一项耗资源的任务，应选择变更频率较小的数据。
    对于使用少数客户(Web服务器或组件服务器)同时为成百上千名用户提供服务的Web应用程序或中间层组件，最好使用SqlDependency类。因此，可以缓存大型表，但是在应用程序收到一个修改通知时，通过终止缓存以错开重新加载这些表的时机，只有在用户再次引用数据时，才会重新加载缓存。

    SqlDependency是.net2.0封装的一个类型，在sql2005及以上版本才能使用。

使用方法：

1、必须在 MSDB 数据库中的 QueryNotificationService 服务上向Guest用户授予发送权限。方法如下，注意要区分大小写。 (我在做实时证并没有操作此步也可以运行，可能是以前就有此功能了吧)

USE MSDB
GRANT SEND ON SERVICE::
[http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService]
TO GUEST

 

2、启用CLR
在一个消息到达服务队列时，一个包含.Net代码的存储过程sp_DispatcherProc将使用一个队列来派发消息。因此必须启用Sql Server中的CLR功能。启用方法如下：

Use Master
Exec sp_configure 'clr enabled',1
Reconfigure

 

3、SqlDependency 对象会使用 Service Broker 将消息发送给 QueryNotificationService服务，所以需要启用 Service Broker。可以通过下边语句查看是否启用。

select DatabasePropertyex('Northwind','IsBrokerEnabled')
--返回1表示true，返加0表示false

 

    启动Service Broker语句如下：

use master
Alter Database Northwind set enable_broker

 

 

4、使用SqlDependency。

建立一个WinFrom项目，窗口名称为FormDependency，在窗口上放一个DataGridView。编写下边代码。

using System.Data.SqlClient;
using System.Configuration;
namespace SqlDependencyUse
{
    public partial class FormDependency : Form
    {
        private static string connectString = ConfigurationManager.ConnectionStrings["NHibernateSampleDb"].ToString();
        private delegate void GridDelegate(DataTable table);
        private SqlDependency dep;
        public FormDependency()
        {
            InitializeComponent();
        }
        private void FormDependency_Load(object sender, EventArgs e)
        {
            //启动一个与Sql Server进行通信的客户端进程
            SqlDependency.Start(connectString);
            UpdateGrid();
        }
        private void FormDependency_FormClosed(object sender, FormClosedEventArgs e)
        {
            //终止与Sql Server通信
            SqlDependency.Stop(connectString);
        }
        private void UpdateGrid()
        {
            string sql = "select [CustomerId],[Firstname],[Lastname],[Version] from [dbo].[Customer]";
            DataTable dt = new DataTable();
            using (SqlConnection cn = new SqlConnection(connectString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                    cn.Open();
                    //SqlCommand对象包含一个Notification属性，可以将SqlCommand对象传递给SqlDependency对象的构造函数，以设置该属性。
                    dep = new SqlDependency(cmd);
                    //当有DML操作时，onChange事件会接收来自Sql Server通过sq_DispatcherProc存储过程发送给应用程序的消息。
                    dep.OnChange += new OnChangeEventHandler(dep_OnChange);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        dt.Load(rdr);
                    }
                }
            }
            dgvView.Invoke(new GridDelegate(dgvDelegateMethod), dt);
        }
        private void dgvDelegateMethod(DataTable dtVal)
        {
            dgvView.DataSource = dtVal;
        }
        /// <summary>
        /// SqlDependency的onChange事件处理
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void dep_OnChange(object sender, SqlNotificationEventArgs e)
        {
            System.Diagnostics.Debug.WriteLine("接收到事件");
            if (e.Info == SqlNotificationInfo.Invalid)
            {
                MessageBox.Show("Invalid Statement");
                return;
            }
            UpdateGrid();
        }
    }
}

    注： 上边代码中的查询语句写法要非常严格。必须按SQL Server Service Broker提供制定规则的查询语句，一般来讲，必须是简单的sql查询语句（不能用*，不能用top，不能用函数，包括聚合函数，不能用子查询，包括where后的子查询，不能用外连接，自连接，不能用临时表，不能用变量，不能用视图，不能垮库，表名之前必须加类似dbo数据库所有者这样的前缀）例如：select * from table1，select column1 from table1，select count(*) from table1 都是错误的sql查询语句，select column1 from dbo.table1 则是正确的语句。

     我刚刚测试时，发现始终无法触发onChange事件，让我郁闷了好长时间，后来发现SQL中没有加[dbo]。

 

 5、测试，打开SqlServer，修改其中的数据。界面上DataGridView的数据也会一起更新。