SQL Drip 15-calling C # programs in SQL Server 2008

Source: SQL Drip 15-calling C # programs in SQL Server 2008

　　

T-SQL is very efficient when executing common queries, but it is less efficient to execute loops and judge such statements. With the help of the CLR, we can extend C # programs in SQL Server 2008 to complete procedural queries such as loops, or other features that are inconvenient for SQL. This essay will cover the replacement of regular expressions for extending C # programs in SQL Server.

2. Create a new class library program named Regex, open Visual Studio 2008, click File, click New, click Project, select the class library in the New Project dialog box that pops up, and the project name is regex.
3. Name the class Class1 in your project as regex, and write the following code in this class:usingSystem;
   usingSystem.Collections.Generic;
   usingSystem.Linq;
   usingSystem.Text;
   usingSystem.Data.SqlTypes;
   usingSystem.Text.RegularExpressions;
   usingMicrosoft.SqlServer.Server;
   namespaceREGEXP
   {
   PublicPartialclassREGEXP
   {
   [SqlFunction (IsDeterministic=true, DataAccess=Dataaccesskind.none)]
   PublicStaticSqlString Regexreplace (
   SqlString input, SqlString pattern, SqlString replacement)
   {
   return(SqlString) Regex.Replace (
   Input. Value, pattern. Value, replacement. Value);
   }
   }
   }
   This class uses the Replace function in the System.Text.RegularExpressions.Regex class, which is a common function in C # and uses regular expressions to implement the Replace function. Compiling this class library project generates RegExp.dll, which will be used later.
5. Open the SQL Server 2008 management interface below, we need to deploy this DLL to the database, and then register a method, but before that, we need to open the CLR invocation function in SQL Server, run the following SQL statement:
   exec sp_configure " CLR enabled " , 1 reconfigure
7. Run the following statement to extract the intermediate language (IL) from this DLL, and if you try it yourself, pay attention to modifying the DLL file storage path.
   Use AdventureWorks;
   Create Assembly REGEXP from ' D:\MyProject\RegExp\RegExp\bin\Debug\RegExp.dll ' This time we can see this collection in SQL Server, click Expand Database AdventureWorks, click Expand Programmability, click Expand Assemblies to see the regex, such as 1.
   
   Figure 1
8. Write a SQL function to register this assembly, the code is as follows
   

   Create functionDbo. Regexreplace (
   
   @input  as nvarchar(Max),
   
   @pattern  as nvarchar(Max),
   
   @replacement  as nvarchar(Max))
   
   returns nvarchar(Max)
   
    with returns NULL  on NULLInput
   
   External name[RegExp].[Regexp.regexp].[Regexreplace]  
   
   Go 

   Thanks for the "Frog Prince" reminder, has been corrected
   Note:
   A. The function is returns rather than return and is easily mistaken.
   B.with returns NULL on NULL input means that if any of the arguments are null when the function is called, the return value of the function will be null.
   C. The last sentence refers to the format of the class library, which I originally thought [Myassemblyname]. [Myassemblyname]. [Mymethodname] is OK, but it needs to be written [Myassemblyname]. [Myassemblyname.myclassname]. [Mymethodname] This can only run the above statement, or the error is not found in the assembly related classes, so far puzzled, very confusing.
10. Now we can call this function just like any other SQL function, and here are some examples of calls.
    
    It's easy to replace the letter Z in China with Z.
    
    This replaces the operator + with the Add.
    
    This is the result of the statement with the RETURNS null on NULL input.

SQL Drip 15-calling C # programs in SQL Server 2008