sql server clr user defined functions

需要可以在sql server中直接批次更改使用者密碼的function

利用既有的加解密function,在visual studio新建一個database project ,稍微改一下function把他變成 Microsoft.SqlServer.Server.SqlFunction()

function 寫法可以參考 msdn上的範例

寫好後有二個方式可以放到sql server上
1.直接在vs project 中,按右鍵選 deploy, 會將有所有帶 Microsoft.SqlServer.Server.SqlFunction() 的function都部署到sql server, 這個方式比較適合開發測試用,按一下就可以直接用了.

2.把compile好的.dll檔 複製到sql server主機的任一路徑下,執行下列scripts.

--Step 1, congfigure database
USE [tempDB]
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

--Step 2, drop depency objects
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[zfnEncryption]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [zfnEncryption]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[zfnDecryption]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [zfnDecryption]
GO

--Step 3. create assembly
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'sqlUtility')
DROP ASSEMBLY [sqlUtility]
GO
CREATE ASSEMBLY sqlUtility
FROM 'c:\test\sqlUtility.dll' -- depends on what server you are in
WITH PERMISSION_SET = safe
GO

--Step 4, create clr function
--Step 4.1, Encryption function
CREATE FUNCTION zfnEncryption(@pwd nvarchar(128))
returns nvarchar(128)
as EXTERNAL NAME [sqlUtility].[sqlUtility.UserDefinedFunctions].[Encryption]
GO

--Step 4.2, Decryption function
CREATE FUNCTION sr.zfnDecryption(@pwd nvarchar(128))
returns nvarchar(128)
as EXTERNAL NAME [sqlUtility].[sqlUtility.UserDefinedFunctions].[Decryption]

--Step 999, test function
select userid,dbo.zfnEncryption('prefix'+ userid) as encryptionPwd
from test