Hello,
the examples from MSDN like the following, don't run on my computer. A System.Data.SqlServer don't exist. No SQLCommand Type, no SQLContext.GetCommand Method and so on...
Ist there something wrong with my installation or how do i execute commands
inside a CLR SQL Server Trigger?
Regards,
Markus
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlServer
Imports System.Data.SqlTypes
Imports System.Text.RegularExpressions
Public Class EmailTrigger
Public Shared Sub EmailAudit()
Dim triggContext As SqlTriggerContext = _
SqlContext.GetTriggerContext()
Dim userName As String
If triggContext.TriggerAction = TriggerAction.Insert Then
Dim sqlComm As SqlCommand = SqlContext.GetCommand()
Dim sqlP As SqlPipe = SqlContext.GetPipe()
sqlComm.CommandText = "SELECT Username from INSERTED"
Dim dataRecord As Object = sqlComm.ExecuteScalar
userName = dataRecord.ToString
If IsValidEMailAddress(userName) Then
sqlComm.CommandText = "INSERT Emails " & _
"VALUES ('" & userName & "')"
sqlP.Send(sqlComm.CommandText)
sqlP.Execute(sqlComm)
End If
End If
End Sub
Public Shared Function IsValidEMailAddress(ByVal email As String) _
As Boolean
Return Regex.IsMatch(email, _
"^([\w-]+\.)*?[\w-]+@.[\w-]+\.([\w-]+\.)*?[\w]+$")
End Function
End Class
It seems that something like this would work:
Dim pipe As SqlPipe = SqlContext.Pipe
Dim sqlcommand As New System.Data.SqlClient.SqlCommand
sqlcommand.CommandText = _
"Update Haushaltshilfe set Wochentag = 'Montag' where Wochentag = 'Dienstag'"
pipe.ExecuteAndSend(sqlcommand)
sqlcommand.CommandText = "Select * from inserted"
pipe.ExecuteAndSend(sqlcommand)
But how do i get the results?
Is it really so that the examples from msdn with System.Data.SQLServer don't work?|||The reason why it's not working for you is because the example you're trying is old, and they have now merged the old SqlServer provider for System.Data.SqlServer with the client side provider and created a new namespace: Microsoft.SqlServer.Server. In addition some of the methods on the SqlContext object have been replaced with properties.
Replace the Import System.Data.SqlServer with Import Microsoft.SqlServer.Server and do an extra import for System.Data.SqlClient. Then in the code where you say GetTriggerContext, use the propery TriggerContext instead. you also need to create a connection: Dim sqlConn as SqlConnection = new SqlConnection("Context Connection=true") and the open the connection. From the connection you can create the command: sqlConn.CreateCommand. Instead of GetPipe you use the Pipe property and finally you call ExecuteAndSend on the Pipe with your command as param.
Niels
|||Thank you very much Niels!
Are there web-pages where the latest changes in VS 2005 and SQL Server are documented?|||You can check Bob B's blog and mine. We're trying keep track of what's changing.
