通用类:
Imports System.Data.SqlClient
Public Class DataBaseMonitor
Dim _connectionstring As String = My.Settings.RecordConnectionString
Dim conn As SqlConnection
Dim _cmd As String
Dim _queue As String = String.Empty
Public Event OnChange(sender As Object, e As SqlNotificationEventArgs)
Public Property ConnectionString As String
Get
Return _connectionstring
End Get
Set(value As String)
_connectionstring = value
conn = New SqlConnection(value)
End Set
End Property
Public Property CommandText As String
Get
Return _cmd
End Get
Set(value As String)
_cmd = value
End Set
End Property
Public Property Queue As String
Get
Return _queue
End Get
Set(value As String)
_queue = value
End Set
End Property
Sub New(cmdtext As String)
ConnectionString = _connectionstring
CommandText = cmdtext
End Sub
Sub New(cmdtext As String, connstring As String)
ConnectionString = connstring
CommandText = cmdtext
End Sub
Public Sub Start()
If Queue = String.Empty Then
SqlDependency.Start(ConnectionString)
Else
SqlDependency.Start(ConnectionString, Queue)
End If
addOne()
End Sub
Private Sub dependency_OnChange(sender As Object, e As SqlNotificationEventArgs)
RaiseEvent OnChange(sender, e)
addOne()
End Sub
Private Sub addOne()
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
Dim Command As New SqlCommand(CommandText, conn)
Dim dependency = New SqlDependency(Command)
Command.ExecuteNonQuery()
AddHandler dependency.OnChange, AddressOf dependency_OnChange
End Sub
Public Sub StopMonitor()
If Queue = String.Empty Then
SqlDependency.Stop(ConnectionString)
Else
SqlDependency.Stop(ConnectionString, Queue)
End If
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
End Sub
Public Sub Dispose()
conn.Dispose()
End Sub
End Class
具体类:
Imports System.Data.SqlClient
Public Class CopyLogMonitor
Public Event OnChange(sender As Object, e As SqlNotificationEventArgs)
WithEvents monitor As DAL.DataBaseMonitor
Sub New()
Dim sql As String = "select 住院号,次数,嘉禾登记 from dbo.recordcopylogs where 复印时间>'20170101'"
monitor = New DAL.DataBaseMonitor(sql)
monitor.Start()
End Sub
Private Sub monitor_OnChange(sender As Object, e As SqlNotificationEventArgs) Handles monitor.OnChange
If e.Type = SqlNotificationType.Change Then
RaiseEvent OnChange(sender, e)
End If
End Sub
Public Sub StopMonitor()
monitor.StopMonitor()
End Sub
End Class