Programando SQL Server 2005 con el CLR Integración SQLCLR

1. Programando SQL Server 2005 con el CLRIntegración SQLCLR

2. Adolfo Wiernikadolfo@wiernik.net • Microsoft Regional Director - http://msdn.microsoft.com/isv/rd • Mentor Solid Quality Learning - http://www.solidqualitylearning.com • Fundador, Costa Rica User Group .NET - http://www.crug.net • Orador INETA Latinoamérica - http://www.ineta.org/latam • Blog - http://www.wiernik.net Jose Ricardo Ribeiroricardor@microsoft.com • En Microsoft desde 1998 • Desde el 2003 - Regional Program Manager • SQL Server Latinoamérica

3. Series de Webcasts • Introducción a SQL Server 2005 para desarrolladoresViernes, 22 de Julio de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277969&Culture=es-MX • Nuevas características del lenguaje T-SQL en SQL Server 2005Lunes, 25 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277973&Culture=es-MX • Aprovechando XML dentro de la base de datos con SQL Server 2005Viernes, 29 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277975&Culture=es-MX • Programando SQL Server 2005 con el CLR – Integración SQL-CLRLunes, 01 de Agosto de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277977&Culture=es-MX • Nuevas características en ADO.NET 2.0Viernes, 05 de Agosto de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277978&Culture=es-MX

4. Agenda • Integración SQL Server - Common Language Runtime • Beneficios • Ventajas • Porque • Objetivos de Diseño • Arquitectura • Catalogando Assemblies • Construyendo objetos SQLCLR • User Defined Functions • User Defined Aggregate Functions • User Defined Types • CLR Stored Procedures • CLR Triggers

5. Nuevas Características para Desarrollo SQL Server Engine • SQL Service Broker • HTTP Support (Native HTTP) • Multiple Active Result Sets (MARS) • Snapshot Isolation Level Reporting Services • Multiple Output Formats • Parameters (Static, Dynamic, Hierarchical) • Bulk Delivery of Personalized Content • Support Multiple Data Sources • STS (Web Parts, Doc Libraries) • Visual Design Tool • Charting, Sorting, Filtering, Drill-Through • Scheduling, Caching • Complete Scripting Engine • Scale Out architecture • Open XML Report Definition Notification Services SQL Server Mobile Edition MDAC • SNAC • Microsoft Installer base setup ADO.NET 2.0 • Notification Support • Object Model enhancements SQL Client .NET Data Provider • Server Cursor Support • Asynchronous Execution • System.Transactions Security • Separation of Users and Schema • Data encryption primitives Administration • SQL Management Objects (SMO) • Analysis Management Objects (AMO) • Replication Management Objects (RMO) T-SQL • Recursive Queries • Common Table Expressions • PIVOT – UNPIVOT Operators • APPLY Operator • Exception Handling .NET Framework • Common Language Runtime Integration • User-defined Aggregates • User-defined Data Types • User-defined Functions • SQL Server .NET Data Provider • Extended Triggers Data Types • Managed SQL Types • New XML Datatype • Varchar (MAX) Varbinary (MAX) XML • XQUERY Support • XML Data Manipulation Language • FOR XML Enhancements • XML Schema (XSD) Support • MSXML 6.0 (Native) • .Net XML Framework Full-text Search • Indexing of XML Datatype

6. Agenda • SQL Server as a runtime host • Using assemblies • Procedural code or Transact-SQL • Writing .NET procedural code • .NET data types and System.Data.SqlTypes • Attributes

7. Benefits • Better programming model • Improved safety and security • Ability to define data types and aggregate functions • Streamlined development through a standardized environment • Potential for improved performance and scalability

8. Advantages? • When to use Transact-SQL: • designed for direct data access and manipulation in the database • Transact-SQL does not support : • arrays • Collections • for-each loops • bit shifting • classes • When to use Managed Code: • Calculations • complicated execution logic • extensive support for many complex tasks including string handling and regular expressions, advanced math operations, file access, cryptography • access to thousands of pre-built classes and routines

9. Why? • You can author (in managed code) • stored procedures • Triggers • user-defined functions • user-defined types • user-defined aggregates

10. Design Goals for SQL CLR Integration • Reliability • User code should not be allowed to perform operations that compromise the integrity of the database engine process or overwrite database engine memory buffers or internal data structures • Scalability • ensure that the scalability of the system is not compromised by user code calling application programming interfaces (APIs) for threading, memory, and synchronization primitives directly • Security • User code must follow SQL Server authentication and authorization rules when accessing database objects such as tables and columns, database administrators should be able to control access to operating system resources from user code • Performance • must perform as well or better than equivalent implementations by native database engine functionality or through Transact-SQL

11. CLR calls low-level routines implemented by SQL Server for threading, scheduling, synchronization, and memory management Same primitives that the rest of the SQL Server engine uses Enforce: Scalability, Reliability, Security & Performance SQL CLR Integration Architecture Metadata MSIL Assembly JIT Compiler Common Language Runtime Low level routines SQL Server Win32 API Windows OS

12. Scalability • Memory Management: • CLR calls SQL Server primitives for allocating and de-allocating its memory • ensures that the CLR and SQL Server are not competing with each other for memory • This allows SQL Server memory manager to: • reject CLR memory requests when system memory is constrained • ask CLR to reduce its memory use when other tasks need memory • Threading • CLR calls SQL Server APIs for creating threads • Both for running user code and for its own internal use • To synchronize, CLR calls SQL Server synchronization objects • This allows the SQL Server scheduler to control threading, for example: • schedule other tasks when a thread is waiting on a synchronization object (for example, while GC) • detect deadlocks that involve locks taken by CLR synchronization objects • ability to detect and stop threads that have not yielded for a significant amount of time • identify "runaway" threads in the CLR and manage their priority

13. Reliability • Programming Model: • The programming model for managed code in SQL Server involves writing functions, procedures, and types which typically do not require the use of state held across multiple invocations or the sharing of state across multiple user sessions. • The presence of shared state can cause critical exceptions that impact the scalability and the reliability of the application. • Avoid the use of static variables and static data members of classes used in SQL Server. For SAFE and EXTERNAL-ACCESS assemblies, SQL Server examines the metadata of the assembly at CREATE ASSEMBLY time, and fails the creation of such assemblies if it finds the use of static data members and variables • SAFE and EXTERNAL ACCESS assemblies cannot be annotated with some custom attributes • Example: System.Security.SuppressUnmanagedCodeSecurityAttribute, System.ContextStaticAttribute System.MTAThreadAttribute • Check complete list in BOL • Depend only on “blessed” list of assemblies • Hard coded list of revised assemblies • List can not be extended

14. Security • Code Access Security (CAS) • Machine Policy • User Policy • specific to the Windows account that the SQL Server service is set up to run as • Host Policy (SQL Server) • The permissions granted to code in SQL Server is the intersection of permissions granted by the above three policy levels • The set of CAS permissions granted to assemblies by the SQL Server Host policy level is determined by the permission set specified when creating the assembly • SAFE • EXTERNAL_ACCESS • UNSAFE • Microsoft Frameworks assemblies and SQL Server system assemblies use a fixed policy that grants them full trust

15. Security - SAFE • Default permission set • Code cannot access external system resources: • files, the network, environment variables, or the registry • can only access data from the local SQL Server databases • And perform any computation on that data • Recommended permission set

16. Security – EXTERNAL_ACCESS • Allows access to certain external system resources • files, networks, web services, environmental variables, and the registry • Can only access assemblies that are verifiably type-safe • Only SQL Server logins with EXTERNAL ACCESS permissions can create EXTERNAL_ACCESS assemblies • Code in EXTERNAL_ACCESS assemblies runs by default under the SQL Server service account and accesses external resources under that account (unless the code explicitly impersonates the caller). • Permission to create EXTERNAL_ACCESS assemblies should be granted only to logins who are trusted to execute code under the SQL Server service account

17. Security - UNSAFE • Unrestricted access to resources, both within and outside SQL Server • Can call unmanaged code • Can execute type-unsafe code • Should be granted only to highly trusted assemblies • Only members of the sysadmin fixed server role can create UNSAFE assemblies

18. Enabling CLR Integration • Off by default EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO sp_configure 'clr enabled', 1 GO RECONFIGURE GO

19. Cataloging assemblies • CREATE ASSEMBLY is used to catalog an assembly • bits can be loaded from disk or stream • assembly is assigned a symbolic name • bits are stored in system table • SQL Server principal running create must have • assembly catalog permissions • file system access to the code assembly name symbolic name CREATE ASSEMBLY math FROM 'c:\types\math.dll'

20. Removing assemblies • DROP ASSEMBLY removes an assembly from catalog • dependent sprocs, udfs, triggers, udts must be dropped first • includes tables and computed columns that use them • will fail if another assembly depends on it removes math assembly DROP ASSEMBLY math

21. Assembly metadata information • SQL Server has revised system metadata views • no direct access to system metadata • assembly information lives in • sys.assemblies • sys.assembly_files • sys.assembly_references

22. What can be built? • User Defined Functions (UDFs) ¤ • Scalar valued (scalar UDF) • Table valued (TVF) • User Defined Procedures (UDP) ¤ • User Defined Types (UDTs) ŧ • User Defined Triggers ¤ • User Defined Aggregates ŧ ¤Mapped to public static function ŧMapped to entire class

23. Basics • Needed namespaces: • System.Data • System.Data.Sql • Microsoft.SqlServer.Server • Server-side provider SqlContext • System.Data.SqlTypes

24. System.Data.SqlTypes • Provides data types with same semantics & precision as in SQL Server • NULL values • Not supported for value types in .NET 1.* • .NET 2.* presents Nullable<T> generic • Boolean values with 3 values • True, False & Unknown • IsTrue(), IsFalse(), IsNull() in SqlBoolean • Arithmetic & Bitwise Operators should accept null values • Overflow detection off by default in .NET, on by default on SqlTypes

25. User Defined Functions • Implemented as public static method in class • Can take parameters • Should return a result • Type of UDFs: • Scalar, returns a single value • Table, returns result set • Benefits: • Allow modular programming • Allow faster execution • Can reduce network traffic • Can be invoked on WHERE to filter rows sent to client

26. User Defined Functions • Scalar UDFs : Can return any scalar data type except text, ntext, image • TVFs, return type is IEnumerable / IEnumerator • Returns a streaming result set • Results can be consumed as soon as first row is available • Results don’t have to be loaded in memory as a whole

27. User Defined Functions Steps for scalar UDFs: • Create a class with public static method • Mark method with SqlFunction attribute • Register assembly CREATE ASSEMBLY asm_Calculos FROM 'Calculos.dll' • Register function CREATE FUNCTION GetList(@p int) RETURNSint AS EXTERNAL NAME asm_Calculos.[FuncionesCalculos].GetList

28. User Defined Aggregate Functions • Operate on a set of input scalars • Generate a single aggregate result • Examples: SUM, AVG, MAX • Previously one needed to write complex cursor logic (either client side or server side) • Query processor manages iteration • Magnitud faster than using cursors

29. User Defined Aggregate Functions • CLR must: • Mark type with SqlUserDefinedAggregate attribute • IsInvariantToDuplicates {true, false} • Example: MIN, MAX • IsInvariantToNulls {true, false} • Example: AVG, MIN, SUM, MAX • IsInvariantToOrder {true, false} • Example: AVG, MIN, SUM, MAX • IsNullIfEmpty {true, false} • Have a public constructor • Special aggregation methods • Init() • Accumulate() • Merge() • Terminate()

30. User Defined Aggregate Functions • CREATE AGGREGATE name(input params)RETURNS out paramEXTERNAL NAME assembly.class name

31. User Defined Types • Can be used wherever a SQL Server type can be used • Tip :: create it as a structure, although class is also supported • Requirements: • Mark with SqlUserDefinedType attribute • Must have a public constructor • Restrictions: • Name must be less than 128 characters • No method overloading (except constructor) • No support for inheritance • Static members can’t be mutable (must be const or readonly)

32. User Defined Type • Things to keep in mind: • Nullability • Serialization • Binary, for comparisons, indexing, contraining, etc. • XML, for exchanging and searching • String comparisons • Validation & Comparisons

33. User Defined Type • CREATE TYPE name[NULL | NOT NULL]EXTERNAL NAME assembly.class • Assembly and type are registered per database • If want to share between databases, then it must be registered in all databases • Assembly must be equivalent  strong name match • If want to use UDT in tempDB, then it must be registered also in tempDB

34. CLR Stored Procedures • Can’t be used in scalar expressions • Can return: • Output Parameters • Use byref parameters in CLR • Tabular Results • Use SqlContext.Pipe.Execute() • Most efficient, sends stream directly to client • Use SqlContext.Pipe.Send() • Allows for manipulation of results before sending to client • Can return SqlDataReader • SqlDataRecord • Allows for dynamic result sets, manually populated • Send through SqlContext.Pipe.Send • Messages • Use SqlContenxt.Pipe.Send() • Same as PRINT CREATE PROCEDURE name AS EXTERNAL NAME Assembly.Class.Metodo

35. CLR Triggers • With CLR triggers, you can: • Reference data in the INSERTED and DELETED tables • Determine which columns have been modified as a result of an UPDATE operation • Access information about database objects affected by the execution of DDL statements • SqlContext.GetTriggerContext() is a factory for SqlTriggerContext class • Provides context information about the trigger • Type of action that triggered • Modified columns in UPDATE • If DDL, XML EventData structure with description of operation

36. CLR Trigger CREATE TRIGGER triggerName ON DBObject FOR INSERT{action} AS EXTERNAL NAME assembly.class.method

37. Conclusiones • Integración SQL Server - Common Language Runtime • Beneficios • Ventajas • Porque • Objetivos de Diseño • Arquitectura • Catalogando Assemblies • Construyendo objetos SQLCLR • User Defined Functions • User Defined Aggregate Functions • User Defined Types • CLR Stored Procedures • CLR Triggers

38. Recursos • SQL Server 2005 – Laboratorios Virtualeshttp://msdn.demoservers.com/login.aspx?group=sql2005http://www.microsoft.com/technet/traincert/virtuallab/sql.mspx(only supports SQL 2000) • SQL Server 2005 http://www.microsoft.com/sql/2005 • SQL Server Express http://www.microsoft.com/sql/express • Visual Studio 2005 http://lab.msdn.microsoft.com/vs2005

39. Series de Webcasts • Introducción a SQL Server 2005 para desarrolladoresViernes, 22 de Julio de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277969&Culture=es-MX • Nuevas características del lenguaje T-SQL en SQL Server 2005Lunes, 25 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277973&Culture=es-MX • Aprovechando XML dentro de la base de datos con SQL Server 2005Viernes, 29 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277975&Culture=es-MX • Programando SQL Server 2005 con el CLR – Integración SQL-CLRLunes, 01 de Agosto de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277977&Culture=es-MX • Nuevas características en ADO.NET 2.0Viernes, 05 de Agosto de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277978&Culture=es-MX

40. Gracias adolfo@wiernik.net adolfo@solidqualitylearning.com Weblog: www.wiernik.net