What is the implementation step for SQL SERVER to call the CLR class library

SQL SERVER calls CLR class library implementation steps is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Calling the CLR class library implementation by SQL Server is divided into the following steps:

1. Create a new CLR class library project in .NET, add a class file to the project, and define the method to be called by SQL Server as a public, static method.

2. Compile the project into a DLL.

3. Register the DLL in SQL Server.

4. Access the specified .NET method by using the function of SQL Server.

One: writing methods in the CLR class library must be static and public

Public class CLRFunctions {public static string HelloWorld (string Name) {Network bitsCN.com return ("Hello" + Name);}}

Second: enable the function of CLR class library

By default, CLR in SQL Server is off, so we need to execute the following command to open CLR

Exec sp_configure 'clr enabled', 1 reconfigure [with override] go

If there is an error that "impromptu updates to the system directory is not supported" during the execution of the above command, you can add the contents in square brackets above.

3: SQL SERVER calls the registration DLL of the CLR class library

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

CREATE ASSEMBLY asmHelloWorld FROM 'C:SQLDLL.dll' if you want to delete a registered DLL, you can use the following method: DROP ASSEMBLY asmHelloWorld

Four: the CLR class library calls our .NET method in SQL Server.

In order to call the .NET method, we can write a SQL Server custom function and use "EXTERNAL NAME" in it to tell SQL Server to use the CLR feature. The code is as follows:

CREATE FUNCTION dbo.clrHelloWorld (@ name as nvarchar) China Network Management Forum bbs.bitsCN.com) RETURNS nvarchar AS EXTERNAL NAME asmHelloWorld. [SQLDLL .CLRFunctions] .HelloWorld

The above custom function does two things. First, a nvarchar parameter is declared, which is equivalent to the string type in .NET (an error will be reported if it is set to varchar and followed by the use of "EXTERNAL NAME"). Then use "EXTERNAL NAME" to call the .NET method. The syntax is as follows:

The assembly name. Class name. Method name

However, when I call a .NET method using this syntax, SQL Server reports an error, so in order for it to work, I use the following syntax:

The assembly registration name. [Assembly name. Class name]. Method name

Now we can use the method implemented by the CLR class library with the following statement:

Will SELECT dbo.clrHelloWorld ('Mark') help you after reading the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.