Transcript Module 8: Implementing Managed Code in the Database

Module 10:
Implementing Managed
Code in the Database
Overview
Introduction to the SQL Server Common
Language Runtime
Importing and Configuring Assemblies
Creating Managed Database Objects
Lesson 1: Introduction to the SQL Server Common
Language Runtime
What Is the .NET Common Language Runtime?
Advantages of Managed Code
SQL Server 2005 CLR Integration
Managed Code vs. Transact-SQL
What Is the .NET Common Language Runtime?
.NET Framework Class Library Support
Thread Support
COM Marshaler
Type Checker
Exception Manager
Security Engine
Debug Engine
MSIL to Native
Compilers
Code Manager
Class Loader
Garbage Collector
Advantages of Managed Code
Advantage
Description
Programming
language
Choice of programming language
Type safety
Type safety through the common type system
Security
Security through code access security and
role-based security
Fast
development
Fast development because of extensive base
class library
Interoperability
Interoperable with unmanaged code
SQL Server 2005 CLR Integration
Run managed code within a database by using inprocess assemblies
Create managed stored procedures, triggers, userdefined functions, user-defined types, and aggregates
Integration benefits:
 Enhanced programming model
 Enhanced safety and security
 Common development environment
 Performance and scalability
Managed Code vs. Transact-SQL
Use managed code for:
 Procedures that feature complex logic
 Access to the .NET Framework class library
 CPU-intensive functions
Use Transact-SQL for data access with only basic
procedural logic
Lesson 2: Importing and Configuring Assemblies
What Is an Assembly?
Syntax for Importing an Assembly
Assembly Trust Levels
Demonstration: Importing and Configuring an Assembly
What Is an Assembly?
Compiled code containing types, methods, and metadata
Unit of deployment for managed code
Executes in-process within the SQLCLR
DLL
.NET Code
Compiled Assembly
SQLCLR
Syntax for Importing an Assembly
Specify a database object name for the assembly
Reference the assembly file
Set assembly trust level (optional)
CREATE ASSEMBLY Contacts
FROM 'C:\ContactsApp\Contacts.dll'
WITH PERMISSION_SET = SAFE
-- default value
Assembly Trust Levels
PERMISSION_SET
SAFE
EXTERNAL_ACCESS
Description
Default setting
Recommended for most scenarios
Access external resources such as
files or registry
Use only if essential
UNSAFE
Unrestricted internal and
external access
Avoid due to potential security risk
Demonstration: Importing and Configuring an Assembly
In this demonstration, you will see how to
import and configure an assembly
Lesson 3: Creating Managed Database Objects
Overview of Managed Database Objects
Managed Stored Procedures, Triggers, and Functions
Managed Aggregates and User-Defined Types
Demonstration: Creating Managed Database Objects
Overview of Managed Database Objects
.NET Assembly
SQL Server 2005
Namespaces
Aggregate Functions
User-Defined Types
Classes
Stored Procedures
Triggers
Methods
User-Defined Functions
Managed Stored Procedures, Triggers, and Functions
Use CREATE PROCEDURE, TRIGGER, or FUNCTION
Link to the assembly by using EXTERNAL NAME
Use the database object as normal
CREATE PROCEDURE Person.UpdatePhoneList AS
EXTERNAL NAME Contacts.[Contacts.PhoneList].SaveList
Assembly Name
Class Name with Namespace
EXEC Person.UpdatePhoneList
Method Name
Managed Aggregates and User-Defined Types
Use CREATE AGGREGATE or TYPE
Link to the assembly by using EXTERNAL NAME
Use the database object as normal
CREATE AGGREGATE Concatenate(@input nvarchar(4000))
RETURNS nvarchar(4000)
EXTERNAL NAME Utilities.[Utilities.Concatenate]
SELECT AccountNumber,
dbo.Concatenate(SalesOrderNumber) Orders
FROM
Sales.SalesOrderHeader
GROUP BY AccountNumber
Demonstration: Creating Managed Database Objects
In this demonstration, you will see how to:
Create a managed user-defined function
Create a managed user-defined type
Lab: Implementing Managed Code in the Database
Exercise 1: Importing an Assembly
Exercise 2: Creating Managed
Database Objects