CLR programming in SQL Server--writing stored procedures and functions for SQL Server with. Net

Original: CLR programming in SQL Server-writing stored procedures and functions for SQL Server with. Net

It has long been known that you can write stored procedures, triggers, and stored procedures with . NET for SQL Server2005 and above, but the system that was developed before is either for historical reasons SQL2000 either does not need to enable the CLRin SQL Server at all , so it has not been tried. Recently, for the reasons of the project, we have done a research in this area, now share the experience here.

The first thing to note is that to enable the CLR in SQL Server must be in SQL Server2005 and later versions, followed by default is not enabled the CLR , which must be displayed set to Enabled. For example, we want to run a function or stored procedure written in . NET in the articlecollectordb database , at least for the following SQL statement:

exec sp_configure ' clr enabled ', 1;--enabled in SQL Server clrreconfigure;go--set trustworthy to Onalter in Articlecollectordb database DATABASE [Articlecollectordb] SET trustworthy on

You may be prompted to restart SQL Serverand reboot If you have this prompt.

Then we encode in VS , where we'll write a function named IsMatch and a stored procedure called SendMail. Create a class library project named Netskycn.data in VS , add a SqlCLR class with the following code:

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>//In sq The CLR method that is executed in the server environment, note that the method that is provided to the SQL Server call must have sqlfunction/sqlprocedure Attribute////week///Date Created: 2012-05-09///Blog address : HTTP://BLOG.CSDN.NET/ZHOUFOXCN http://zhoufoxcn.blog.51cto.com///Sina Weibo address: HTTP://WEIBO.COM/ZHOUFOXCN///</summ Ary> public sealed class SqlCLR {//<summary>///Determine if string matches regular expression///</summary&gt        ;        <param name= "source" > Text to match </param>//<param name= "pattern" > Regular expression to match </param> <param name= "Options" > Regular expression matching options, 1 for ignoring case, 2 for multi-line matching, 3 for ignoring case and multiline matching </param>//<returns></return s> [SqlFunction (isdeterministic = true, DataAccess = Dataaccesskind.none)] public static SqlBoolean Ismat CH (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>//E-mail///</summary>/<param name= "to" > Recipient email Address </par         am>//<param name= "from" > Sender email Address </param>//<param name= "subject" > Message subject </param> <param name= "Body" > Mail content </param>//<paramName= "username" > username used to log in to the SMTP host, note that the previous part of the email address ' @ ' </param>//<param name= ' password ' >        User password used to log on to the SMTP host </param>//<param name= "SMTPHost" > SMTP host to send mail </param> [sqlprocedure]        [Smtppermission (Securityaction.assert)] [SecurityPermission (Securityaction.assert)] public static void SendMail (string to, string from, string subject, str ing 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;//Sets the message subject. isbodyhtml = true;//Sets the message body to HTML format message.            Body = body;//Set the message content SmtpClient client = new SmtpClient (smtphost); Set Send Message authentication method//Note If the sender address is [email protected], the user name is ABC instead of [email protected] client. Credentials = newNetworkCredential (userName, password); Client.        Send (message); }    }}

After compiling the pass, remember the physical full path of the class library, for example:F:\VS2008\netskycn\NetSkycn.Data\bin\Release\NetSkycn.Data.dll, here to emphasize the points: first, a function or stored procedure that will be called to SQL Server in the future must be a static method and must also have a sqlfunction or The SqlProcedure property, second, for some properties that require access to external network resources and security properties (such as the SendMail method in this example , if there are no properties to add the response in the Create SQL function/procedure error message appears).

Now let's start by following the sequence of creating an assembly for SQL Server , creating a function, or a stored procedure, using the following SQL statements during the operation:

--Set trustworthy to Onalter database [Articlecollectordb] set trustworthy on--in the Articlecollectordb. Delete if the object already exists EXISTS (SELECT * from SYS.) SYSOBJECTS WHERE name= ' SendMail ' and xtype= ' PC ') drop PROCEDURE sendmail--If the object already exists then delete if EXISTS (SELECT * from SYS. SYSOBJECTS where name= ' IsMatch ' and xtype= ' FS ') drop FUNCTION ismatch--If SQLCLR assembly is already present, delete the assembly if EXISTS (SELECT * from SYS. assemblies where name= ' SqlCLR ') DROP ASSEMBLY sqlclr--CREATE assembly in SQL Server, create assembly named Sqlclrcreate ASSEMBLY SqlCLR from ' f:\v S2008\netskycn\netskycn.data\bin\release\netskycn.data.dll ' with Permission_set = unsafego--creates a function from the CLR assembly, The function name is IsMatch, there are three parameters,--[SQLCLR] is the full name of the class in. Net (namespace and class name) in SQL Server--[NETSKYCN.DATA.SQLCLR] is. The function name of the class in the net is the CREATE function [dbo]. [IsMatch] (@source as NVARCHAR, @pattern as NVARCHAR, @option int=3) RETURNS BIT as EXTERNAL NAME [SqlCLR]. [NetSkycn.Data.SqlCLR]. [IsMatch]; go--Create a function from a CLR assembly, the function is named IsMatch, there are three parameters,--[SQLCLR] is the full name (namespace and class name) of the class in. NET in SQL Server--[NETSKYCN.DATA.SQLCLR]--[SendMail] is a function name for a class in. NET create PROCEDURE [dbo]. [SendMail] (@to as NVARCHAR, @from as NVARCHAR, @subject as NVARCHAR, @body as NVARCHAR (MAX), @userNa Me as NVARCHAR, @password as NVARCHAR, @smtpHost as NVARCHAR) as EXTERNAL NAME [SqlCLR]. [NetSkycn.Data.SqlCLR]. [SendMail]; GO

If you do not get any error hints, the creation of the function and the stored procedure succeeds. At this point we will see the following scenario:

This means that the creation was successful.

Test the SQL statement that created the function (find the title field in the article table is 3 to data for 5 fields):

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

To test the SQL statement that created the stored procedure:

exec [dbo]. SendMail @to = ' [email protected] ', @from = ' [email protected] ', @subject = ' Test ', @body = ' This mail is sent by SQL Procedure ', @userName = ' Webmaster ', @password = ' 123 ', @smtpHost = ' smtp.qq.com '

The above code is passed in SQL Server 2005 Enterprise Edition ,SQL Server English Enterprise Edition test.

You can see that some SQL statements can be used without being flexible enough. NET to write stored procedures and functions, after the above steps and call SQL statements written by the stored procedures and functions are not different, greatly facilitates the programming.

Zhou Gong

2012-05-12

CLR programming in SQL Server--writing stored procedures and functions for SQL Server with. Net