Le CLR dans SQL Server

puissantes. Le défi lié au CLR consiste à comprendre dans quelles circonstances l’employer et ne pas l’employer.

Le CLR dans SQL Server ne remplace pas T-SQL. Les informations marketing de Microsoft affirment peut-être que vous pouvez désormais employer exclusivement les langages .NET. Mais, dans les présentations techniques, vous entendrez peutêtre des affirmations selon lesquelles T-SQL reste supérieur au CLR pour la majorité des requêtes. Avec ces deux messages apparemment contradictoires, la communauté technique chez Microsoft ne souhaite pas vous mettre en garde contre le CLR, mais fournir de solides conseils sur la manière de l’utiliser correctement dans SQL Server 2005. Le CLR procure plusieurs avantages spécifiques par rapport à T-SQL. Le premier réside dans la création de fonctions ayant besoin d’une forte capacité de calcul. Le CLR vous permet d’avoir des fonctions qui encapsulent des calculs de gestion au sein de la base de données. Lorsque vous employez de telles fonctions principalement pour le calcul (et non la simple extraction) de données, le CLR est plus performant que T-SQL. Son deuxième avantage réside dans sa capacité à gérer du code XML complexe. Troisièmement, il permet de créer des types UDT (définis par l’utilisateur) personnalisés. Enfin, le CLR vous donne la possibilité de remplacer des procédures stockées étendues présentant un risque intrinsèque. A la différence d’une procédure stockée étendue, qui va au-delà du contrôle assuré par SQL Server, le paramètre par défaut pour le CLR s’assure que vos extensions T-SQL personnalisées s’exécutent au sein du contexte de sécurité courant. (Pour savoir dans quels cas utiliser le CLR, consultez les articles de Matt Nunn et Vinod Kumar cités dans les références bibliographiques.) Examinons comment créer un UDT personnalisé en vue de l’employer dans une application.

Comme aucune des fonctionnalités du CLR n’est nécessaire pour avoir une base de données pleinement fonctionnelle, SQL Server 2005 est fourni avec le CLR désactivé. La première étape pour exploiter cette fonctionnalité consiste donc à l’activer. Pour cela, collez la procédure stockée standard T-SQL sp_configure ci-après dans une fenêtre de requête de SQL Server Management Studio.

sp_configure ‘CLR Enabled’, 1
GO RECONFIGURE
EXEC sp_configure

Cette instruction T-SQL active le CLR et retourne vos paramètres de configuration de base de données. Pour plus d’informations sur la commande sp_configure, reportezvous à la documentation Microsoft citée dans les références bibliographiques.

Une fois le CLR activé, vous êtes prêt à débuter la création de votre UDT dans Visual Studio 2005. Vous pouvez recourir au langage Visual Basic ou à C#. Dans le cadre de cet exemple, j’emploie Visual Basic. Vous commencez par utiliser le modèle SQL Server Project dans la liste des types de projet Visual Basic afin de créer le nouveau projet. Après avoir nommé votre projet (dans cet exemple, il est intitulé SQLServer UDT), Visual Studio vous demande d’ajouter une référence à une base de données, comme illustré dans la boîte de dialogue de la figure 1. Dans cette boîte de dialogue, vous spécifiez le fournisseur (provider) à employer (dans ce cas, le .NET Framework Data Provider for SQL Server). Ensuite, dans la boîte de dialogue New Database Reference illustrée à la figure 2, vous identifiez une base de données de développement spécifique. Tapez (local) dans la zone de texte server name pour identifier votre instance SQL Server 2005 locale, afin que Visual Studio l’associe ensuite au projet.

Une fois que vous avez un nouveau projet, vous ajoutez une classe afin de représenter votre UDT. Cliquez avec le bouton droit de la souris sur votre projet et sélectionnez Add User Defined Type… dans le menu contextuel. Vous verrez une nouvelle boîte de dialogue contenant plusieurs types de classes que vous pouvez ajouter à votre projet. Sélectionnez la classe User Defined Type par défaut et modifiez le nom de fichier par défaut en myPointType.vb. Cliquez sur OK pour générer le code illustré sur le listing 2.

Vous noterez que le modèle par défaut génère du code pour votre classe, y compris les propriétés privées associées au bas de celle-ci. En raison des contraintes d’espace, je n’expliquerai pas ici comment mettre en oeuvre le type de point. Il vous suffit de savoir qu’il consistera en deux entiers pouvant être stockés en tant que valeur unique au moyen de cet UDT. Le listing 3 illustre l’aspect du fichier source myPointType.vb une fois que vous affectez cette implémentation simple au type de point. Notez que dans cet exemple, j’ai appliqué ce que je considère comme une implémentation type, en utilisant des régions (Regions) que vous pouvez réduire avant de regrouper des parties associées de votre implémentation de type.

Maintenant que votre classe est à jour, l’étape suivante consiste à la compiler. Le fichier résultant aura une extension .dll et pourra être inclus dans n’importe quel projet référençant ce type personnalisé. Néanmoins, il faut d’abord déployer le nouveau type dans SQL Server. Vous pouvez procéder de deux manières. L’option Deploy de Visual Studio fonctionne avec votre base de données de développement, mais pas avec celle de production. Pour appliquer cette méthode, accédez simplement au menu Build, sélectionnez Deploy et Visual Studio compile et déploie automatiquement votre UDT dans votre base de données SQL Server référencée.

La deuxième méthode de déploiement, valable avec des bases de données de développement et de production, consiste à utiliser T-SQL. Le listing Web 2 illustre les commandes T-SQL pour l’installation de votre UDT dans SQL Server. Notez que ce listing inclut le chemin du répertoire sur ma machine locale. Il faudra personnaliser ce chemin en fonction de votre ordinateur. SQL Server et votre application personnalisée ont besoin de référencer cet assembly pour reconnaître le type.

Afin d’utiliser le nouvel UDT, il suffit de référencer votre nouveau type, puis de créer un paramètre pour votre procédure stockée de type UDT, comme suit :

‘ Create a parameter of type UDT.
Dim paramUDT = updateCommand.Parameters.Add("@ v", SqlDbType.Udt)
paramUDT.UdtTypeName = "myPointType";
paramUDT.Value = new myPointType(4, 22);

Cette introduction rapide présente les notions fondamentales à connaître pour utiliser le CLR .NET en vue de créer un UDT personnalisé qui sera reconnu par votre base de données et votre application. A mesure que vous maîtriserez l’utilisation du CLR, vous pourrez apprendre à exploiter les possibilités du langage XML pour étendre encore vos types de données personnalisés.