Home

L'Intégration de Microsoft SQL Server avec le CLR

 

Principes de base d'intégration CLR

 

Introduction

Transact-SQL fournit autant de souplesse que possible mais, parcequ' il n'est pas un langage de programmation véritable, il a quelques défauts sur certaines questions (la gestion de la mémoire, l'exception de la manutention, le débogage, le traitement des fichiers (ou en continu), l'objet-orientation (classes), ou la gestion des threads). Transact-SQL manque également de fonctions de bibliothèques normales (baies, collections, sérialisation, expressions, ou les manipulations de chaîne, etc.).

Pour améliorer ou compléter les fonctionnalités d'une base de données Microsoft SQL Server, vous pouvez utiliser un langage externe. Une façon dont vous pouvez effectuer cette opération consiste à utiliser un langage qui prend en charge .NET Framework. Cela est fait par le common language runtime (CLR) et dénommé l'intégration du CLR.

L'Intégration avec le CLR permet de créer des objets de base de données ou les opérations à l'aide d'un langage de haut niveau tels que c#, C + c++ / CLI, ou Visual Basic. Ces langues prennent en charge la programmation orientée vers l'objet avec la possibilité de créer des classes, les remplir avec les propriétés ou méthodes, contrôler les niveaux d'accès sur les membres d'une classe, hériter d'une classe, line-inherit de classes (parents, grand-parents, etc.) et contrôler l'héritage (polymorphisme et abstraction), etc.. Profitant du .NET Framework de ces classes, votre code peut également impliquer des collections de bibliothèques dynamiques, etc., qui sont des fonctions qui ne sont pas disponibles dans Transact-SQL.

Une fois que le code est prêt, créez une bibliothèque, appelée un assemblage, puis l'importer et l'utiliser dans Microsoft SQL Server.

Lors de l'initialisation du CLR

Le Code dans le Common Language Runtime (CLR) est écrit dans un langage qui prend en charge le .NET Framework. Il est ainsi possible d'écrire le code dans une seule langue et l'utiliser dans une autre langue. La langue est également responsable de la gestion de la mémoire. Pour ce faire, il est appelé managed code.

Afin d'utiliser le CLR dans une base de données Microsoft SQL Server, vous devez l'activer. Par défaut, il n'est pas (activé).

Author Note

Les instructions dans cette leçon supposent que vous disposez de Microsoft Visual Studio
(ou une édition Express) installé sur votre ordinateur. Si vous ne le faites pas, il se peut que
vous deviez compiler les projets à partir de l'invite de commande.

Practical LearningFormation pratique : Initialisation du CLR

  1. Démarrez Microsoft SQL Server et connectez-vous au serveur
  2. Dans la barre d'outils standard, cliquez sur le bouton Nouvelle requête New Query
  3. Dans la fenêtre de requête, tapez ce qui suit :

    sp_configure 'show advanced options ', 1 ;
    GO
    RECONFIGURE;
    GO
    sp_configure 'clr enabled', 1;
    GO
    RECONFIGURE;
    GO
  4. Appuyez sur F5 pour exécuter
  5. Démarrez Microsoft Visual Studio
  6. Pour créer un nouveau projet, dans le menu principal, cliquez sur Fichier-> New-> Project (ou File-> New Project)
  7. Dans l'arborescence de types de projets, cliquez sur Visual C# si nécessaire
  8. Dans la liste Templates, cliquez sur Windows Forms Application
  9. Définissez le nom de la bcr

    New Project
  10. Définissez l'emplacement d'un chemin d'accès de votre choix.
    Cliquez sur OK
  11. Dans l'Explorateur de solutions, cliquez avec le bouton droit de la souris sur Form1.cs et cliquez sur Renommer
  12. Tapez BethesdaCarRental.cs, puis appuyez sur Entrée.
    Si on demande si vous souhaitez renommer les références, cliquez sur Oui

Principes de base du CLR

Comme je l'ai déjà dit, pour permettre l'intégration du CLR, vous allez écrire votre code dans un langage qui prend en charge le .NET Framework. Cela signifie que la langue va utiliser les classes disponibles dans cette bibliothèque.

Le .NET Framework prend en charge principalement les bases de données par le biais d'un objet appelé un jeu de données/data set. Cet objet est représenté par une classe nommée DataSet. En réalité, un ensemble de données est un groupe de listes exactement comme les tables d'une base de données Microsoft SQL Server. Cela signifie qu'un ensemble de données en soi ne signifie rien. Ce sont les objets (tables) qu'il contient qui le définissent.

Pour prendre en charge les tableaux, le .NET Framework fournit une classe nommée DataTable. Comme vous le savez déjà, une table est faite des colonnes et des enregistrements. Les colonnes d'une table sont prises en charge par une classe nommée DataColumn dans le .NET Framework. Un enregistrement d'une table est représenté dans le .NET Framework par une classe nommée DataRow. Toutes ces classes sont définies dans un espace de noms nommé System.Data. Les espaces de noms System.Data sont créés dans le System.Data.dll. Cela signifie que vous aurez besoin de cette bibliothèque dans votre projet.

Pour fournir une assistance pour les bases de données Microsoft SQL Server, le .NET Framework fournit l'espace de noms System.Data.SqlClient. Cet espace de noms est défini dans la bibliothèque System.Data.dll. L'espace de noms System.Data.SqlClient contient les classes utilisées pour établir une connexion à un serveur, créer des commandes pour une base de données, exécuter les commandes souhaitées ou lire des données à partir des objets de base de données (tables, vues, etc.). Pour utiliser une base de données Microsoft SQL Server, vous devrez généralement vous référer à cet espace de noms.

Une des exigences principales d'un système de base de données est celles de types de données. Les types de données .NET Framework qui prennent en charge, ou sont équivalents pour Microsoft SQL Server, les valeurs sont définies dans l'espace de noms System.Data.SqlTypes. Cet espace de noms contient diverses classes et structures que chacun fournit un équivalent à un type de données Microsoft SQL Server. Voici les types de données que nous avons commencées à utiliser dans la leçon 5 et voici leurs équivalents du .NET Framework :

Microsoft SQL Server Data .NET framework Equivalent
BIT SqlBoolean
TINYINT SqlByte
smallint SqlInt16
int SqlIn32
BIGINT SqlInt64
GUID SqlGuid
float, real SqlSingle
decimal, numéric SqlDecimal, SqlDouble
money, smallmoney SqlMoney
date, time, ddatetime, datetime2,
smalldatetime, datetimeoffset
SqlDateTime
char, nchar, varchar, nvarchar, text, ntext SqlString : Pour effectuer des comparaisons de chaînes, vous pouvez utiliser l'énumération SqlCompareOptions
FILESTREAM SqlFileStream
binary, varbinary SqlBinary
xml SqlXml

L'espace de noms System.Data.SqlTypes contient de nombreuses autres structures que vous pouvez utiliser pour implémenter la fonctionnalité disponible à partir de langages orientés vers l'objet, mais ne sont pas directement disponibles dans Microsoft SQL Server :

  • SqlBytes : cette classe peut être utilisée pour créer une référence à un flux de valeurs
  • SqlChars : cette classe peut être utilisée pour implémenter un tableau de caractères
  • SqlTruncateException : cette exception est levée si vous utilisez une valeur incompatible qui doit être tronquée.
  • SqlNullValueException : cette exception est levée si la valeur d'une variable est définie sur null ou Nothing

L'espace de noms System.Data.SqlTypes est défini dans l'assemblage System.Data.dll.

Pour fournir des fonctionnalités supplémentaires d'une base de données Microsoft SQL Server, le .NET Framework fournit l'espace de noms System.Data.SQL. Cet espace de noms fait également partie de la bibliothèque de System.Data.dll.

équipé de tous ces types de données, les classes et vos connaissances, vous mettrez par programmation les fonctionnalités appropriées pour intégrer votre code avec une base de données Microsoft SQL Server. Pour vous aider avec les classes nécessaires pour cette intégration, le .NET Framework fournit l'espace de noms Microsoft.SQLServer.Server qui est équipé de plusieurs interfaces, les classes et les énumérations.

Pour utiliser une des classes de l'espace de noms Microsoft.SQLServer.Server, vous vous référez à l'aide d'un attribut.

Practical LearningFormation pratique : Présentation du CLR

  1. Dans la boîte à outils, cliquez sur le bouton, puis cliquez sur la forme
  2. En utilisant la fenêtre Propriétés, modifiez les caractéristiques du bouton comme suit :
    (Nom): btnCreate
    Texte : Create
  3. Sur l'écran, cliquez deux fois sur le bouton pour générer son événement Click
  4. Pour créer une base de données, tapez ce qui suit :
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace bcr  
    {      
        public partial class BethesdaCarRental : Form      
        {          
            public BethesdaCarRental()          
            {              
                InitializeComponent();         
            }            
     
    
            private void btnCreate_Click(object sender, EventArgs e)          
            {              
                string strConnection =                  
                    "Data Source=(local);Integrated Security=yes";                
     
                using (SqlConnection connection = new SqlConnection(strConnection))              
                {                  
                SqlCommand command =                      
                    new SqlCommand("CREATE DATABASE BethesdaCarRental;",                      
                    connection);                    
     
                connection.Open();                  
                command.ExecuteNonQuery();                  
                MessageBox.Show("A database named BethesdaCarRental " +  		
                                "has been created.",                                  
                                "Bethesda Car Rental", MessageBoxButtons.OK,                                  
                                MessageBoxIcon.Information);              
               }          
            }      
         }  
    }
  5. Appuyez sur CTRL + F5 pour exécuter
  6. Lorsque le formulaire s'affiche, cliquez sur le bouton
  7. Lorsque la base de données a été créée, dans la boîte de message, cliquez sur OK
  8. Fermez l'écran et revenez à votre environnement de programmation

CLR et fonctions

 

Introduction

Bien que la bibliothèque de Transact-SQL fournit de nombreuses fonctions, vous pouvez avoir besoin de fonctionnalités qu'aucun d'eux ne fournit. Vous pouvez créer votre propre fonction pleinement efficace en c#, C + et c++ / CLI, ou Visual Basic. Une fonction scalaire CLR (SVF) ou une fonction définie par l'utilisateur scalaire est une fonction que vous créez à l'aide de CLR et qui est faite pour être accessible à partir de Microsoft SQL Server.

Les C++, Visual Basic, Pascal, Transact-SQL et de nombreuses autres langues prennent en charge le concept de fonctions. Certains autres langages tels que c# ou Java ne partagent pas cette notion. Ils utilisent uniquement le mot "méthode". Dans cette leçon, nous utiliserons la fonction mot au sens premier. Car nous écrirons notre code en c#, nous devons créer des classes et méthodes.

Création d'une fonction définie par l'utilisateur

Pour vous aider à la création d'une fonction, le .NET Framework fournit une classe nommée SqlFunction. La classe SqlFunction est définie dans l'espace de noms Microsoft.SQLServer.Server. Pour utiliser cette classe, créez une méthode statique marquée avec l'attribut SqlFunctionAtribute.

Si vous le souhaitez, vous pouvez créer une fonction qui aura accès à un ou plusieurs enregistrements sur la base de données. Pour ce faire, utilisez la propriété DataAccess, qui est basée sur une énumération nommée DataAccessKind.

Pour préparer l'objet CLR, vous pouvez créer un projet à l'aide de la langue de votre choix ou d'un environnement de programmation et compiler pour créer une bibliothèque de liens dynamiques (DLL).

Si vous souhaitez indiquer que la fonction que vous définissez est déterministe, marquez avec l'attribut IsDeterministic et affectez la valeur true ou false.

Practical LearningFormation pratique : Création d'une bibliothèque.

  1. Pour ajouter un nouveau projet, dans l'Explorateur de solutions, cliquez avec le bouton droit de la souris sur la solution 'bcr' (1 projet), positionnez la souris sur Ajouter, puis cliquez sur Nouveau projet...
  2. Dans l'arborescence de types de projets, développez Visual c# si nécessaire.
    Sous Visual c#), cliquez sur Windows
  3. Dans la liste modèles, cliquez sur Empty Project
  4. Dans la zone de texte Nom, remplacez la chaîne de calculations
    Add New Project
  5. N'oubliez pas l'emplacement (ou Notez-le).
    Cliquez sur OK
  6. Dans l'Explorateur de solutions, dans le cadre de calculs, cliquez avec le bouton droit de la souris sur References-> Add Reference...
  7. Dans l'onglet .NET de la boîte de dialogue Add Reference, cliquez sur System
  8. Maintenez la touche Ctrl
  9. Cliquez sur System.Data
  10. Version Ctrl

    Add Reference
  11. Cliquez sur OK
  12. Dans l'Explorateur de solutions, cliquez avec le bouton droit de la souris sur Calculations-> Add-> New Item...
  13. Dans la liste modèles, cliquez sur Code File
  14. Définissez le nom Accessory
  15. Cliquez sur Ajouter
  16. Dans l'éditeur de code, tapez le texte suivant :
    using System;
    using Microsoft.SqlServer.Server;
    
    public class Accessories
    {
        [SqlFunction(IsDeterministic = false)]      
        public static void CalculatePayroll(decimal Week1Time,                                          
                                            decimal Week2Time,                                          
                                            decimal HourlySalary,                                          
                                            out decimal RegularTime,                                          
                                            out decimal RegularPay,                                          
                                            out decimal Overtime,
                                            out decimal OvertimePay,
                                            out decimal TotalEarnings)
        {          
             decimal overtimeSalary = 0.00M;          
             decimal week1RegularTime = 0.00M;          
             decimal week1RegularPay = 0.00M;          
             decimal week1Overtime = 0.00M;          
             decimal week1OvertimePay = 0.00M;          
             decimal week2RegularTime = 0.00M;          
             decimal week2RegularPay = 0.00M;          
             decimal week2Overtime = 0.00M;          
             decimal week2OvertimePay = 0.00M;            
           
             // The overtime is paid time and half          
             overtimeSalary = HourlySalary * 1.5M;           
      
             // If the employee worked under 40 hours, there is no overtime          
             if (Week1Time < 40)          
             {              
                 week1RegularTime = Week1Time;              
                 week1RegularPay = HourlySalary * week1RegularTime;              
                 week1Overtime = 0M;              
                 week1OvertimePay = 0M;          
             }          
             // If the employee worked over 40 hours, calculate the overtime          
             else if (Week1Time >= 40)          
             {              
                 week1RegularTime = 40M;              
                 week1RegularPay = HourlySalary * 40M;              
                 week1Overtime = Week1Time - 40M;              
                 week1OvertimePay = week1Overtime * overtimeSalary;          
             }            
     
             if (Week2Time < 40)          
             {              
                 week2RegularTime = Week2Time;              
                 week2RegularPay  = HourlySalary * week2RegularTime;              
                 week2Overtime    = 0M;              
                 week2OvertimePay = 0M;          
             }          
             else if (Week2Time >= 40)          
             {   
                 week2RegularTime = 40;             
                 week2RegularPay  = HourlySalary * 40M;              
                 week2Overtime    = Week2Time - 40M;              
                 week2OvertimePay = week2Overtime * overtimeSalary;          
              }            
     
              RegularTime = week1RegularTime + week2RegularTime;          
              Overtime = week1Overtime + week2Overtime;          
              RegularPay = week1RegularPay + week2RegularPay;          
              OvertimePay = week1OvertimePay + week2OvertimePay;          
              TotalEarnings = RegularPay + OvertimePay;      
          }  
    }
  17. Dans l'Explorateur de solutions, cliquez avec le bouton droit de la souris sur calculs et cliquez sur Propriétés...
  18. Dans la fenêtre Propriétés, cliquez sur la flèche de la zone de liste déroulante Type de sortie, puis sélectionnez Class Library

    Properties
  19. Fermez la fenêtre
  20. Pour créer l'assemblage, dans l'Explorateur de solutions, cliquez avec le bouton droit de la souris sur calculations et cliquez sur Build

Création d'un assemblage dans Microsoft SQL Server

Après la création de la bibliothèque, vous devez l'importer dans Microsoft SQL Server. Pour le faire, dans Microsoft SQL Server, vous devez créer un assemblage à l'aide de l'instruction CREATE ASSEMBLY. La formule à suivre est la suivante :

CREATE ASSEMBLY assembly_name 
[AUTHORIZATION owner_name] 
FROM {<client_assembly_specifier > | <assembly_bits > [,.. .n]} 
[WITH PERMISSION_SET = {SAFE | EXTERNAL_ACCESS | UNSAFE}] 
[;]

Vous commencez avec l'expression CREATE ASSEMBLY suivie d'un nom. Si vous souhaitez spécifier l'utilisateur qui sera propriétaire de l'assemblage, tapez AUTHORIZATION, suivi du nom de l'utilisateur ou du rôle. Il s'agit d'une option et vous pouvez l'omettre.

Pour spécifier la DLL que vous souhaitez importer, tapez FROM suivi par le nom du fichier ou le chemin d'accès à la DLL. Vous devez spécifier le nom du fichier et son extension.

Vous devez spécifier le type et le niveau d'accès qui sera accordé sur l'assemblage lorsqu'il aura été créé. Pour ce faire, tapez PERMISSION_SET suivi par :

  • SAFE : cet Assemblage n'accédera pas aux fichiers externes, au réseau ou au registre. Ceci est considéré comme le niveau le plus fiable et sécurisé d'accès
  • EXTERNAL_ACCESS : cet assemblage peut accéder aux fichiers externes, le réseau ou le registre
  • UNSAFE : cet Assemblage aura un accès illimité aux objets sur le serveur. Car ce niveau est très risqué, il ne s'applique que si vous savez ce que vous êtes en train de faire

Practical LearningFormation pratique : Importation de la bibliothèque

  1. Revenez à Microsoft SQL Server
  2. Sélectionnez l'ensemble du code que vous avez écrit précédemment et appuyez sur la touche Suppr
  3. Pour créer un assemblage pour importer la fonction, tapez la commande suivante (modifiez le chemin d'accès basé sur l'emplacement dans lequel vous avez créé le projet :
    USE BethesdaCarRental;
    GO
    
    CREATE ASSEMBLY BCRCalculations
    FROM N'C:\Exercise\bcr\calculations\bin\Debug\calculations.dll
    ' WITH PERMISSION_SET = SAFE;
    
    GO
  4. Appuyez sur la touche F5 pour l'exécuter.
    Assurez-vous que vous obtenez un message indiquant que la (les) Commande (s) a/ont été réalisée (s) avec succès

Utilisation de la fonction dans Microsoft SQL Server

Après l'importation de la bibliothèque, vous pouvez l'utiliser dans Microsoft SQL Server. Une façon que vous pouvez effectuer cette opération se compose de la création d'un objet qui peut y accéder. Par exemple, vous pouvez créer une procédure stockée qui exécute la fonction dans l'assemblage et rend ses résultats. Si vous créez la procédure stockée, la formule à utiliser est la suivante :

CREATE {PROC | PROCEDURE} [schema_name.] procedure_name [; number] 
    [{@parameter [type_schema_name.]data_type } 
AS EXTERNAL NAME assembly_name.class_name.method_name

Démarrez avec CREATE PROC ou CREATE PROCEDURE suivi d'un nom de votre choix. Spécifiez ensuite les arguments, le cas échéant. Les arguments doivent être le même nombre, les mêmes types et la même séquence comme ceux définis dans la fonction de la bibliothèque que vous avez importée. Les types de données doivent également être compatibles.

Après l' (la liste des) argument (s), tapez AS EXTERNAL NAME, suivi par la formule suivante :

AssemblyName. ClassName. FunctionName

Après avoir créé l'instruction entière, vous pouvez l'exécuter. Après avoir créé la procédure, vous pouvez l'exécuter pour l'appeler et voir les résultats.

Practical LearningFormation pratique : Utilisation de la fonction dans Microsoft SQL Server

  1. Pour créer une procédure qui obtient la fonction prête à l'emploi, sélectionnez le code dans la fenêtre de requête et le type :
    USE BethesdaCarRental ; 
    GO 
     
     
    CREATE PROCEDURE EvaluatePayroll @ W1Time décimal (6, 2), 
                                     @ W2Time décimal (6, 2), 
                                     @ HourlyRate money, 
                                     @ RegTime décimal (6, 2) output, 
                                     @ RegPay décimal (6, 2) output, 
                                     @ Over decimal (6, 2) output, 
                                     @ OverPay decimal (6, 2) output, 
                                     @ TotalSalary decimal (6, 2) output 
    AS EXTERNAL NAME BCRCalculations.Accessories.CalculatePayroll ; 
     
     
    GO
  2. Appuyez sur F5 pour exécuter
  3. Pour utiliser la fonction qui a été créée dans le code CLR, sélectionnez le code dans la fenêtre de requête et le type :
    USE BethesdaCarRental;
    GO
    
    DECLARE @Week1 decimal(6, 2);
    DECLARE @Week2 decimal(6, 2);
    DECLARE @Rate money;
    DECLARE @RegWork decimal(6, 2);
    DECLARE @RegWage decimal(6, 2);
    DECLARE @OverPeriod decimal(6, 2);
    DECLARE @OverPeriodPay decimal(6, 2);
    DECLARE @NetPay decimal(6, 2);
    
    SET @Week1 = 42.50;  
    SET @Week2 = 36.00;  
    SET @Rate = 25.85;  
    EXEC EvaluatePayroll @Week1, @Week2, @Rate,  		
                         @RegWork out, @RegWage out,  	
                         @OverPeriod out, @OverPeriodPay out,  		
                         @NetPay out;  
    PRINT 'Payroll Calculation';  
    PRINT '------------------------';  
    PRINT 'Week 1:         ' + CONVERT(nvarchar(20), @Week1, 20);  
    PRINT 'Week 2:         ' + CONVERT(nvarchar(20), @Week2, 20);  
    PRINT 'Hourly Salary:  ' + CONVERT(nvarchar(20), @Rate, 20);  
    PRINT 'Regular Time:   ' + CONVERT(nvarchar(20), @RegWork, 20);  
    PRINT 'Regular Pay:    ' + CONVERT(nvarchar(20), @RegWage, 20);  
    PRINT 'Overtime:       ' + CONVERT(nvarchar(20), @OverPeriod, 20);  
    PRINT 'Overtime Pay:   ' + CONVERT(nvarchar(20), @OverPeriodPay, 20);  
    PRINT 'Total Earnings: ' + CONVERT(nvarchar(20), @NetPay, 20);  
    PRINT '------------------------';  
    GO
  4. Appuyez sur F5 pour exécuter
    Payroll calculation
    ------------------------
    Week 1:          42.50 
    Week 2:          36,00 
    Hourly Salary :  25.85 
    Regular Time  :  76.00
    Regular Pay :    1964.60 
    Overtime :       2,50 
    Overtime Pay :   96.94 : 
    Total Earnings : 2061.54
    ------------------------

Autres objets CLR

 

CLR et procédures stockées

Outre les fonctions, le CLR vous permet de créer une procédure. Pour prendre en charge les procédures, le .NET Framework fournit la classe de SqlProcedure définie dans l'espace de noms Microsoft.SQLServer.Server. Pour utiliser cette classe, créez une méthode statique, mais marquez-le avec cet attribut.

Practical LearningFormation pratique : Création et utilisation d'une procédure stockée

  1. Démarrez Microsoft Visual Studio
  2. Pour créer un nouveau projet, dans le menu principal, cliquez sur File-> New-> Project...
  3. Dans l'arborescence de types de projets, sélectionnez Visual c# si nécessaire.
    Dans la liste modèles, cliquez sur Class Library
  4. Dans la zone de texte Nom, remplacez la chaîne GetFullName

    Add New Project
  5. Cliquez sur OK
     
 

Précédent Copyright © 2010 Yevol.com Accueil