簡單過生活

ALTER DATABASE [資料庫] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

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]

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