CLR programming in SQL Server -- use. NET to write stored procedures and functions for SQL Server

I knew it could be used very early. NET writes stored procedures, triggers, and stored procedures for SQL Server2005 and later versions. However, the previously developed system either uses SQL2000 for historical reasons or does not need to enable CLR in SQL Server, so I never tried. Recently, I did a research in this area for project reasons. I 'd like to share my experiences here.
The first thing to note is that you must enable CLR in SQL Server in SQL Server2005 or later versions. Secondly, by default, CLR is not enabled and must be set to enable. For example, to run a function or stored procedure written in. NET in the ArticleCollectorDB database, at least the following SQL statement is required:

 
 

  
  
Exec sp_configure 'clr enabled', 1; -- enable clr in SQL Server
  
  
Reconfigure;
  
  
Go
  
  
-- Set TRUSTWORTHY to ON in the ArticleCollectorDB Database
  
  
Alter database [ArticleCollectorDB] SET TRUSTWORTHY ON
 
 

At this time, you may be prompted to restart SQL Server. If this prompt is displayed, restart it.
Then we encode in VS. Here we will compile a function named IsMatch and a stored procedure named SendMail respectively. Create a class library project named NetSkycn. Data in VS and add a SqlCLR class. The Code is as follows:

 
 

  
  
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>
  
  
/// CLR method executed in the SQL Server environment. Note that the method provided to SQL Server must have SqlFunction/SqlProcedure Attribute.
  
  
/// By Zhou Gong
  
  
/// Creation date:
  
  
// Blog address: http://blog.csdn.net/zhoufoxcn http://zhoufoxcn.blog.51cto.com
  
  
// Sina Weibo address: http://weibo.com/zhoufoxcn
  
  
/// </Summary>
  
  
Public sealed class SqlCLR
  
  
{
  
  
/// <Summary>
  
  
/// Determine whether a string matches a regular expression
  
  
/// </Summary>
  
  
/// <Param name = "source"> text to be matched </param>
  
  
/// <Param name = "pattern"> Regular Expression </param>
  
  
/// <Param name = "options"> Regular Expression matching option. The value 1 indicates case-insensitive, the value 2 indicates multi-row matching, and the value 3 indicates case-insensitive and multi-row matching. </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>
  
  
/// Send an email
  
  
/// </Summary>
  
  
/// <Param name = "to"> recipient email address </param>
  
  
/// <Param name = "from"> sender's email address </param>
  
  
/// <Param name = "subject"> subject </param>
  
  
/// <Param name = "body"> email content </param>
  
  
/// <Param name = "username"> User name used to log on to the smtp host. Note that the email address is '@' in the previous section. </param>
  
  
/// <Param name = "password"> User password used to log on to the smtp host </param>
  
  
/// <Param name = "smtpHost"> smtp host used for sending emails </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 ();
  
  
MailMessage message = new MailMessage (addressFrom, addressTo );
  
  
Message. Subject = subject; // set the mail Subject
  
  
Message. IsBodyHtml = true; // set the email body to html format.
  
  
Message. Body = body; // you can specify the email 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 the abc@def.com
  
  
Client. Credentials = new NetworkCredential (userName, password );
  
  
Client. Send (message );
  
  
}
  
  
 
  
  
}
  
  
}
 
 

After compilation, remember the full physical path of the class library, for example, F: \ VS2008 \ netskycn \ NetSkycn. data \ bin \ Release \ NetSkycn. data. dll, which should be emphasized here: 1. For functions or stored procedures that will be provided to SQL Server in the future, they must be static methods and must also contain the SqlFunction or SqlProcedure attribute; 2. For some attributes that require access to external network resources and security attributes, you must also add the response attributes, such as the SendMail method in this example, if no response property is added, an error message is displayed when SQL Function/Procedure is created ).
Now we start to follow the sequence of creating an assembly for SQL Server, then creating a function, or a stored procedure. The SQL statements used during the operation are as follows:

 
 

  
  
-- Set TRUSTWORTHY to ON in the ArticleCollectorDB Database
  
  
Alter database [ArticleCollectorDB] SET TRUSTWORTHY ON
  
  
 
  
  
-- Delete an object if it already exists.
  
  
If exists (SELECT * from sys. sysobjects where name = 'sendmail' and xtype = 'pc ')
  
  
Drop procedure SendMail
  
  
 
  
  
-- Delete an object if it already exists.
  
  
If exists (SELECT * from sys. sysobjects where name = 'ismatch' and xtype = 'fs ')
  
  
Drop function IsMatch
  
  
 
  
  
-- If a SqlCLR Assembly already exists, delete the assembly.
  
  
If exists (SELECT * from sys. assemblies where name = 'sqlclr ')
  
  
Drop assembly SqlCLR
  
  
 
  
  
-- Create an assembly in SQL Server. The Assembly name is SqlCLR.
  
  
Create assembly SqlCLR FROM 'f: \ VS2008 \ netskycn \ NetSkycn. Data \ bin \ Release \ NetSkycn. Data. dll 'WITH PERMISSION_SET = UNSAFE
  
  
GO
  
  
 
  
  
-- Create a function from the CLR assembly. The function is named IsMatch and has three parameters,
  
  
-- [SqlCLR] is the set name in SQL Server.
  
  
-- [NetSkycn. Data. SqlCLR] is the full name and namespace of the class in. NET)
  
  
-- [IsMatch] Is the function name of the class in. 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
  
  
 
  
  
-- Create a function from the CLR assembly. The function is named IsMatch and has three parameters,
  
  
-- [SqlCLR] is the set name in SQL Server.
  
  
-- [NetSkycn. Data. SqlCLR] is the full name and namespace of the class in. NET)
  
  
-- [SendMail] Is the function name of the class in. 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
 
 

If no error message is displayed, the function and stored procedure are successfully created. Now we can see the following situation:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/1J6314W3-0.jpg "border =" 0 "alt =" "/>

This indicates the creation is successful.
The SQL statement used to test the function is used to search for data with three to five fields in the title field of the article table ):

 
 

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

Test the SQL statement used to create a stored procedure:

 
 

  
  
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' 
 
 

The above code has been tested on SQL Server 2005 Chinese Enterprise Edition and SQL Server 2008 English Enterprise Edition.
It can be seen that some SQL statements are not flexible enough to use.. NET to write stored procedures and functions. After the above steps, there is no difference with the stored procedures and functions that call SQL statements to write, which greatly facilitates programming.

Zhou Gong
2012-05-12

 

This article is from the "Zhou Gong Zhou Jinqiao) column" blog, please be sure to keep this source http://zhoufoxcn.blog.51cto.com/792419/859245