SQL Server calls DLL program

Calling a DLL in SQL Server is divided into two steps

1. Create a DLL file (DLL file divided into 3 types, say one of them)

2. Put the DLL file into the SQL Server assembly. Then define a function that can be used to refer to the functions in the DLL.

1 Creating a DLL file

1.1 Click "File", "New", "Project", class library, change the namespace to Testdll, add the following code

(The framework must be changed to.) NET3.5 and 3.5, because SQL Server 2008 is only supported. NET 3.5 and,. NET 4.0 was released in 2010, not supported and Normal, C # methods must be static methods, because static functions can be called without instantiating a class)

Create DLL program (sum, lowercase letters to uppercase letters)

usingSystem;usingSystem.Collections.Generic;usingSystem.Text;namespacetestdll{ Public classClass1 {//ask 0 to N and when N is less than 0 o'clock returns 0//method must be a static method, because a static function can invoke a class without instantiating it         Public Static intCalc (intN) {intsum; Sum=0; if(n>=0)            {                 for(inti =0; I <= N; i++) {sum= Sum +i; }                returnsum; }            Else            {                return 0; }                   }        //Convert a string to uppercase         Public Static stringTodaxie (strings) {returnS.toupper (); }    }}

1.2 Right-click on "Project", click "Generate" or press F6 directly

1.3 Right click on "Project", click "Open folder in file Resource Management system", click Bin->debug, then you will see a TestDLL.dll file.

2 referencing DLL files in SQL

2.1 Allow SQL Server to allow user assemblies to run

Run the following code in SQL Server:

EXEC ' CLR enabled ' ' 1 ';  -- 0 for not allowed, 1 for running RECONFIGURE

Where Testdll is the name you have in the database for this assembly.

2.3 Create a function that uses the DLL file

Use the following SQL statement

--convert to uppercase lettersCreate FUNCTION [dbo].[ToUpper](@InputString [nvarchar]( -))RETURNS [nvarchar]( $)  with EXECUTE  asCALLER asEXTERNAL NAME[Testdll].[Testdll.class1].[Todaxie]GO--Sum functionCREATE FUNCTION [dbo].[Calc](@n int)--the function nameRETURNS int  --return type with EXECUTE  asCALLER asEXTERNAL NAME[Testdll].[Testdll.class1].[Calc]  --calling a C # assemblyGO

Take note of the few words.

Testdll refers to the name of the DLL in your assembly.

Testdll refers to the namespace of the class in the DLL file

Class1 refers to the class name of the class in the DLL file.

Todaxie refers to the static method that is called in the DLL file.

Finally, you can call the function like this

print dbo. ToUpper ('abc')   -- The result of the output is the ABC  SELECT dbo. Calc (3)       -- The result of the output is 6

SQL Server calls DLL program