Procedura dettagliata: creazione di una stored procedure nel codice gestito

Articolo
08/11/2011

Le stored procedure per i database di Microsoft SQL Server 2005 e SQL Server 2008 possono essere scritte in codice gestito utilizzando i linguaggi .NET Framework come Visual Basic e Visual C#. Le stored procedure scritte in codice gestito vengono denominate stored procedure Common Language Runtime di SQL Server o stored procedure CLR SQL.

Per creare stored procedure SQL è necessario aggiungere elementi Stored procedure ai progetti di database CLR SQL. Dopo avere distribuito le stored procedure create in codice gestito, esse vengono chiamate ed eseguite come qualsiasi altra stored procedure.

Di seguito sono elencate alcune delle attività illustrate nella procedura dettagliata:

Creazione di un nuovo Progetto di database CLR SQL
Creazione di una stored procedure in codice gestito
Distribuzione della stored procedure in un database di SQL Server 2008
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, è necessario quanto segue:

Una connessione al database di esempio AdventureWorks2008 in esecuzione su SQL Server 2008. Per ulteriori informazioni, vedere la pagina Esempi di prodotti di Microsoft SQL Server nel sito Web Microsoft (la pagina potrebbe essere in inglese).

Creazione del progetto

Per creare il nuovo progetto di database CLR SQL

Scegliere il comando per la creazione di un nuovo progetto dal menu File.
Selezionare Progetto di database CLR SQL, assegnare al progetto il nome SQLCLRStoredProcedure e scegliere OK. Per ulteriori informazioni, vedere Procedura: creare un progetto per gli oggetti di database che utilizzano l'integrazione di Common Language Runtime di SQL Server.

Connessione a un database SQL Server 2008

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

Per effettuare la connessione al database di esempio AdventureWorks

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

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

Configurazione del progetto di database CLR SQL

Dopo avere creato il progetto di database CLR SQL, potrebbe essere necessario modificare alcune proprietà del progetto per una corretta distribuzione.

Per configurare le proprietà del progetto di database CLR SQL

In Esplora soluzioni selezionare il progetto per il quale si desidera configurare le proprietà.
Scegliere Proprietà di NomeProgetto dal menu Progetto, dove NomeProgetto è il nome assegnato al progetto di database CLR SQL.
Nella scheda Applicazione, in Assembly, specificare il nome che si desidera assegnare all'assembly compilato dal progetto di database CLR SQL.

Nella scheda Applicazione, in Versione .NET Framework di destinazione, fare clic sulla versione di .NET Framework supportata dalla versione di destinazione di SQL Server.

Importante
SQL Server 2005 e SQL Server 2008 supportano solo i progetti SQL Server compilati con .NET Framework 2.0. Se si tenta di implementare un progetto SQL Server in SQL Server 2005 o SQL Server 2008, viene visualizzato un errore: 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 (dove AssemblyName è il nome dell'assembly che si sta implementando).

SQL Server 2005 e SQL Server 2008 supportano solo i progetti SQL Server compilati con .NET Framework 2.0. Se si tenta di implementare un progetto SQL Server in SQL Server 2005 o SQL Server 2008, viene visualizzato un errore: 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 (dove AssemblyName è il nome dell'assembly che si sta implementando).

Nella scheda Database, in Proprietario assembly, digitare il nome di un utente o di un ruolo come proprietario dell'assembly.

Importante
Questo valore deve essere il nome di un ruolo di cui l'utente corrente è un membro oppure l'utente corrente deve disporre di un'autorizzazione IMPERSONATE. Se non si specifica un proprietario dell'assembly, la proprietà verrà concessa all'utente corrente. Questa impostazione corrisponde all'argomento AUTHORIZATION nome_proprietario per l'istruzione SQL Server CREATE ASSEMBLY. Per ulteriori informazioni, vedere CREATE ASSEMBLY (Transact-SQL) nel sito Web Microsoft.

Questo valore deve essere il nome di un ruolo di cui l'utente corrente è un membro oppure l'utente corrente deve disporre di un'autorizzazione IMPERSONATE. Se non si specifica un proprietario dell'assembly, la proprietà verrà concessa all'utente corrente. Questa impostazione corrisponde all'argomento AUTHORIZATION nome_proprietario per l'istruzione SQL Server CREATE ASSEMBLY. Per ulteriori informazioni, vedere CREATE ASSEMBLY (Transact-SQL) nel sito Web Microsoft.

Scegliere Salva tutto dal menu File.

Creazione della stored procedure SQL Server

Dopo aver creato il progetto di database CLR SQL, aggiungergli una stored procedure.

Per creare la stored procedure SQL Server

Scegliere Aggiungi nuovo elemento dal menu Progetto.
Nella finestra di dialogo Aggiungi nuovo elemento selezionare la Stored Procedure.
Digitare InsertCurrency come Nome per la nuova stored procedure.
Fare clic su Add.

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

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

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 ulteriori informazioni sulla creazione di script di test, vedere Procedura: modificare lo script Test.sql per eseguire oggetti che utilizzano l'integrazione di Common Language Runtime di SQL Server.

Per ulteriori informazioni su come eseguire il debug SQL, vedere Debugging SQL Database Objects.

Per distribuire ed eseguire la stored procedure InsertCurrency

In Esplora soluzioni espandere la cartella Script di test e fare doppio clic sul file Test.sql.

Nota

È possibile specificare altri script come script di debug predefinito. Per ulteriori informazioni, vedere Procedura: modificare lo script Test.sql per eseguire oggetti che utilizzano l'integrazione di Common Language Runtime di SQL Server.

Sostituire il codice nel file Test.sql con il codice seguente:

'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'

Premere F5 per compilare, distribuire ed eseguire il debug della stored procedure. Per informazioni sulla distribuzione senza debug, vedere Procedura: distribuire elementi di progetti di database CLR SQL Server su SQL Server.

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

Vedere anche

Condividi tramite