开发者社区> 杰克.陈> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL Server中的CLR编程——用.NET为SQL Server编写存储过程和函数

简介: 原文: SQL Server中的CLR编程——用.NET为SQL Server编写存储过程和函数 很早就知道可以用.NET为SQL Server2005及以上版本编写存储过程、触发器和存储过程的,不过之前开发的系统要么因为历史原因用的是SQL2000要么根本用不着在SQL Server中启用CLR,所以一直没有尝试。
+关注继续查看
原文: SQL Server中的CLR编程——用.NET为SQL Server编写存储过程和函数

很早就知道可以用.NETSQL Server2005及以上版本编写存储过程、触发器和存储过程的,不过之前开发的系统要么因为历史原因用的是SQL2000要么根本用不着在SQL Server中启用CLR,所以一直没有尝试。最近因为项目的原因,在这方面做了一个调研,现在在这里分享一下心得。

首先要说明的是要在SQL Server中启用CLR必须是在SQL Server2005及以上版本,其次在默认情况下是没有启用CLR的,必须要显示设置为启用。比如我们要在ArticleCollectorDB数据库中运行用.NET编写的函数或者存储过程,至少先要进行下面的SQL语句:

exec sp_configure 'clr enabled', 1;--在SQL Server中启用CLR
reconfigure;
go
--在ArticleCollectorDB数据库中设置TRUSTWORTHY为ON
ALTER DATABASE [ArticleCollectorDB] SET TRUSTWORTHY ON

这时可能会得到提示要重新启动SQL Server,如果有此提示则重新启动一下。

接着我们在VS中进行编码,在这里我们将分别编写一个名为IsMatch的函数和一个名为SendMail存储过程。在VS中创建一个名为NetSkycn.Data的类库项目,添加一个SqlCLR的类,代码如下:

using System.Data.SqlTypes;
using System.Net;
using System.Net.Mail;
using System.Security.Permissions;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
namespace NetSkycn.Data
{
    /// <summary>
    /// 在SQL Server环境中执行的CLR方法,注意提供给SQL Server调用的方法必须有SqlFunction/SqlProcedure Attribute
    /// 作者:周公
    /// 创建日期:2012-05-09
    /// 博客地址:http://blog.csdn.net/zhoufoxcn http://zhoufoxcn.blog.51cto.com
    /// 新浪微博地址:http://weibo.com/zhoufoxcn
    /// </summary>
    public sealed class SqlCLR
    {
        /// <summary>
        /// 判断字符串是否匹配正则表达式
        /// </summary>
        /// <param name="source">要匹配的文本</param>
        /// <param name="pattern">进行匹配的正则表达式</param>
        /// <param name="options">正则表达式匹配选项,1为忽略大小写,2为多行匹配,3为忽略大小写且多行匹配</param>
        /// <returns></returns>
        [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
        public static SqlBoolean IsMatch(string source, string pattern,int options)
        {
            if (string.IsNullOrEmpty(source) || string.IsNullOrEmpty(pattern))
            {
                return SqlBoolean.False;
            }
            RegexOptions regexOptions=RegexOptions.None;
            int optionIgnoreCase = 1;
            int optionMultiline = 2;
            if ((options & optionIgnoreCase) != 0)
            {
                regexOptions = regexOptions | RegexOptions.IgnoreCase;
            }
            if ((options & optionMultiline) != 0)
            {
                regexOptions = regexOptions | RegexOptions.Multiline;
            }
            return (SqlBoolean)(Regex.IsMatch(source, pattern, regexOptions));
        }
        /// <summary>
        /// 发送邮件
        /// </summary>
        /// <param name="to">收件人邮件地址</param>
        /// <param name="from">发件人邮件地址</param>
        /// <param name="subject">邮件主题</param>
        /// <param name="body">邮件内容</param>
        /// <param name="username">登录smtp主机时用到的用户名,注意是邮件地址'@'以前的部分</param>
        /// <param name="password">登录smtp主机时用到的用户密码</param>
        /// <param name="smtpHost">发送邮件用到的smtp主机</param>
        [SqlProcedure]
        [SmtpPermission(SecurityAction.Assert)]
        [SecurityPermission(SecurityAction.Assert)]
        public static void SendMail(string to, string from, string subject, string body, string userName, string password, string smtpHost)
        {
            MailAddress addressFrom = new MailAddress(from);
            MailAddress addressTo = new MailAddress(to);
            MailMessage message = new MailMessage(addressFrom, addressTo);
            message.Subject = subject;//设置邮件主题
            message.IsBodyHtml = true;//设置邮件正文为html格式
            message.Body = body;//设置邮件内容
            SmtpClient client = new SmtpClient(smtpHost);
            //设置发送邮件身份验证方式
            //注意如果发件人地址是abc@def.com,则用户名是abc而不是abc@def.com
            client.Credentials = new NetworkCredential(userName, password);
            client.Send(message);
        }
    }
}

编译通过之后,记住类库的物理全路径,比如:F:\VS2008\netskycn\NetSkycn.Data\bin\Release\NetSkycn.Data.dll,在这里要强调几点:一、对于将来提供给SQL Server调用的函数或者存储过程必须是静态方法,并且还必须带有SqlFunction或者SqlProcedure属性;二、对于一些需要访问外部网络资源和安全属性的还必须添加响应的属性(如本例中的SendMail方法,如果没有添加响应的属性在创建SQL Function/Procedure时会出现错误提示)。

现在我们开始遵循先为SQL Server创建程序集、后创建函数或者存储过程的顺序来操作,在操作过程中用到的SQL语句如下:

--在ArticleCollectorDB数据库中设置TRUSTWORTHY为ON
ALTER DATABASE [ArticleCollectorDB] SET TRUSTWORTHY ON
--如果已经存在该对象则删除
IF EXISTS(SELECT * FROM SYS.SYSOBJECTS WHERE NAME='SendMail' AND XTYPE='PC') 
DROP PROCEDURE SendMail
--如果已经存在该对象则删除
IF EXISTS(SELECT * FROM SYS.SYSOBJECTS WHERE NAME='IsMatch' AND XTYPE='FS') 
DROP FUNCTION IsMatch
--如果已经存在SqlCLR程序集则删除该程序集
IF EXISTS(SELECT * FROM SYS.ASSEMBLIES WHERE NAME='SqlCLR') 
DROP  ASSEMBLY SqlCLR
--在SQL Server中创建程序集,,创建的程序集名为SqlCLR
CREATE ASSEMBLY SqlCLR FROM 'F:\VS2008\netskycn\NetSkycn.Data\bin\Release\NetSkycn.Data.dll' WITH PERMISSION_SET = UNSAFE
GO
--从CLR程序集中创建函数,函数名为IsMatch,有三个参数,
--[SqlCLR]是SQL Server中程序集名
--[NetSkycn.Data.SqlCLR]是.NET中的类的全名(命名空间及类名)
--[IsMatch]是.NET中类的函数名
CREATE FUNCTION [dbo].[IsMatch] 
( 
    @source AS NVARCHAR(200),
    @pattern AS NVARCHAR(200),
@option INT=3
) 
RETURNS BIT 
AS 
    EXTERNAL NAME [SqlCLR].[NetSkycn.Data.SqlCLR].[IsMatch]; 
GO
--从CLR程序集中创建函数,函数名为IsMatch,有三个参数,
--[SqlCLR]是SQL Server中程序集名
--[NetSkycn.Data.SqlCLR]是.NET中的类的全名(命名空间及类名)
--[SendMail]是.NET中类的函数名
CREATE PROCEDURE [dbo].[SendMail] 
( 
    @to AS NVARCHAR(200),
    @from AS NVARCHAR(200),
    @subject AS NVARCHAR(200),
    @body AS NVARCHAR(MAX),
    @userName AS NVARCHAR(200),
    @password AS NVARCHAR(200),
    @smtpHost AS NVARCHAR(200)
)
AS 
    EXTERNAL NAME [SqlCLR].[NetSkycn.Data.SqlCLR].[SendMail]; 
GO

如果没有得到任何错误提示,则表示创建函数和存储过程成功。至此我们会看到如下情形:

这表示创建成功。

测试创建函数的SQL语句(查找article表中title字段是35个字段的数据):

select * from article where dbo.IsMatch(Title,'^[\u4e00-\u9fa5]{3,5}$',3)=1

测试创建存储过程的SQL语句:

exec [dbo].SendMail @to='test@qq.com',@from='webmaster@qq.com',@subject='test',@body='This mail was sent by SQL Procedure',@userName='webmaster',@password='123',@smtpHost='smtp.qq.com'

以上代码在SQL Server 2005中文企业版、SQL Server 2008英文企业版测试通过。

可以看出在一些SQL语句不够灵活的情况下,可以使用.NET来编写存储过程和函数,通过以上步骤之后和调用SQL语句写的存储过程和函数没有区别,极大地方便了编程。

周公

2012-05-12

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQLSERVER存储过程语法详解
SQL SERVER存储过程语法: Create PROC [ EDURE ] procedure_name [ ; number ]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ]   [ WITH     { RECOMPILE | ENCRY
986 0
SQL Server基础之<存储过程>
原文:SQL Server基础之   简单来说,存储过程就是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不仅限于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。
1087 0
SQL Server内存遭遇操作系统进程压榨案例
原文:SQL Server内存遭遇操作系统进程压榨案例 场景:   最近一台DB服务器偶尔出现CPU报警,我的邮件报警阈(请读yù)值设置的是15%,开始时没当回事,以为是有什么统计类的查询,后来越来越频繁。
931 0
SQL Server页类型汇总+疑问
原文:SQL Server页类型汇总+疑问 该文章整理自:http://www.sqlnotes.info/2011/10/31/page-type/             SQL Server中包含多种不同类型的页,来满足数据存储的需求。
899 0
sqlServer存储过程
1、创建存储过程报错:     'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。 解决方法: use databaseName 后面要加上一句: GO ...
536 0
+关注
杰克.陈
一个安静的程序猿~
文章
问答
文章排行榜
最热
最新
相关电子书
更多
SQL Server 2017
立即下载
SQL Sever迁移PG经验
立即下载
SQL Server在电子商务中的应用与实践
立即下载