如何手工部署Sql Server CLR程序集

发布网友发布时间：2022-05-04 20:31

共2个回答

热心网友时间：2022-05-04 22:00

/*
1.例子,创建CLR标量函数(根据表名,统计该表的记录数)
用记事本编辑,保存为FirstUdf.cs
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
public class T
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static int ReturnOrderCount(string tName)
    {
        using (SqlConnection conn
            = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(
               string.Format(@"SELECT COUNT(*) AS Cnt FROM {0}",tName), conn);
            return (int)cmd.ExecuteScalar();
        }
    }
}
2.编译DLL
2.1打开CMD,进入.Net目录(C:\Windows\Microsoft.NET\Framework\v3.5) 输入命令
或者
2.2在该目录,创建BAT,执行即可
编译命令
csc.exe /t:library /out:FirstUdf.dll FirstUdf.cs
3.加载DLL,创建函数,查看结果
*/
CREATE ASSEMBLY FirstUdf FROM 'C:\Windows\Microsoft.NET\Framework\v3.5\FirstUdf.dll';
GO
CREATE FUNCTION Fn_GetCount(@A nVarchar(100)) RETURNS INT
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount;
GO
SELECT dbo.Fn_GetCount('apo_city');
GO