SQL Server CLR 极速入门，启用、设计、部署、运行

最新推荐文章于 2022-10-06 11:38:30 发布
最新推荐文章于 2022-10-06 11:38:30 发布
阅读量857 收藏
点赞数
分类专栏： SQL Server 文章标签： sql server 正则表达式 string search input 数据库

原文：http://www.yongfa365.com/Item/SQL-Server-CLR.html

 

首先总结：SQL Server CLR 设计简单，部署方便，效率很高，很安全，随数据库移动。
测试用例，视频演示在此下载：http://download.csdn.net/source/2279319
环境：SQL Server 2005/2008，Visual Studio 2005/2008

在SQL Server里启用CLR：

在SQL Server里执行以下命名，来启用CLR

view plain copy to clipboard print ?
  1. exec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr
  2. reconfigure 
exec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr
reconfigure

用Visual Studio设计功能，并部署到相关数据库：

打开Visual Studio-->新建项目-->数据库-->SQL Server项目-->添加数据库引用里新建链接（一会将会把CLR部署到这个数据库上）-->右击解决方案，添加“用户自定义函数”
这时，系统会生成一个示例文件 Function1.cs 内容：

view plain copy to clipboard print ?
  1. using System;
  2. using System.Data;
  3. using System.Data.SqlClient;
  4. using System.Data.SqlTypes;
  5. using Microsoft.SqlServer.Server;
  7. public partial class UserDefinedFunctions
  8. {
  9. [Microsoft.SqlServer.Server.SqlFunction]
  10. public static SqlString Function1()
  11. {
  12. // 在此处放置代码
  13. return new SqlString("Hello");
  14. }
  15. }; 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Function1()
    {
        // 在此处放置代码
        return new SqlString("Hello");
    }
};

现在可以直接右击解决方案，选择“部署”，状态栏里显示“部署已成功”

在SQL Server里执行我们部署的CLR函数：

再次进入SQL Server,进入到相关数据库，执行 Select dbo.Function1(),全显示执行结果："Hello"
这个函数你可以在 “数据库-->可编程性-->函数-->标量值函数” 里看到

OK,这就是整个流程，Very Easy.

更高级应用：

当然我们用CLR 不是只为了让他生成一个Hello就完事的，这里来说明一下柳永法(yongfa365)的用途：

去年给公司设计了个OA系统，公司的一些文件内容都非常长，所以选择了varchar(max),初期感觉查询速度还挺快，后来觉得越来越慢。

初步分析结果显示：

  1. 数据有近8000条
  2. 有3000多条数据len(txtContent)得到结果在4000字符以上
  3. 使用“数据库引擎优化顾问”，对其优化提速为"0%"
  4. SQL语句类似：SELECT * FROM dbo.Articles WHERE txtContent LIKE '%柳永法%'
  5. 以前做过的所有系统，从没有遇到这种问题

近一步分析结果：

  1. 数据条数很少，速度却这么慢，分析可能是数据库引擎问题 换台机器试问题依旧，排除
  2. like效率问题，以前的系统都是条数多，而这次遇到的是每条数据里字段内容很长，like除了在数据条数大时会出现性能问题外，还跟每条的字段内容长度有关。在网上查询并测试确认，确实是数据内容长度问题，而这个系统里是不可能使用 like '柳永法%'这样可以使用索引的查询的。

想来想去只能是使用全文索引，但总会有一些记录查不出来,而这个要求就这么高，所以暂时放弃。这时想到了SQL Server CLR,以前只是听过，觉得可能有用，都收藏了起来，现在打开Chrome,把Google Bookmark上收藏的关于SQL Server的CLR的链接全部打开研究了几分钟，自己写了个函数，部署，测试，哈哈……。忒玄妙了，以前的txtContent LIKE '%柳永法%'用时10到12秒，而用我写的SQL Server CLR函数dbo.ContainsOne(txtContent,'柳永法')=1只用了1秒左右，够神奇吧。

执行以下语句三次，相当于8年后数据量，有6万多条数据

view plain copy to clipboard print ?
  1. INSERT dbo.Articles (txtTitle ,txtContent) SELECT txtTitle , txtContent FROM dbo.Articles 
INSERT dbo.Articles (txtTitle ,txtContent) SELECT txtTitle , txtContent  FROM dbo.Articles

再执行测试，一般的 like用时82秒，而clr用时5秒，够有看头吧。

函数及测试语句如下：

view plain copy to clipboard print ?
  1. [Microsoft.SqlServer.Server.SqlFunction]
  2. public static SqlBoolean ContainsOne(SqlChars input, string search)
  3. {
  4. return new string(input.Value).Contains(search);
  5. }
  7. SELECT COUNT(*) FROM dbo.Articles WHERE dbo.ContainsOne(txtContent,'柳永法')=1 
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean ContainsOne(SqlChars input, string search)
{
    return new string(input.Value).Contains(search);
}

SELECT COUNT(*) FROM dbo.Articles WHERE dbo.ContainsOne(txtContent,'柳永法')=1

另外，我比较热衷于正则表达式，所以我还想给SQL Server增加一个正则表达式替换的功能，写起来也非常容易：
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
{
return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);
}

娃哈哈，一切都这么的顺利，这么的得心应手，怎能不让我推荐,在此贴上我写的一此函数：

view plain copy to clipboard print ?
  1. using System;
  2. using System.Data;
  3. using System.Data.SqlClient;
  4. using System.Data.SqlTypes;
  5. using Microsoft.SqlServer.Server;
  6. using System.Text.RegularExpressions;
  7. using System.Collections.Generic;
  8. using System.IO;
  10. /*
  11. 请先在SQL Server里执行以下命名，来启用CLR
  12. exec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr
  13. reconfigure
  14. */
  16. public partial class UserDefinedFunctions
  17. {
  18. /// <summary>
  19. /// SQL CLR 使用正则表达式替换，eg:
  20. /// select dbo.RegexReplace('<span>柳永法http://www.yongfa365.com/</span>','<.+?>','')
  21. /// update Articles set txtContent=dbo.RegexReplace(txtContent,'<.+?>','')
  22. /// --结果：柳永法http://www.yongfa365.com/
  23. /// </summary>
  24. /// <param name="input">源串，或字段名</param>
  25. /// <param name="pattern">正则表达式</param>
  26. /// <returns>替换后结果</returns>
  27. [Microsoft.SqlServer.Server.SqlFunction]
  28. public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
  29. {
  30. return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);
  31. }
  36. /// <summary>
  37. /// SQL CLR 使用正则表达式替换，eg:
  38. /// select dbo.RegexSearch('<span>柳永法</span>','<.+?>','')
  39. /// select * from Articles where dbo.RegexSearch(txtContent,'柳永法')=1;
  40. /// </summary>
  41. /// <param name="input">源串，或字段名</param>
  42. /// <param name="pattern">正则表达式</param>
  43. /// <returns>查询结果，1,0</returns>
  44. [Microsoft.SqlServer.Server.SqlFunction]
  45. public static SqlBoolean RegexSearch(SqlChars input, string pattern)
  46. {
  47. return Regex.Match(new string(input.Value), pattern, RegexOptions.Compiled).Success;
  48. }
  50. /// <summary>
  51. /// SQL CLR 使用.net的Contains查找是否满足条件,eg:
  52. /// select dbo.ContainsOne('我是柳永法，','柳永法');
  53. /// select * from Articles where dbo.ContainsOne(txtContent,'柳永法')=1;
  54. /// </summary>
  55. /// <param name="input">源串，或字段名</param>
  56. /// <param name="search">要搜索的字符串</param>
  57. /// <returns>返回是否匹配,1,0</returns>
  58. [Microsoft.SqlServer.Server.SqlFunction]
  59. public static SqlBoolean ContainsOne(SqlChars input, string search)
  60. {
  61. return new string(input.Value).Contains(search);
  62. }
  65. /// <summary>
  66. /// SQL CLR 使用.net的Contains查找是否满足其中之一的条件,eg:
  67. /// select dbo.ContainsAny('我是柳永法，','柳,永,法');
  68. /// select * from Articles where dbo.ContainsAny(txtContent,'柳,永,法')=1;
  69. /// </summary>
  70. /// <param name="input">源串，或字段名</param>
  71. /// <param name="search">要搜索的字符串，以","分隔，自己处理空格问题</param>
  72. /// <returns>返回是否匹配,1,0</returns>
  73. [Microsoft.SqlServer.Server.SqlFunction]
  74. public static SqlBoolean ContainsAny(SqlChars input, string search)
  75. {
  76. string strTemp=new string(input.Value);
  77. foreach (string item in search.Split(','))
  78. {
  79. if (strTemp.Contains(item))
  80. {
  81. return true;
  82. }
  83. }
  84. return false;
  85. }
  87. /// <summary>
  88. /// SQL CLR 使用.net的Contains查找是否满足所有的条件,eg:
  89. /// select dbo.ContainsAll('我是柳永法，','柳,永,法');
  90. /// select * from Articles where dbo.ContainsAll(txtContent,'柳,永,法')=1;
  91. /// </summary>
  92. /// <param name="input">源串，或字段名</param>
  93. /// <param name="search">要搜索的字符串，以","分隔，自己处理空格问题</param>
  94. /// <returns>返回是否匹配,1,0</returns>
  95. [Microsoft.SqlServer.Server.SqlFunction]
  96. public static SqlBoolean ContainsAll(SqlChars input, string search)
  97. {
  98. string strTemp = new string(input.Value);
  99. foreach (string item in search.Split(','))
  100. {
  101. if (!strTemp.Contains(item))
  102. {
  103. return false;
  104. }
  105. }
  106. return true;
  107. }
  109. }; 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.IO;

/*
请先在SQL Server里执行以下命名，来启用CLR
exec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr
reconfigure
*/

public partial class UserDefinedFunctions
{
    /// <summary>
    /// SQL CLR 使用正则表达式替换，eg:
    /// select dbo.RegexReplace('<span>柳永法http://www.yongfa365.com/</span>','<.+?>','')
    /// update Articles set txtContent=dbo.RegexReplace(txtContent,'<.+?>','')
    /// --结果：柳永法http://www.yongfa365.com/
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="pattern">正则表达式</param>
    /// <returns>替换后结果</returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
    {
        return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);
    }




    /// <summary>
    /// SQL CLR 使用正则表达式替换，eg:
    /// select dbo.RegexSearch('<span>柳永法</span>','<.+?>','')
    /// select * from Articles where dbo.RegexSearch(txtContent,'柳永法')=1;
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="pattern">正则表达式</param>
    /// <returns>查询结果，1,0</returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean RegexSearch(SqlChars input, string pattern)
    {
        return Regex.Match(new string(input.Value), pattern, RegexOptions.Compiled).Success;
    }

    /// <summary>
    /// SQL CLR 使用.net的Contains查找是否满足条件,eg:
    /// select dbo.ContainsOne('我是柳永法，','柳永法');
    /// select * from Articles where dbo.ContainsOne(txtContent,'柳永法')=1;
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="search">要搜索的字符串</param>
    /// <returns>返回是否匹配,1,0</returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean ContainsOne(SqlChars input, string search)
    {
        return new string(input.Value).Contains(search);
    }


    /// <summary>
    /// SQL CLR 使用.net的Contains查找是否满足其中之一的条件,eg:
    /// select dbo.ContainsAny('我是柳永法，','柳,永,法');
    /// select * from Articles where dbo.ContainsAny(txtContent,'柳,永,法')=1;
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="search">要搜索的字符串，以","分隔，自己处理空格问题</param>
    /// <returns>返回是否匹配,1,0</returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean ContainsAny(SqlChars input, string search)
    {
        string strTemp=new string(input.Value);
        foreach (string item in search.Split(','))
        {
            if (strTemp.Contains(item))
            {
                return true;
            }
        }
        return false;
    }

    /// <summary>
    /// SQL CLR 使用.net的Contains查找是否满足所有的条件,eg:
    /// select dbo.ContainsAll('我是柳永法，','柳,永,法');
    /// select * from Articles where dbo.ContainsAll(txtContent,'柳,永,法')=1;
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="search">要搜索的字符串，以","分隔，自己处理空格问题</param>
    /// <returns>返回是否匹配,1,0</returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean ContainsAll(SqlChars input, string search)
    {
        string strTemp = new string(input.Value);
        foreach (string item in search.Split(','))
        {
            if (!strTemp.Contains(item))
            {
                return false;
            }
        }
        return true;
    }

};

重要提示：

  1. 官方说明里有其dll部署方法，比较麻烦，推荐直接用Visual Studio部署，方便快捷。
  2. SQL Server CLR 部署到某个数据库后，便成为那个数据库的一部分，即便备份及还原到其它机器上，它依然具有CLR带来的功能。
  3. SqlString 数据类型转换成 nvarchar(4,000)，而 SqlChars 转换成 nvarchar(max)。尽可能使用 nvarchar(max) 并且最大程度地保证灵活性。然而，如果所有相关字符串包含的字符都少于 4,000 个，使用 nvarchar(4,000) 则性能可得到显著改善。
  4. CLR里返回的bool对应SQL Server里的bit,即：1/0/Null,而不是true/false,所以，没法直接用dbo.ContainsOne(txtContent,'柳永法')实现bool形，而得这么用：dbo.ContainsOne(txtContent,'柳永法')=1


参考：
SQL Server CLR 集成简介：http://msdn.microsoft.com/zh-cn/library/ms254498(VS.80).aspx
SQL Server 2005 正则表达式使模式匹配和数据提取变得更容易：http://msdn.microsoft.com/zh-cn/magazine/cc163473.aspx
SQLCLR（一）入门：http://www.cnblogs.com/DavidFan/archive/2007/05/08/738557.html
应用C#和SQLCLR编写SQL Server用户定义函数：http://blog.csdn.net/zhzuo/archive/2009/05/24/4212982.aspx#mark4

SQL Server CLR 极速入门，启用、设计、部署、运行 视频教程及相关代码
04-24
/// <summary> /// SQL CLR 使用正则表达式替换，eg: /// select dbo.RegexReplace('<span>柳永法</span>','<.+?>','') /// update Articles set txtContent=dbo.RegexReplace(txtContent,'<.+?>','') /// --结果：柳永法 /// </summary> /// <param name="input">源串，或字段名</param> /// <param name="pattern">正则表达式</param> /// <returns>替换后结果</returns> /// <summary> /// SQL CLR 使用.net的Contains查找是否满足条件,eg: /// select dbo.ContainsOne('我是柳永法，','柳永法'); /// select * from Articles where dbo.ContainsOne(txtContent,'柳永法')=1; /// </summary> /// <param name="input">源串，或字段名</param> /// <param name="search">要搜索的字符串</param> /// <returns>返回是否匹配,1,0</returns> 相关介绍： http://www.yongfa365.com/Item/SQL-Server-CLR.html
SQL Server CLR 极速入门,启用、设计、部署、运行
yx717yx
07-06 206
　　原文：http://www.yongfa365.com/Item/SQL-Server-CLR.html 　　首先总结：SQL Server CLR 设计简单，部署方便，效率很高，很安全，随数据库移动。 　　测试用例，视频演示在此下载：http://download.csdn.net/source/2279319 　　环境：SQL Server 2005/2008，Visual Stu...
.NET框架源码解读之启动CLR
weixin_30772261的博客
02-25 165
前面提到在SSCLI环境里运行.NET程序的时候，执行的命令类似java程序的执行过程，即通过clix程序解释执行.net程序。这个过程看起来跟在windows环境下执行.net程序表面上看起来不一样 – Windows环境下的CLR直接执行.net程序文件即可执行，其实内部运作机制是一样的，在后文我会讲解到。 首先我们先来解读下clix的源码，其源码位置位于：clr\src\tools\cli...
SQL Server安全基线
个人博客
12-29 1790
SQL Server安全基线 支持SQL Server 2012 访问控制 1.高危 -确保Sysadmin角色中所有经过SQL身份验证的登录名的“ CHECK_EXPIRATION”选项都设置为“ ON” 描述: 将Windows中使用的相同密码过期策略应用于SQL Server内部使用的密码。 确保SQL登录名符合Windows Server Benchmark所应用的安全密码策略，将确保频繁更改具有sysadmin特权的SQL登录名的密码，以帮助防止通过蛮力攻击造成的损害。 CONTROL SER
Microsoft sql server system clr types 2012
03-31
标题“Microsoft SQL Server System CLR Types 2012”指的是微软SQL Server数据库管理系统的一个关键组件，主要用于处理与.NET Framework交互的系统级CLR（Common Language Runtime）类型。在SQL Server中，CLR集成...
Microsoft System CLR Types for Microsoft SQL Server 2014
03-27
对于64位版本的"System CLR Types for SQL Server 2014"，它专为在64位操作系统上运行的SQL Server实例设计。这意味着它能够处理更大的数据集，利用更多的系统内存，并且在处理大规模计算时能提供更好的性能。 描述...
SQL SERVER CLR 讲解及小例
02-06
- 启用CLR集成：在SQL Server Management Studio中，通过“服务器属性”->“安全性”->“ clr整合”启用CLR集成，并设置为“启用”。 - 设置信任的assembly级别：可以设置为“外部程序集”或“安全的”，前者允许...
将SQL Server CLR存储过程安装为十六进制字符串
04-07
这可以通过创建CLR（Common Language Runtime）存储过程实现，它允许使用C#、VB.NET等.NET语言编写存储过程，然后在SQL Server环境中执行。本主题将深入探讨如何将一个.NET程序集编码为十六进制字符串，以便于在SQL ...
MSSQL之二十二 CLR及SQL
张晨光老师的播客
09-07 1762
在SQL Server中使用CLR调用.NET方法介绍我们一起来做个示例，在.NET中新建一个类，并在这个类里新建一个方法，然后在SQL Server中调用这个方法。按照微软所述，通过宿主 Microsoft .NET Framework 2.0 公共语言运行库 (CLR)，SQL Server 2005显著地增强了数据库编程模型。 这使得开发人员可以用任何CLR语言（如C#、VB.NET或C+
Microsoft System CLR Types for Microsoft SQL Server 2012 SQLSysClrTypes.msi
11-09
搭建WSUS使用报告功能，需要安装 MICROSOFT REPORT VIEWER 2012 的 ReportViewer.msi和 Microsoft System CLR Types for Microsoft SQL Server 2012 的 SQLSysClrTypes.msi ，找了一圈获得能用的，好东西分享一下。
SQL server CLR命令执行
Purpose_7的博客
08-23 931
SQL server CLR命令执行
SQL Server CLR全攻略
u011916661的专栏
09-01 1380
CLR介绍和配置 Microsoft SQL Server 现在具备与 Microsoft Windows .NET Framework 的公共语言运行时 (CLR) 组件集成的功能。CLR 为托管代码提供服务，例如跨语言集成、代码访问安全性、对象生存期管理以及调试和分析支持。对于 SQL Server 用户和应用程序开发人员来说，CLR集成意味着您现在可以使用任何.NET Framework
SQL Server CLR 启用、部署
草宝虫的博客
12-10 741
sqlserver启用CLR exec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr RECONFIGURE SQL Server CLR 部署到某个数据库后，便成为那个数据库的一部分，即便备份及还原到其它机器上，它依然具有CLR带来的功能。 SqlString 数据类型转换成 nvarchar(4,000)，而 SqlChars ...
如何启用clr enabled配置选项
热门推荐
killkill
04-24 1万+
 错误信息如下： 禁止在.Net Framewrok中执行用户代码.启用"clr enabled"配置选项解决方案：--在Sql Server中执行这段代码可以开启CLRexec sp_configure show advanced options, 1;goreconfigure;goexec sp_configure clr enabled, 1goreconfi
禁止在 .NET Framework 中执行用户代码。启用 "clr enabled" 配置选项。
icefireliu的专栏
11-03 3070
出现如下问题"消息 6263，级别 16，状态 1，第 1 行禁止在 .NET Framework 中执行用户代码。启用 "clr enabled" 配置选项。"解决方法:在查询分析其中执行如下代码:exec sp_configure show advanced options, 1;goreconfigure;goexec sp_configure clr enable
C#入门级-使用VS2022编写C#（包括WPF，对象属性赋值、x名称空间介绍）——商场收银系统
dlwlrma_516的博客
10-06 5763
C#入门级-使用VS2022编写C#（包括WPF）——商场收银系统
sqlserver 2008 开启CLR
weixin_34163741的博客
08-09 238
Common language runtime (CLR) 特性支持在sql server中编写和执行.net的存储过程、触发器、和函数但是要想执行CLR代码，首先要开启CLR特性 1.查看CLR特性是否开启 select * from sys.configurations where name='clr enabled'; 　　 value_in_use表明没有开启“clr enabled...
SQL Server实现CLR步骤及其需要配置注意的问题（转）
清竹贮香
08-06 8800
介绍 我们一起来做个示例，在.NET中新建一个类，并在这个类里新建一个方法，然后在SQL Server中调用这个方法。按照微软所述，通过宿主 Microsoft .NET Framework 2.0 公共语言运行库 (CLR)，SQL Server 2005显著地增强了数据库编程模型。 这使得开发人员可以用任何CLR语言（如C#、VB.NET或C++等）来写存储过程、触发器和用户自定义函数。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值