Datasets in your CLR objects
With Whidbey Beta 2, the ability to use Datasets in your CLR Objects within Sql Express and Sql Server 2005 has been added. Below is some sample code I wrote in Visual Basic 2005.
While the code is not something that I would do in production, it shows how the concept works.
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class Triggers
' Enter existing table or view for the target and uncomment the attribute line
' <Microsoft.SqlServer.Server.SqlTrigger(Name:="Trigger1", Target:="Table1", Event:="FOR UPDATE")> _
<SqlTrigger(Name:="trTest", Target:="tblObject", Event:="FOR INSERT, UPDATE")> _
Public Shared Sub trTest()
' Add your code here
Dim strSql As String = "select DateUpdated, tblObjectId from inserted"
Dim sqlCn As SqlConnection = New SqlConnection("Context connection=true")
Dim sqlCm As New SqlCommand()
Dim sqlPObjectId As New SqlParameter
Dim sqlDa As New SqlDataAdapter(strSql, sqlCn)
Dim dsData As New DataSet()
Try
sqlDa.Fill(dsData, "inserted")
For Each drData As DataRow In dsData.Tables("inserted").Rows
drData("DateUpdated") = DateTime.Now
Next
strSql = "update tblObject set DateUpdated=getdate() where tblObjectId=@tblObjectId"
sqlCm.CommandText = strSql
sqlCm.CommandType = CommandType.Text
sqlCm.Connection = sqlCn
sqlPObjectId.ParameterName = "@tblObjectId"
sqlPObjectId.SourceColumn = "tblObjectId"
sqlPObjectId.SqlDbType = SqlDbType.Int
sqlCm.Parameters.Add(sqlPObjectId)
sqlDa.UpdateCommand = sqlCm
sqlDa.Update(dsData.Tables("inserted"))
Finally
If sqlCn.State <> ConnectionState.Closed Then
sqlCn.Close()
End If
sqlCn.Dispose()
sqlDa.Dispose()
sqlCm.Dispose()
dsData.Dispose()
End Try
End Sub
End Class