打开 CLR 开关
exec sp_configure 'clr enabled',1
reconfigure
go
诺程序集有Framework 的其他引用则需要为所引用的dll
如:
using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Management;
using System.Runtime.InteropServices;
则需要:
CREATE ASSEMBLY asmHelloWorld FROM 'C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/System.Management.dll'
WITH PERMISSION_SET = UNSAFE
采用非安全模式验证(注意)
然后再创建要用的程序集
create ASSEMBLY asmHelloWorld1 FROM 'D:/Program.dll'
WITH PERMISSION_SET = UNSAFE
接下来就可以创建SQL函数关联dll中的函数了:
CREATE FUNCTION dbo.MacID ( )
RETURNS nvarchar(200)
AS EXTERNAL NAME asmHelloWorld1.[Hardware.HardwareInfo].GetMacID --命名空间、类名、函数名
调用一下试试:
SELECT dbo.MacID();
附录:dll函数
//取MAC编号
public static String GetMacID()
{
try
{
ManagementClass mcMAC = new ManagementClass("Win32_NetworkAdapterConfiguration");
ManagementObjectCollection mocMAC = mcMAC.GetInstances();
String strMacID = null;
foreach (ManagementObject m in mocMAC)
if ((bool)m["IPEnabled"])
{
strMacID = m["MacAddress"].ToString();
break;
}
return strMacID;
}
catch
{
return "";
}
}