程序博客网 > 夏普s2 知乎

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

来源：互联网 发布：夏普s2 知乎 编辑：程序博客网 时间：2024/04/30 10:50

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

在SQL Server里启用CLR：

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

view plaincopy to clipboardprint?
  1. exec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr   
  2. reconfigure  

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

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

view plaincopy to clipboardprint?
  1. using System;   
  2. using System.Data;   
  3. using System.Data.SqlClient;   
  4. using System.Data.SqlTypes;   
  5. using Microsoft.SqlServer.Server;   
  6.   
  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. };  

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

在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 plaincopy to clipboardprint?
  1. INSERT dbo.Articles (txtTitle ,txtContent) SELECT txtTitle , txtContent  FROM dbo.Articles  

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

函数及测试语句如下：
 

view plaincopy to clipboardprint?
  1. [Microsoft.SqlServer.Server.SqlFunction]   
  2. public static SqlBoolean ContainsOne(SqlChars input, string search)   
  3. {   
  4.     return new string(input.Value).Contains(search);   
  5. }   
  6.   
  7. 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 plaincopy to clipboardprint?
  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;   
  9.   
  10. /*   
  11. 请先在SQL Server里执行以下命名，来启用CLR   
  12. exec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr   
  13. reconfigure   
  14. */   
  15.   
  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.     }   
  32.   
  33.   
  34.   
  35.   
  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.     }   
  49.   
  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.     }   
  63.   
  64.   
  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.     }   
  86.   
  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.     }   
  108.   
  109. };  

重要提示：

  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

夏普s2 知乎 夏普s2 知乎
原创粉丝点击
热门IT博客
淘宝的会员名是什么淘宝的会员名是什么
定制家具 知乎定制家具 知乎
南京市秦淮区网络问政
网络兼职正规赚钱平台
mysql连接池参数
时间序列相似性算法
centos查看端口占用
11月阿里云推荐码
sqlserver如何创建表
河北11选5遗漏数据查询
js 数组里添加对象
四级单词一笑而过软件
unity3d编程教程
打印发票边距调整数据
女尊小说软件
报价驱动市场 知乎
湖南大学 人工智能
xbox怎么玩 知乎
派驻纪检监察组 知乎
js设置只读
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 歌曲下辈子不做女人 下辈子如果我还记得你歌词 因为我不知道下辈子 下辖 76集团军下辖几个旅 71集团军下辖几个旅 三下五除二造句 用三下五除二造句 不耻下问造句 赶紧造句二年级 用你我他造句 名人名言造句 成语造句大全200条 明察秋毫造句 用骄傲造句指不谦虚 大功告成造句 津津有味造句大全 从到造句 居然造句三年级 用三个像造句 下造句 堪忧造句 尝试造句 看见造句 忽然造句 要是就造句 里造句 应该造句 在,在,在,在造句 以及造句 不仅也造句 一样造句 既,也造句 是,还是造句 用也造句 在在在在造句 是,是,还是造句 佩服造句 将来造句 以为造句 一会儿 一会儿造句二年级

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里