采用SQLServer2005Broker和SqlDependency类来提供数据更改通知(

作者：杜庆坤66 | 来源：互联网 | 2023-09-23 10:40

在SQLServer2005中，SQLServerServiceBroker用于创建会话以交换消息。消息交换在目标和发起方这两端之间进行。使用

在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> (此项配置全局缓存设置，可选)

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文件：

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片 Code

<connectionStrings>

<add name="Conn" providerName="System.Data.SqlClient" connectionString="Data Source=localhost;Initial Catalog=AdventureWorksDW;User ID=sa;Password=sa"/>

connectionStrings>

数据访问类：

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片 Code

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

/**////

/// SqlDbHelper 的摘要说明

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

///

public sealed class SqlDbHelper

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

{

单态模式#region 单态模式

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

static SqlDbHelper sqlDbHelper = new SqlDbHelper();

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

public static SqlDbHelper Instance() 采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

{ return sqlDbHelper; }

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

private SqlDbHelper()

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

{

this.connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

}

#endregion

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

private string connectionString;

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

public string ConnectionString

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

{

get

{ return connectionString; }

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

set

{ connectionString = value; }

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

}

public DataTable GetDataTable(string strSql)

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

{

string connStr = this.connectionString;

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

using (SqlConnection conn = new SqlConnection(connStr))

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

{

SqlCommand comm = new SqlCommand(strSql, conn);

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

SqlDataAdapter da = new SqlDataAdapter(comm);

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

DataTable dt = new DataTable();

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

da.Fill(dt);

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

return dt;

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

}

public int ExecuteNonQuery(string strSql)

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

{

string connStr = this.connectionString;

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

using (SqlConnection conn = new SqlConnection(connStr))

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

{

SqlCommand comm = new SqlCommand(strSql, conn);

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

if (conn.State == ConnectionState.Closed) conn.Open();

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

return comm.ExecuteNonQuery();

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

}

public SqlDependency AddSqlDependency(string strSql, OnChangeEventHandler sqlDep_OnChange)

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

{

string connStr = this.connectionString;

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

using (SqlConnection conn = new SqlConnection(connStr))

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

{

SqlCommand comm = new SqlCommand(strSql, conn);

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

// 添加依赖

SqlDependency sqlDep = new SqlDependency(comm);

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

sqlDep.OnChange += sqlDep_OnChange;

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

if (conn.State == ConnectionState.Closed) conn.Open();

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

comm.ExecuteNonQuery();

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

return sqlDep;

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

}

/**////

/// 对表增加依赖列，用于Sql依赖，或者用某个int列也可

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片

///

表名，如果不是dbo所有者，请提供包括所有者的完整表名

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片 ///

public int AddDependencyCloumn(string tableName)

{

return this.ExecuteNonQuery(string.Format("declare @num int "

+ "set @num = (select count(*) from syscolumns where id=object_id(&＃39;{0}&＃39;) and name = &＃39;dep&＃39;) "

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片 + "if @num = 0 ALTER TABLE {0} ADD dep bit NOT NULL CONSTRAINT dep{0} DEFAULT 0 ", tableName));

}

}

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片 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;

}

}

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片 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 &＃39;1&＃39; = &＃39;1&＃39; "

+ ") select * from t where rowNum between {0} and {1} ", startIndex, maxIndex);

return base.GetDataTable(strSql, "dbo", "DimCustomer", "CustomerKey");

}

#endregion

}

测试页面：

采用SQLServer2005Broker和SqlDependency类来提供数据更改通知( - 文章图片 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>行 <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服务器会稍微增加缓存调度和内存增加的负担，并且在数据库相应表发生更改后服务器会清除该表相关的所有缓存。

推荐阅读

default
C# 实现服务账户密码重置功能

本文详细介绍了如何使用C#实现不同类型的系统服务账户（如Windows服务、计划任务和IIS应用池）的密码重置方法。 ... [详细]

蜡笔小新 2024-11-22 21:55:10
controller
MVC模式下的电子取证技术初探

本文探讨了在MVC（模型-视图-控制器）架构下进行电子取证的技术方法，通过实际案例分析，提供了详细的取证步骤和技术要点。 ... [详细]

蜡笔小新 2024-11-23 12:13:06
string
深入解析Android联系人数据库设计：AbstractContactsProvider

本文探讨了Android系统中联系人数据库的设计，特别是AbstractContactsProvider类的作用与实现。文章提供了对源代码的详细分析，并解释了该类如何支持跨数据库操作及事务处理。源代码可从官方Android网站下载。 ... [详细]

蜡笔小新 2024-11-24 18:04:54
jsp
深入理解PHP中的超全局变量与AJAX技术

本文详细介绍了PHP中的几种超全局变量，包括$GLOBAL、$_SERVER、$_POST、$_GET等，并探讨了AJAX的工作原理及其优缺点。通过具体示例，帮助读者更好地理解和应用这些技术。 ... [详细]

蜡笔小新 2024-11-24 16:35:09
string
MyBatis中SQL查询参数传递方法详解

本文详细介绍了在MyBatis框架中如何通过#和$两种方式来传递SQL查询参数。使用#方式可以提高执行效率，而使用$则有助于在复杂SQL语句中更好地查看日志。此外，文章还探讨了不同场景下的参数传递方法，包括实体对象、基本数据类型以及混合参数的使用。 ... [详细]

蜡笔小新 2024-11-24 15:36:41
tree
MySQL 高级特性深度解析

本文深入探讨了MySQL中的高级特性，包括索引机制、锁的使用及管理、以及如何利用慢查询日志优化性能。适合有一定MySQL基础的读者进一步提升技能。 ... [详细]

蜡笔小新 2024-11-23 22:01:49
string
利用Scrapy构建的数据采集与分析可视化系统

本文探讨了如何使用Scrapy框架构建高效的数据采集系统，以及如何通过异步处理技术提升数据存储的效率。同时，文章还介绍了针对不同网站采用的不同采集策略。 ... [详细]

蜡笔小新 2024-11-23 16:56:38
request
Python网络编程：深入探讨TCP粘包问题及解决方案

本文详细探讨了TCP协议下的粘包现象及其产生的原因，并提供了通过自定义报头解决粘包问题的具体实现方案。同时，对比了TCP与UDP协议在数据传输上的不同特性。 ... [详细]

蜡笔小新 2024-11-23 15:55:15
string
Java Socket编程指南：构建多客户端支持的服务端

本文基于Java官方文档进行了适当修改，旨在介绍如何实现一个能够同时处理多个客户端请求的服务端程序。在前文中，我们探讨了单客户端访问的服务端实现，而本篇将深入讲解多客户端环境下的服务端设计与实现。 ... [详细]

蜡笔小新 2024-11-22 23:16:34
controller
Kubernetes Services详解

本文深入探讨了Kubernetes中的服务（Services）概念，解释了如何通过Services实现Pods之间的稳定通信，以及如何管理没有选择器的服务。 ... [详细]

蜡笔小新 2024-11-24 18:37:01
request
使用UploadProgress实现文件上传进度条

本文介绍了如何通过安装和配置php_uploadprogress扩展来实现文件上传时的进度条显示功能。通过一个简单的示例，详细解释了从安装扩展到编写具体代码的全过程。 ... [详细]

蜡笔小新 2024-11-24 00:04:13
string
解析 Android Service 中 onStartCommand 方法的不同返回值

本文详细探讨了 Android Service 组件中 onStartCommand 方法的四种不同返回值及其应用场景。Service 可以在后台执行长时间的操作，无需提供用户界面，支持通过启动和绑定两种方式创建。 ... [详细]

蜡笔小新 2024-11-23 20:54:16
string
深入解析：Hive JDBC编程实践

本文详细介绍了如何通过JDBC连接Hive进行数据操作，包括Hive服务的启动、相关依赖的配置以及具体代码示例，适合对Hive和JDBC有一定了解的开发者阅读。 ... [详细]

蜡笔小新 2024-11-23 16:52:02
jsp
SSM框架实战：分页功能实现详解

本文详细介绍如何在SSM（Spring + Spring MVC + MyBatis）框架中实现分页功能。包括分页的基本概念、数据准备、前端分页栏的设计与实现、后端分页逻辑的编写以及最终的测试步骤。 ... [详细]

蜡笔小新 2024-11-23 11:40:33
request
Python 实现监控与运维自动化方案

本文探讨了使用Python实现监控信息收集的方法，涵盖从基础的日志记录到复杂的系统运维解决方案，旨在帮助开发者和运维人员提升工作效率。 ... [详细]

蜡笔小新 2024-11-23 11:25:14

杜庆坤66

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章