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

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


  • 121 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 2005 the common language runtime clr integration

SQL Server 2005The Common Language Runtime (CLR) Integration


Clr integration clr introduction

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


Clr integration clr diagram

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 integration sql server 2005 clr

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


Clr integration deep integration with the database

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


Sql server 2005 the common language runtime clr integration

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 integration available classes

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 integration sql server projects in visual studio 2005

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 integration the system data sqlserver namespace

CLR IntegrationThe System.Data.SqlServer Namespace


Clr integration registering an assembly

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 integration assembly security permission set

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 integration meta data of assemblies

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 integration user defined functions

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 integration user defined functions example

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 integration user defined functions1

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

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

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 integration stored procedure

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 integration create sql server proc

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 integration triggers

CLR IntegrationTriggers

public class ContactCode

{

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

public static void ChangeEmail()

{SqlTriggerContext trg = SqlContext.GetTriggerContext();


Clr integration when to use t sql

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 integration feature comparison with t sql

CLR IntegrationFeature Comparison with T-SQL


Clr integration best uses of sqlclr

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 guidance mid tier vs data tier

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