SQL CLR Learning

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.