Blog ENI : Toute la veille numérique !
-25€ dès 75€ sur les livres en ligne, vidéos... avec le code FUSEE25. J'en profite !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. SQL Server 2019 - SQL, Transact SQL
  3. SQL, Transact SQL
Extrait - SQL Server 2019 - SQL, Transact SQL Conception et réalisation d'une base de données (avec exercices pratiques et corrigés)
Extraits du livre
SQL Server 2019 - SQL, Transact SQL Conception et réalisation d'une base de données (avec exercices pratiques et corrigés)
5 avis
Revenir à la page d'achat du livre

Le Common Language Runtime (CLR)

SQL Server et le CLR

SQL Server est en mesure de gérer des données de plus en plus complexes mais il se doit également d’offrir la possibilité de définir sur le serveur des traitements de plus en plus précis et parfois complexes, ceci afin de fournir toujours plus de fonctionnalités. Dans certains cas, le langage Transact-SQL ne permet pas de définir le traitement voulu. Grâce à l’intégration du CLR (Common Language Runtime) .NET directement dans le moteur de base de données, il est possible de programmer des traitements à l’aide de C# ou bien VB.NET.

La programmation de ces traitements pourra alors se faire directement depuis Visual Studio. Pour que les méthodes et procédures ainsi définies soient utilisables depuis les instructions SQL, un mappage Transact-SQL avec le code MSIL (Microsoft Intermediate Language) est réalisé.

Le fait de passer par le CLR intégré à SQL Server est beaucoup plus sûr (et plus simple) que de faire appel à des procédures stockées étendues. En effet le CLR présent dans SQL Server n’est que partiel et ne couvre pas toutes les fonctionnalités offertes par le Framework .NET. Par exemple, tous les objets relatifs à la gestion de l’interface graphique n’y sont pas. En limitant le nombre de fonctionnalités offertes par le CLR, il est possible ainsi de se prémunir de certains codes malveillants.

1. Le Transact-SQL ou le CLR ?

En introduisant le code CLR dans SQL Server, un nouveau problème se pose alors au développeur. Quand choisir de travailler avec un langage du Framework .NET et quand lui préférer le Transact-SQL ?

Contrairement à ce qui pourrait être perçu par un premier regard trop rapide sur SQL Server, le Transact-SQL n’est en aucun cas menacé par le code CLR. Au contraire, chacun possède ses avantages et ses inconvénients. En fonction du travail à réaliser, le choix se portera tout naturellement sur l’un ou l’autre.

Quelques-uns des principaux avantages de chaque langage sont énumérés ci-dessous :

Le Transact-SQL sera privilégié pour :

  • la manipulation intensive des données, c’est-à-dire...

La création du code CLR géré dans SQL Server

1. L’activation de la prise en charge du code CLR

Avant de commencer à écrire du code CLR pour SQL Server, il est nécessaire d’activer la prise en charge de ce type de code par SQL Server. Il ne s’agit pas d’une option ou d’un composant à installer mais d’une option de configuration à activer. En effet, pour des raisons de sécurité cette option est désactivée par défaut. Elle peut être activée depuis le Transact-SQL à l’aide de la procédure stockée sp_configure. Il s’agit alors de modifier la valeur de l’option de configuration CLR_ENABLED. Cette configuration via le Transact-SQL peut être réalisée depuis SQL Server Management Studio ou bien depuis SQLCMD dans une invite de commandes.

EXEC sp_configure 'CLR_ENABLED', '1'; 
RECONFIGURE WITH OVERRIDE; 

Affichage :

L’option de configuration CLR_ENABLED est passée de 0 à 1. Pour installer, exécutez l’instruction RECONFIGURE.

Il est également nécessaire d’accepter que l’assembly ne soit pas signée :

EXEC sp_configure 'SHOW_ADVANCED_OPTIONS', '1'; 
RECONFIGURE; 
 
EXEC sp_configure 'CLR_STRICT_SECURITY', '0'; 
RECONFIGURE WITH OVERRIDE; 

2. Le travail dans Visual Studio

Bien qu’il soit théoriquement possible d’écrire le code source depuis n’importe quel éditeur, même le bloc-notes, il est très fortement recommandé d’utiliser Visual Studio pour définir des éléments en code CLR gérés pour SQL Server. En effet, depuis Visual Studio, le déploiement sur l’instance SQL Server et le mappage CLR-Transact-SQL sont faits de façon automatique.

Après avoir lancé Visual Studio, il s’agit donc de créer un projet de type Projet de base de données SQL Server.

images/07RI01.png

Ensuite, Visual Studio demande les informations de création du projet.

images/07RI02.png

Les différents types de développement proposés par le CLR sont exposés et illustrés par un exemple. Les exemples sont indiqués en C#, mais il est tout à fait possible de réaliser le même travail...

La manipulation d’une base de données avec PowerShell

Le langage de script PowerShell est présent sur les serveurs Windows et donne la possibilité aux administrateurs de travailler sous forme de scripts. Sans revenir sur une présentation détaillée du PowerShell, il est bon de rappeler que ce langage accède nativement au Framework .NET et bénéficie de la richesse de celui-ci.

PowerShell bénéficie donc de l’accès à l’API de programmation SMO (SQL Server Management Objects). Cette API, très puissante, contient tous les éléments permettant de reproduire par programmation les fonctionnalités présentes dans SQL Server Management Studio. Cette puissance induit une problématique de complexité d’utilisation. Il n’est pas toujours trivial d’identifier les éléments souhaités dans SMO.

1. L’installation de SMO

Depuis la version 17 de SQL Server Management Studio, plus aucun module PowerShell n’est installé de facto. Il faut l’installer en ouvrant une console PowerShell en tant qu’administrateur et en utilisant la commande suivante :

Install-Module -Name SqlServer -AllowClobber 

Il faut répondre "O" pour l’installation de NuGet et pour l’utilisation d’un ré-férentiel non approuvé.

2. L’utilisation...

Exercices

1. Un mot de passe pour les clients

Les clients peuvent maintenant accéder à leur compte à distance. Pour cela, un mot de passe est ajouté à leur compte. Pour des raisons de sécurité, il est bien évidemment hors de question de stocker le mot de passe : uniquement son empreinte (hash) est stockée. L’empreinte est calculée à l’aide de l’algorithme SHA-512. Une complexité de mot de passe est exigée : au moins 8 caractères, au moins une lettre minuscule, au moins une lettre majuscule et au moins un chiffre.

Créez un type personnalisé permettant de gérer ces mots de passe. Ajoutez une colonne mdpHash à la table Clients afin de pouvoir exécuter les instructions suivantes :

UPDATE clients SET mdpHash = 'SQL4ever!' WHERE noCli=1;  
UPDATE clients SET mdpHash = 'Ilove5QL' WHERE noCli=2;  
SELECT * FROM clients;  
  
SELECT mdpHash.Hash FROM Clients;  
   
SELECT  
       CASE mdpHash.Tester('SQL4ever!')  
             WHEN 1 THEN 'ok connecté'  
             ELSE 'erreur de connexion'  
       END Connexion FROM Clients WHERE...

Correction des exercices

1. Un mot de passe pour les clients

using System;  
using System.Data.SqlTypes;  
using System.IO;  
using System.Security.Cryptography;  
using System.Text;  
using System.Text.RegularExpressions;  
using Microsoft.SqlServer.Server;  
   
  
[Serializable]  
[SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000)]  
public struct MotDePasse: INullable, IBinarySerialize  
{  
    private bool _null;  
    private string _hash;  
   
    private static SHA512 SHA512 => SHA512.Create();  
   
    public override string ToString()  
    {  
        return _hash;  
    }  
   
    public bool IsNull => _null;  
      
    public static MotDePasse Null  
    {  
        get  
        {  
            MotDePasse h = new MotDePasse();  
            h._null = true;  
            return h;  
        }  
    }  
   ...