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.

Wally

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

No Comments