In SQL Server CLR is used to call.NET method

introduce
Let's do an example 1, create a new class in.NET, create a new method in that class, and then call the method in SQL Server. According to Microsoft, SQL Server 2005 significantly enhances the database programming model by hosting the Microsoft.NET Framework 2.0 common language runtime (CLR). This allows developers to write stored procedures, triggers, and user-defined functions in any CLR language, such as C#, VB.NET, or C++.

How do we implement these functions?
To use CLR, we need to do the following steps:
1. Create a new class in.NET and create a new public method in the class.
2. Compile this class to 1 DLL.
3. Register this DLL in SQL Server.
4. Create a new SQL Server function to access the specified.NET method.

Next, let's step up to complete an example
First, create a new library project called "SQLServerCLRTest" in Visual Studio. Then, create a new class named "CLRFunctions" and add a method named "HelloWold" to it. The code is as follows:

 
public class CLRFunctions 
{ 
public static string HelloWorld(string Name) 
{ 
return ("Hello " + Name); 
} 
} 

This is a very simple method (in order for SQL Server to call it, it must be public and static), this method has an string parameter and returns the message "Hello" plus the parameter you passed in.

Now we need to compile this project into 1 DLL and register it in SQL Server. This is also simple. Right-click on the project in VS, select "build" and the program will generate one DLL. If your project is in debug mode, you can find the compiled DLL in the path shown below.

 
C:\Documents and Settings\mark.smith\My Documents\Visual Studio 2005\Projects\SQLServerCLRTest\SQLServerCLRTest\bin\Debug\SQLServerCLRTest.dll 

Once we find this DLL, we can copy it to our SQL Server machine, if it's the same machine we just have to remember this path.

Enable CLR functionality
By default, CLR in SQL Server is turned off, so we need to open CLR by executing the following command:

 
exec sp_configure 'clr enabled',1 
reconfigure 
go 

Registered DLL
In order to call the method we wrote, we need to register the DLL we just compiled in SQL Server. We can register DLL (the path to your DLL file) in the database using the following command

 
CREATE ASSEMBLY asmHelloWorld FROM 'C:\SQLServerCLRTest.dll' 

Call our.NET method in SQL Server
To call the.NET method, we can write an SQL Server custom function and use "EXTERNAL NAME" within it to notify SQL Server to use the CLR function. The code is as follows:

 
CREATE FUNCTION dbo.clrHelloWorld 
( 
@name as nvarchar(200) 
) 
RETURNS nvarchar(200) 
AS EXTERNAL NAME asmHelloWorld.[SQLServerCLRTest.CLRFunctions].HelloWorld 

The above custom function does two things. The first is to declare an nvarchar parameter, which is equivalent to the string type in.NET (if you set it to varchar and use "EXTERNAL NAME", you will get an error). Then use "EXTERNAL NAME" to call the.NET method. The syntax is as follows:
Assembly name. Class name. Method name
However, when I call the.NET method using this syntax, SQL Server will report an error, so to make it work properly, I use the following syntax:
Assembly name.[class name]. Method name
Now we can call the.NET method with the following statement:

 
SELECT dbo.clrHelloWorld('Mark') 

When you run this code, you will get 1 return result "Hello Mark".
We demonstrated how to implement CLR of SQL Server with a very simple example, which can give us a lot of useful help.