Is it possible to have an event raised from a SQL CLR VB trigger,and manage it on another vb.net app?
I would like to have my vb.net app show a message (for instance) every time a record is added to SQL. I explored CLR ...seems to work but have a problem...
Actually, the trigger fires well when doing a SQL insert made by pushing the button on the form of my app, and i'm sure of this because i made some debug, but seems not to raise the event or the event can't be seen by my app, event if the DLL is imported and the class is ok.
The Trigger DLL registered on SQL is simply
Partial Public Class Trig2VBNET
Public Shared Event NewAlarm()
<SqlTrigger(Name:="Trig2VBNET", Target:="ListenKFT", Event:="FOR INSERT")> _
Public Shared Sub subNewAlarm()
RaiseEvent NewAlarm()
End Sub
End Class
while my app is:
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports Trig2VBNET
Public Class Form1
Private Sub OnNewAlarm()
MsgBox("new alarm")
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load
AddHandler Trig2VBNET.newAlarm, AddressOf OnNewAlarm
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim Sql As String = _
"INSERT INTO [dbo].[Alarms] ([Group], [Name], [RECEIVE_DATETIME], [MESSAGE], [ACCEPT_DATETIME], [ID_ALLARM], [ID_OPERATOR])" & _
"VALUES (N'MECK', N'PIPPO01', N'11081218035200', N'ALLARME : Polling;', N'110812180352', 1807332, 0)"
Dim SourceSQLConn As New SqlConnection("Data Source=10.0.0.136;Initial Catalog=ListenKFT;User Id=sa ;Password=cippa")
Using SourceSQLConn
SourceSQLConn.Open()
Dim SQLcmd As New SqlCommand(Sql, SourceSQLConn)
SQLcmd.ExecuteNonQuery()
End Using
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Trig2VBNET.subNewAlarm()
End Sub
End Class
The strange thing is that if i push button2...the OnNewAlarm fires, while if i push button1 (SQL insert) the trigger fires internally in SQL but the vb rooutine associated, that is OnNewAlarm doesn't fires....why?
Does anyone know what is the problem and how to solve?
I would like to have my vb.net app show a message (for instance) every time a record is added to SQL. I explored CLR ...seems to work but have a problem...
Actually, the trigger fires well when doing a SQL insert made by pushing the button on the form of my app, and i'm sure of this because i made some debug, but seems not to raise the event or the event can't be seen by my app, event if the DLL is imported and the class is ok.
The Trigger DLL registered on SQL is simply
Partial Public Class Trig2VBNET
Public Shared Event NewAlarm()
<SqlTrigger(Name:="Trig2VBNET", Target:="ListenKFT", Event:="FOR INSERT")> _
Public Shared Sub subNewAlarm()
RaiseEvent NewAlarm()
End Sub
End Class
while my app is:
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports Trig2VBNET
Public Class Form1
Private Sub OnNewAlarm()
MsgBox("new alarm")
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load
AddHandler Trig2VBNET.newAlarm, AddressOf OnNewAlarm
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim Sql As String = _
"INSERT INTO [dbo].[Alarms] ([Group], [Name], [RECEIVE_DATETIME], [MESSAGE], [ACCEPT_DATETIME], [ID_ALLARM], [ID_OPERATOR])" & _
"VALUES (N'MECK', N'PIPPO01', N'11081218035200', N'ALLARME : Polling;', N'110812180352', 1807332, 0)"
Dim SourceSQLConn As New SqlConnection("Data Source=10.0.0.136;Initial Catalog=ListenKFT;User Id=sa ;Password=cippa")
Using SourceSQLConn
SourceSQLConn.Open()
Dim SQLcmd As New SqlCommand(Sql, SourceSQLConn)
SQLcmd.ExecuteNonQuery()
End Using
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Trig2VBNET.subNewAlarm()
End Sub
End Class
The strange thing is that if i push button2...the OnNewAlarm fires, while if i push button1 (SQL insert) the trigger fires internally in SQL but the vb rooutine associated, that is OnNewAlarm doesn't fires....why?
Does anyone know what is the problem and how to solve?