SQL Server/T-SQL Tutorial/CLR/CLR

Converting SQL Server to CLR Data Types

CLR Data Type(s)      SQL Server Data Type(s)
SqlBytes              varbinary, binary
SqlBinary             varbinary, binary
SqlChars              nvarchar, nchar
SqlString             nvarchar, nchar
SqlGuid               uniqueidentifier
SqlBoolean            bit
SqlByte               tinyint
SqlInt16              smallint
SqlInt32              int
SqlInt64              bigint
SqlMoney              smallmoney, money
SqlDecimal            decimal, numeric
SqlSingle             real
SqlDouble             float
SqlDateTime           smalldatetime, datetime
SqlXml                xml

Creating a CLR Scalar User-Defined Function

CREATE ASSEMBLY SQLArrayBuilder FROM "C:\SQLArrayBuilder.DLL"
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION dbo.CountSalesOrderHeader
(@ArrayString nvarchar(400), 
 @ArrayDelimiter nchar(1), 
 @ArrayItemSelection smallint)
RETURNS nvarchar(400)
AS
EXTERNAL NAME SQLArrayBuilder.SQLArrayBuilder.ChooseValueFromArray
GO
SELECT dbo.CountSalesOrderHeader
("B,S", ",", 3) Choice
SELECT dbo.CountSalesOrderHeader
("B D", ",", 2) Choice
///////////////////
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Text.RegularExpressions
Public Class SQLArrayBuilder
    Public Shared Function ChooseValueFromArray(ArrayString as String, ArrayDelimiter as String, ArrayItemSelection as SqlInt16) as SqlString
    
    Dim NewArrayString as String() = Regex.Split(ArrayString, ArrayDelimiter)
    Dim NewArrayItemSelection as SqlInt16=ArrayItemSelection-1
    Dim ReturnString as SQLString = NewArrayString(NewArrayItemSelection)
    Return ReturnString
    
    End Function
End Class

Creating a CLR Trigger

CREATE TABLE dbo.MyAudit
(MyAuditID int
NOT NULL IDENTITY(1,1) PRIMARY KEY ,
LoadDT datetime NOT NULL)
CREATE ASSEMBLY YourTrigger
FROM "C:\YourTrigger.dll"
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
CREATE TRIGGER dbo.trg_i_DWNotify
ON dbo.MyAudit AFTER INSERT
AS
EXTERNAL NAME
YourTrigger.[YourTrigger.DW_Trigger].ExportFile
INSERT dbo.MyAudit
(LoadDT)
VALUES(GETDATE())
///////////
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.IO
Public Class DW_Trigger
    Public Shared Sub ExportFile()
        Dim DWTrigger As SqlTriggerContext
        DWTrigger = SqlContext.TriggerContext
        If (DWTrigger.TriggerAction = TriggerAction.Insert) Then
            Dim DWsw As StreamWriter = New StreamWriter("C:\test.txt")
            DWsw.WriteLine(Now())
            DWsw.Close()
        End If
    End Sub
End Class

Creating the CLR Stored Procedure

vbc /t:library /out:C:\ReadFiles.DLL /r:"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlaccess.dll" C:\ReadFiles.vb"
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
Public Class ReadFiles
    Public Shared Sub Main(ByVal sFile As SqlString)
        Dim sReader As StreamReader = New StreamReader(sFile)
        Dim sLine As String
        Dim sPipe As SqlPipe = SqlContext.Pipe
        Do
            sLine = sReader.ReadLine()
            If Not sLine Is Nothing Then
                sPipe.Send(sLine)
            End If
        Loop Until sLine Is Nothing
        sReader.Close()
    End Sub
End Class
////////////////////
CREATE PROCEDURE dbo.usp_FileReader
(@FileName nvarchar(1024))
AS EXTERNAL NAME ReadFiles.ReadFiles.Main
GO
EXEC dbo.usp_FileReader
N"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG.1"

--Enabling CLR Support in SQL Server 2005

EXEC sp_configure "clr enabled", 1
RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE BookStore
SET TRUSTWORTHY ON

Loading the Assembly Into SQL Server

CREATE ASSEMBLY assembly_name
[ AUTHORIZATION owner_name ]
FROM { "[\\computer_name\]share_name\[path\]manifest_file_name"
  | "[local_path\]manifest_file_name"|
{ varbinary_literal | varbinary_expression }}
[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]