Procédure pas à pas : création d'une procédure stockée dans le code managé

Article
08/10/2011

Vous pouvez écrire des procédures stockées pour les bases de données Microsoft SQL Server 2005 et SQL Server 2008 en code managé à l'aide des langages .NET Framework tels que Visual Basic et Visual C#. Les procédures stockées écrites en code managé sont appelées procédures stockées du Langage Common Runtime SQL Server ou procédures stockées CLR SQL.

Vous pouvez créer des procédures stockées SQL en ajoutant des éléments Procédure stockée aux projets de base de données CLR SQL. Une fois que vous avez déployé les procédures stockées que vous avez créées en code managé, celles-ci sont appelées et exécutées comme n'importe quelle autre procédure stockée.

Cette procédure pas à pas décrit les tâches suivantes :

Création d'un nouveau Projet de base de données CLR SQL
Création d'une procédure stockée en code managé
Déploiement de la procédure stockée vers une base de données SQL Server 2008
Création d'un script pour tester la procédure stockée sur la base de données
Interrogation des données dans la base de données pour vérifier que la procédure stockée s'exécute correctement

Composants requis

Pour exécuter cette procédure pas à pas, vous devez :

d'une connexion à l'exemple de base de données AdventureWorks2008 qui s'exécute sur SQL Server 2008. Pour plus d'informations, consultez la page Microsoft SQL Server Product Samples (page éventuellement en anglais) sur le site Web Microsoft.

Création du projet

Pour créer le nouveau projet de base de données CLR SQL

Dans le menu Fichier, créez un nouveau projet.
Sélectionnez Projet de base de données CLR SQL, nommez le projet ProcédureStockéeCLRSQL, puis cliquez sur OK. Pour plus d'informations, consultez Comment : créer un projet pour des objets de base de données qui utilisent l'intégration du Common Language Runtime SQL Server.

Connexion à une base de données SQL Server 2008

Cette procédure pas à pas requiert une connexion à l'exemple de base de données AdventureWorks2008 qui s'exécute sur SQL Server 2008. Si une connexion à l'exemple de base de données AdventureWorks2008 est disponible dans l'Explorateur de serveurs, elle figurera dans Ajouter une référence de base de données, boîte de dialogue.

Pour vous connecter à l'exemple de base de données AdventureWorks

Connectez-vous à l'exemple de base de données AdventureWorks2008 fourni avec SQL Server 2008 en renseignant la boîte de dialogue Ajouter une connexion. Pour plus d'informations, consultez Ajouter une/Modifier la connexion (Microsoft SQL Server).

ou
Sélectionnez une connexion existante à l'exemple de base de données AdventureWorks2008 dans la boîte de dialogue Ajouter une référence de base de données. Pour plus d'informations, consultez Ajouter une référence de base de données, boîte de dialogue.

Configuration du projet de base de données CLR SQL

Une fois que vous avez créé votre projet de base de données CLR SQL, vous pouvez modifier plusieurs propriétés de projet avant de déployer votre projet avec succès.

Pour configurer les propriétés du projet de base de données CLR SQL

Dans l'Explorateur de solutions, sélectionnez le projet pour lequel vous souhaitez configurer des propriétés.
Dans le menu Projet, cliquez sur Propriétés de NomProjet, où NomProjet est le nom que vous avez donné au projet de base de données CLR SQL.
Sous l'onglet Application, dans Nom de l'assembly, spécifiez le nom que vous souhaitez donner à l'assembly généré à partir de votre projet de base de données CLR SQL.
Sous l'onglet Application, dans Framework cible, cliquez sur la version du .NET Framework prise en charge par la version cible de SQL Server.

Important

SQL Server 2005 et SQL Server 2008 prennent uniquement en charge les projets SQL Server générés avec .NET Framework 2.0. Si vous essayezSQL Server de déployer un projet versSQL Server 2005 ouSQL Server 2008, une erreur s'affiche : Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database (où AssemblyName est le nom de l'assembly que vous déployez).
Sous l'onglet Base de données, dans Propriétaire de l'assembly, tapez le nom d'un utilisateur ou d'un rôle en tant que propriétaire de l'assembly.

Important

Il doit s'agir du nom d'un rôle dont l'utilisateur actuel est membre ou l'utilisateur actuel doit disposer de l'autorisation IMPERSONATE. Si vous ne spécifiez aucun propriétaire pour l'assembly, la propriété est attribuée à l'utilisateur actuel. Ce paramètre correspond à l'argument nom_propriétaire AUTHORIZATION pour l'instruction CREATE ASSEMBLY SQL Server. Pour plus d'informations, consultez CREATE ASSEMBLY (Transact-SQL) sur le site Web Microsoft.
Dans le menu Fichier, cliquez sur Enregistrer tout.

Création de la procédure stockée SQL Server

Après avoir créé le projet de base de données CLR SQL, ajoutez-lui une procédure stockée.

Pour créer la procédure stockée SQL Server

Dans le menu Projet, cliquez sur Ajouter un nouvel élément.
Dans la boîte de dialogue Ajouter un nouvel élément, sélectionnez Procédure stockée.
Tapez InsertCurrency comme Nom pour la nouvelle procédure stockée.
Cliquez sur Ajouter.

Remplacez le code dans l'Éditeur de code par les éléments suivants :

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

Partial Public Class StoredProcedures

    <SqlProcedure()>
    Public Shared Sub InsertCurrency(
        ByVal currencyCode As SqlString, ByVal name As SqlString)

        Using conn As New SqlConnection("context connection=true")

            Dim InsertCurrencyCommand As New SqlCommand()
            Dim currencyCodeParam As New SqlParameter("@CurrencyCode", SqlDbType.NVarChar)
            Dim nameParam As New SqlParameter("@Name", SqlDbType.NVarChar)

            currencyCodeParam.Value = currencyCode
            nameParam.Value = name


            InsertCurrencyCommand.Parameters.Add(currencyCodeParam)
            InsertCurrencyCommand.Parameters.Add(nameParam)

            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" &
                " VALUES(@CurrencyCode, @Name, GetDate())"

            InsertCurrencyCommand.Connection = conn

            conn.Open()
            InsertCurrencyCommand.ExecuteNonQuery()
            conn.Close()
        End Using
    End Sub
End Class

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [SqlProcedure()]
    public static void InsertCurrency_CS(
        SqlString currencyCode, SqlString name)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand InsertCurrencyCommand = new SqlCommand();
            SqlParameter currencyCodeParam = new SqlParameter("@CurrencyCode", SqlDbType.NVarChar);
            SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar);

            currencyCodeParam.Value = currencyCode;
            nameParam.Value = name;

            InsertCurrencyCommand.Parameters.Add(currencyCodeParam);
            InsertCurrencyCommand.Parameters.Add(nameParam);

            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
                " VALUES(@CurrencyCode, @Name, GetDate())";

            InsertCurrencyCommand.Connection = conn;

            conn.Open();
            InsertCurrencyCommand.ExecuteNonQuery();
            conn.Close();
        }
    }
}

Déploiement, exécution et débogage de la procédure stockée

Lorsque vous avez créé une nouvelle procédure stockée, elle peut être générée, déployée sur SQL Server et déboguée en appuyant sur F5. Ajoutez d'abord le code permettant d'exécuter et de tester votre procédure stockée au fichier Test.sql situé dans le dossier TestScripts de votre projet. Pour plus d'informations sur la création de scripts de test, consultez Comment : modifier le script Test.sql pour exécuter des objets qui utilisent l'intégration du Common Language Runtime SQL Server.

Pour plus d'informations sur le débogage de SQL, consultez Debugging SQL Database Objects.

Pour déployer et exécuter la procédure stockée InsertCurrency

Dans l'Explorateur de solutions, développez le dossier Scripts de test et double-cliquez sur le fichier Test.sql.

Notes

Vous pouvez spécifier d'autres scripts comme script de débogage par défaut. Pour plus d'informations, consultez Comment : modifier le script Test.sql pour exécuter des objets qui utilisent l'intégration du Common Language Runtime SQL Server.

Remplacez le code dans le fichier Test.sql par le code suivant :

'VB
EXEC InsertCurrency 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

//C#
EXEC InsertCurrency_CS 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

Appuyez sur F5 pour générer, déployer et déboguer la procédure stockée. Pour plus d'informations sur le déploiement sans débogage, consultez Comment : déployer des éléments de projet de base de données CLR SQL sur un serveur SQL Server.

Affichez les résultats affichés dans la fenêtre Sortie et entrez la sortie de spectacle de : Sortie de base de données.

Voir aussi

Partager via