你的位置:首页 > 数据库

[数据库]SQL Server 和CLR集成


通过在 Microsoft SQL Server 中托管 CLR(称为 CLR 集成),可以在托管代码中编写存储过程、触发器、用户定义函数、用户定义类型和用户定义聚合函数。 因为托管代码在执行之前会编译为本机代码,所以,在有些方案中可以大大提高性能。此处举例的是把dll文件的16进制文件流拷贝出来案例。

1.0x...是文件XXX的十六进制流,可以使用UltraEdit等编辑器把相关dll文件的十六进制流copy出来。
GO--/****** Object: SqlAssembly [MySoftSqlFunctions]  Script Date: 02/27/2015 14:17:10 ******/IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'MySoftSqlFunctions' and is_user_defined = 1)--DROP ASSEMBLY [MySoftSqlFunctions]/****** Object: SqlAssembly [MySoftSqlFunctions]  Script Date: 02/27/2015 14:17:10 ******/BEGINCREATE ASSEMBLY [MySoftSqlFunctions]AUTHORIZATION [dbo]FROM 0x...(此处是dll十六进制流)WITH PERMISSION_SET = SAFEend --/****** Object: UserDefinedFunction [dbo].[SeqNewID]  Script Date: 02/28/2015 11:30:42 ******/IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SeqNewID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))EXEC('CREATE FUNCTION [dbo].[SeqNewID]()RETURNS [uniqueidentifier] WITH EXECUTE AS CALLERASEXTERNAL NAME [MySoftSqlFunctions].[MysoftSqlFunctions.CLRFunctions].[SeqNewID]') GOIF EXISTS ( SELECT name      FROM  sysobjects      WHERE  name = 'usp_NewSqeGUID'          AND type = 'P' )  BEGIN    DROP PROCEDURE usp_NewSqeGUID   ENDGO CREATE proc [dbo].[usp_NewSqeGUID] @NewGUID VARCHAR(50) outputas begin declare @tOut table(newguid uniqueidentifier default(NewSequentialId())); insert into @tOut default values select @NewGUID=newguid from @tOut endGOSeqNewID.sql SeqNewID.sql

 案例下载地址:http://files.cnblogs.com/files/lijiebolg/CLR%E9%9B%86%E6%88%90%E6%A1%88%E4%BE%8B.rar