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

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.

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

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.

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é…

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.

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 :

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 :

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.

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é :

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.

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.

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 !

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

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.

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.

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 :

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.

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.