廣告贊助

 

使用 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

http://sweeteason.pixnet.net/blog/post/32291409-%E8%87%AA%E5%B7%B1%E7%AD%86%E8%A8%98---vb.net-%22%E8%B7%A8%E5%9F%B7%E8%A1%8C%E7%B7%92%E4%BD%9C%E6%A5%AD%E7%84%A1%E6%95%88%22

創作者介紹
創作者 iammic 的頭像
iammic

簡單過生活

iammic 發表在 痞客邦 留言(0) 人氣()