![Page 1: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/1.jpg)
SQL Server 2005 The Common Language Runtime (CLR) Integration
![Page 2: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/2.jpg)
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
![Page 3: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/3.jpg)
CLR IntegrationCLR Diagram
Common Language Runtime Diagram
Class Loader
MSIL to NativeCompilers (JIT)
CodeManager
GarbageCollector (GC)
Security Engine Debug Engine
Type Checker Exception Manager
Thread Support COM Marshaler
Base Class Library Support
![Page 4: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/4.jpg)
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
![Page 5: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/5.jpg)
CLR IntegrationDeep Integration with the Database
CLR Hosting layer provides coordination Assembly Loading Memory management Security Model Reliability Threads & Fibers Deadlock detection Execution contextWindows OSWindows OS
SQL OS LayerSQL OS Layer
SQL EngineSQL Engine
HostingHostingLayerLayer
CLRCLR
![Page 6: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/6.jpg)
VS .NET VS .NET ProjectProject
Assembly: “TaxLib.dll”
VB,C#,C++VB,C#,C++ BuildBuild
SQL ServerSQL Server
SQL Data Definition: SQL Data Definition: create create assembly … assembly … create function … create function … create create procedure … procedure … create trigger … create trigger … create create type …type …
SQL Queries: SQL Queries: select select sum(sum(tax(sal,state)tax(sal,state)) ) from Emp where county from Emp where county = ‘King’= ‘King’
Runtime hosted by SQL
(in-proc)
The Developer The Developer ExperienceExperience
![Page 7: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/7.jpg)
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, …
![Page 8: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/8.jpg)
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
![Page 9: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/9.jpg)
CLR IntegrationThe System.Data.SqlServer Namespace
ClassClass DescriptionDescription
SqlContext Provides access to other objects, like a Provides access to other objects, like a connectionconnection
SqlConnection An open connection to a SQL Server An open connection to a SQL Server databasedatabase
SqlCommand Used to send a command to the database Used to send a command to the database serverserver
SqlParameter Supplies a parameter for a SqlCommand Supplies a parameter for a SqlCommand objectobject
SqlPipe Used to send results or information to the Used to send results or information to the clientclient
SqlDataReader Reads the data one row at a time, forward Reads the data one row at a time, forward onlyonly
SqlResultSet For working with flexible server-side cursorsFor working with flexible server-side cursors
SqlTransaction For providing transactional behaviorFor providing transactional behavior
SqlTriggerContext
Provides information about the trigger Provides information about the trigger actionaction
![Page 10: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/10.jpg)
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 }
![Page 11: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/11.jpg)
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)
![Page 12: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/12.jpg)
CLR Integration Meta Data of Assemblies
Cre
ate
Ass
embl
yDetails of Assembly:
Sys.assembliesAssembly source code:
Sys.assembly_filesAssembly references:
Sys.assembly_references
Other meta data information•SYS.OBJECTSSYS.OBJECTS•SYS.ASSEMBLY_MODULESSYS.ASSEMBLY_MODULES•SYS.ASSEMBLY_TYPESSYS.ASSEMBLY_TYPES
![Page 13: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/13.jpg)
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
![Page 14: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/14.jpg)
CLR Integration User Defined Functions -Example
public class MyFunctions {
[SqlFunction]public static SqlString GetLongDate(SqlDateTime DateVal){
// Return the date as a long stringreturn DateVal.Value.ToLongDateString();
}
}
![Page 15: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/15.jpg)
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. )
![Page 16: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/16.jpg)
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
![Page 17: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/17.jpg)
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
![Page 18: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/18.jpg)
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);
}}
![Page 19: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/19.jpg)
CLR IntegrationCreate Sql Server Proc.
Syntax :create procedure ProcNameas external name
<assemblyname>.<classname>.<methodname>
Example :create procedure GetContactsName as external
name assemblyname.ContactCode. GetContactNames
![Page 20: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/20.jpg)
CLR IntegrationTriggers
public class ContactCode
{[SqlTrigger(Name="ContactUpdTrg",
Target="Person.Contact", Event="FOR UPDATE")]
public static void ChangeEmail()
{SqlTriggerContext trg = SqlContext.GetTriggerContext();
![Page 21: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/21.jpg)
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
![Page 22: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/22.jpg)
CLR IntegrationFeature Comparison with T-SQL
T-SQLT-SQL CLRCLR
User User Defined Defined FunctionsFunctions
XX XX
Stored Stored ProceduresProcedures
XX XX
TriggersTriggers XX XX
User User Defined Defined TypesTypes
XX
AggregatesAggregates XX
![Page 23: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/23.jpg)
CLR Integration Best 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
![Page 24: SQL Server 2005 The Common Language Runtime (CLR) Integration](https://reader034.vdocuments.us/reader034/viewer/2022051316/56815809550346895dc57962/html5/thumbnails/24.jpg)
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