程序博客网 > mac cad中文字体下载

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

来源：互联网 发布：mac cad中文字体下载 编辑：程序博客网 时间：2024/04/30 10:11

原文：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 plaincopy to clipboardprint?
  1. exec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr
  2. reconfigure 
exec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clrreconfigure

用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;
  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 plaincopy to clipboardprint?
  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 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. }
  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 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;
  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 Articlesset 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 Articleswhere 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 Articleswhere 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里执行以下命名，来启用CLRexec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clrreconfigure*/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

mac cad中文字体下载 mac cad中文字体下载
原创粉丝点击
热门IT博客
java的编程工具java的编程工具
女性假两性畸形知乎女性假两性畸形知乎
中国内河航运 知乎
马自达323轮毂数据
ae数据图表模板
初中题库软件
炫舞辅助源码
淘宝手机充值赚钱吗
淘宝狗狗疫苗是真的吗
windows xp pe u盘版
php 判断对象类型
淘宝上的驱鼠器
九龙朝兵魂数据
php基础书籍
广西公务员网络考试
笨女孩知乎
java house咖啡
卡扎菲怎么死的 知乎
java中if else的用法
手机上写代码的软件php
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 补气补血吃什么 补气补血吃什么好 补气的中药 补气药 什么补气 女人补血补气 吃什么补气 补气养神动物 补气益血的食物 补气的食物有哪些 补气的中药有哪些 胖人补气能瘦下来 补气升阳 怎么补气 补气喝什么 补气养血吃什么 补血补气的食物 补气怎么补 补气的 补气食材 补气食品有哪些 吃什么可以补气 什么可以补气 补气吃什么食物 女人怎么补气 吃什么能补气 什么汤补气 补气食品 补气的药有哪些 气短吃什么补气好 什么食物补气 什么东西补气 怎样补气 什么食物补气好 补气的食材 吃什么水果补气 女人吃什么补气 喝什么补气 吃什么食物补气 女人补气养血吃什么 补气药有哪些

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