在SQLServer2005中， SQL Server Service Broker 用于创建会话以交换消息。 消息交换在目标和发起方这两端之间进行。 使用 SqlDependency 订阅查询通知是直接的：SqlDependency 对象将管理数据库中设置通知涉及到的复杂性。建立通知后，对象便会监视实现通知
在SQLServer2005中， SQL Server Service Broker 用于创建会话以交换消息。 消息交换在目标和发起方这两端之间进行。
使用 SqlDependency 订阅查询通知是直接的：SqlDependency 对象将管理数据库中设置通知涉及到的复杂性。建立通知后，对象便会监视实现通知的基础数据库对象，当 SQL Server 创建查询通知时，将在应用程序中调用事件处理程序。
对于应用程序接收SQL Server Service Broker通知，只能获取到对应数据库表数据做了何种更新，而无法获取更新的数据，而我们却可以利用这个通知，来做缓存依赖，来达到缓存过期的目的。
使用 SqlDependency 订阅查询通知必须向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 则是正确的语句。
以下以一个实际的例子( sqlDep 项目 ) 来说明如何使用 ServerBroker 和SqlDependency类来做缓存依赖，充分利用服务器资源和提高应用程序性能，并且封装以提供给开发人员最大的便利性，我们需要按照如下步骤操作：
1. 首先要在使用的数据库执行 ALTER DATABASE AdventureWorks SET ENABLE_BROKER 以启用该功能，执行时必须关闭所有可能锁表的操作和作业。
2. 打开sqlDep示例，运行ServiceBrokerTest.aspx以查看结果，查看代码。
3. 在现有应用程序中增加 更改通知以及缓存机制。
a) 在webconfig configuration > 节中添加 connectionStrings > 节，并配置连接字符串。
b) 在webconfig system.web > 节中添加
caching >
cache percentagePhysicalMemoryUsedLimit = " 60 " privateBytesPollTime = " 00:05:00 " />
caching > ( 此项配置全局缓存设置，可选 )
c) 建立数据访问层，如何封装编写不限，只要具有返回数据的方法即可。
d) 嵌入或者重写DaBase.cs中的 protected virtual DataTable GetDataTable 方法，具体请参考sqlDep示例，该方法提供自动响应程序表发生的更改，自动设定缓存机制，封装此方法后，对于开发人员，只需要按照以往开发习惯提供任意sql语句编写程序获取数据。
e) 继承DaBase类或自己编写具有 protected virtual DataTable GetDataTable 方法的类，并调用该方法，参见DaDimCustomer.cs。
以下我们以sqlDep做测试，以验证可行性及其性能：
我们以SqlServer2005自带的 AdventureWorksDW 数据库中的 DimCustomer 表为例，该表有29列，各种数据类型都有，18484行，7984KB数据，平均每行0.43KB。
我们以每次查询20页，查询该表的所有列作为测试。由于缓存的是查询结果，所以内存变化可以根据每次查询的数据量为基准，20行大小大约是8.6KB，缓存默认设置是允许使用服务器内存的90%，
假设对应的数据库表不做更新操作，假设Web服务器有1G的内存可使用缓存，
则可以缓存12万份不重复结果(这里没有计算.net本身每个数据实体，每个缓存相关数据所占有的空间，相对于数据而言可以忽略不计)，
缓存命中率大都集中在常用查询，例如商品列表第一页，某个商品分类第一页等，一旦有某个用户使用了查询，则其他用户可以不需要访问数据库即可得到所需数据。即使缓存如果超过了程序规定的最大数据，.net运行时也会自动随即清空缓存，这并不影响程序运行。
以下附上完整代码：
WebConfig文件：
Code
connectionStrings >
add name = " Conn " providerName = " System.Data.SqlClient " connectionString = " Data Source=localhost;Initial Catalog=AdventureWorksDW;User ID=sa;Password=sa " />
connectionStrings >
数据访问类：
Code
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
/**/ ///
/// SqlDbHelper 的摘要说明
///
public sealed class SqlDbHelper
{
单态模式 #region 单态模式
static SqlDbHelper sqlDbHelper = new SqlDbHelper();
public static SqlDbHelper Instance() { return sqlDbHelper; }
private SqlDbHelper()
{
this .connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[ " Conn " ].ConnectionString;
}
#endregion
private string connectionString;
public string ConnectionString
{
get { return connectionString; }
set { connectionString = value; }
}
public DataTable GetDataTable( string strSql)
{
string connStr = this .connectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand comm = new SqlCommand(strSql, conn);
SqlDataAdapter da = new SqlDataAdapter(comm);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
public int ExecuteNonQuery( string strSql)
{
string connStr = this .connectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand comm = new SqlCommand(strSql, conn);
if (conn.State == ConnectionState.Closed) conn.Open();
return comm.ExecuteNonQuery();
}
}
public SqlDependency AddSqlDependency( string strSql, OnChangeEventHandler sqlDep_OnChange)
{
string connStr = this .connectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand comm = new SqlCommand(strSql, conn);
// 添加依赖
SqlDependency sqlDep = new SqlDependency(comm);
sqlDep.OnChange += sqlDep_OnChange;
if (conn.State == ConnectionState.Closed) conn.Open();
comm.ExecuteNonQuery();
return sqlDep;
}
}
/**/ ///
/// 对表增加依赖列，用于Sql依赖，或者用某个int列也可
///
/// 表名，如果不是dbo所有者，请提供包括所有者的完整表名
///
public int AddDependencyCloumn( string tableName)
{
return this .ExecuteNonQuery( string .Format( " declare @num int "
+ " set @num = (select count(*) from syscolumns where id=object_id('{0}') and name = 'dep') "
+ " if @num = 0 ALTER TABLE {0} ADD dep bit NOT NULL CONSTRAINT dep{0} DEFAULT 0 " , tableName));
}
}
Code
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Caching;
///
/// 要使用SqlServer2005 Service Broker，首先要在使用的数据库执行 ALTER DATABASE AdventureWorks SET ENABLE_BROKER 以启用该功能，执行时必须关闭所有可能锁表的操作和作业
/// 使用依赖的sql语句，不能用*，不能用top，不能用函数，包括聚合函数，不能用子查询，包括where后的子查询，
/// 不能用外连接，自连接，不能用临时表，不能用变量，不能用视图，不能垮库，表名之前必须加类似dbo数据库所有者这样的前缀
/// 依赖只针提供一次通知，所以重新发起某次查询则需要重新提供依赖sql语句
///
/// 优点：此应用比较适合访问次数大于更新次数的情况，访问次数比更新次数越多，速度提升越明显
/// 缺点：对服务器内存要求较高
///
public abstract class DaBase
{
private SqlDbHelper sqlDbHelper;
//
private Cache pageCache;
public DaBase(Cache cache)
{
sqlDbHelper = SqlDbHelper.Instance();
this .pageCache = cache;
}
///
/// 清除缓存
///
/// 缓存名称
protected virtual void ClearCache( string cacheName)
{
System.Collections.IDictionaryEnumerator cacheEnum = pageCache.GetEnumerator();
while (cacheEnum.MoveNext())
{
// 只清除与此业务相关的缓存，根据表名
if (cacheEnum.Key.ToString().ToLower().IndexOf(cacheName.ToLower()) > 0 )
pageCache.Remove(cacheEnum.Key.ToString());
}
}
///
/// 创建Service Borker通知(请确认Service Borker已开启)，自动响应程序表发生的更改，自动设定缓存机制
///
/// System.Web.Caching.Cache对象
/// 查询数据的sql语句
/// 数据库表所有者
/// 表名
/// 列名，随意某个小列(最好是bit,tinyint,varchar(1),int)
///
protected virtual DataTable GetDataTable( string selectSql, string dbOwner, string tableName, string column)
{
// 用于Service Broker跟踪的表范围sql
string depSql = string .Format( " select {0} from {1}.{2} " , column, dbOwner, tableName);
DataTable dt = new DataTable();
if (pageCache[selectSql] != null )
dt = pageCache[selectSql] as DataTable;
else
{
// 触发行级依赖，如果该表的指定范围内的行被修改，则会收到SqlServer的通知，并且清空相应缓存
SqlDependency sqlDep = sqlDbHelper.AddSqlDependency(depSql,
delegate ( object sender, SqlNotificationEventArgs e)
{
if (e.Info == SqlNotificationInfo.Invalid)
{
// sqlDbHelper.ExecuteNonQuery("ALTER DATABASE AdventureWorksDW SET ENABLE_BROKER");
// 写文件，数据库未开启Service Broker或者提供了无法通知的语句，例如没有写包括数据库所有者的表名。
}
this .ClearCache(tableName);
});
dt = sqlDbHelper.GetDataTable(selectSql);
pageCache[selectSql] = dt;
}
return dt;
}
}
Code
using System;
using System.Data;
using System.Web;
using System.Web.Caching;
public class DimCustomer : DaBase
{
public DimCustomer(Cache pageCache) : base (pageCache) { }
#region 分页查询顾客信息
public DataTable SelectDimCustomer( int startIndex, int maxIndex)
{
// 用于查询的sql语句
string strSql = string .Format( " with t as ( "
+ " select row_number() over(order by CustomerKey Desc) as rowNum, * "
+ " from DimCustomer where '1' = '1' "
+ " ) select * from t where rowNum between {0} and {1} " , startIndex, maxIndex);
return base .GetDataTable(strSql, " dbo " , " DimCustomer " , " CustomerKey " );
}
#endregion
}
测试页面：
Code
@ Page Language = " C# " AutoEventWireup = " true " CodeFile = " ServiceBrokerTest.aspx.cs " Inherits = " ServiceBrokerTest " %>
DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
html xmlns = " http://www.w3.org/1999/xhtml " >
head runat = " server " >
title > 无标题页 title >
head >
body >
form id = " form1 " runat = " server " >
div >
asp:TextBox ID = " txt1 " Text = " 1 " runat = " server " > asp:TextBox > 到 asp:TextBox ID = " txt2 " Text = " 20 " runat = " server " > asp:TextBox > 行 & nbsp; asp:Button ID = " btn1 " runat = " server " Text = " 获取数据 " OnClick = " btn1_Click " />
br />
asp:GridView ID = " gv1 " runat = " server " AutoGenerateColumns = " true " > asp:GridView >
div >
form >
body >
html >
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class ServiceBrokerTest : System.Web.UI.Page
{
protected void Page_Load( object sender, EventArgs e)
{
}
protected void btn1_Click( object sender, EventArgs e)
{
DimCustomer da = new DimCustomer( this .Cache);
this .gv1.DataSource = da.SelectDimCustomer(Convert.ToInt32( this .txt1.Text), Convert.ToInt32( this .txt2.Text));
this .gv1.DataBind();
// SqlDbHelper sqlDbHelper = SqlDbHelper.Instance();
// DataTable dt = sqlDbHelper.GetDataTable("select top 10 * from DimCustomer");
// this.gv1.DataSource = dt;
// this.gv1.DataBind();
}
}
总结：
特点：特别适合更新不频繁但是读取频繁的表，会大大提高应用程序性能。
优点：缓存越多，SQL服务器负担就越小，大大减少了IO读操作以及网络传输占用。
缺点：Web服务器会稍微增加缓存调度和内存增加的负担，并且在数据库相应表发生更改后服务器会清除该表相关的所有缓存。
查看更多关于采用SQLServer2005Broker和SqlDependency类来提供数据更改通知(的详细内容...