asp.net实现SQL2005的通知数据缓存

首先第一步是确保您的 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如下

 1 <?xml version="1.0"?>
 2 <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
 3     <appSettings/>
 4     <connectionStrings>
 5         <add name="mySource" connectionString="Data Source=./sql2005;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=sasa" providerName="System.Data.SqlClient"></add>
 6     </connectionStrings>
 7     <system.web>
 8         <compilation debug="true"/>
 9         <authentication mode="Windows"/>
10     </system.web>
11 </configuration>

 

3.编写global.asax文件,启动监听sql2005通知事件.

1.<%@ Application Language="C#" %>
2.<%@ Import Namespace="System.Data.SqlClient" %>
3.
4.<script runat="server">
5.
6.    void Application_Start(object sender, EventArgs e) 
7.    {
8.        string connStr=ConfigurationManager.ConnectionStrings["mySource"].ConnectionString;
9.        SqlDependency.Start(connStr);
10.    }
11.    
12.    void Application_End(object sender, EventArgs e) 
13.    {
14.        string connStr = ConfigurationManager.ConnectionStrings["mySource"].ConnectionString;
15.        SqlDependency.Stop(connStr);
16.    }
17.</script>

 

4.编写数据访问代码.创建一个EmployeeData的类,代码如下

1.using System;
2.using System.Data;
3.using System.Data.SqlClient;
4.using System.Configuration;
5.using System.Data.Common;
6.using System.Web;
7.using System.Web.Caching;
8.using System.Web.Security;
9.using System.Web.UI;
10.using System.Web.UI.WebControls;
11.using System.Web.UI.WebControls.WebParts;
12.using System.Web.UI.HtmlControls;
13.
14./**//// <summary>
15./// EmployeeData 的摘要说明 
16./// </summary> 
17.public class EmployeeData
18.{
19.    public EmployeeData()
20.    {
21.    }
22.
23.    private HttpContext context;
24.
25.    public DataSet GetCacheData()
26.    {
27.        context = HttpContext.Current;
28.        DataSet cache =(DataSet) context.Cache["employee"];
29.        if (cache == null)
30.        {
31.            return GetData();
32.        }
33.        else
34.        {
35.            return cache;
36.        }
37.    }
38.
39.
40.    public DataSet GetData()
41.    {
42.        string connStr = ConfigurationManager.ConnectionStrings["mySource"].ConnectionString;
43.        SqlConnection conn = new SqlConnection(connStr);
44.        SqlDataAdapter adp = new SqlDataAdapter("select id,name from dbo.employee", conn);
45.        SqlCacheDependency dep = new SqlCacheDependency(adp.SelectCommand);
46.        DataSet ds=new DataSet();
47.        adp.Fill(ds);
48.        context.Cache.Add("employee", ds, dep, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Default, new CacheItemRemovedCallback(this.DataDiff));
49.        return ds;
50.    }
51.
52.    public void DataDiff(string key, object value, CacheItemRemovedReason reason)
53.    {
54.        Console.WriteLine("key:" + key);
55.        GetData();
56.    }
57.
58.}

 

这里需要注意的是 select语句的写法, 不能使用 select *  的方式,一定要在表名前加架构名称 如我们这里的 dbo.employee.

5.编写测试页面代码.

1.<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
2.
3.<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4.
5.<html xmlns="http://www.w3.org/1999/xhtml" >
6.<head runat="server">
7.    <title>无标题页</title>
8.</head>
9.<body>
10.    <form id="form1" runat="server">
11.    <div>
12.        <asp:GridView ID="GridView1" runat="server" >
13.        </asp:GridView>
14.    </div>
15.    </form>
16.</body>
17.</html>
18.

 

6.插入后台代码

1.using System;
2.using System.Data;
3.using System.Configuration;
4.using System.Web.Caching;
5.using System.Data.SqlClient;
6.using System.Web;
7.using System.Web.Security;
8.using System.Web.UI;
9.using System.Web.UI.WebControls;
10.using System.Web.UI.WebControls.WebParts;
11.using System.Web.UI.HtmlControls;
12.
13.public partial class _Default : System.Web.UI.Page 
14.{
15.    protected void Page_Load(object sender, EventArgs e)
16.    {
17.        EmployeeData em=new EmployeeData();
18.        GridView1.DataSource = em.GetCacheData();
19.        GridView1.DataBind();
20.    }
21.
22.}

 

posted @ 2014-04-02 13:54  Jasper_贾斯白  阅读(189)  评论(0)  编辑  收藏  举报