首先第一步是确保您的 Service Broker 已经激活,激活 Service Broker (Transact-SQL)如下:
USE master ;
GO
ALTER DATABASE YouDatabase SET ENABLE_BROKER ;
GO
如果您的数据库与应用程序是分布在网络上的两台服务器上,那么您有必要激活 Service Broker 网络(Transact-SQL)
创建 Service Broker 端点,其间指定端口号和身份验证级别,代码如下:
USE YouDataBase;
GO
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) ;
GO
接下来是实现在asp.net 2.0缓存的Demo (以下内容引用自由港 http://www.cnblogs.com/yg_zhang/archive/2006/09/20/508961.html 非常感谢他)
1.首先在sqlserver2005 中创建一个test的数据库.添加一个 employee的数据库表.
CREATE TABLE [dbo].[employee](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50)
2使用 vs2005 创建一个新的asp.net项目.
web.config如下
- <?xml version="1.0"?>
- <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
- <appSettings/>
- <connectionStrings>
- <add name="mySource" connectionString="Data Source=./sql2005;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=sasa" providerName="System.Data.SqlClient"></add>
- </connectionStrings>
- <system.web>
- <compilation debug="true"/>
- <authentication mode="Windows"/>
- </system.web>
- </configuration>
3.编写global.asax文件,启动监听sql2005通知事件.
- <%@ Application Language="C#" %>
- <%@ Import Namespace="System.Data.SqlClient" %>
- <script runat="server">
- void Application_Start(object sender, EventArgs e)
- {
- string connStr=ConfigurationManager.ConnectionStrings["mySource"].ConnectionString;
- SqlDependency.Start(connStr);
- }
- void Application_End(object sender, EventArgs e)
- {
- string connStr = ConfigurationManager.ConnectionStrings["mySource"].ConnectionString;
- SqlDependency.Stop(connStr);
- }
- </script>
4.编写数据访问代码.创建一个EmployeeData的类,代码如下
- using System;
- using System.Data;
- using System.Data.SqlClient;
- using System.Configuration;
- using System.Data.Common;
- using System.Web;
- using System.Web.Caching;
- using System.Web.Security;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Web.UI.WebControls.WebParts;
- using System.Web.UI.HtmlControls;
- /**//// <summary>
- /// EmployeeData 的摘要说明
- /// </summary>
- public class EmployeeData
- {
- public EmployeeData()
- {
- }
- private HttpContext context;
- public DataSet GetCacheData()
- {
- context = HttpContext.Current;
- DataSet cache =(DataSet) context.Cache["employee"];
- if (cache == null)
- {
- return GetData();
- }
- else
- {
- return cache;
- }
- }
- public DataSet GetData()
- {
- string connStr = ConfigurationManager.ConnectionStrings["mySource"].ConnectionString;
- SqlConnection conn = new SqlConnection(connStr);
- SqlDataAdapter adp = new SqlDataAdapter("select id,name from dbo.employee", conn);
- SqlCacheDependency dep = new SqlCacheDependency(adp.SelectCommand);
- DataSet ds=new DataSet();
- adp.Fill(ds);
- context.Cache.Add("employee", ds, dep, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Default, new CacheItemRemovedCallback(this.DataDiff));
- return ds;
- }
- public void DataDiff(string key, object value, CacheItemRemovedReason reason)
- {
- Console.WriteLine("key:" + key);
- GetData();
- }
- }
这里需要注意的是 select语句的写法, 不能使用 select * 的方式,一定要在表名前加架构名称 如我们这里的 dbo.employee.
5.编写测试页面代码.
- <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
- <!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:GridView ID="GridView1" runat="server" >
- </asp:GridView>
- </div>
- </form>
- </body>
- </html>
6.插入后台代码
- using System;
- using System.Data;
- using System.Configuration;
- using System.Web.Caching;
- using System.Data.SqlClient;
- 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 _Default : System.Web.UI.Page
- {
- protected void Page_Load(object sender, EventArgs e)
- {
- EmployeeData em=new EmployeeData();
- GridView1.DataSource = em.GetCacheData();
- GridView1.DataBind();
- }
- }