"Go" SQL SERVER CLR stored procedure implementation

To do a recent project, you need to do a CLR stored procedure for SQL SERVER 2005 and study the implementation of the CLR. For the convenience of later use, summed up my implementation process here, but also for the CLR interested but do not know how to achieve the friends to do a reference, if there are different views, hope to teach more. Here is a first example of implementing CLR stored procedures.

1. First, enable CLR functionality for the database

sp_configure ' clr enabled ', 1;
GO
RECONFIGURE;
GO

2. That is, to create a CLR stored procedure, you first have to write a class library in C # (or another language) to implement the logic that the stored procedure executes. This class library can be created in two ways: one is to create a normal C # class library, and the other is to create a SQL Server Project with VS2008. Method: New project/database projects/ Microsoft SQL Server/sql clr/sql Server Project.

Whichever method you use, you should eventually meet these two conditions:

(1) for SQL Server 2005, the. NET assembly used must be. NET 2.0.

(2) The method to be stored as a procedure must be static,public, with the addition of sqlprocedure attached properties.

1    [sqlprocedure]2     Public Static void Execsqlclrproc (intint  iversion)3   {4   5    }

Here I use vs to build a SQL Server Project, because it can automatically implement these two steps for us. With this property of [SqlProcedure], SQL Server can use this public method to create a stored procedure.

3. Once the CLR project is built, you need to implement the CLR into the database.

It can be implemented in two ways:

(1): With the deploy operation in VS2008, the built-in storage can be automatically implemented into the database.

(2): Executed in T-sql:

CREATE ASSEMBLY from ' DLL Path ' with permission_set = safe| external_access| Unsafe creates an assembly for the DLL.

Create Proc Ncip_parsemessage
Params
As
EXTERNAL NAME Methodpath

Creates a stored procedure based on the methods exposed in the assembly.

It is important to note that SQL Server provides three levels of access to CLR assemblies: Safe,external_access,unsafe. If the level of safe is to be implemented, then any of the two methods used directly can be implemented. But because I want to access network resources in the stored procedure, I want to implement it with the permission level of external_access or unsafe

4. A CLR stored procedure that implements the External_access permission level. (unsafe use is not recommended)

(1) Use strong name for SQL CLR. First generate an SNK file with SN.exe, right click on the project-properties-signing-select sign the assembly, select the generated SNK file.

(2) Generate a key for the DLL in the database.

Use master
CREATE asymmetric KEY sqlclrkey from executable FILE = ' DLL Path '
CREATE LOGIN loginname from asymmetric KEY sqlclrkey
GRANT EXTERNAL ACCESS ASSEMBLY to LoginName

Then it can be implemented in the 3 method.

5. Multiple assemblies refer to each other. If you have more than one assembly and there are references before, you might want to do the following:

(1) in the AssemblyInfo.cs of each referenced project, add the following code:

1 using system.security; 2 [assembly:allowpartiallytrustedcallers]

(2) If you are using SQL Server project, also change the project property/assembly name to a unique name

(3) Generate an SNK file for each individual project and create the appropriate asymmetric Key,login to grant LOGIN the appropriate access rights.

"Go" SQL SERVER CLR stored procedure implementation