forked from ktaranov/sqlserver-kit
-
Notifications
You must be signed in to change notification settings - Fork 0
/
dbo.sp_Example_RegisterAssembly_PDFCLR.sql
52 lines (44 loc) · 1.55 KB
/
dbo.sp_Example_RegisterAssembly_PDFCLR.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
CREATE PROCEDURE dbo.spExample_RegisterAssembly_PDFCLR
AS
BEGIN
DECLARE @FilePath varchar(1024)
SET @FilePath = 'c:\ServerEnvironment\'
CREATE TABLE #References (AssemblyName sysname, FQFileName varchar(1024))
INSERT INTO #References (AssemblyName, FQFileName) VALUES ('System.Drawing', 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll')
INSERT INTO #References (AssemblyName, FQFileName) VALUES ('itextsharp', @FilePath + 'itextsharp.dll')
DECLARE @DropWrapperSQL varchar(MAX)
SET @DropWrapperSQL = '
IF OBJECT_ID(''dbo.udfRenderPDF'') IS NOT NULL BEGIN
DROP FUNCTION dbo.udfRenderPDF;
END
'
DECLARE @CreateWrapperSQL varchar(MAX)
SET @CreateWrapperSQL = '
CREATE FUNCTION [dbo].[udfRenderPDF](
@TemplatePDF varbinary(MAX),
@FieldsXML xml
)
RETURNS [varbinary](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PDFCLR].[Functions].[RenderPDF]
'
--C# Source Code.
--Paste CLR source in below. Replace all occurrences a single quote with two single quotes.
DECLARE @SourceCode nvarchar(MAX)
SET @SourceCode = '
//------start of CLR Source------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
....Rest of C# source code goes here
//------end of CLR Source------ '
EXEC dbo.spsysBuildCLRAssembly
@AssemblyName = 'PDFCLR',
@FileName = 'PDFCLR_SQLCLR.cs',
@FilePath = @FilePath,
@DropWrapperSQL = @DropWrapperSQL,
@CreateWrapperSQL = @CreateWrapperSQL,
@SourceCode = @SourceCode
END