SQL Server 2005 : procédures stockées en .Net

Ronald VASSEUR

- Titre : SQL Server 2005 : procédures stockées en .Net

- Auteur : Ronald VASSEUR

- Parution : 11 juillet 2005

- Mise à jour : 28 février 2020

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2005-2013 Ronald VASSEUR. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- Introduction

- Prérequis logiciels

- I. Intégration de la CLR dans SQL Server 2005

- II. SQL Server Project et Visual Studio 2005

- III. Écrire une procédure stockée en .Net

- IV. Publier une procédure stockée dans SQL Server avec Visual Studio

- Conclusion

Introduction

Au cours de cet article, nous allons voir les apports d'une innovation majeure de SQL Server 2005, à savoir l'intégration de la CLR 2.0. Nous verrons comment créer et déployer du code .Net directement à l'intérieur du serveur de base de données, pour étayer mes propos, je prendrai pour exemple la création et le déploiement d'une procédure stockée en VB.Net dans SQL Server 2005.

Prérequis logiciels

1. Visual Studio 2005 Bêta 2 ou supérieur.

2. SQL Server 2005 Bêta 2 ou supérieur.

I - Intégration de la CLR dans SQL Server 2005

Une des innovations majeures de SQL Server dans sa version 2005 est sans conteste l'intégration de la CLR 2.0, à l'instar de Windows, votre serveur de base de données préféré intègre sa propre CLR. Cela a pour conséquence directe de pouvoir exécuter du code .Net à l'intérieur même du processus de SQL Server. Il est bien entendu toujours possible d'utiliser Transact-SQL (T-SQL pour les intimes…), mais aussi, et c'est nouveau, VB.Net, C# ou tout autre langage .Net. Clairement, cela signifie que vous allez pouvoir développer des procédures stockées, des triggers, des fonctions ou même des nouveaux types de données pour vos bases, dans votre langage .Net favori.

Définition de la CLR : la Common Language Runtime (CLR) est un environnement d'exécution sécurisé et robuste qui supporte du code écrit dans plusieurs langages différents (C++, VB, C#, Pascal, Cobol…) et simplifie le développement, la gestion et le déploiement d'applications. On peut la comparer à la Java Virtual Machine (JVM) ou au Runtime Visual Basic 6 (msvbvm60.dll).

La CLR est constituée d'un ensemble de services standards (Modèle de programmation orientée objet, sécurité, ramasse-miettes) dont chaque programme .NET peut tirer profit.

Définition de Leduke issue de la FAQ .Net de Developpez.com.

On voit donc que l'intégration de la CLR 2.0 dans SQL Server 2005 ouvre de grandes perspectives aux développeurs .Net puisqu'ils pourront déployer leur réalisation directement dans le serveur de bases de données. Le processus de déploiement d'une assembly à l'intérieur de SQL Server n'est, en soi pas très complexe, mais nous verrons dans cet article que Visual Studio 2005 vous simplifie cette opération à tel point que cela devient un véritable jeu d'enfant.

Il est bien évident qu'exécuter du code .Net à l'intérieur de SQL Server peut être en soi une source d'insécurité si ce code est mal sécurisé ou souffre de bugs… il n'est donc pas question (pour les développeurs, mais surtout pour les DBA) de laisser libre accès à tout et n'importe quoi aux assemblies, pour cela trois niveaux de sécurité ont été prévus :

1. Safe ;

2. External-Access ;

3. Unsafe.

Le mode d'exécution le plus sûr étant Safe, il est très vivement recommandé de l'employer systématiquement, il s'agît d'ailleurs le mode par défaut. En mode Safe, l'assembly n'aura accès à rien en dehors du contexte d'exécution de SQL Server, elle n'aura, par exemple, pas accès au système de fichiers, son univers se limitera à SQL Server seulement.

External-Access est le mode de sécurité et de permissions intermédiaires, ici l'assembly, en plus du serveur SQL même, va avoir accès à des ressources externes comme le registre, le réseau et le système de fichiers du serveur. Comme toute autre assembly .Net, il est bien entendu nécessaire de posséder les autorisations adéquates à ces ressources, il ne s'agit donc pas de créer une brèche béante dans votre serveur ! Enfin, le mode Unsafe est le niveau de sécurisation le moins élevé, ici l'assembly a potentiellement (toujours selon les autorisations accordées) accès à tous les types de ressources imaginables, pour des raisons évidentes de sécurité, il est déconseillé d'utiliser ce mode !

Comme nous venons de le voir, l'intégration de la CLR offre de nombreuses possibilités aux développeurs .Net, il s'agit d'un réel substitut à l'utilisation de T-SQL. Cela va même, dans une certaine mesure, modifier votre façon de concevoir des applications puisqu'il sera possible d'intégrer facilement une partie de la logique de vos applications directement dans le serveur de base de données. Vous avez potentiellement accès à la puissance et à la richesse du Framework .Net 2.0 à l'intérieur même de SQL Server. Nous pouvons donc raisonnablement penser que l'intégration de la CLR 2.0 dans SQL Server est une innovation majeure. Voyons maintenant comment tirer profit de cela dans Visual Studio au travers de la création d'une procédure stockée en code managé…

II - SQL Server Project et Visual Studio 2005

Visual Studio propose, dans sa version 2005, un nouveau type de projet : le SQL Server Project, il ne s'agît pas d'un simple projet de base de données comme dans la version précédente de VS .Net, mais bel et bien de réaliser divers éléments pour SQL Server par exemple des procédures stockées, des triggers, des fonctions d'agrégats ou même des types de données. Comme nous l'avons vu précédemment, l'intégration de la CLR dans SQL Server a ouvert de nouveaux horizons aux développeurs, Visual Studio vous donne ici les moyens de les atteindre facilement.

Le but de cet article étant de vous montrer comment créer une procédure stockée en .Net, nous allons voir ici comment créer et paramétrer un SQL Server Project pour arriver à nos fins.

Remarque : avant toute chose, vous devez avoir une base de données existante dans SQL Server, c'est dans cette même base que sera déployée notre procédure stockée, pour les besoins de l'article, j'ai créé une base nommée Developpez, j'y ferai référence tout au long de mes explications.

Passons aux choses concrètes.

Dans Visual Studio cliquez sur File puis New Project, dans Visual Basic allez dans Database et sélectionnez SQL Server Project. Une fois arrivé là, vous devez voir une fenêtre identique à celle-ci :

Création d'un nouveau projet SQL Server

Nommez alors votre projet dans la zone appropriée, ici ce sera sqlServerDeveloppez, cliquez sur OK. Dès lors une deuxième fenêtre s'affiche, il s'agit ici de fournir les informations de connexion relatives au serveur sur lequel l'on souhaitera déployer notre projet. Cela consiste donc à fournir le nom du serveur (et éventuellement de l'instance), les paramètres d'authentification (intégrée ou un couple login et password ayant les autorisations nécessaires), et enfin de sélectionner la base de données à utiliser. À cet instant cette fenêtre doit correspondre à cela :

Base de données à utiliser

Vous pouvez désormais cliquer sur OK, à cet instant, Visual Studio vous demande si vous désirez activer le débogage SQL/CLR sur cette connexion, cliquez sur Oui. Voilà, si tout s'est bien déroulé Visual Studio doit être en train de créer votre projet en fonction de toutes les informations que vous avez fournies. Comme pour n'importe quel autre type de projet, vous pouvez consulter dans l'explorateur de solution la structure de ce projet.

Débogage SQL/CLR

Jusqu'ici vous n'avez créé qu'un projet « générique », il faut maintenant spécifier que vous voulez développer une procédure stockée, pour cela cliquez avec le bouton droit de votre souris sur votre projet dans l'explorateur de solution et sélectionnez add new item, vous l'aurez deviné, choisissez Stored Procedure dans la liste… Vous avez dans cette liste un aperçu des éléments que vous pouvez intégrer dans un SQL Server Project. Nommez cette procédure maPremiereProcedure.

Vous pouvez voir qu'en choisissant une procédure stockée Visual Studio vous fournit un squelette pour celle-ci, avec notamment les Imports et les diverses déclarations nécessaires ! Voici, ci-dessous, une copie du code qui vous est donné :

Squelette du code généré par Visual Studio 2005

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub  maPremiereProcedure ()
' Add your code here
End Sub

End Class

Au travers de ce code, vous voyez qu'il ne diffère pas énormément d'une classe « normale ». Tout est prêt pour accueillir votre code.

III - Écrire une procédure stockée en .Net

Nous allons créer une procédure stockée basique, elle retournera simplement le nombre d'enregistrements d'une table dont le nom sera passé en paramètre. Nous allons donc faire une requête T-SQL utilisant la fonction count(). Pour cela, il nous faut rajouter un imports de namespace pour réaliser la connexion, il s'agit de System.Data.SqlClient.

Remarque : dans ce code vous pouvez voir que l'on utilise Using et End Using qui sont une nouveauté de VB.Net 2.0, cela permet d'appeler automatiquement la méthode Dispose() pour les objets correspondants, pour que cela soit possible ils doivent implémenter l'interface IDisposable, à la fin du bloc, les ressources utilisées par les objets dans ce même bloc sont libérées.

Pour coder notre procédure stockée, Visual Studio 2005 a déjà réalisé les imports nécessaires et placé Microsoft.SqlServer.Server.SqlProcedure() pour signaler au compilateur qu'il s'agit d'une procédure stockée. Ensuite, pour calculer combien il y a d'enregistrements dans une table il n'y a rien de particulier à faire, voici les étapes à réaliser.

Il faut, tout d'abord, instancier un objet SqlConnection avec en paramètre du constructeur le contexte de connexion (en effet, nul besoin d'une « connection string » puisque notre code s'exécutera au sein même du processus de SQL Server 2005). Ensuite, il faut ouvrir la connexion, puis instancier un objet SqlCommand en lui passant au constructeur la requête T-SQL et la connexion à utiliser.

Vient alors le moment d'utiliser la nouvelle méthode ExecuteEndSend() de la classe SqlPipe, cette méthode permet, comme son nom l'indique, d'exécuter notre commande T-SQL et d'en renvoyer les résultats à « l'auteur » de la requête, on lui passe en paramètre notre objet SqlCommand.

Puis, pour finir, puisque nous avons ouvert notre connexion, nous allons simplement la refermer. Le End Using, va se charger de libérer les ressources nécessaires, cette nouveauté en VB.Net est très appréciable !

Code commenté de la procédure stockée

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

' Attribut qui spécifie qu'il s'agît d'une procédure stockée
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub maPremiereProcedure(ByVal maTable As String)

' Instanciation d'un objet SqlConnection
Using maConnexion As New SqlConnection("context connection = true")

' Instanciation de notre requête dans un objet SqlCommand
Dim maCommande As SqlCommand = New SqlCommand("SELECT count(*)FROM dbo." & maTable, maConnexion)
' Ouverture de la connexion
maConnexion.Open()
' Éxécution de la commande et renvoi du résultat
SqlContext.Pipe.ExecuteAndSend(maCommande)
' Fermeture de la connexion
maConnexion.Close()

' Fin du "Using", les ressources à l'intérieur du bloc "Using" vont être libérées
End Using

End Sub

End Class

Remarque : Microsoft recommande de signer numériquement ses assemblies, pour des raisons de sécurité et de gestion des différentes versions que vous pourrez créer. Pour signer son assembly, Visual Studio 2005 intègre une nouveauté, il suffit d'aller dans l'explorateur de solution, de cliquer sur son projet avec le bouton droit de la souris et de choisir Properties. Il faut ensuite aller dans l'onglet Signing, puis cocher Sign the assembly, sélectionnez New dans la liste déroulante, une fenêtre identique à l'image ci-dessous s'ouvre, et vous invite à saisir un nom de fichier (fichier qui contiendra des informations pour signer l'assembly), cliquez sur OK, et voilà, votre assembly est signée. Vous pouvez éventuellement saisir un mot de passe pour sécuriser le fichier de signature qui sera créé.

Signature de l'assembly

Après avoir vu comment coder notre procédure stockée, nous allons maintenant voir comment la déployer directement sur notre serveur SQL Server 2005.

IV - Publier une procédure stockée dans SQL Server avec Visual Studio

Publier une procédure stockée dans SQL Server 2005, depuis Visual Studio, est un véritable jeu d'enfant. En effet, regardez bien, cela va allez très vite.

Compilez votre projet en cliquant sur Build puis « Build le_nom_de_votre_projet », si tout va bien la compilation ne pose pas de problème, vous n'avez alors plus qu'à cliquer sur votre projet avec le bouton droit de la souris dans l'explorateur de solution et à choisir « Deploy ». Dès lors, la fenêtre que vous pouvez voir ci-dessous s'ouvre et vous demande si nécessaire un login et un mot de passe ayant les autorisations nécessaires pour déployer cette procédure stockée dans SQL Server 2005.

Déploiement de la procédure stockée

Remarque : si c'est la première fois que vous utilisez la CLR intégrée dans SQL Server, elle doit sûrement être désactivée, pour l'activer, il suffit d'exécuter une commande T-SQL dans SQL Express Manager, voici cette commande :

Commande d'activation de la CLR dans SQL Server 2005

EXEC sp_configure @configname = 'clr enabled', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO

Si le déploiement s'est bien déroulé, dans la fenêtre output de Visual Studio vous devez voir s'afficher :

========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

Cela signifie que votre assembly .Net, et donc votre procédure stockée, se trouve désormais directement intégrée dans SQL Server 2005. Vous pouvez bien entendu vérifier sa présence et son fonctionnement par vous-même dans le SQL Express Manager. Pour cela, allez dans l'explorateur jusqu'au nœud de la base de données que vous avez utilisée, puis dans le dossier programability; allez voir dans Stored Procedure et vous trouverez votre ou vos procédure(s) stockée(s). Votre procédure stockée est bien évidemment utilisable depuis du code managé dans une application .Net mais aussi depuis n'importe quel script T-SQL ayant les permissions requises, votre développement est un devenu un élément à part entière de SQL Server, il s'exécute à l'intérieur de celui-ci de par l'intégration de la CLR.

Voici comment exécuter votre procédure stockée dans SQL Express Manager : dans la fenêtre « Query Editor » saisissez le script suivant et lancez son exécution :

Commande d'exécution de la procédure stockée

USE Developpez

EXEC maPremiereProcedure "nom_d'une_table"
GO

Le résultat (le nombre d'enregistrements dans la table passée en paramètre) vous est retourné. Voilà, vous venez de réaliser et de tester votre première procédure stockée en .Net.

Conclusion

Au travers de cet article, nous avons pu entrevoir les possibilités offertes par l'intégration de la CLR dans SQL Server 2005. L'imbrication avec Visual Studio vous permet d'être très productif, on peut citer en exemple le système de déploiement de vos assembly que l'on a vu un peu plus haut. Au-delà des procédures stockées en .Net, la CLR 2.0 va vous permettre d'étendre les capacités et la personnalisation de SQL Server 2005, pour voir cela plus en détail, je vous donne rendez-vous dans de prochains articles.

Si vous souhaitez avoir plus d'informations sur SQL Server 2005 je vous recommande l'excellent article de Thomas Lebrun que vous pouvez trouver ici.

Un grand merci à Freegreg pour la relecture de cet article.