Nous allons voir dans cet article comment créer et exporter des procédures et fonctions stockées en C#, VB.NET et T-SQL pour SQL Server 2005.

Introduction

Présentation

Dans cet article nous allons présenter et apprendre à utiliser la technologie CLR 2.0 intégrée à SQL Server 2005. Common Language Runtime permet d’écrire du code .NET (peut importe le langage de programmation) dans un environnement sécurisé ce qui permet d’écrire les procédures et fonctions stockées, des triggers, des agrégats ainsi que des nouveaux types de données dans un langage .NET donc en programmation Objet et d’en facilité l’exécution, la gestion et la mise à jour. Cette facilité de développement est accrue grâce à l’outil de développement Visual Studio 2005.

Sécurité

Pour augmenter la sécurité des assemblies (car un programme écrit en .NET non sécurisé ou avec des bugs, peut être un véritable trou de sécurité pour un serveur). Afin de palier à ce problème, trois niveaux de sécurité peuvent être définis pour une assemblie : SAFE (Sécurisé), EXTERNAL-ACCESS (Externe) et UNSAFE (Non sécurisé). Ce niveau de permission peut être défini dans les propriétés de votre projet Visual Studio 2005 dans l’onglet Base de données, par défaut le niveau est sécurisé, il est très conseillé de ne pas le modifier.

• Sécurisé : Niveau par défaut. L’assemblie n’a accès qu’au contexte de SQL Server et à rien d’autre (Ressources système, accès aux fichiers du système d’exploitation…).
• Externe : Niveau intermédiaire. L’assemblie a accès à toutes les ressources externes (Ressources réseau, base de registre, fichiers…) si les autorisations lui sont accordées.
• Non sécurisé : Niveau le plus bas. L’assemblie a accès à toutes les ressources exploitables par le Framework 2.0 si les autorisations lui sont accordées.

Il est bien sûr toujours possible de créer vos procédures et fonctions stockées en T-SQL qui reste toujours efficace. Nous ferons dans cet article une procédure et une fonction stockée écrite dans les langages VB.NET, C# et en T-SQL.

Préparation de l’environnement de travail

Les logiciels nécessaires dans cet article

• Visual Studio 2005 (Outils de développement de la procédure ou de la fonction)
• SQL Server 2005 (Moteur de base de données)
• SQL Server Management Studio (Outils de gestion graphique de la base de données)

Vous pouvez télécharger les versions Express de Visual Basic 2005 Express Edition, Visual C# Express Edition et SQL Server 2005 Express Edition sur le site de Microsoft France : http://www.microsoft.com/france/msdn/vstudio/express/default.mspx Je vous rappelle que les versions Express sont plus limitées au niveau des fonctionnalités mais sont gratuites, illimitées dans le temps et l’utilisation commerciale est autorisée.

Les langages

Dans cet article, nous ne présenterons que la création de procédures et fonctions stockées dans les langages Visual Basic .NET, C#.NET et en T-SQL dans un but de comparaison. Bien sûr, il est possible de développer vos assemblies dans n’importe quel langage de programmation .NET.

Activation du CLR

Par défaut, après l’installation de SQL Server 2005, le CLR est désactivé. Vous ne devez l’activer uniquement si vous en avez besoin. Il existe deux manières de l’activer. La première est d’utiliser l’interface graphique « SQL Server Management Studio » et de lancer la requête suivante :

EXEC sp_configure @configname = ‘clr enabled’, @configvalue = 1

La seconde méthode est d’utiliser l’outil « Configuration de la surface d’exposition de SQL Server 2005 »

Il est possible de configurer la surface d’exposition d’un serveur distant (si l’option d’accès à distance a été activée sur le serveur) en cliquant sur modifier l’ordinateur, à partir de là , vous pouvez choisir le nom du serveur à configurer. Ensuite, cliquez sur Configuration de la surface d’exposition pour les fonctionnalités.

Allez dans le menu Intégration du CLR et cochez la case Activer l’intégration du CLR si cela n’est pas déjà fait. Appliquez cette modification et redémarrez le moteur SQL.

Développement

Création du projet Visual Studio 2005

Ouvrez Visual Studio 2005 et créez un nouveau projet de Base de données (En C# ou VB.NET). Donnez un nom à votre projet, choisissez le répertoire de stockage de la solution et une nouvelle fenêtre apparaîtra.

Choisissez le nom du serveur (En cliquant sur le bouton Actualiser la liste des serveurs disponibles apparaitrons. Une fois le serveur sélectionné, choisissez le nom de la base de données. Ici nous utiliserons la base LaboDotNet (La source est disponible à la fin de cet article). Pour finir, cliquez sur Tester la connexion pour vérifier si la connexion à la base de données est correcte. Cliquez sur OK.

Activez le débogage SQL/CLR en cliquant sur Oui.

Voilà , votre projet de base de données est terminé maintenant passons au développement d’une procédure et d’une fonction stockée. Dans notre base de données, nous utilisons une table USER qui contient le nom, le prénom et le site SUPINFO d’un utilisateur du laboratoire DotNET (La source est disponible à la fin de l’article).

Création d’une procédure stockée

Pour ajouter une procédure stockée dans votre projet, faite un clic droit sur le nom du projet dans l’explorateur de solution, puis choisissez Ajouter un nouvel élément.

Choisissez Procédure stockée, donnez lui un nom. Ici nous allons créer une procédure permettant l’ajout d’un utilisateur dans la table USER nous l’appellerons donc : AddUser. Vous obtenez le code suivant :

Code VB.NET

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

Partial Public Class StoredProcedures

‹Microsoft.SqlServer.Server.SqlProcedure()›   _
Public Shared Sub AddUser()

    ‘ Ajoutez ici votre code

End Sub

End Class

Code C#

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {         [Microsoft.SqlServer.Server.SqlProcedure]     public static void AddUser()   {               // Placez votre code ici       } };

Voici le script de création de la table USER qui nous permettra de voir comment ajouter un utilisateur dans cette table : CREATE TABLE [dbo].[USERS](    [USER_ID] [int] IDENTITY(1,1) NOT NULL,       [USER_LASTNAME] [nvarchar](60) COLLATE French_CI_AS NOT NULL,        [USER_FIRSTNAME] [nvarchar](60) COLLATE French_CI_AS NOT NULL,         [USER_BIRTHDAY] [datetime] NULL,      [USER_SITE] [nvarchar](60) COLLATE French_CI_AS NULL ) Pour ajouter un utilisateur à la table USER, il nous faut donc son nom, son prénom et le site SUPINFO, ce sont les éléments qu’il faut mettre dans la signature de la procédure. Nous avons donc le code suivant pour la procédure stockée :

Code VB.NET

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class StoredProcedures   ‹Microsoft.SqlServer.Server.SqlProcedure()› _   Public Shared Sub AddUser(ByVal UserName As String, _     ByVal UserFirstName As String, ByVal UserSite As String)     ‘ Instanciation de la connexion     Dim cnx As New SqlConnection(”context connection = true”)     ‘ Création de la requête     Dim query As String = “INSERT INTO USERS (USER_LASTNAME, USER_FIRSTNAME, USER_SITE)” & _                         ” VALUES (’” & UserName & “‘, ‘” & UserFirstName & _                          ”‘, ‘” & UserSite & “‘)”     Try       ‘ Création de la commande SQL       Dim cmd As SqlCommand = New SqlCommand(query, cnx)       ‘ On ouvre la connexion       cnx.Open()       ‘ On exécute la commande sur le serveur et on envoie les résultats au client       SqlContext.Pipe.ExecuteAndSend(cmd)     Catch ex As Exception       SqlContext.Pipe.Send(”ERROR : ” & ex.Message)     Finally       ‘ On ferme la connexion si elle a été ouverte (S’il n’y a pas eu d’erreur)       If cnx.State = ConnectionState.Open Then cnx.Close()     End Try   End Sub End Class

Code C#

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {   [Microsoft.SqlServer.Server.SqlProcedure]   public static void AddUser(String UserName, UserFirstName, String UserSite)   {     // Instanciation de la connexion     SqlConnection cnx = new SqlConnection(”context connection = true”);     // Création de la requête     String query = “INSERT INTO USERS (USER_LASTNAME, USER_FIRSTNAME, USER_SITE) ” _             + “VALUES ” + UserName + “‘, ‘” + UserFirstName + “‘, ‘” _            + UserSite + “‘)”;     try     {       // Création de la commande SQL       SqlCommand cmd = new SqlCommand(query, cnx);       // On ouvre la connexion       cnx.Open();       // On exécute la commande sur le serveur SQL et on envoie les résultats au client       SqlContext.Pipe.ExecuteAndSend(cmd);     }     catch (Exception e)     {       SqlContext.Pipe.Send(”ERROR : ” + e.Message);     }     finally     {       // On ferme la connexion si elle a été ouverte (S’il n’y a pas eu d’erreur)       if (cnx.State == ConnectionState.Open) { cnx.Close(); }     }   } };

Code T-SQL

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO — ============================================= — Author: LAVOCAT Fabien — Create date: 03/07/2007 — Description: Add an user — ============================================= CREATE PROCEDURE [dbo].[AddUser]      @LASTNAME NVARCHAR(60),        @FIRSTNAME NVARCHAR(60), @SITE NVARCHAR(60) AS BEGIN    INSERT INTO USERS (USER_LASTNAME, USER_FIRSTNAME, USER_SITE)     VALUES (@LASTNAME, @FIRSTNAME, @SITE); END

Voilà, vous avez fait une procédure stockée, maintenant passons à la création d’une fonction. Pour passer au déploiement de votre procédure sur votre serveur SQL, passez à la partie « Déploiement ».

Création d’une fonction stockée

Nous allons maintenant créer une fonction qui permettra de récupérer le nombre d’utilisateur dans la table USER. Faites un clic droit sur le projet dans l’explorateur de solution et faites ajouter une fonction définie par l’utilisateur.

Vous obtenez le code suivant :

Code VB.NET

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class UserDefinedFunctions   ‹Microsoft.SqlServer.Server.SqlFunction()› _ Public Shared Function GetNbUsers() As SqlString             ‘ Ajoutez ici votre code              Return New SqlString(”Hello”)     End Function End Class

Code C#

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions {         [Microsoft.SqlServer.Server.SqlFunction]      public static SqlString GetNbUsers()         {              // Placez votre code ici              return new SqlString(”Hello”);         } };

Donc le code de la fonction devient :

Code VB.NET

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class UserDefinedFunctions   ‹Microsoft.SqlServer.Server.SqlFunction()› _   Public Shared Function GetNbUsers() As SqlInt32     ‘ Instanciation de la connexion     Dim cnx As New SqlConnection(”context connection = true”)     Dim ValeurRetournee As SqlInt32 = New SqlInt32(-1) ‘ -1 = erreur     ‘ Création de la requête     Dim query As String = “SELECT COUNT(*) FROM USERS”     Try       ‘ Création de la commande SQL       Dim cmd As SqlCommand = New SqlCommand(query, cnx)       ‘ On ouvre la connexion       cnx.Open()       ‘ On exécute la commande sur le serveur SQL et on envoie les résultats au client       ValeurRetournee = CInt(cmd.ExecuteScalar())     Catch ex As Exception       SqlContext.Pipe.Send(”ERROR : ” & ex.Message)     Finally       ‘ On ferme la connexion si elle a été ouverte (S’il n’y a pas eu d’erreur)       If cnx.State = ConnectionState.Open Then cnx.Close()     End Try     ‘ On retourne la valeur au client     Return ValeurRetournee   End Function End Class

Code C#

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions {   [Microsoft.SqlServer.Server.SqlFunction]   public static SqlInt32 GetNbUsers()   {     // Instanciation de la connexion     SqlConnection cnx = new SqlConnection(”context connection = true”);     SqlInt32 valeurRetournee = new SqlInt32(-1); // -1 = Erreur     // Création de la requête     String query = “SELECT COUNT(*) FROM USERS”;     try     {       // Création de la commande SQL       SqlCommand cmd = new SqlCommand(query, cnx);       // On ouvre la connexion       cnx.Open();       // On exécute la commande sur le serveur SQL et on envoie les résultats au client       valeurRetournee = (int)cmd.ExecuteScalar();     }     catch (Exception e)     {       SqlContext.Pipe.Send(”ERROR : ” + e.Message);     }     finally     {       // On ferme la connexion si elle a été ouverte (S’il n’y a pas eu d’erreur)       if (cnx.State == ConnectionState.Open) { cnx.Close(); }     }     return valeurRetournee;   } };

Code T-SQL

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO — ============================================= — Author: LAVOCAT Fabien — Create date: 03/07/2007 — Description: Get number users — ============================================= CREATE FUNCTION GetNbUsers () RETURNS INT AS RETURN (        SELECT COUNT(*) FROM USERS; ) GO

Déploiement

Le déploiement de fonctions, procédures, triggers… est vraiment très simple grâce à Visual Studio 2005. En effet un simple clic sur le menu Générer > Déployer la solution vous permet d’obtenir votre solution directement dans SQL Server 2005. Pour vérifier que tout à bien été correctement envoyé au serveur SQL, utilisez l’outil SQL Server Management Studio. Choisissez votre base de données (Ici LaboDotNet) - Programmabilité puis Procédures stockées ou fonctions suivant ce que vous avez codé. Vous obtiendrez ainsi la liste de toutes les procédures ou fonctions disponibles dans cette base de données.

En faisant un clic droit sur votre procédure ou fonction stockée il est possible l’exécuter afin de la tester sur la base de données.

Sources

Vous pouvez télécharger les sources des programmes des procédures et fonctions en C# et VB.NET ainsi que le script de création de la base de données de test que nous avons utilisée dans cet article. Source VB.NET Source C#.NET Source Base de données LaboDotNet + Table USERS