Sql server 2005 the common language runtime clr integration
Sponsored Links
This presentation is the property of its rightful owner.
1 / 24

SQL Server 2005 The Common Language Runtime (CLR) Integration PowerPoint PPT Presentation


  • 131 Views
  • Uploaded on
  • Presentation posted in: General

SQL Server 2005 The Common Language Runtime (CLR) Integration. CLR Integration CLR Introduction. Highlights Common type system (CTS) Mapping of data types. Programming language  Framework Just-in-time (JIT) compilers JIT compiles intermediary language (MSIL) into native code

Download Presentation

SQL Server 2005 The Common Language Runtime (CLR) Integration

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


SQL Server 2005The Common Language Runtime (CLR) Integration


CLR IntegrationCLR Introduction

  • Highlights

    • Common type system (CTS)

      • Mapping of data types. Programming language  Framework

    • Just-in-time (JIT) compilers

      • JIT compiles intermediary language (MSIL) into native code

      • Highly optimized for platform or device

    • Garbage collector

    • Permission and policy-based security

    • Exceptions

    • Threading

    • Diagnostics and profiling


Thread Support

COM Marshaler

Type Checker

Exception Manager

Security Engine

Debug Engine

MSIL to Native

Compilers (JIT)

Code

Manager

Garbage

Collector (GC)

CLR IntegrationCLR Diagram

  • Common Language Runtime Diagram

Base Class Library Support

Class Loader


CLR IntegrationSQL Server 2005 – CLR

  • Run managed code within a database by using in-process assemblies

  • Create managed stored procedures, triggers, user-defined functions, user-defined types, and aggregates

  • Integration benefits:

    • Enhanced programming model

    • Enhanced safety and security

    • Common development environment

    • Performance and scalability


SQL Engine

CLR

Hosting

Layer

SQL OS Layer

Windows OS

CLR IntegrationDeep Integration with the Database

  • CLR Hosting layer provides coordination

    • Assembly Loading

    • Memory management

    • Security Model

    • Reliability

    • Threads & Fibers

    • Deadlock detection

    • Execution context


Build

VB,C#,C++

Assembly: “TaxLib.dll”

SQL Data Definition: create assembly … create function … create procedure … create trigger … create type …

SQL Queries: select sum(tax(sal,state)) from Emp where county = ‘King’

SQL Server

The Developer Experience

VS .NET Project

Runtime hosted by SQL (in-proc)


CLR IntegrationAvailable Classes

  • Even in supported assemblies, some APIs are not available in SQL

    • Environment.Exit(), Console, etc.

  • Potentially unreliable constructs disabled

    • No thread creation

    • No shared state or synchronization

    • No listening on sockets in server

    • No finalizers

  • Eliminate functionality N/A to database

    • System.Windows.Forms

    • System.Drawing

    • System.Web, …


CLR IntegrationSQL Server Projects in Visual Studio 2005

  • Project for creating managed database objects

  • Automatically includes necessary references

    • System

    • System.Data.dll

  • Includes templates for each object type

    • Stored procedure

    • Trigger

    • User-defined function

    • User-defined type

    • Aggregate

  • Allows immediate deployment and debugging


CLR IntegrationThe System.Data.SqlServer Namespace


CLR IntegrationRegistering an assembly

  • CREATE ASSEMBLY assembly_name

    • [ AUTHORIZATION owner_name ]

    • FROM { < client_assembly_specifier > | < assembly_bits > [,...n] }

    • [ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]

    • < client_assembly_specifier > :: =

    • '[\\machine_name\]share_name\[path\]manifest_file_name'

    • < assembly_bits > :: =

    • { varbinary_literal | varbinary_expression }


CLR IntegrationAssembly Security -PERMISSION_SET

  • SAFE

    • May not access external resources: registry, file system, or network

    • May access data using the current context but not via SQLClient or any other data provider

    • No thread processing

  • EXTERNAL_ACCESS

    • May access external resources:registry, file system, network, environment variables

  • UNSAFE

    • May access external resources

    • Can use SQLClient and other data providers

    • Can use thread constructs

    • (No restrictions; similar to extended stored procedures)


CLR IntegrationMeta Data of Assemblies

Create

Assembly

Assembly source code:

Sys.assembly_files

Assembly references:

Sys.assembly_references

Details of Assembly:

Sys.assemblies

Other meta data information

  • SYS.OBJECTS

  • SYS.ASSEMBLY_MODULES

  • SYS.ASSEMBLY_TYPES


CLR IntegrationUser Defined Functions

  • Similar to T-SQL function

  • Written in CLR language

    • Decorated with [SqlFunction] attribute in code

    • Assembly loaded into the database

    • Function defined from assembly

  • Limits on functions

    • must be in public class

    • cannot be in nested class

    • method must be public and static


CLR IntegrationUser Defined Functions -Example

public class MyFunctions

{

[SqlFunction]

public static SqlString GetLongDate(SqlDateTime DateVal)

{

// Return the date as a long string

return DateVal.Value.ToLongDateString();

}

}


CLR IntegrationUser Defined Functions

  • Properties have impact on whether or not computed column that use these functions can be indexed.

    • IsDeterministic = true

      (it always produces the same output values given the same input values and the same database state.)

    • DataAccess

      • DataAccessKind.None: Does not access data.

      • DataAccessKind.Read: Only reads data.

    • SystemDataAccess

      • SystemDataAccessKind.None: Does not access system data.

      • SystemDataAccessKind.Read: Only reads system data.

    • IsPrecise = { true | false }

      (that indicates whether the routine involves imprecise computations such as floating point operations. )


CLR Integration.Net Stored Procedures (1)

  • Capable of doing everything a T-SQL proc can do.

  • Uses a Shared method (static in C#)

  • Pass parameters both ways

    • OUTPUT parameters should be byref (ref in C#)

  • Return multiple result sets


CLR Integration.Net Stored Proc Can Return (2)

  • Numeric return code

  • Count of rows affected by the command

  • Scalar value

  • Single row

  • One or more multi row result sets

  • A stream of XML


CLR IntegrationStored Procedure

public class ContactCode

{

[SqlProcedure]

public static void GetContactNames()

{

SqlCommand cmd = ……. ……

cmd.CommandText = "SELECT FirstName + ' ' + LastName" +

  • " AS [Name] FROM Person.Contact";

    SqlDataReader rdr = cmd.ExecuteReader();

    SqlPipe sp = …………..;

    sp.Send(rdr);

    }

    }


CLR IntegrationCreate Sql Server Proc.

Syntax :

create procedure ProcName

as external name <assemblyname>.<classname>.<methodname>

Example :

create procedure GetContactsName as external name assemblyname.ContactCode. GetContactNames


CLR IntegrationTriggers

public class ContactCode

{

[SqlTrigger(Name="ContactUpdTrg", Target="Person.Contact", Event="FOR UPDATE")]

public static void ChangeEmail()

{SqlTriggerContext trg = SqlContext.GetTriggerContext();


CLR IntegrationWhen to use T-SQL

  • T-SQL better used for data access

    • All pre-SQL Server 2005 code is written in T-SQL

    • SQL Server 2005 adds exception handling to T-SQL

  • T-SQL can be faster for data access

    • Direct access to SQL Server's internal buffers

    • Rich, data-centric library of functions

    • No conversion of types


CLR IntegrationFeature Comparison with T-SQL


CLR IntegrationBest uses of SQLCLR

  • Computational functions are always faster

  • Streaming table valued functions

  • User defined aggregates

    • Orders magnitude faster than server or client cursor solutions

  • Scalar functions

    • Function body is compiled to native code

  • Use managed code for:

    • Procedures that feature complex logic

    • Access to the .NET Framework class library

    • CPU intensive functions


SQLCLR GuidanceMid Tier vs. Data Tier

  • SQLCLR support does not mean move all business logic to server

  • Candidates for moving to server

    • Centralized data validation

    • Process large amount of data while needing a small portion of it for application use


  • Login