Shulou(Shulou.com)06/02 Report--

This article mainly shows you "how to call the CLR class library in SQL Server", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to call the CLR class library in SQL Server" this article.

The method of calling the CLR class library in SQL Server is implemented in 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.

1: 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);}}

2: enable the CLR class library function

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.

Registration DLL of 3: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 the registered DLL, you can use the following method:

DROP ASSEMBLY asmHelloWorld

The 4:CLR class library calls our .NET methods 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 call the method using the CLR class library with the following statement:

SELECT dbo.clrHelloWorld ('Mark') above is all the content of the article "how to call the CLR Library in SQL Server". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.