sql server 下寫net函數,預存程序的方法

sql server 下寫net函數,預存程序的方法

 

建立項目->資料庫專案->Microsoft SQLServer ->SQL CLR->SQLServer項目

 

添加新類sqlclr.cs

public sealed class sqlclr    {        //Regex匹配函數        [SqlFunction(IsDeterministic =true,DataAccess=DataAccessKind.None)]        public static SqlBoolean IsMatch(string source,string pattern,int options)        {            SqlBoolean sqlbool=SqlBoolean.False;            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;            }            sqlbool=(SqlBoolean)(Regex.IsMatch(source,pattern,regexOptions));            return sqlbool;        }                //發送郵件函數        [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);        }    }

資料庫中SQL語句註冊dll檔案

//-----------------------------use cbjyq_flowdbgosp_configure 'clr enable', 1GOreconfigurealter database cbjyq_flowdb 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;if exists(select * from sys.assemblies where name='SqlWorkFlow_cw_dll')   drop assembly SqlWorkFlow_cw_dll;create assembly SqlWorkFlow_cw_dll from 'E:\項目集合\VSSCodeCenter\xxx系統\TdcbOffice\輸出TdcbOffice\SqlWorkFlow_cw.dll' with permission_set=unsafe;gocreate function dbo.IsMatch(    @source as NVARCHAR(200),    @patter as NVARCHAR(200),    @option int=3)returns bitas    EXTERNAL NAME [SqlWorkFlow_cw_dll].[SqlWorkFlow_cw.sqlclr].[IsMatch];gocreate 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 [SqlWorkFlow_cw_dll].[SqlWorkFlow_cw.sqlclr].[SendMail];go//-----------------------------SQL語句調用測試select * from ENTITY_MESSAGE where dbo.IsMatch(name,'^[\u4e00-\u9fa5]{3,5}$',3)=1exec dbo.SendMail @to='hsg77@163.com',@from='webmaster@qq.com',@subject='test',@body='This mail was sent by sql procedure',@username='webmaster',@password='123',@smtpHost='smtp.qq.com'