View
376
Download
1
Category
Preview:
Citation preview
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
Recommended