How to Write net functions and stored procedures in SQL Server

How to Write net functions and stored procedures in SQL Server

 

Create a project> database project> Microsoft sqlserver> SQL clr> SQL Server Project

 

Add new class sqlclr. CS

Public sealed class sqlclr {// Regular Expression matching function [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. none; int optionignorecase = 1; int optionmultil INE = 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;} // function for sending emails [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; // set the message subject. isbodyhtml = true; // set the message body to the HTML format message. body = body; // set the mail content smtpclient client = new smtpclient (smtphost); // set the mail Sender authentication method // note that if the sender address is a abc@def.com, the user name is ABC, instead of abc@def.com client. credentials = new networkcredential (username, password); // client. send (Message );}}

Register DLL files with SQL statements in the database

// ------------------------------- Use cbjyq_flowdbgosp_configure 'clr enable', 1 goreconfigurealter 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: \ project set \ vsscodecenter \ XXX System \ tdcboffice \ output tdcboffice \ sqlworkflow_c1_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_c1_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_c1_sqlclr]. [Sendmail]; go // ----------------------------- SQL statement call test select * From entity_message where DBO. ismatch (name, '^ [\ u4e00-\ u9fa5] {3, 5} $', 3) = 1 exec DBO. sendmail @ to = 'hsg77 @ 163.com ', @ from = 'webmaster @ QQ.com', @ subject = 'test', @ body = 'this mail was sent by SQL procedure ', @ username = 'webmaster', @ Password = '000000', @ smtphost = 'smtp .qq.com'