How to Use CLR to call. NET methods in SQL Server

Let's take an example. Create a new class in. NET, create a new method in this class, and then call this method in SQL Server. According to Microsoft, SQL Server 2.0 significantly enhanced the database programming model through the host Microsoft. NET Framework 2005 Common Language Runtime Library (CLR. This allows developers to use any CLR language (such as C #, VB. NET or C ++) to write stored procedures, triggers, and user-defined functions.

How can we implement these functions?

To use CLR, perform the following steps:

1. Create a new class in. NET and create a public method in this class.

2. Compile this class as a DLL.

3. register the DLL in SQL Server.

4. Create an SQL Server Function to access the specified. NET method.

Next, let's complete an example.

First, create a new class library project named "SQLServerCLRTest" in Visual Studio. 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 a string type parameter, the returned message is "Hello" and the input parameter is added.

Now, we need to compile this project as a DLL and register it in SQL Server. This is also relatively simple. Right-click the project in Vs and select "generate". Then the program will generate a DLL. If your project is in the debugging mode, you can find the compiled DLL in the path shown below.

C: documents and settingsmark. smithmy
Documentsvisual maxcompute 2005 Projects
Sqlserverclrtestsqlserverclrtest indebugsqlserverclrtest. dll
 

After finding this DLL, we can copy it to our SQL Server machine. If it is the same machine, we just need to remember this path.

Enable CLR

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

Exec sp_configure 'clr enabled', 1
Reconfigure
Go
 

Register DLL

To call the method we wrote, you need to register the DLL we just compiled in SQL Server. We can use the following command in the database to register the DLL (path of your DLL file)

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" in 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 preceding udfs do two tasks. First, an nvarchar parameter is declared, which is equivalent to the string type in. NET (if it is set to varchar and "external name" is used later, an error is returned ). Then, use "external name" to call the. NET method. Syntax:

Set Name. Class Name. Method Name

However, when I use this syntax to call the. NET method, SQL Server reports an error. To make it work normally, I use the following syntax:

Set Name. [class name]. Method Name

Now we can use the following statement to call the. NET method:

SELECT dbo. clrHelloWorld ('mark ')

When you run this code, you will get a returned result "Hello Mark ".

We use a simple example to demonstrate how to implement the SQL Server CLR, which can bring us a lot of useful help.