CLR C#

本篇文章的主要目的就是要介绍基于CLR C#的编程与开发。这里不会从基础知识讲起，而是告诉各位读者如何搭建快速有效的开发通道，避免反复实验导致浪费很多精力和时间。至于为什么选择SQLServer，新来的读者可以参考“选择工作平台”中的相关解释。

为什么选择CLR C#，而不是采用传统的存储过程开发，或者SQLServer外接其他开发环境（例如：C++，Python，Java等等）进行开发，有如下几点原因：

（1）传统的存储过程效率低下。
以最简单的统计原始语料的汉字频次来说，1000多万条原始数据要处理好几个小时。如果采用并行处理，线程过多后则容易出现死锁。而且效率提高并不明显。

（2）SQLServer外接其他开发环境效率一样低下。
主要原因是外接数据通道需要消耗时间。由于外接环境与SQLServer无法嵌入一体，在项目维护上也会多出很多麻烦。

（3）CLR C#效率非常高，且与SQLServer非常搭配。
CLR C#内可以直接调用内存来进行统计和数学计算，然后将数据结果再存入数据库之中。由于直接内嵌在SQLServer之内，因此通讯上会节约很多时间。还是以统计原始语料的汉字频次来说，1000多万条原始数据在90秒左右就可以处理完毕，可以说优势非常明显。

当然CLR C#并非完全没有缺点，这里同样要说明如下几点：

（1）调试困难
CLR C#如果出现问题，调试几乎不可能。虽然微软给出了应对方案，但是经过实际测试，效果并不好。因此，只能借助传统的“打印输出”模式。由于CLR C#的限制，“打印输出”也不是直接调用打印输出函数，需要自己实现想办法“打印输出”。例如：把中间过程存入内存或者数据库之中，运行结束后，再去查看。

（2）内存管理问题
CLR C#函数都是全局静态函数和对象。SQLServer运行期间会一直驻留在内存之中，即使重新注册DLL，也改变不了静态对象的状态。因此在CLR C#中所使用的内存存储，如果没必要保留，要注意及时清空。否则可能会存在很大的内存泄露隐患。

（3）安全问题
CLR C#由于直接嵌入到数据库之中，会存在安全隐患（抢占和死锁，尚未测试）。当然对于单机版用户来说，这个几乎不是问题。如果试图运行在更加复杂的环境中，安全问题是需要仔细考虑的。因此，需要将项目的安全级别调整为UNSAFE。

搭建CLR C#环境

下面简单介绍下如何搭建CLR C#环境。这里以Visual Studio 2022和SQL Server 2019为例。

（1）打开Visual Studio 2022，选择建立新项目。

在创建新项目时，直接在搜索栏里面输入SQL，就会显示出“SQL Server 数据库项目”。以此为基础建立新项目。如果发现找不到，那么检查一下Visual Studio是否有模块没有安装。

（2）新添代码文件

选择对应的项目，并新添代码文件。为了保证能够正常编译，需要补充相关的引用。

补充完相关引用后，就可以正常生成了。但是还不能发布。

（3）发布CLR C#文件

在对应项目的Debug目录中，可以找到DLL文件。需要将这个DLL文件发布到SQL Server 2019中。这个过程可以手工操作，也可以通过Visual Studio 2022发布。当然后者更加方便。

为了保证能够顺利发布，需要增加一段发布前的用户脚本。

/*
 预先部署脚本模板							
--------------------------------------------------------------------------------------
 此文件包含将在生成脚本之前执行的 SQL 语句。	
 使用 SQLCMD 语法将文件包含在预先部署脚本中。			
 示例:      :r .\myfile.sql								
 使用 SQLCMD 语法引用预先部署脚本中的变量。		
 示例:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

---开启所有服务器配置选项
EXEC sp_configure N'show advanced options', N'1' 
RECONFIGURE WITH OVERRIDE

--开启clr enabled 选项
EXEC sp_configure N'clr enabled', N'1'
RECONFIGURE WITH OVERRIDE

--关闭所有服务器配置选项
EXEC sp_configure N'show advanced options', N'0' 
RECONFIGURE WITH OVERRIDE

-- 声明临时变量
DECLARE @SqlHash AS BINARY(64);

-- 找到最近一行记录
SELECT TOP 1 @SqlHash = [hash] FROM sys.trusted_assemblies
WHERE [created_by] = 'MicrosoftAccount\********@********.com' ORDER BY [create_date] DESC;
-- 检查结果
IF @@ROWCOUNT = 1
    EXEC sp_drop_trusted_assembly @SqlHash;

-- 生成Hash
SET @SqlHash =
(
	SELECT HASHBYTES('SHA2_512',
		(SELECT * FROM OPENROWSET (BULK 'E:\******\SimpleNLDB\NLDB\bin\Debug\NLDB.dll', SINGLE_BLOB) AS [Data]))
);

-- 检查哈希
IF NOT EXISTS(SELECT TOP 1 * FROM sys.trusted_assemblies WHERE hash = @SqlHash)
BEGIN
    -- 增加信任代码
    EXEC sp_add_trusted_assembly @SqlHash
END