各位下午好。
我正在处理一个使用WinForms VB.NET和MSSQL的项目,我需要使用SQLDependency来监视表中的新插入。在测试时我遇到了一个问题。启动应用程序后插入一次,我确实收到了插入了一些内容的通知,但在此之后,OnChange事件就不再被调用了。
Private Sub LoadOrder()
Dim objError As New AppFramework.Logging.EventLog
Dim objQuoOrders As New QuotationOrders.QuotationOrder
Try
objQuoOrders.Companyid = SystemCompanyId
objQuoOrders.Picker = GsUserName
objQuoOrders.InvType = "O"
ThreadSafe(Sub() gcOrders.DataSource = objQuoOrders.GetOrdersByPicker())
Catch ex As Exception
MessageBox.Show(ex.Message, "LoadOrder", MessageBoxButtons.OK, MessageBoxIcon.Error)
objError.LogError(SystemApplicationLogSource, "AceFinancials", ex)
End Try
End Sub
Private Sub ActivateDependency()
Dim objError As New AppFramework.Logging.EventLog
Try
If sqlConnection IsNot Nothing AndAlso sqlConnection.State = ConnectionState.Open Then sqlConnection.Close()
sqlConnection = New SqlConnection(AppFramework.Database.Connection.ConnectionString)
sqlConnection.Open()
SqlDependency.Stop(AppFramework.Database.Connection.ConnectionString)
SqlDependency.Start(AppFramework.Database.Connection.ConnectionString)
Using cmd As SqlCommand = New SqlCommand(" Select tb_quotation_notifications.notification_id, tb_quotation_notifications.notification_invid
From dbo.tb_quotation_notifications
Where tb_quotation_notifications.notification_picker_id = " & UserID &
" And tb_quotation_notifications.company_id = " & SystemCompanyId, sqlConnection)
Dim dependency As SqlDependency = New SqlDependency(cmd)
AddHandler dependency.OnChange, AddressOf dependency_OnChange
cmd.ExecuteNonQuery()
End Using
Catch ex As Exception
MessageBox.Show(ex.Message, "ActivateDependency", MessageBoxButtons.OK, MessageBoxIcon.Error)
objError.LogError(SystemApplicationLogSource, "AceFinancials", ex)
End Try
End Sub
Private Sub dependency_OnChange(sender As Object, e As SqlNotificationEventArgs)
Dim objError As New AppFramework.Logging.EventLog
Try
If e.Info = SqlNotificationInfo.Insert Then
NotificationManager.ShowNotification(NotificationManager.Notifications(0))
LoadOrder()
ActivateDependency()
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "dependency_OnChange", MessageBoxButtons.OK, MessageBoxIcon.Error)
objError.LogError(SystemApplicationLogSource, "AceFinancials", ex)
End Try
End Sub
Private Sub ThreadSafe(method As MethodInvoker)
Dim objError As New AppFramework.Logging.EventLog
Try
If (InvokeRequired) Then
Invoke(method)
Else
method()
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "ThreadSafe", MessageBoxButtons.OK, MessageBoxIcon.Error)
objError.LogError(SystemApplicationLogSource, "AceFinancials", ex)
End Try
End Sub
如果您需要更多信息,我将适当更新我的问题
1条答案
按热度按时间yjghlzjz1#
在丹·古兹曼的大力帮助下,我发现了问题所在。
在OnChange事件触发后,您需要删除事件处理程序,然后再次订阅该事件,这与我所做的不同,我所做的是停止SqlDependency并再次启动它。
在我的例子中,我是这样做的
更改时事件
订阅事件
多谢了祝你今天愉快。
编辑:
我移动了窗体加载函数中的SqlDependency.Start()和窗体关闭函数中的stop