SQL Server/T-SQL Tutorial/CLR/CLR

Converting SQL Server to CLR Data Types

   <source lang="sql">

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</source>

Creating a CLR Scalar User-Defined Function

   <source lang="sql">

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</source>

Creating a CLR Trigger

   <source lang="sql">

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</source>

Creating the CLR Stored Procedure

   <source lang="sql">

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"</source>

--Enabling CLR Support in SQL Server 2005

   <source lang="sql">

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

Loading the Assembly Into SQL Server

   <source lang="sql">

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 } ]</source>