SQL Server Add external Assembly basic operations

Original: SQL Server add external Assembly basic operations

Summary: Sometimes SQL Server built-in functions are not so useful, you can reference the external assembly, under the ugly, do add external assembly operations

1, prepare the program, compile a myclr DLL.

 Public class Clrclass    {        [Microsoft.SqlServer.Server.SqlFunction]        publicstaticint myfun (  intint b)   // must use static method, non-static method will error.          {            return// is actually a judging minimum function         }    

2. Add an assembly.

CREATE ASSEMBLY MyClr1      from ' I:\Test\MyClr\MyClr\bin\Release\MyClr.dll '          with = Safe/EXTERNAL access/--  (default recommended/access to external resources/unrestricted access to resources)

• For assemblies created with the SAFE or EXTERNAL access permission set:
• The assembly code should be type-safe. Type safety is established by running the common Language Runtime Validation tool on an assembly.
• Assemblies should not contain any static data members in their classes unless they are marked as read-only.
• A class in an assembly cannot contain a finalizer method.

The unsafe mode is an access resource that is not subject to any restrictions.

This is a limitation on the online manual to face these patterns.

If there is a static variable, it will be an error in safe mode.

 Public class Clrclass    {        staticint0;        [Microsoft.SqlServer.Server.SqlFunction]          Public Static int Myfun (intint b)   // must use static method, non-static method.          {            1;             return a <= b? a:b;        }

Then the database executes

ALTER ASSEMBLY MYCLR1  from ' I:\Test\MyClr\MyClr\bin\Release\MyClr.dll '  with = Safe;

Ding-ding, winning immediately

Workaround. Remove the static variable, change the static variable to readonly mode, or change the assembly to unsafe mode. Removing static variables and readonly is not a word. Change to unsafe mode, you can execute the following statement

 alter  database  TestDB "set  trustworthy on ; --  go  alter   ASSEMBLY MyClr1  from   '  

You create an assembly, and then you add a function map to use the

Add a function mapCREATE functionClrfbitcontains (@a  as INT,@b  as int )returns INT asEXTERNAL NAME MyClr1.CLRClass.MyFun confirm the need to open CLR execution permissionsexecsp_configure'CLR enabled','1'ReconfigureSELECTDbo. Clrfbitcontains (1,3PS: Each time you modify the dynamic library, you need to re-execute the assembly definition once to update the synchronization.CREATEASSEMBLY MYCLR1 from 'I:\Test\MyClr\MyClr\bin\Release\MyClr.dll' 

Okay, OK.

SQL Server Add external Assembly basic operations