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