Procedura dettagliata: creazione di una stored procedure nel codice gestito

Aggiornamento: novembre 2007

Le stored procedure per i database di SQL Server 2005 possono ora essere scritte in codice gestito, utilizzando linguaggi .NET Framework come Visual Basic, C# e C++. Le stored procedure scritte in codice gestito sono chiamate stored procedure CLR.

Per creare stored procedure SQL è necessario aggiungere elementi Stored Procedure ai progetti SQL Server. Una volta eseguita la distribuzione su SQL Server, le stored procedure create nel codice gestito vengono chiamate ed eseguite come qualsiasi altra stored procedure.

Di seguito vengono illustrate le attività incluse nella procedura dettagliata:

• Creazione di un nuovo progetto Applicazione Windows.

• Creazione di una stored procedure in codice gestito.

• Distribuzione della stored procedure in un database di SQL Server 2005.

• Creazione di uno script per il test della stored procedure sul database.

• Esecuzione di query di dati nel database per confermare la corretta esecuzione della stored procedure.

Prerequisiti

Per completare questa procedura dettagliata, è necessaria:

• Una connessione al database di esempio AdventureWorks in esecuzione su SQL Server 2005. Per ulteriori informazioni, vedere Procedura: installare database di esempio.

Creazione del progetto

Per creare il nuovo progetto SQL Server

1. Scegliere il comando per la creazione di un nuovo progetto dal menu File.

2. Selezionare Progetto SQL Server, assegnare al progetto il nome SQLCLRStoredProcedure e scegliere OK. Per ulteriori informazioni, vedere Procedura: creare un progetto SQL Server.

Connessione a un database SQL Server 2005

Questa procedura dettagliata richiede una connessione al database di esempio AdventureWorks in esecuzione su SQL Server 2005. Se tale connessione è disponibile in Esplora server, verrà elencata nella Finestra di dialogo Aggiungi riferimento al database.

Nota:

Per impostazione predefinita, la funzionalità di integrazione di Common Language Runtime (CLR) è disattivata in Microsoft SQL Server e deve essere attivata per poter utilizzare gli elementi del progetto SQL Server. Per attivare la funzionalità di integrazione di CLR, utilizzare l'opzione clr enabled della stored procedure sp_configure. Per ulteriori informazioni, vedere Attivazione dell'integrazione CLR.

Per effettuare la connessione al database di esempio AdventureWorks

• Effettuare la connessione al database di esempio AdventureWorks incluso in SQL Server 2005 completando la finestra di dialogo Aggiungi connessione. Per ulteriori informazioni, vedere Aggiungi/Modifica connessione (Microsoft SQL Server).

  - oppure -

• Selezionare una connessione esistente al database di esempio AdventureWorks nella finestra di dialogo Aggiungi riferimento al database. Per ulteriori informazioni, vedere Finestra di dialogo Aggiungi riferimento al database.

Creazione della stored procedure SQL Server

Dopo aver creato il progetto SQL Server, aggiungergli una stored procedure.

Per creare la stored procedure SQL Server

1. Scegliere Aggiungi nuovo elemento dal menu Progetto.

2. Selezionare Stored procedure nella Finestra di dialogo Aggiungi nuovo elemento.

3. Digitare InsertCurrency come Nome per la nuova stored procedure.

4. Scegliere Aggiungi.

5. Sostituire il codice nell'editor di codice con quanto segue:

   Nota:
   Gli esempi di C++ devono essere compilati con l'opzione del compilatore /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();
       }
   };
   

Distribuzione, esecuzione e debug della stored procedure

Una volta creata, una stored procedure può essere compilata, distribuita al server SQL e sottoposta a debug premendo F5. Innanzitutto, nel file Test.sql ubicato nella cartella Script di test del progetto aggiungere il codice per eseguire e verificare la stored procedure. Per Visual C++, questo file è denominato debug.sql. Per ulteriori informazioni sulla creazione di script di test, vedere Procedura: modificare lo script Test.sql per eseguire oggetti SQL.

Per ulteriori informazioni sul debug SQL, vedere Debug di oggetti database SQL.

Per distribuire ed eseguire la stored procedure InsertCurrency

1. Per Visual Basic e Visual C#, in Esplora soluzioni espandere la cartella Script di test e fare doppio clic sul file Test.sql.

   Per Visual C++, in Esplora soluzioni fare doppio clic sul file debug.sql.

2. Sostituire il codice nel file Test.sql (debug.sql in Visual C++) con il codice seguente:

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

3. Premere F5 per compilare, distribuire ed eseguire il debug della stored procedure. Per informazioni sulla distribuzione senza debug, vedere Procedura: distribuire elementi del progetto SQL Server su SQL Server.

   Visualizzare i risultati presenti nella finestra Output e selezionare Mostra output di: Output database.

Vedere anche

Attività

Procedura: creare ed eseguire una stored procedure CLR di SQL Server

Procedura: creare ed eseguire un trigger CLR di SQL Server

Procedura: creare ed eseguire un aggregato CLR di SQL Server

Procedura: creare ed eseguire una funzione CLR definita dall'utente di SQL Server

Procedura: creare ed eseguire un tipo CLR definito dall'utente di SQL Server

Procedura: eseguire il debug di una stored procedure CLR SQL

Concetti

Introduzione all'integrazione con CLR di SQL Server (ADO.NET)

Vantaggi dell'utilizzo di codice gestito per creare oggetti di database

Modelli di elementi per progetti SQL Server

Riferimenti

Attributi per oggetti di database e progetti SQL Server

Altre risorse

Esecuzione del debug di database CLR SQL