使用 SqlDependency 偵測 SQL Server 資料變更(VB.NET)
SETP 1:啟用 Broker
ALTER DATABASE [資料庫] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE; |
SETP 2:建立資料表
CREATE TABLE [dbo].[Student]( [ST_NO] [varchar](20) NOT NULL, [ST_NAME] [nchar](20) NULL, CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ( [ST_NO] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] |
SETP 3:新增 Windows Form 專案
SETP 4:新增 From1,並加上 TextBox1 元件
程式:
Imports System.Data.SqlClient Public Class Form1 '資料庫連線字串 Dim connectionString As String = "Data Source=.;Initial Catalog=Test;User Id=sa;Password=1234;" Dim connection As SqlConnection Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load connection = New SqlConnection(connectionString) connection.Open() SqlDependency.Start(connectionString) '呼叫偵測資料表內容變更函式 Detect() End Sub Sub Detect() '設定要偵測的資料表,資料表名稱前需要加上 dbo. Using command As New SqlCommand("SELECT ST_NO,ST_NAME FROM dbo.Student", connection) Dim dependency As New SqlDependency(command) '設定資料表內容變更事件 AddHandler dependency.OnChange, AddressOf OnChange Using reader = command.ExecuteReader() While (reader.Read) ' Process the DataReader. End While End Using End Using End Sub Sub OnChange(ByVal sender As Object, ByVal e As SqlNotificationEventArgs) '提醒資料表內容已更新 SetMsg("發現 Student 資料表內容變更", TextBox1) '呼叫偵測資料表內容變更函式 Detect() End Sub '更新 UI 時會造成跨執行緒無效問題,需要使用 Delegate 方式進行處理 Private Delegate Sub UpdateMsg(ByVal msg As String, ByVal con As Control) Private Sub SetMsg(ByVal msg As String, ByVal con As Control) If Me.InvokeRequired() Then Dim um As New UpdateMsg(AddressOf SetMsg) Me.Invoke(um, msg, con) Else TextBox1.Text = msg End If End Sub Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing SqlDependency.Stop(connectionString) connection.Close() End Sub End Class |
參考資料:
https://msdn.microsoft.com/zh-tw/library/62xk7953(v=vs.110).aspx
https://stackoverflow.com/questions/1094202/enable-sql-server-broker-taking-too-long-time
留言列表