в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:24:44Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/CLR/CLR - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия