Avantages & Inconvénients

dvoituron@outlook.com

Denis VOITURON – Principal .NET Architect

@DenisVoituronwww.dvoituron.com

2007

Principal Microsoft .NET Architect chez Trasys / NRB

Denis Voituron

1995

Ingénieur Civil (Mons – Belgique)

1999

Fondateur d’une société spécialisée dans les CMS

dvoituron@outlook.com

@DenisVoituron

https://www.dvoituron.com

2015

Créateur et animateur du podcast DevApps.be

Agenda

ConceptsHistorique

SQL Server

Azure

Sécurité

Hello World (démo)

Fonction Scalaire

Fonction Table

Déploiements

Déclencheur (Trigger)

Fonction d’agrégation

Avantages & Inconvénients

Concepts

Qu’est ce que SQL CLR ?

Intégration du moteur d’exécution .NET dans SQL Server

• Intégration de la machine virtuelle du Framework .NET :

Common Language Runtime (CLR).

• Mise à disposition de fonctionnalités de développement riches

et hébergées par SQL Server.

• La couche d'hébergement coordonne les chargements des

assemblies (DLL), les tâches (Threads), la mémoire, la sécurité,

le contexte d'exécution, etc.

• Propose de nouvelles fonctionnalités sécurisées

(contrairement aux Extended Stored Procedures – xp)

SQL Engine

Windows

SQL OS

Hosting Layer

CLR

Disponible depuis SQL Server 2005

Pas (encore) dans Azure SQL Database SQL Server dans une VM Azure

SQL CLR et Azure

SQL CLR et AzureLe 19 avril 2017, Scott Guthrie a présenté Azure SQL Managed Instance

https://channel9.msdn.com/Events/Data-Science/Microsoft-Data-Amp-2017

Intégration du CLR dans SQL Server

SELECT value FROM sys.dm_clr_properties WHERE name = 'version'

Catégorie 2005 2008 2012 / 2014 / 2016

Supported .NET

Framework Libraries

CustomMarshalers

Microsoft.VisualBasic

Microsoft.VisualC

mscorlib

System

System.Configuration

System.Data

System.Data.OracleClient

System.Data.SqlXml

System.Deployment

System.Security

System.Transactions

System.Web.Services

System.Xml

+ System.Core

System.Xml.Linq

User-Defined Types

User-Defined Aggregates

Table-Valued Functions

Max 8000 bytes

Max 8000 bytes & 1 input parameter

No

Max 2 GB

Max 2 GB & 1 or more parameters

Yes

CLR Version 2.0 4.0

.NET Framework

Version2.0 / 3.0 / 3.5 4.0 / 4.5 / 4.5.x

.NET Framework Version

Installed with SQL Server2.0 3.5 SP1 4.0

Ref: http://www.sqlservercentral.com/articles/Stairway+Series/119429

Sécurité

Query Processor

SQL CLR Modules(Stored Procedures, User-Defined Types / Aggregates, Table-Valued Functions, Triggers)

CLR Hosting Module (.NET Framework BCLs)

SQL Server

Database Engine

Database

SAFE

Native DLLs,

COM DLLs

UNSAFE

Files, Registry,

Network

EXTERNAL_ACCESS

SQL Query Results

Hello World

Activer les CLR

Par défaut, les CLR ne sont pas activées.

SqlContext est un object .NET qui donne accès aux composants

• IsAvailable - Détermine si le contexte est disponible.

• WindowsIdentity - Récupère l’identité de l’appelant.

• SqlTriggerContext - Fournit des informations sur le déclencheur qui a été activé.

• SqlPipe - Canal pour envoyer des messages ou des résultats.

sp_configure 'clr enabled', 1 GO

RECONFIGUREGO

Créer une fonction CLR “Scalaire”

[SqlFunction(DataAccess = DataAccessKind.Read)]public static bool ComparableTo(string source, string target){

return String.Compare(new string(source.Where(c => char.IsLetterOrDigit(c)).ToArray()),new string(target.Where(c => char.IsLetterOrDigit(c)).ToArray()),CultureInfo.InvariantCulture, CompareOptions.IgnoreNonSpace | CompareOptions.IgnoreCase) == 0;

}

CREATE ASSEMBLY [MyLibrary] FROM 'C:\MyLibrary.dll'WITH PERMISSION_SET = SAFE

CREATE FUNCTION [ComparableTo] (@source NVARCHAR(MAX), @target NVARCHAR(MAX))RETURNS BITAS EXTERNAL NAME [MyLibrary].[MyLibrary.MyClass].[ComparableTo];

SELECT dbo.ComparableTo('Van Deput va à l''école', 'Vandeput va a l''ecole ')

Déployer dans SQL Server

Utilisation de SqlClrDeployment https://github.com/Apps72/Dev.SqlClrDeployement

SqlClrDeployment MyLibrary.dll -cs="Server=(localdb)\ProjectsV12;Database=Scott;"

SqlClrDeployment MyLibrary.dll –output=MonScript.sql

Créer une fonction CLR “Table”

public static IEnumerable GetTable(){

}

public class MyData{

public int Id { get; set; }public DateTime HireDate { get; set; }

}

var connection = new SqlConnection("context connection=true");connection.Open();

using (var adapter = new SqlDataAdapter(" SELECT EMPNO AS ID, HIREDATE FROM EMP ", connection)){

var data = new DataTable();adapter.Fill(data);

}

var result = new List<MyData>();

foreach (DataRow row in data.Rows){

result.Add(new MyData(){

Id = Convert.ToInt32(row["ID"]),HireDate = Convert.ToDateTime(row["HIREDATE"])

});}return result;

Créer une fonction CLR “Table”

public static IEnumerable GetTable(){

using (var cmd = new SqlDatabaseCommand(SqlDatabaseCommand.GetContextConnection())){

cmd.CommandText.AppendLine(" SELECT EMPNO AS ID, HIREDATE FROM EMP ");return cmd.ExecuteTable<MyData>();

}}

Install-Package Apps72.Dev.Data.SqlServerClr

public class MyData{

public int Id { get; set; }public DateTime HireDate { get; set; }public int Year{

get { return DateTime.Today.Year - HireDate.Year; }}

}

CREATE FUNCTION [GetTable] ()RETURNS TABLE(ID INT, Year INT)AS EXTERNAL NAME [MyLibrary].[MyLibrary.MyClass].[GetTable];

Créer une fonction CLR “Table”

[SqlFunction(DataAccess = DataAccessKind.Read,FillRowMethodName = "FillRow_Table",TableDefinition = "ID INT, Year INT")]

public static IEnumerable GetTable(){

using (var cmd = new SqlDatabaseCommand(SqlDatabaseCommand.GetContextConnection())){

cmd.CommandText.AppendLine(" SELECT EMPNO AS ID, HIREDATE FROM EMP ");return cmd.ExecuteTable<MyData>();

}}

private static void FillRow_Table(Object obj, out int id, out int year){

var item = (MyData)obj;id = item.Id;year = item.Year;

}

SELECT * FROM dbo.GetTable()

ID Year

7369 37

7499 36

7521 36

Créer un déclencheur

[SqlTrigger(Target = "EMP", Event = "FOR INSERT, UPDATE")]public static void EMP_Inserted(){

string action = Enum.GetName(typeof(TriggerAction), SqlContext.TriggerContext.TriggerAction);SqlContext.Pipe.Send($"{action} raised.");

}

CREATE TRIGGER [EMP_Inserted]ON EMP FOR INSERT, UPDATEAS EXTERNAL NAME [MyLibrary].[MyLibrary.MyClass].[EMP_Inserted];

UPDATE EMP SET ENAME = 'SMITH' WHERE EMPNO = 7369

Update raised.

(1 row(s) affected)

Créer un fonction d’agrégation[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = -1)]public struct StringJoin : IBinarySerialize{

private List<string> values;

public void Init() {values = new List<string>();

}

public void Accumulate(SqlString Value) {values.Add(Value.IsNull ? string.Empty : Value.Value);

}

public void Merge(StringJoin Group) {values.AddRange(Group.values);

}

public SqlString Terminate() {values.Sort();return new SqlString(string.Join("; ", values.ToArray()));

}...

SELECT dbo.StringJoin(DNAME) FROM DEPT

ACCOUNTING; OPERATIONS; RESEARCH; SALES

Pourquoi ?

Quand utiliser SQL CLR ?

Utiliser...

• Performances

o Traitements ligne par ligne (forward only).

o Logique (trop) complexes pour le T-SQL.

o A la place des curseurs.

• Maintenance

o Gestion centralisée du code.

o Simplification des Unit Tests.

o Versionning des DLLs.

• Fonctionnalités

o Fonctionnalités importantes du .NET.

o Logique métier dépendant de tiers.

Ne pas utiliser...

• Si la fonctionnalité existe en T-SQL.

• Pour les commandes DML.

• Pour gérer uniquement des

données.

• Quand la base de données doit être

portable.

• Pour gérer la base sous forme

d’objets.

Conclusion

Références

• Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server)

http://www.sqlservercentral.com/articles/Stairway+Series/119429

• Top things developers should know about SQL Server

https://channel9.msdn.com/Blogs/dpeeast/Northeast-Roadshow-

Top-things-developers-should-know-about-SQL-Server-Part-1

• Professional SQL Server 2005 CLR Programming

https://www.amazon.com/dp/0470054034

• Introduction to SQL Server CLR Integration

https://docs.microsoft.com/en-

us/dotnet/framework/data/adonet/sql/introduction-to-sql-server-clr-integration

• SqlDatabaseCommand – CLR

https://www.nuget.org/packages/Apps72.Dev.Data.SqlServerClr

• SqlClrDeployment.exe

https://github.com/Apps72/Dev.SqlClrDeployement

Concepts

Hello World(démo)

Avantages &

Inconvénients

THANK YOUFOR ATTENDING

https://fr.slideshare.net/dvoituron

https://github.com/dvoituron

dvoituron@outlook.com

www.dvoituron.com

@DenisVoituron