Comment : créer et exécuter une procédure stockée SQL Server CLR

Article
08/21/2008

Mise à jour : novembre 2007

Créez 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 un ordinateur 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.

Remarque :
La fonctionnalité d'intégration du Common Language Runtime (CLR) est désactivée par défaut dans Microsoft SQL Server et doit être activée pour utiliser des é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.

Remarque :
Les boîtes de dialogue et les commandes de menu qui s'affichent peuvent être différentes de celles qui sont décrites dans l'aide, en fonction de vos paramètres actifs ou de l'édition utilisée. Pour modifier vos paramètres, choisissez Importation et exportation de paramètres dans le menu Outils. Pour plus d'informations, consultez Paramètres Visual Studio.

Création de procédures stockées SQL Server

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

Ouvrez un Projet SQL Server existant ou créez-en un. Pour plus d'informations, consultez Comment : créer un projet SQL Server.
Dans le menu Projet, sélectionnez Ajouter un nouvel élément.
Sélectionnez Procédure stockée dans la Ajouter un nouvel élément, boîte de dialogue.
Tapez le Nom de la nouvelle procédure stockée.
Ajoutez le code à exécuter lors de l'application de la procédure stockée. Voir l'exemple ci-dessous.

Remarque :

Les exemples C++ doivent être compilés avec l'option du compilateur /clr:safe.
Pour Visual Basic et Visual C#, dans l'Explorateur de solutions, ouvrez le dossier TestScripts et double-cliquez sur le fichier Test.sql.

Pour Visual C++, dans l'Explorateur de solutions, ouvrez le fichier debug.sql.
Ajoutez au fichier Test.sql (debug.sql en Visual C++) le code permettant d'exécuter la procédure stockée. Consultez le second exemple ci-dessous.
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 dans la Sortie, fenêtre et sélectionnez Afficher la sortie à partir de : Sortie de base de données.

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

Exemple

L'exemple de code suivant crée une procédure stockée qui insère un enregistrement dans la table Currency de l'exemple de base de données Adventure Works. Une fois la procédure créée, déployez-la sur SQL Server. Pour plus d'informations, consultez Comment : déployer des éléments de projet SQL Server sur un serveur SQL Server.

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

Ajoutez le code permettant d'exécuter et de tester votre procédure stockée au fichier Test.sql (debug.sql en Visual C++) situé dans le dossier TestScripts de votre projet. Par exemple, si vous avez déployé une procédure stockée, exécutez-la en appelant EXEC <StoredProcedureName> et en passant les paramètres attendus. Si votre procédure stockée ne retourne aucune valeur, insérez le code supplémentaire afin de vérifier que les données ont été affectées par la procédure stockée.

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

Voir aussi

Partager via

Comment : créer et exécuter une procédure stockée SQL Server CLR

Création de procédures stockées SQL Server

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

Exemple

Voir aussi

Tâches

Concepts

Référence

Autres ressources

Ressources supplémentaires