摘要:SignalR 與 SqlDependency 建立ASP.NET realtime應用
近期接觸到一個案例，需求為當資料庫有新的異動時，需即時更新Client端的Datagrid,但不需要一直request去檢查資料造成負擔，
於是乎google不少資料，決定用SqlDependency監看資料表異動 並由Onchange事件觸發hub 裏頭的動態方法 操作client端(SignalR framework ),其原理 可參考http://blogs.msdn.com/b/msdntaiwan/archive/2013/09/09/signalr-reimagine-web-development.aspx
以下直接Step by Step
步驟一
於參考點選管理Nuget套件
步驟二
安裝SignalR 2.0.1
步驟三
於App_Code加入OWIN
加入Code
using System;
using System.Threading.Tasks;
using Microsoft.Owin;
using Owin;
[assembly: OwinStartup(typeof(SignalRDEMO.Startup1))]
namespace SignalRDEMO
{
public class Startup1
{
public void Configuration(IAppBuilder app)
{
// Any connection or hub wire up and configuration should go here
app.MapSignalR();
}
}
}步驟四
加入Hub類別
加入Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.AspNet.SignalR;
using Microsoft.AspNet.SignalR.Hubs;
namespace SignalRDEMO
{
[HubName("SignalRDEMOHub")]
public class SignalRDEMOHub : Hub
{
public void SetState()
{
GetData ss = new GetData();
ss.GetStudentData();
}
public static void ShowData()
{
// 呼叫用戶端方法
var context = GlobalHost.ConnectionManager.GetHubContext();
context.Clients.All.SetState();
}
}
}
步驟五
建立SqlDependency 連線 與OnChange事件
using SignalRDEMO;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
///
/// GetData 的摘要描述
///
public class GetData
{
public GetData()
{
//
// TODO: 在這裡新增建構函式邏輯
//
}
public class StudentData
{
public int ID { get; set; }
public string Name { get; set; }
}
public IEnumerable GetStudentData()
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(@"SELECT [ID]
,[Name]
FROM [dbo].[Students]
", connection))
{
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
using (var reader = command.ExecuteReader())
return reader.Cast()
.Select(x => new StudentData()
{
ID = x.GetInt32(0),
Name = x.GetString(1),
}).ToList();
}
}
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
//這裡是觸發異動事件
SignalRDEMOHub.ShowData();
}
步驟六
加入.asax
void Application_Start(object sender, EventArgs e)
{
// 在應用程式啟動時執行的程式碼
System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
}
void Application_End(object sender, EventArgs e)
{
// 在應用程式關閉時執行的程式碼
System.Data.SqlClient.SqlDependency.Stop(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
} 步驟七
設計一個Default.aspx 並且放入Gridview 搭配updatepanel
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<div style="display: none">
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
</div>
<asp:GridView ID="GridView1" runat="server"></asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</form>
</body>------------------------------------------------------------------------------------------------------------------------------------------------------------
參考自
http://techbrij.com/database-change-notifications-asp-net-signalr-sqldependency