دوست عزیز بهترین راه استفاده ار رویداد است:
کد تغییر یافته کلاس:
Imports Microsoft.SqlServer.Management.Smo
Public Class DatabaseWatcher
Private Shared WatcherServer As Server
Private Shared db As Database
Public Shared Event TableChanged As EventHandler(Of EventArgs)
Private Shared Sub Table_Changed(ByVal sender As Object, ByVal e As ServerEventArgs)
For Each prpty In e.Properties
'If prpty.Value = "TBL_Tmp_For_Event" Then
If prpty.Value = "IN_Letter_Tmp_For_Event" Then
'Do Here!
' MsgBox("اطلاعات جدول تغيير کرد!")
RaiseEvent TableChanged(prpty.Value, New EventArgs)
End If
Next
End Sub
Public Shared Sub RegisterEvent()
Try
WatcherServer = New Server("ALI-PC\ASQLSERVER")
WatcherServer.ConnectionContext.AutoDisconnectMode = False
db = WatcherServer.Databases("Indicator")
Dim databaseCreateEventSet As New DatabaseEventSet
databaseCreateEventSet.DropTable = True
Dim serverCreateEventHandler As ServerEventHandler
serverCreateEventHandler = New ServerEventHandler(AddressOf Table_Changed)
db.Events.SubscribeToEvents(databaseCreateEventSet , serverCreateEventHandler)
db.Events.StartEvents()
Catch ex As Exception
MsgBox("SMO ERROR : " & vbNewLine & ex.Message)
My.Computer.Clipboard.SetText(ex.Message)
End Try
End Sub
End Class
یک فرم نمونه که از کلاس استفاده می کند:
Imports System.Data.SqlClient
Imports System.Data
Public Class Form1
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
DoAsycEventraise()
RefreshGrid()
End Sub
''' <summary>
''' چون وقتی متد موجود در کلاس صدا زده می شود تا رویداد پایگاه داده ارسال شود فرم باید
''' مدتی منتظر بماند تا رویداد ریجستر شود و برنامه تاخیر در اجرا دارد
''' برای همین این متد در یک نخ جدا اجرا می شود
''' </summary>
''' <remarks></remarks>
Private Sub DoAsycEventraise()
Try
CheckForIllegalCrossThreadCalls = False
AddHandler DatabaseWatcher.TableChanged, AddressOf SQLEvent_Handler
Dim t As New Threading.Thread(AddressOf DatabaseWatcher.RegisterEvent)
t.SetApartmentState(Threading.ApartmentState.MTA)
t.Start()
Catch ex As Exception
End Try
End Sub
Private Sub SQLEvent_Handler(sender As System.Object, e As System.EventArgs)
RefreshGrid()
End Sub
Private Sub RefreshGrid()
Try
Application.DoEvents()
Dim TSQL As String = "Select * From IN_Letter"
Using Conn As New SqlConnection("Data Source=ALI-PC\ASQLSERVER;Initial Catalog=Indicator;Integrated Security=True")
Dim adp As New SqlDataAdapter(TSQL, Conn)
Dim res As New DataTable
adp.Fill(res)
adp.Dispose()
Conn.Dispose()
If res IsNot Nothing Then Me.DataGridView1.DataSource = res
End Using
Catch ex As Exception
End Try
End Sub
End Class