SQL Server/T-SQL Tutorial/CLR/CLR — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:24, 26 мая 2010
Содержание
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>