introduce
Let's do 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 2005 significantly enhances the database programming model by hosting the Microsoft. NET Framework 2.0 Common Language Runtime (CLR). This allows developers to use any CLR language, such as C #, VB. NET or C + +, etc.) to write stored procedures, triggers, and user-defined functions.
How do we implement these functions?？
In order to use the CLR, we need to do the following steps:
1. Create a new class in. NET and create a new public method in this class.
2. Compile this class as a DLL.
3, register this DLL in SQL Server.
4, create a new 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. Then, create a new class named "Clrfunctions" and add a method named "Hellowold" inside it, as follows:
Copy Code code 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 parameter, and the return message is "Hello" plus the argument you passed in.
Now we need to compile this project as a DLL and register it in SQL Server. This is also relatively simple, right click on the project in VS, select "Build" after the program will generate a DLL. If your project is debug mode, you can find the compiled DLL in a path like the one shown below.
Copy Code code as follows:
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, and if it's the same machine, we just have to remember the path.
Enable CLR features
By default, the CLR in SQL Server is turned off, so we need to execute the following command to open the CLR:
Copy Code code as follows:
exec sp_configure ' clr enabled ', 1
Reconfigure
Go
Registering DLLs
In order to invoke the method we wrote, we 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 to the path of your DLL file)
Copy Code code as follows:
CREATE ASSEMBLY asmhelloworld from ' C:\SQLServerCLRTest.dll '
call our. NET method in SQL Server
To invoke. net method, we can write a SQL Server custom function and use "EXTERNAL NAME" within it to notify SQL Server to use CLR functionality. The code is as follows:
Copy Code code 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 a nvarchar parameter, which is equivalent to. NET, if it is set to varchar and the word "EXTERNAL NAME" is used later, an error is made. Then use "EXTERNAL NAME" to invoke. net method. The syntax is as follows:
assembly name. Class Name. Method Name
But when I use this syntax to invoke. net method, SQL Server complains, so in order for it to work correctly, I use the following syntax:
The assembly name. [Class name]. Method Name
Now we can call it with the following statement. net method:
Copy Code code as follows:
SELECT dbo.clrhelloworld (' Mark ')
When you run the code, you get a return result, "Hello Mark."
We demonstrated how to implement the CLR of SQL Server through a very simple example, and it can give us a lot of useful help.
