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 prendrais 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
Visual Studio 2005 Beta 2 ou supérieur
SQL Server 2005 Beta 2 ou supérieur
1. 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 soit, 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
soit 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.
Safe
External-Access
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'éxécution de SQL Server, elle n'aura, par exemple, pas
accès au système de fichier, 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 tout autre assembly .Net, il est bien
entendu nécessaire de posséder les autorisations adéquates à ces ressources, il ne s'agît
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'agît d'une réelle alternative à 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é…
2. 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 comme 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ée 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'agît 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. A 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é :
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.
3. Ecrire 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'agît 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'agît
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'éxecutera 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 PublicClass StoredProcedures
' Attribut qui spécifie qu'il s'agît d'une procédure stockée
<Microsoft.SqlServer.Server.SqlProcedure()> _
PublicSharedSub maPremiereProcedure(ByVal maTable AsString)
' Instanciation d'un objet SqlConnection
Using maConnexion AsNew SqlConnection("context connection = true")
' Instanciation de notre requête dans un objet SqlCommandDim maCommande As SqlCommand = New SqlCommand("SELECT count(*)FROM dbo." & maTable, maConnexion)
' Ouverture de la connexion
maConnexion.Open()
' Execution 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éesEnd Using
EndSubEndClass
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ée.
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.
4. 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'a
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 surement être désactivée, pour l'activer, il
suffit juste 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 :
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é, 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
part 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 lancer 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.
