SQL CLR Learning

SQL CLR (SQL Common Language Runtime) is a new feature that has occurred since SQL Server 2005, which injects CLR services from the. NET framework into SQL Server so that some of the database objects of SQL Server can be used The. NET Framework programming language development (currently only supports vb.net and C #), including stored procedures, user-defined functions, triggers, user-defined types, and user-defined summary functions ^[1].

Directory

• 1 Architecture
• 2 Security
• 3 Example
• 4 References

Architecture

The SQL CLR is a version implemented using the Hosting (mount) attribute in the. NET Framework, which allows the components of the. NET Framework to be installed in SQL Server through the implementation of ADO 2.0 In the Microsoft.SqlServer.Server name space. NET mediation data for the ability to obtain SQL Server database objects:

• Stored procedures: SqlProcedureAttribute .
• User function: SqlFunctionAttribute .
• Trigger Program: SqlTriggerAttribute .
• User custom rollup: SqlUserDefinedAggregate .
• User custom type: SqlUserDefinedType .

After the development of the SQL CLR components, it is necessary to use CREATE ASSEMBLY directives to install the components into SQL Server, and then use the corresponding DDL directives to bring the open functions of the components into the database objects in order to be called in the SQL instructions.

CREATE ASSEMBLY sqlclrtest' C:\mydbapp\sqlclrtest.dll '= SAFE   

Security

For SQL Server, the SQL CLR component is an external code, so in SQL Server's default installation configuration, the SQL CLR is blocked from being used, and to use it, you must first turn it on ^[2]:

' CLR enabled '1;

For the SQL CLR component itself, SQL Server also does a triple protection ^[3]:

• SAFE: Only minimal permissions can be run, and external resources and external code cannot be accessed.
• external_access: You can access external resources, such as files, login databases, network resources, and so on.
• UNSAFE: Unlimited access to external resources, even the Win32 API can be called.

In most cases, a component can be used normally using SAFE, unless you want to access external files to use external_access, which activates the UNSAFE hierarchy only in special cases, such as calling external business logic components.

Example

The following examples are code that uses C # to develop SQL Server user functions:

[Microsoft.Sql server.Server.SqlFunction]PublicStatic SqlString hashpasswordstring(SqlString hashstring){sha384managed HashAlgorithm=New sha384managed();Byte[] SrcPassword=System.Text.Encoding.Ascii.GetBytes(hashstring.Value);Byte[] DestPassword=Null;String hashedpasswordstring= null; DestPassword = HashAlgorithm.  ComputeHash(SrcPassword); hashedpasswordstring = System.  Text.  Encoding.  ASCII.  GetString(DestPassword); HashAlgorithm = null; return new SqlString(hashedpasswordstring);}        

The script that installs the component to SQL Server is:

= SAFE

Introducing this function into the DDL script in SQL Server is:

FUNCTION dbo. Hashpassword(   varchar(4000))VARCHAR(4000[ Myassemblylibrary]. [Mysqlclr]. [hashpasswordstring]               

Once installed and introduced, it can be used as a general SQL function:

SELECT dbo. Hashpassword(' MyPassword ')--the value of MyPassword after being Minatomachi River. 

SQL CLR Learning