2779B Module 10 - Implementing Managed Code in the Database

Advantages

Language –any .NET language

Type Safety – common type system

Security – “code access security” and role base security

Fast Development – Ooo Extensive base class library

Interoperability – can interact with unmanaged code (not .net)

CLR Integration

-use managed code within db by using imported assemblies

-create stored procedures, triggers, user-defined funcs, user defined types, and aggregates from imported, managed assemblies

-Benefits

-programming model

-Safety

-familiar dev environment

-performance and scalability

When to use this managed code

-use managed code for

-stored procedures that use complex logic

-accessing .NET framework class library

-CPU-intensive funcs

-“hard stuff”

-Use TSQL for data access with little or no procedural logic, or with complex data access

What is an assembly?

-a unit of deployment for managed code

-contains all functionality that the managed code was designed to hold

-methods are compiled into a class

-a class becomes an assembly when compiled

-properties are used by the assembly

-in SQL server assemblies execute in process

-.Net has two kinds of assemblies (.dll and .exe)

-in sql server you can only use dll

How do you import an assembly?

-Syntax

CREATE ASSEMBY Contacts

FROM ‘c:\ContactsApp\Contacts.dll’

WITH PERMISSION_SET = SAFE –default value (permission_set is optional)

-There are three trust levels

Trust Levels (code access security)

-nothing to do with the user who is executing your object, it only restricts the code. (dll).

-three levels

1. SAFE: Recommended for most assemblies (cant access anything outside SQL Server)

2. EXTERNAL_ACCESS: Allows access to external variables and resources.

3. UNSAFE: Unrestricted access internal and external (Potential security risk)

(Note: you have to be a member of sysadmin to declare am assembly unsafe)

Demonstration

--You need to enable CLR integration to allow this stuff.

-- Enable CLR integration

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

-- Register assembly

USE AdventureWorks

CREATE ASSEMBLY Utilities

FROM 'D:\Democode\Utilities.dll'

WITH PERMISSION_SET = Safe

GO

-- Drop assembly

DROP ASSEMBLY Utilities

GO

Creating Database Objects from Assemblies

-woo

-syntax:

CREATE PROC Person.UpdatePhoneList AS
EXTERNAL NAME Contacts.[Contacts.PhList].Save

Assembly Name . Class Name with Namespace . Method Name

Assembly Name: Contacts

Class Name with Namespace: Contacts.PhList --VB.NET

Method Name: save

Creating Aggregates and User defined Types

-Syntax:
CREATE AGGREGATE Concatenate(@input nvarchar(4000))

RETURNS nvarchar(2000)

EXTERNAL NAME Utilities.[Utilities.Concat]

To use:

SELECT accountnumber, concat(Salesordernumber) orders from Sales.SalesordersHeader