using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace SqlDependencyInAspNet
{
public partial class _Default : System.Web.UI.Page
{
string connectionString = ConfigurationManager.ConnectionStrings["NHibernateSampleDb"].ToString();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlDependency.Start(connectionString);
if (Cache["TableDate"] == null)
{
GetData();
}
DataTable dt = (DataTable)Cache["TableDate"];
gvData.DataSource = dt;
gvData.DataBind();
}
}
/// <summary>
/// 得到资料
/// </summary>
private void GetData()
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
using (SqlCommand cmd=cn.CreateCommand())
{
cn.Open();
cmd.CommandText = "select [CustomerId],[Firstname],[Lastname],[Version] from [dbo].[Customer]";
SqlDependency dep = new SqlDependency(cmd);
//当有DML操作时，onChange事件会接收来自Sql Server通过sq_DispatcherProc存储过程发送给应用程序的消息。
dep.OnChange += new OnChangeEventHandler(dep_OnChange);
DataTable dt = new DataTable();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
dt.Load(rdr);
}
Cache["TableDate"] = dt;
}
}
}
/// <summary>
/// 当有异动时，接收资料并缓存起来。
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void dep_OnChange(object sender, SqlNotificationEventArgs e)
{
/*
当取数据时间长时，有可能数据未取完，窗口就初关闭.所以先删除Cahce,在取数据。
*/
Cache.Remove("Cache");
GetData();
}
}
}
protected void Application_End(object sender, EventArgs e)
{
SqlDependency.Stop(System.Configuration.ConfigurationManager.ConnectionStrings["NHibernateSampleDb"].ToString());
}