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

Article
08/21/2008

Mise à jour : novembre 2007

Les procédures stockées pour les bases de données SQL Server 2005 peuvent désormais être écrites en code managé à l'aide de langages .NET Framework tels que Visual Basic, C# et C++. Les procédures stockées écrites en code managé sont appelées procédures stockées CLR.

Vous pouvez créer des procédures stockées SQL en ajoutant des éléments Procédure stockée aux projets SQL Server. Une fois le déploiement sur SQL Server réussi, les procédures stockées créées dans du code managé sont appelées et exécutées comme toute autre procédure stockée.

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

Création d'un nouveau projet Application Windows.
Création d'une procédure stockée dans du code managé.
Déploiement de la procédure stockée sur une base de données SQL Server 2005.
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 avez besoin des éléments suivants :

une connexion à l'exemple de base de données AdventureWorks qui s'exécute sous SQL Server 2005. Pour plus d'informations, consultez Comment : installer des exemples de bases de données.

Création du projet

Pour créer le nouveau projet SQL Server

Dans le menu Fichier, créez un nouveau projet.
Sélectionnez Projet SQL Server, nommez le projet SQLCLRStoredProcedure et cliquez sur OK. Pour plus d'informations, consultez Comment : créer un projet SQL Server.

Connexion à une base de données SQL Server 2005

Cette procédure pas à pas nécessite une connexion à l'exemple de base de données AdventureWorks qui s'exécute sous SQL Server 2005. Si une connexion à l'exemple de base de données AdventureWorks est disponible dans l'Explorateur de serveurs, elle est répertoriée dans la Ajouter une référence de base de données, boîte de dialogue.

Remarque :
Par défaut, la fonctionnalité d'intégration du Common Language Runtime (CLR) est désactivée dans Microsoft SQL Server. Elle doit être activée pour utiliser les éléments de projet SQL Server. Pour activer l'intégration du CLR, utilisez l'option clr enabled de la procédure stockée sp_configure. Pour plus d'informations, consultez Activation de l'intégration CLR.

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

Connectez-vous à l'exemple de base de données AdventureWorks fourni avec SQL Server 2005 en remplissant 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 AdventureWorks 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.

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

Une fois le projet SQL Server créé, ajoutez-y 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.
Sélectionnez Procédure stockée dans la Ajouter un nouvel élément, boîte de dialogue.
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 :

Remarque :
Les exemples C++ doivent être compilés avec l'option du compilateur /clr:safe.

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();
        }
    }
}

#include "stdafx.h"

#using <System.dll>
#using <System.Data.dll>
#using <System.Xml.dll>

using namespace System;
using namespace System::Data;
using namespace System::Data::Sql;
using namespace System::Data::SqlClient;
using namespace System::Data::SqlTypes;
using namespace Microsoft::SqlServer::Server;

// In order to debug your Stored Procedure, add the following to your debug.sql file:
//
// EXEC InsertCurrency_CPP 'AAA', 'Currency Test'
// SELECT * FROM Sales.Currency WHERE CurrencyCode = 'AAA'

public ref class StoredProcedures
{
public:
    [SqlProcedure]
    static void InsertCurrency_CPP(SqlString currencyCode, SqlString name)
    {
        SqlConnection ^conn = gcnew SqlConnection("context connection=true");

        SqlCommand ^insertCurrencyCommand = gcnew SqlCommand();
        SqlParameter ^currencyCodeParam =
            gcnew SqlParameter("@CurrencyCode", SqlDbType::NVarChar);
        SqlParameter ^nameParam =
            gcnew SqlParameter("@Name", SqlDbType::NVarChar);

        insertCurrencyCommand->CommandText =
            "insert Sales.Currency(CurrencyCode, Name, ModifiedDate)" +
            " values(@CurrencyCode, @Name)";
        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. En Visual C++, ce fichier s'appelle debug.sql. Pour plus d'informations sur la création de scripts de test, consultez Comment : modifier le script Test.sql pour exécuter des objets SQL.

Pour plus d'informations sur le débogage de SQL, consultez Débogage d'objets de base de données SQL.

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

Pour Visual Basic et Visual C#, dans l'Explorateur de solutions, développez le dossier TestScripts et double-cliquez sur le fichier Test.sql.

Pour Visual C++, dans l'Explorateur de solutions, double-cliquez sur le fichier debug.sql.

Remplacez le code dans le fichier Test.sql (debug.sql en Visual C++) par le code suivant :

EXEC InsertCurrency '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 SQL Server sur un serveur SQL Server.

Consultez les résultats affichés dans la fenêtre Sortie et sélectionnez Afficher la sortie à partir de : Sortie de base de données.

Voir aussi

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

Composants requis

Création du projet

Pour créer le nouveau projet SQL Server

Connexion à une base de données SQL Server 2005

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

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

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

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

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

Voir aussi

Tâches

Concepts

Référence

Autres ressources

Ressources supplémentaires