Creazione di stored procedure e funzioni definite dall'utente con codice gestito (C#)

di Scott Mitchell

Scarica il PDF

Microsoft SQL Server 2005 si integra con .NET Common Language Runtime per consentire agli sviluppatori di creare oggetti di database tramite codice gestito. Questa esercitazione illustra come creare stored procedure gestite e funzioni definite dall'utente gestite con il codice Visual Basic o C#. Si noterà anche come queste edizioni di Visual Studio consentono di eseguire il debug di tali oggetti di database gestiti.

Introduzione

I database come Microsoft SQL Server 2005 usano Transact-Structured Query Language (T-SQL) per l'inserimento, la modifica e il recupero dei dati. La maggior parte dei sistemi di database include costrutti per raggruppare una serie di istruzioni SQL che possono quindi essere eseguite come singola unità riutilizzabile. Le stored procedure sono un esempio. Un altro è Funzioni definite dall'utente(UDFs), un costrutto che verrà esaminato in modo più dettagliato nel passaggio 9.

Al suo centro, SQL è progettato per l'uso di set di dati. Le SELECTistruzioni , UPDATEe DELETE si applicano in modo intrinseco a tutti i record della tabella corrispondente e sono limitate solo dalle clausole.WHERE Esistono tuttavia molte funzionalità del linguaggio progettate per l'uso di un record alla volta e per la modifica dei dati scalari. CURSOR s consente di eseguire il ciclo di un set di record attraverso uno alla volta. Funzioni di manipolazione delle stringhe come LEFT, CHARINDEXe PATINDEX funzionano con i dati scalari. SQL include anche istruzioni del flusso di controllo come IF e WHILE.

Prima di Microsoft SQL Server 2005, le stored procedure e le storedfs potrebbero essere definite solo come una raccolta di istruzioni T-SQL. SQL Server 2005, tuttavia, è stato progettato per fornire l'integrazione con Common Language Runtime (CLR), ovvero il runtime usato da tutti gli assembly .NET. Di conseguenza, è possibile creare stored procedure e UDF in un database SQL Server 2005 usando il codice gestito. Vale a dire, è possibile creare una stored procedure o UDF come metodo in una classe C#. In questo modo queste stored procedure e UDFs possono usare funzionalità in .NET Framework e dalle proprie classi personalizzate.

In questa esercitazione verrà illustrato come creare stored procedure gestite e User-Defined Funzioni e come integrarli nel database Northwind. Iniziamo!

Nota

Gli oggetti di database gestiti offrono alcuni vantaggi rispetto alle controparti SQL. La ricchezza del linguaggio e la familiarità e la capacità di riutilizzare il codice e la logica esistenti sono i vantaggi principali. Tuttavia, gli oggetti di database gestiti sono probabilmente meno efficienti quando si riguardano set di dati che non comportano una logica procedurale. Per una discussione più approfondita sui vantaggi dell'uso del codice gestito rispetto a T-SQL, vedere i vantaggi dell'uso del codice gestito per creare oggetti di database.

Passaggio 1: Spostamento del database Northwind fuori App_Data

Tutte le esercitazioni finora hanno usato un file di database di Microsoft SQL Server 2005 Express Edition nella cartella dell'applicazione App_Data Web. Inserimento del database nella App_Data distribuzione semplificata ed esecuzione di queste esercitazioni come tutti i file si trovano all'interno di una directory e non sono necessari passaggi di configurazione aggiuntivi per testare l'esercitazione.

Per questa esercitazione, tuttavia, consente di spostare il database Northwind fuori App_Data e registrarlo in modo esplicito con l'istanza del database SQL Server 2005 Express Edition. Anche se è possibile eseguire i passaggi per questa esercitazione con il database nella App_Data cartella, alcuni passaggi sono molto più semplici registrando in modo esplicito il database con l'istanza del database SQL Server 2005 Express Edition.

Il download per questa esercitazione include i due file di database e NORTHWND.MDFNORTHWND_log.LDF inseriti in una cartella denominata DataFiles. Se si segue insieme alla propria implementazione delle esercitazioni, chiudere Visual Studio e spostare i NORTHWND.MDF file e NORTHWND_log.LDF dalla cartella del App_Data sito Web a una cartella all'esterno del sito Web. Dopo aver spostato i file di database in un'altra cartella, è necessario registrare il database Northwind con l'istanza del database SQL Server 2005 Express Edition. Questa operazione può essere eseguita da SQL Server Management Studio. Se si dispone di un'edizione non Express di SQL Server 2005 installata nel computer, probabilmente è già installato Management Studio. Se nel computer si dispone solo di SQL Server 2005 Express Edition, scaricare e installare Microsoft SQL Server Management Studio.

Avviare SQL Server Management Studio. Come illustrato nella figura 1, Management Studio inizia chiedendo a quale server connettersi. Immettere localhost\SQLExpress per il nome del server, scegliere Autenticazione di Windows nell'elenco a discesa Autenticazione e fare clic su Connetti.

Figura 1: Connettersi all'istanza di database appropriata

Dopo la connessione, la finestra Esplora oggetti elenca le informazioni sull'istanza del database SQL Server 2005 Express Edition, inclusi i database, le informazioni di sicurezza, le opzioni di gestione e così via.

È necessario collegare il database Northwind nella DataFiles cartella (o ovunque sia stato spostato) all'istanza del database SQL Server 2005 Express Edition. Fare clic con il pulsante destro del mouse sulla cartella Database e scegliere l'opzione Collega dal menu di scelta rapida. Verrà visualizzata la finestra di dialogo Collega database. Fare clic sul pulsante Aggiungi, eseguire il drill-down sul file appropriato NORTHWND.MDF e fare clic su OK. A questo punto la schermata dovrebbe essere simile alla figura 2.

Figura 2: Connettersi all'istanza di database appropriata (fare clic per visualizzare l'immagine full-size)

Nota

Quando ci si connette all'istanza di SQL Server 2005 Express Edition tramite Management Studio, la finestra di dialogo Collega database non consente di eseguire il drill-down nelle directory del profilo utente, ad esempio Documenti personali. Assicurarsi pertanto di inserire i NORTHWND.MDF file e NORTHWND_log.LDF in una directory del profilo non utente.

Fare clic sul pulsante OK per collegare il database. La finestra di dialogo Collega database chiuderà e la Esplora oggetti dovrebbe ora elencare il database appena collegato. Le probabilità che il database Northwind abbia un nome simile 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDFa . Rinominare il database in Northwind facendo clic con il pulsante destro del mouse sul database e scegliendo Rinomina.

Figura 3: Rinominare il database in Northwind

Passaggio 2: Creazione di una nuova soluzione e SQL Server progetto in Visual Studio

Per creare stored procedure gestite o UDFs in SQL Server 2005, verrà scritta la stored procedure e la logica UDF come codice C# in una classe. Dopo aver scritto il codice, sarà necessario compilare questa classe in un assembly (un .dll file), registrare l'assembly con il database SQL Server e quindi creare una stored procedure o un oggetto UDF nel database che punta al metodo corrispondente nell'assembly. Questi passaggi possono essere eseguiti manualmente. È possibile creare il codice in qualsiasi editor di testo, compilarlo dalla riga di comando usando il compilatore C# (csc.exe), registrarlo con il database usando il CREATE ASSEMBLY comando o da Management Studio e aggiungere la stored procedure o l'oggetto UDF tramite mezzi simili. Fortunatamente, le versioni Professional e Team Systems di Visual Studio includono un tipo di progetto SQL Server che automatizza queste attività. In questa esercitazione verrà illustrato l'uso del tipo di progetto SQL Server per creare una stored procedure gestita e una stored procedure gestita.

Nota

Se si usa Visual Web Developer o l'edizione Standard di Visual Studio, sarà necessario usare invece l'approccio manuale. Il passaggio 13 fornisce istruzioni dettagliate per eseguire manualmente questi passaggi. Ti consigliamo di leggere i passaggi da 2 a 12 prima di leggere il passaggio 13 perché questi passaggi includono istruzioni importanti di configurazione SQL Server che devono essere applicate indipendentemente dalla versione di Visual Studio in uso.

Iniziare aprendo Visual Studio. Dal menu File scegliere Nuovo progetto per visualizzare la finestra di dialogo Nuovo progetto (vedere Figura 4). Eseguire il drill-down al tipo di progetto database e quindi, dai modelli elencati a destra, scegliere di creare un nuovo progetto SQL Server. Ho scelto di assegnare un nome a questo progetto ManagedDatabaseConstructs e posizionarlo all'interno di una soluzione denominata Tutorial75.

Figura 4: Creare un nuovo progetto di SQL Server (fare clic per visualizzare l'immagine a dimensioni complete)

Fare clic sul pulsante OK nella finestra di dialogo Nuovo progetto per creare la soluzione e SQL Server Progetto.

Un SQL Server Progetto è associato a un database specifico. Di conseguenza, dopo aver creato il nuovo progetto SQL Server viene chiesto immediatamente di specificare queste informazioni. La figura 5 mostra la finestra di dialogo Nuovo riferimento database compilata per puntare al database Northwind registrato nell'istanza del database SQL Server 2005 Express Edition nel passaggio 1.

Figura 5: Associare il progetto di SQL Server al database Northwind

Per eseguire il debug delle stored procedure gestite e delle storedfs che verranno create all'interno di questo progetto, è necessario abilitare il supporto del debug SQL/CLR per la connessione. Ogni volta che si associa un SQL Server Project a un nuovo database (come illustrato nella figura 5), Visual Studio chiede se si vuole abilitare il debug SQL/CLR nella connessione (vedere la figura 6). Fare clic su Sì.

Figura 6: Abilitare il debug SQL/CLR

A questo punto il nuovo progetto SQL Server è stato aggiunto alla soluzione. Contiene una cartella denominata con un file denominato Test ScriptsTest.sql, usato per il debug degli oggetti di database gestiti creati nel progetto. Verrà esaminato il debug nel passaggio 12.

È ora possibile aggiungere nuove stored procedure gestite e UDFs a questo progetto, ma prima di consentire di includere prima l'applicazione Web esistente nella soluzione. Dal menu File selezionare l'opzione Aggiungi e scegliere Sito Web esistente. Passare alla cartella del sito Web appropriata e fare clic su OK. Come illustrato nella figura 7, la soluzione verrà aggiornata in modo da includere due progetti: il sito Web e il ManagedDatabaseConstructs SQL Server Project.

Figura 7: Il Esplora soluzioni include ora due progetti

Il NORTHWNDConnectionString valore in Web.config fa riferimento al NORTHWND.MDF file nella App_Data cartella . Poiché il database è stato rimosso e App_Data registrato in modo esplicito nell'istanza del database di SQL Server 2005 Express Edition, è necessario aggiornare il NORTHWNDConnectionString valore in modo corrispondente. Aprire il Web.config file nel sito Web e modificare il NORTHWNDConnectionString valore in modo che il stringa di connessione legga: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. Dopo questa modifica, la <connectionStrings> sezione in Web.config dovrebbe essere simile alla seguente:

<connectionStrings>
    <add name="NORTHWNDConnectionString" connectionString=
        "Data Source=localhost\SQLExpress;Initial Catalog=Northwind;
            Integrated Security=True;Pooling=false"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Nota

Come illustrato nell'esercitazione precedente, quando si esegue il debug di un oggetto SQL Server da un'applicazione client, ad esempio un sito Web ASP.NET, è necessario disabilitare il pool di connessioni. Il stringa di connessione illustrato in precedenza disabilita il pool di connessioni ( Pooling=false ). Se non si prevede di eseguire il debug delle stored procedure gestite e delle funzioni definite dall'utente dal sito Web di ASP.NET, abilitare il pool di connessioni.

Passaggio 3: Creazione di una stored procedure gestita

Per aggiungere una stored procedure gestita al database Northwind, è prima necessario creare la stored procedure come metodo nel SQL Server Project. Dal Esplora soluzioni fare clic con il pulsante destro del mouse sul nome del ManagedDatabaseConstructs progetto e scegliere di aggiungere un nuovo elemento. Verrà visualizzata la finestra di dialogo Aggiungi nuovo elemento, che elenca i tipi di oggetti di database gestiti che possono essere aggiunti al progetto. Come illustrato nella figura 8, sono incluse stored procedure e funzioni di User-Defined, tra le altre.

Per iniziare, aggiungere una stored procedure che restituisce semplicemente tutti i prodotti che sono stati interrotti. Assegnare al nuovo file GetDiscontinuedProducts.csdella stored procedure il nome .

Figura 8: Aggiungere una nuova stored procedure denominata GetDiscontinuedProducts.cs (fare clic per visualizzare un'immagine di dimensioni intere)

Verrà creato un nuovo file di classe C# con il contenuto seguente:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetDiscontinuedProducts()
    {
        // Put your code here
    }
};

Si noti che la stored procedure viene implementata come static metodo all'interno di un partial file di classe denominato StoredProcedures. Inoltre, il GetDiscontinuedProducts metodo viene decorato con SqlProcedure attribute, che contrassegna il metodo come stored procedure.

Nel codice seguente viene creato un SqlCommand oggetto e viene CommandText impostato su una SELECT query che restituisce tutte le colonne della Products tabella per i prodotti il cui Discontinued campo è uguale a 1. Esegue quindi il comando e invia i risultati all'applicazione client. Aggiungere questo codice al GetDiscontinuedProducts metodo .

// Create the command
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = 
      @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
               QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
               ReorderLevel, Discontinued
        FROM Products 
        WHERE Discontinued = 1";
// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);

Tutti gli oggetti di database gestiti hanno accesso a un SqlContext oggetto che rappresenta il contesto del chiamante. fornisce SqlContext l'accesso a un SqlPipe oggetto tramite la relativa Pipe proprietà . Questo SqlPipe oggetto viene utilizzato per traghettare le informazioni tra il database SQL Server e l'applicazione chiamante. Come suggerisce il nome, il ExecuteAndSend metodo esegue un oggetto passato SqlCommand e invia i risultati all'applicazione client.

Nota

Gli oggetti di database gestiti sono più adatti per stored procedure e funzioni definite dall'utente che usano la logica procedurale anziché la logica basata su set. La logica procedurale implica l'uso di set di dati su base riga per riga o l'uso di dati scalari. Il GetDiscontinuedProducts metodo appena creato, tuttavia, non implica logica procedurale. Pertanto, sarebbe idealmente implementato come stored procedure T-SQL. Viene implementato come stored procedure gestita per illustrare i passaggi necessari per la creazione e la distribuzione di stored procedure gestite.

Passaggio 4: Distribuzione della stored procedure gestita

Al termine di questo codice, è possibile distribuirlo nel database Northwind. La distribuzione di un SQL Server Progetto compila il codice in un assembly, registra l'assembly con il database e crea gli oggetti corrispondenti nel database, collegandoli ai metodi appropriati nell'assembly. L'esatto set di attività eseguite dall'opzione Distribuisci è più precisamente scritto nel passaggio 13. Fare clic con il pulsante destro del mouse sul nome del ManagedDatabaseConstructs progetto nel Esplora soluzioni e scegliere l'opzione Distribuisci. Tuttavia, la distribuzione ha esito negativo con l'errore seguente: sintassi non corretta accanto a 'EXTERNAL'. Per abilitare tale caratteristica può essere necessario impostare su un valore superiore il livello di compatibilità del database corrente. Vedere la Guida per la stored procedure sp_dbcmptlevel.

Questo messaggio di errore si verifica quando si tenta di registrare l'assembly con il database Northwind. Per registrare un assembly con un database SQL Server 2005, il livello di compatibilità del database deve essere impostato su 90. Per impostazione predefinita, i nuovi database SQL Server 2005 hanno un livello di compatibilità pari a 90. Tuttavia, i database creati con Microsoft SQL Server 2000 hanno un livello di compatibilità predefinito pari a 80. Poiché il database Northwind è stato inizialmente un database di Microsoft SQL Server 2000, il livello di compatibilità è attualmente impostato su 80 e pertanto deve essere aumentato a 90 per registrare oggetti di database gestiti.

Per aggiornare il livello di compatibilità del database, aprire una finestra Nuova query in Management Studio e immettere:

exec sp_dbcmptlevel 'Northwind', 90

Fare clic sull'icona Esegui nella barra degli strumenti per eseguire la query precedente.

Figura 9: Aggiornare il livello di compatibilità del database Northwind (fare clic per visualizzare l'immagine a dimensione intera)

Dopo aver aggiornato il livello di compatibilità, ridistribuire il progetto SQL Server. Questa volta la distribuzione deve essere completata senza errori.

Tornare a SQL Server Management Studio, fare clic con il pulsante destro del mouse sul database Northwind nel Esplora oggetti e scegliere Aggiorna. Eseguire quindi il drill-down nella cartella Programmabilità e quindi espandere la cartella Assembly. Come illustrato nella figura 10, il database Northwind include ora l'assembly generato dal ManagedDatabaseConstructs progetto.

Figura 10: L'assembly ManagedDatabaseConstructs è ora registrato con il database Northwind

Espandere anche la cartella Stored procedure. Verrà visualizzata una stored procedure denominata GetDiscontinuedProducts. Questa stored procedure è stata creata dal processo di distribuzione e punta al GetDiscontinuedProducts metodo nell'assembly ManagedDatabaseConstructs . Quando la GetDiscontinuedProducts stored procedure viene eseguita, a sua volta, esegue il GetDiscontinuedProducts metodo . Poiché si tratta di una stored procedure gestita, non può essere modificata tramite Management Studio (quindi l'icona di blocco accanto al nome della stored procedure).

Figura 11: La GetDiscontinuedProducts stored procedure è elencata nella cartella stored procedure

È ancora necessario superare un altro ostacolo prima di poter chiamare la stored procedure gestita: il database è configurato per impedire l'esecuzione del codice gestito. Verificarlo aprendo una nuova finestra di query ed eseguendo la GetDiscontinuedProducts stored procedure. Verrà visualizzato il messaggio di errore seguente: L'esecuzione del codice utente in .NET Framework è disabilitata. Abilitare l'opzione di configurazione 'clr enabled.

Per esaminare le informazioni di configurazione del database Northwind, immettere ed eseguire il comando exec sp_configure nella finestra di query. Ciò mostra che l'impostazione clr enabled è attualmente impostata su 0.

Figura 12: L'impostazione clr abilitata è attualmente impostata su 0 (fare clic per visualizzare l'immagine a dimensione intera)

Si noti che ogni impostazione di configurazione nella figura 12 include quattro valori elencati: i valori minimo e massimo e i valori di configurazione ed esecuzione. Per aggiornare il valore di configurazione per l'impostazione clr enabled, eseguire il comando seguente:

exec sp_configure 'clr enabled', 1

Se si esegue nuovamente , exec sp_configure si noterà che l'istruzione precedente ha aggiornato il valore di configurazione dell'impostazione clr enabled su 1, ma che il valore di esecuzione è ancora impostato su 0. Affinché questa modifica di configurazione influisca, è necessario eseguire il RECONFIGURE comando , che imposterà il valore di esecuzione sul valore di configurazione corrente. È sufficiente immettere RECONFIGURE nella finestra della query e fare clic sull'icona Esegui nella barra degli strumenti. Se si esegue exec sp_configure ora verrà visualizzato il valore 1 per l'impostazione clr enabled s config e i valori di esecuzione.

Al termine della configurazione abilitata per clr, è possibile eseguire la stored procedure gestita GetDiscontinuedProducts . Nella finestra di query immettere ed eseguire il comando execGetDiscontinuedProducts. Richiamando la stored procedure, il codice gestito corrispondente nel GetDiscontinuedProducts metodo viene eseguito. Questo codice esegue una SELECT query per restituire tutti i prodotti che non sono più disponibili e restituisce questi dati all'applicazione chiamante, SQL Server Management Studio in questa istanza. Management Studio riceve questi risultati e li visualizza nella finestra Risultati.

Figura 13: La GetDiscontinuedProducts stored procedure restituisce tutti i prodotti non più utilizzati (fare clic per visualizzare l'immagine a dimensione intera)

Passaggio 5: Creazione di stored procedure gestite che accettano parametri di input

Molte delle query e delle stored procedure create in queste esercitazioni hanno usato parametri. Nell'esercitazione Creazione di nuove stored procedure per l'esercitazione TableAdapters di DataSet tipizzato è stata creata una stored procedure denominata GetProductsByCategoryID che ha accettato un parametro di input denominato @CategoryID. La stored procedure ha quindi restituito tutti i prodotti il cui CategoryID campo corrisponde al valore del parametro fornito @CategoryID .

Per creare una stored procedure gestita che accetta parametri di input, è sufficiente specificare tali parametri nella definizione del metodo. Per illustrare questo problema, è possibile aggiungere un'altra stored procedure gestita al ManagedDatabaseConstructs progetto denominato GetProductsWithPriceLessThan. Questa stored procedure gestita accetterà un parametro di input che specifica un prezzo e restituirà tutti i prodotti il cui UnitPrice campo è minore del valore del parametro.

Per aggiungere una nuova stored procedure al progetto, fare clic con il pulsante destro del mouse sul nome del ManagedDatabaseConstructs progetto e scegliere di aggiungere una nuova stored procedure. Assegnare al file il nome GetProductsWithPriceLessThan.cs. Come illustrato nel passaggio 3, verrà creato un nuovo file di classe C# con un metodo denominato GetProductsWithPriceLessThan inserito all'interno della partial classe StoredProcedures.

Aggiornare la GetProductsWithPriceLessThan definizione del metodo in modo che accetti un SqlMoney parametro di input denominato price e scrivi il codice per eseguire e restituire i risultati della query:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductsWithPriceLessThan(SqlMoney price)
{
    // Create the command
    SqlCommand myCommand = new SqlCommand();
    myCommand.CommandText =
          @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
                   QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
                   ReorderLevel, Discontinued
            FROM Products
            WHERE UnitPrice < @MaxPrice";
    myCommand.Parameters.AddWithValue("@MaxPrice", price);
    // Execute the command and send back the results
    SqlContext.Pipe.ExecuteAndSend(myCommand);
}

La GetProductsWithPriceLessThan definizione e il codice del metodo sono simili alla definizione e al codice del GetDiscontinuedProducts metodo creato nel passaggio 3. Le uniche differenze sono che il GetProductsWithPriceLessThan metodo accetta come parametro di input (), la SqlCommand query di s include un parametro (price@MaxPrice) e un parametro viene aggiunto alla SqlCommand raccolta s Parameters e assegnato il valore della price variabile.

Dopo aver aggiunto questo codice, ridistribuire il progetto SQL Server. Tornare quindi a SQL Server Management Studio e aggiornare la cartella Stored Procedure. Verrà visualizzata una nuova voce, GetProductsWithPriceLessThan. Da una finestra di query immettere ed eseguire il comando exec GetProductsWithPriceLessThan 25, che elenca tutti i prodotti meno di $25, come illustrato nella figura 14.

Figura 14: i prodotti con $25 vengono visualizzati (Fare clic per visualizzare l'immagine a dimensioni complete)

Passaggio 6: Chiamata della stored procedure gestita dal livello di accesso ai dati

A questo punto sono state aggiunte le GetDiscontinuedProducts stored procedure gestite e GetProductsWithPriceLessThan gestite al ManagedDatabaseConstructs progetto e le sono state registrate con il database Northwind SQL Server. Sono state richiamate anche queste stored procedure gestite da SQL Server Management Studio (vedere la figura 13 e 14). Per consentire all'applicazione di ASP.NET di usare queste stored procedure gestite, è tuttavia necessario aggiungerle ai livelli di accesso ai dati e alla logica di business nell'architettura. In questo passaggio verranno aggiunti due nuovi metodi all'interno ProductsTableAdapter dell'oggetto NorthwindWithSprocs DataSet tipizzato, creato inizialmente nell'esercitazione Creazione di nuove stored procedure per l'esercitazione TableAdapters di DataSet tipizzato . Nel passaggio 7 verranno aggiunti metodi corrispondenti al BLL.

NorthwindWithSprocs Aprire Typed DataSet in Visual Studio e iniziare aggiungendo un nuovo metodo all'oggetto ProductsTableAdapter denominato GetDiscontinuedProducts. Per aggiungere un nuovo metodo a tableAdapter, fare clic con il pulsante destro del mouse sul nome di TableAdapter nel Designer e scegliere l'opzione Aggiungi query dal menu di scelta rapida.

Nota

Poiché è stato spostato il database Northwind dalla cartella all'istanza App_Data del database SQL Server 2005 Express Edition, è imperativo che il stringa di connessione corrispondente in Web.config essere aggiornato per riflettere questa modifica. Nel passaggio 2 è stato illustrato l'aggiornamento del NORTHWNDConnectionString valore in Web.config. Se si è dimenticato di eseguire questo aggiornamento, verrà visualizzato il messaggio di errore Non è stato possibile aggiungere query. Impossibile trovare la connessione NORTHWNDConnectionString per l'oggetto Web.config in una finestra di dialogo quando si tenta di aggiungere un nuovo metodo all'oggetto TableAdapter. Per risolvere questo errore, fare clic su OK e quindi passare a Web.config e aggiornare il NORTHWNDConnectionString valore come illustrato nel passaggio 2. Provare quindi a aggiungere nuovamente il metodo all'oggetto TableAdapter. Questa volta deve funzionare senza errori.

L'aggiunta di un nuovo metodo avvia la procedura guidata Di configurazione query TableAdapter, usata molte volte nelle esercitazioni precedenti. Il primo passaggio chiede di specificare come TableAdapter deve accedere al database: tramite un'istruzione SQL ad hoc o tramite una stored procedure nuova o esistente. Poiché è già stata creata e registrata la stored procedure gestita con il GetDiscontinuedProducts database, scegliere l'opzione Usa stored procedure esistente e premere Avanti.

Figura 15: scegliere l'opzione Usa stored procedure esistente (Fare clic per visualizzare l'immagine a dimensioni complete)

La schermata successiva richiede di richiamare il metodo nella stored procedure. Scegliere la stored procedure gestita dall'elenco GetDiscontinuedProducts a discesa e premere Avanti.

Figura 16: Selezionare la stored procedure gestita (fare clic per visualizzare l'immagineGetDiscontinuedProducts full-size)

Viene quindi chiesto di specificare se la stored procedure restituisce righe, un singolo valore o niente. Poiché GetDiscontinuedProducts restituisce il set di righe di prodotto interrotte, scegliere la prima opzione (dati tabulari) e fare clic su Avanti.

Figura 17: Selezionare l'opzione Dati tabulari (Fare clic per visualizzare l'immagine a dimensioni complete)

La schermata finale della procedura guidata consente di specificare i modelli di accesso ai dati usati e i nomi dei metodi risultanti. Lasciare selezionate entrambe le caselle di controllo e denominare i metodi FillByDiscontinued e GetDiscontinuedProducts. Fare clic su Fine per completare la procedura guidata.

Figura 18: Assegnare un nome ai metodi FillByDiscontinued e GetDiscontinuedProducts (Fare clic per visualizzare l'immagine full-size)

Ripetere questi passaggi per creare metodi denominati FillByPriceLessThan e GetProductsWithPriceLessThan nella ProductsTableAdapterGetProductsWithPriceLessThan stored procedure gestita.

La figura 19 mostra uno screenshot del Designer DataSet dopo l'aggiunta dei metodi alle ProductsTableAdapterGetDiscontinuedProducts stored procedure e GetProductsWithPriceLessThan gestite.

Figura 19: include ProductsTableAdapter i nuovi metodi aggiunti in questo passaggio (fare clic per visualizzare l'immagine full-size)

Passaggio 7: Aggiunta di metodi corrispondenti al livello di logica di business

Dopo aver aggiornato il livello di accesso ai dati per includere metodi per chiamare le stored procedure gestite aggiunte in Passaggi 4 e 5, è necessario aggiungere metodi corrispondenti al livello di logica di business. Aggiungere i due metodi seguenti alla ProductsBLLWithSprocs classe:

[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetDiscontinuedProducts()
{
    return Adapter.GetDiscontinuedProducts();
}
[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable 
    GetProductsWithPriceLessThan(decimal priceLessThan)
{
    return Adapter.GetProductsWithPriceLessThan(priceLessThan);
}

Entrambi i metodi chiamano semplicemente il metodo DAL corrispondente e restituiscono l'istanza ProductsDataTable . Il DataObjectMethodAttribute markup precedente a ogni metodo causa l'inserimento di questi metodi nell'elenco a discesa nella scheda SELECT della procedura guidata Configura origine dati di ObjectDataSource.

Passaggio 8: richiamare le stored procedure gestite dal livello di presentazione

Con i livelli di accesso ai dati e logica di business aumentata per includere il supporto per chiamare le GetDiscontinuedProducts stored procedure gestite e GetProductsWithPriceLessThan , ora, è possibile visualizzare i risultati di queste stored procedure tramite una pagina ASP.NET.

Aprire la ManagedFunctionsAndSprocs.aspx pagina nella AdvancedDAL cartella e, dalla casella degli strumenti, trascinare GridView nella Designer. Impostare la proprietà GridView su ID e, dal relativo smart tag, associarla a un nuovo OggettoDataSource denominato DiscontinuedProductsDataSource.DiscontinuedProducts Configurare ObjectDataSource per eseguire il pull dei dati dal ProductsBLLWithSprocs metodo della GetDiscontinuedProducts classe.

Figura 20: Configurare ObjectDataSource per usare la classe (fare clic per visualizzare l'immagineProductsBLLWithSprocs full-size)

Figura 21: scegliere il metodo dall'elenco Drop-Down nella scheda SELECT (fare clic per visualizzare l'immagineGetDiscontinuedProducts a dimensioni complete)

Poiché questa griglia verrà usata per visualizzare solo le informazioni sul prodotto, impostare gli elenchi a discesa nelle schede UPDATE, INSERT e DELETE su (Nessuno) e quindi fare clic su Fine.

Al termine della procedura guidata, Visual Studio aggiungerà automaticamente un oggetto BoundField o CheckBoxField per ogni campo dati in ProductsDataTable. È necessario rimuovere tutti questi campi, ad eccezione ProductName di e Discontinued, a questo punto il markup dichiarativo GridView e ObjectDataSource dovrebbe essere simile al seguente:

<asp:GridView ID="DiscontinuedProducts" runat="server" 
    AutoGenerateColumns="False" DataKeyNames="ProductID" 
    DataSourceID="DiscontinuedProductsDataSource">
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
            SortExpression="ProductName" />
        <asp:CheckBoxField DataField="Discontinued" 
            HeaderText="Discontinued" 
            SortExpression="Discontinued" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="DiscontinuedProductsDataSource" runat="server" 
    OldValuesParameterFormatString="original_{0}"
    SelectMethod="GetDiscontinuedProducts" TypeName="ProductsBLLWithSprocs">
</asp:ObjectDataSource>

Prendere un momento per visualizzare questa pagina tramite un browser. Quando viene visitata la pagina, ObjectDataSource chiama il ProductsBLLWithSprocs metodo della classe.GetDiscontinuedProducts Come illustrato nel passaggio 7, questo metodo chiama il metodo al metodo della classe S GetDiscontinuedProducts di ProductsDataTable DAL, che richiama la GetDiscontinuedProducts stored procedure. Questa stored procedure è una stored procedure gestita ed esegue il codice creato nel passaggio 3, restituendo i prodotti interrotti.

I risultati restituiti dalla stored procedure gestita vengono inseriti in pacchetto in un ProductsDataTable oggetto DAL e quindi restituiti al BLL, che quindi li restituisce al livello presentazione in cui sono associati a GridView e visualizzati. Come previsto, la griglia elenca i prodotti che sono stati interrotti.

Figura 22: i prodotti interrotti sono elencati (fare clic per visualizzare l'immagine a dimensioni complete)

Per altre procedure, aggiungere un controllo TextBox e un altro GridView alla pagina. Se gridView visualizza i prodotti meno della quantità immessa in TextBox chiamando il ProductsBLLWithSprocs metodo della GetProductsWithPriceLessThan classe.

Passaggio 9: Creazione e chiamata di UUDF T-SQL

User-Defined Funzioni o UDFs sono oggetti di database che simulano strettamente la semantica delle funzioni nei linguaggi di programmazione. Analogamente a una funzione in C#, le funzioni definite dall'utente possono includere un numero variabile di parametri di input e restituire un valore di un tipo specifico. Una funzione UDF può restituire dati scalari, una stringa, un numero intero e così via, o dati tabulari. Si esaminino rapidamente entrambi i tipi di UDFs, a partire da una funzione UDF che restituisce un tipo di dati scalare.

La funzione UDF seguente calcola il valore stimato dell'inventario per un determinato prodotto. Lo fa prendendo in tre parametri di input , i UnitPricevalori , UnitsInStocke Discontinued per un determinato prodotto, e restituisce un valore di tipo money. Calcola il valore stimato dell'inventario moltiplicando l'oggetto in base a UnitPriceUnitsInStock. Per gli elementi interrotti, questo valore viene dimezzato.

CREATE FUNCTION udf_ComputeInventoryValue
(
    @UnitPrice money,
    @UnitsInStock smallint,
    @Discontinued bit
)
RETURNS money
AS
BEGIN
    DECLARE @Value decimal
    SET @Value = ISNULL(@UnitPrice, 0) * ISNULL(@UnitsInStock, 0)
    IF @Discontinued = 1
        SET @Value = @Value * 0.5
    
    RETURN @Value
END

Dopo aver aggiunto questa funzione UDF al database, è possibile trovare tramite Management Studio espandendo la cartella Programmabilità, quindi Funzioni e quindi Funzioni scalari. Può essere usato in una SELECT query come in questo modo:

SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue
    (UnitPrice, UnitsInStock, Discontinued) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

Ho aggiunto la udf_ComputeInventoryValue funzione UDF al database Northwind; La figura 23 mostra l'output della query precedente SELECT quando visualizzato tramite Management Studio. Si noti anche che la funzione definita dall'utente è elencata nella cartella Funzioni scalari nel Esplora oggetti.

Figura 23: i valori di inventario di ogni prodotto sono elencati (fare clic per visualizzare l'immagine full-size)

Le UDF possono anche restituire dati tabulari. Ad esempio, è possibile creare una funzione UDF che restituisce i prodotti appartenenti a una categoria specifica:

CREATE FUNCTION dbo.udf_GetProductsByCategoryID
(    
    @CategoryID int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ProductID, ProductName, SupplierID, CategoryID, 
           QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
           ReorderLevel, Discontinued
    FROM Products
    WHERE CategoryID = @CategoryID
)

La udf_GetProductsByCategoryID funzione UDF accetta un @CategoryID parametro di input e restituisce i risultati della query specificata SELECT . Dopo la creazione, questa funzione definita dall'utente FROM può essere fatto riferimento nella clausola (o JOIN) di una SELECT query. Nell'esempio seguente verranno restituiti i ProductIDvalori , ProductNamee CategoryID per ognuna delle bevande.

SELECT ProductID, ProductName, CategoryID
FROM dbo.udf_GetProductsByCategoryID(1)

Ho aggiunto la udf_GetProductsByCategoryID funzione UDF al database Northwind; La figura 24 mostra l'output della query precedente SELECT quando visualizzato tramite Management Studio. Le UDFS che restituiscono dati tabulari sono disponibili nella cartella Funzioni con valori di tabella Esplora oggetti.

Figura 24: l'oggetto ProductID, ProductNamee CategoryID sono elencati per ogni bevanda (fare clic per visualizzare l'immagine full-size)

Nota

Per altre informazioni sulla creazione e sull'uso delle funzioni definite dall'utente, vedere Introduzione alle funzioni di User-Defined. Vedere anche vantaggi e svantaggi delle funzioni di User-Defined.

Passaggio 10: Creazione di una funzione utente gestita

Le udf_ComputeInventoryValue funzioni definite dall'utente e udf_GetProductsByCategoryID create negli esempi precedenti sono oggetti di database T-SQL. SQL Server 2005 supporta anche funzioni definite dall'utente gestite, che possono essere aggiunte al ManagedDatabaseConstructs progetto esattamente come le stored procedure gestite dei passaggi 3 e 5. Per questo passaggio, è possibile implementare la funzione definita dall'utente udf_ComputeInventoryValue nel codice gestito.

Per aggiungere una funzione definita dall'utente gestita al ManagedDatabaseConstructs progetto, fare clic con il pulsante destro del mouse sul nome del progetto in Esplora soluzioni e scegliere Aggiungi un nuovo elemento. Selezionare il modello di User-Defined nella finestra di dialogo Aggiungi nuovo elemento e assegnare al nuovo file udf_ComputeInventoryValue_Managed.csUDF il nome .

Figura 25: Aggiungere una nuova funzione definita dall'utente gestita al ManagedDatabaseConstructs progetto (fare clic per visualizzare l'immagine a dimensione intera)

Il modello funzione User-Defined crea una partial classe denominata UserDefinedFunctions con un metodo il cui nome corrisponde al nome del file di classe (udf_ComputeInventoryValue_Managedin questa istanza). Questo metodo viene decorato usando l'attributo , che contrassegna il metodo come funzione definita dall'utente SqlFunctiongestita.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString udf_ComputeInventoryValue_Managed()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};

Il udf_ComputeInventoryValue metodo restituisce attualmente un SqlString oggetto e non accetta parametri di input. È necessario aggiornare la definizione del metodo in modo che accetti tre parametri di input, UnitPrice, UnitsInStocke Discontinued , e restituisca un SqlMoney oggetto . La logica per il calcolo del valore di inventario è identica a quella nella funzione definita dall'utente T-SQL udf_ComputeInventoryValue .

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlMoney udf_ComputeInventoryValue_Managed
    (SqlMoney UnitPrice, SqlInt16 UnitsInStock, SqlBoolean Discontinued)
{
    SqlMoney inventoryValue = 0;
    if (!UnitPrice.IsNull && !UnitsInStock.IsNull)
    {
        inventoryValue = UnitPrice * UnitsInStock;
        if (Discontinued == true)
            inventoryValue = inventoryValue * new SqlMoney(0.5);
    }
    return inventoryValue;
}

Si noti che i parametri di input del metodo UDF sono dei tipi SQL corrispondenti: SqlMoney per il UnitPrice campo , SqlInt16 per UnitsInStocke SqlBoolean per Discontinued. Questi tipi di dati riflettono i tipi definiti nella Products tabella: la UnitPrice colonna è di tipo money, la UnitsInStock colonna di tipo smallinte la Discontinued colonna di tipo bit.

Il codice inizia creando un'istanza SqlMoney denominata a inventoryValue cui viene assegnato un valore pari a 0. La Products tabella consente i valori di UnitsInPrice database NULL nelle colonne e UnitsInStock . È quindi necessario verificare prima di tutto se questi valori contengono NULL s, operazione eseguita tramite la proprietà dell'oggettoIsNullSqlMoney. Se entrambi UnitPrice e UnitsInStock contengono valori nonNULL , viene calcolato il inventoryValue valore in modo che sia il prodotto dei due. Quindi, se Discontinued è true, si metà del valore.

Nota

L'oggetto SqlMoney consente di moltiplicare solo due SqlMoney istanze. Non consente a un'istanza SqlMoney di essere moltiplicata per un numero a virgola mobile letterale. Pertanto, per dimezzarla inventoryValue viene moltiplicata per una nuova SqlMoney istanza con il valore 0,5.

Passaggio 11: Distribuzione della funzione definita dall'utente gestita

Ora che la funzione definita dall'utente gestita è stata creata, è possibile distribuirla nel database Northwind. Come illustrato nel passaggio 4, gli oggetti gestiti in un progetto di SQL Server vengono distribuiti facendo clic con il pulsante destro del mouse sul nome del progetto nel Esplora soluzioni e scegliendo l'opzione Distribuisci dal menu di scelta rapida.

Dopo aver distribuito il progetto, tornare a SQL Server Management Studio e aggiornare la cartella Funzioni con valori scalari. Verranno ora visualizzate due voci:

• dbo.udf_ComputeInventoryValue - La funzione definita dall'utente T-SQL creata nel passaggio 9 e
• dbo.udf ComputeInventoryValue_Managed - La funzione definita dall'utente gestita creata nel passaggio 10 appena distribuita.

Per testare questa funzione definita dall'utente gestita, eseguire la query seguente da Management Studio:

SELECT ProductID, ProductName, 
       dbo.udf_ComputeInventoryValue_Managed(
                 UnitPrice, 
                 UnitsInStock, 
                 Discontinued
              ) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

Questo comando usa la funzione definita dall'utente gestita udf ComputeInventoryValue_Managed anziché la funzione definita dall'utente T-SQL udf_ComputeInventoryValue , ma l'output è lo stesso. Fare riferimento alla figura 23 per visualizzare uno screenshot dell'output della funzione definita dall'utente.

Passaggio 12: Debug degli oggetti di database gestiti

Nell'esercitazione Debug di stored procedure sono stati illustrati i tre opzioni per il debug SQL Server tramite Visual Studio: debug diretto del database, debug dell'applicazione e debug da un progetto di SQL Server. Non è possibile eseguire il debug di oggetti di database gestiti tramite debug diretto del database, ma è possibile eseguire il debug da un'applicazione client e direttamente dal progetto SQL Server. Per consentire il funzionamento del debug, tuttavia, il database SQL Server 2005 deve consentire il debug di SQL/CLR. Tenere presente che quando è stato creato per la prima volta il ManagedDatabaseConstructs progetto Visual Studio ha chiesto se si vuole abilitare il debug di SQL/CLR (vedere la figura 6 nel passaggio 2). Questa impostazione può essere modificata facendo clic con il pulsante destro del mouse sul database dalla finestra Esplora server.

Figura 26: Assicurarsi che il database consenta il debug di SQL/CLR

Si supponga di voler eseguire il debug della GetProductsWithPriceLessThan stored procedure gestita. Per iniziare, impostare un punto di interruzione all'interno del codice del GetProductsWithPriceLessThan metodo .

Figura 27: Impostare un punto di interruzione nel GetProductsWithPriceLessThan metodo (fare clic per visualizzare l'immagine a dimensione intera)

Esaminare prima di tutto il debug degli oggetti di database gestiti dal progetto SQL Server. Poiché la soluzione include due progetti, il ManagedDatabaseConstructs progetto SQL Server insieme al sito Web, per eseguire il debug dal progetto SQL Server è necessario indicare a Visual Studio di avviare il ManagedDatabaseConstructs progetto SQL Server all'avvio del debug. Fare clic con il pulsante destro del mouse sul ManagedDatabaseConstructs progetto in Esplora soluzioni e scegliere l'opzione Imposta come progetto di avvio dal menu di scelta rapida.

Quando il ManagedDatabaseConstructs progetto viene avviato dal debugger, esegue le istruzioni SQL nel Test.sql file, che si trova nella Test Scripts cartella . Ad esempio, per testare la GetProductsWithPriceLessThan stored procedure gestita, sostituire il contenuto del file esistente Test.sql con l'istruzione seguente, che richiama la GetProductsWithPriceLessThan stored procedure gestita passando il @CategoryID valore 14.95:

exec GetProductsWithPriceLessThan 14.95

Dopo aver immesso lo script precedente in Test.sql, avviare il debug passando al menu Debug e scegliendo Avvia debug oppure premendo F5 o l'icona di riproduzione verde nella barra degli strumenti. Verranno compilati i progetti all'interno della soluzione, verranno distribuiti gli oggetti di database gestiti nel database Northwind e quindi lo script verrà eseguito Test.sql . A questo punto, il punto di interruzione verrà raggiunto ed è possibile esaminare GetProductsWithPriceLessThan i valori dei parametri di input e così via.

Figura 28: Il punto di interruzione nel GetProductsWithPriceLessThan metodo è stato raggiunto (fare clic per visualizzare l'immagine a dimensione intera)

Per eseguire il debug di un oggetto di database SQL tramite un'applicazione client, è fondamentale configurare il database per supportare il debug dell'applicazione. Fare clic con il pulsante destro del mouse sul database in Esplora server e verificare che l'opzione Debug applicazioni sia selezionata. Inoltre, è necessario configurare l'applicazione ASP.NET per l'integrazione con il debugger SQL e per disabilitare il pool di connessioni. Questi passaggi sono stati descritti in dettaglio nel passaggio 2 dell'esercitazione Debug di stored procedure .

Dopo aver configurato l'applicazione e il database ASP.NET, impostare il sito Web ASP.NET come progetto di avvio e avviare il debug. Se si visita una pagina che chiama uno degli oggetti gestiti con un punto di interruzione, l'applicazione verrà arrestata e il controllo verrà spostato nel debugger, in cui è possibile scorrere il codice come illustrato nella figura 28.

Passaggio 13: Compilazione e distribuzione manuale di oggetti di database gestiti

SQL Server Progetti semplificano la creazione, la compilazione e la distribuzione di oggetti di database gestiti. Sfortunatamente, SQL Server Progetti sono disponibili solo nelle edizioni Professional e Team Systems di Visual Studio. Se si usa Visual Web Developer o l'edizione Standard di Visual Studio e si vuole usare oggetti di database gestiti, sarà necessario crearli e distribuirli manualmente. Questo richiede quattro passaggi:

1. Creare un file contenente il codice sorgente per l'oggetto di database gestito.
2. Compilare l'oggetto in un assembly,
3. Registrare l'assembly con il database SQL Server 2005 e
4. Creare un oggetto di database in SQL Server che punta al metodo appropriato nell'assembly.

Per illustrare queste attività, è possibile creare una nuova stored procedure gestita che restituisce i prodotti il cui UnitPrice valore è maggiore di un valore specificato. Creare un nuovo file nel computer denominato GetProductsWithPriceGreaterThan.cs e immettere il codice seguente nel file (è possibile usare Visual Studio, Blocco note o qualsiasi editor di testo per eseguire questa operazione):

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetProductsWithPriceGreaterThan(SqlMoney price)
    {
        // Create the command
        SqlCommand myCommand = new SqlCommand();
        myCommand.CommandText =
            @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
                     QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
                     ReorderLevel, Discontinued
              FROM Products
              WHERE UnitPrice > @MinPrice";
        myCommand.Parameters.AddWithValue("@MinPrice", price);
        // Execute the command and send back the results
        SqlContext.Pipe.ExecuteAndSend(myCommand);
    }
};

Questo codice è quasi identico a quello del GetProductsWithPriceLessThan metodo creato nel passaggio 5. Le uniche differenze sono i nomi dei metodi, la WHERE clausola e il nome del parametro usato nella query. Tornare al GetProductsWithPriceLessThan metodo , la WHERE clausola read: WHERE UnitPrice < @MaxPrice. In , in GetProductsWithPriceGreaterThanviene usato : WHERE UnitPrice > @MinPrice .

È ora necessario compilare questa classe in un assembly. Dalla riga di comando passare alla directory in cui è stato salvato il GetProductsWithPriceGreaterThan.cs file e usare il compilatore C# (csc.exe) per compilare il file di classe in un assembly:

csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs

Se la cartella contenente csc.exe in non nel sistema è PATHnecessario fare riferimento completamente al relativo percorso, %WINDOWS%\Microsoft.NET\Framework\version\, come indicato di seguito:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs

Figura 29: Compilare in un assembly (fare clic per visualizzareGetProductsWithPriceGreaterThan.cs l'immagine a dimensione intera)

Il /t flag specifica che il file di classe C# deve essere compilato in una DLL (anziché in un eseguibile). Il /out flag specifica il nome dell'assembly risultante.

Nota

Anziché compilare il GetProductsWithPriceGreaterThan.cs file di classe dalla riga di comando, in alternativa è possibile usare Visual C# Express Edition o creare un progetto libreria di classi separato in Visual Studio Standard Edition. S ren Jacob Lauritsen ha fornito un progetto Visual C# Express Edition di questo tipo con codice per la GetProductsWithPriceGreaterThan stored procedure e le due stored procedure gestite e la funzione definita dall'utente creata nei passaggi 3, 5 e 10. Il progetto S ren include anche i comandi T-SQL necessari per aggiungere gli oggetti di database corrispondenti.

Con il codice compilato in un assembly, è possibile registrare l'assembly all'interno del database SQL Server 2005. Questa operazione può essere eseguita tramite T-SQL, usando il comando CREATE ASSEMBLYo tramite SQL Server Management Studio. È possibile concentrarsi sull'uso di Management Studio.

Da Management Studio espandere la cartella Programmabilità nel database Northwind. Una delle relative sottocartelle è Assembly. Per aggiungere manualmente un nuovo assembly al database, fare clic con il pulsante destro del mouse sulla cartella Assembly e scegliere Nuovo assembly dal menu di scelta rapida. Verrà visualizzata la finestra di dialogo Nuovo assembly (vedere la figura 30). Fare clic sul pulsante Sfoglia, selezionare l'assembly ManuallyCreatedDBObjects.dll appena compilato e quindi fare clic su OK per aggiungere l'assembly al database. L'assembly ManuallyCreatedDBObjects.dll non dovrebbe essere visualizzato nel Esplora oggetti.

Figura 30: Aggiungere l'assembly al database (fare clic per visualizzare l'immagineManuallyCreatedDBObjects.dll a dimensione intera)

Figura 31: l'oggetto ManuallyCreatedDBObjects.dll è elencato nel Esplora oggetti

Anche se l'assembly è stato aggiunto al database Northwind, è ancora necessario associare una stored procedure al GetProductsWithPriceGreaterThan metodo nell'assembly. A tale scopo, aprire una nuova finestra di query ed eseguire lo script seguente:

CREATE PROCEDURE [dbo].[GetProductsWithPriceGreaterThan] 
( 
    @price money 
) 
WITH EXECUTE AS CALLER 
AS 
EXTERNAL NAME [ManuallyCreatedDBObjects].[StoredProcedures].[GetProductsWithPriceGreaterThan] 
GO

Verrà creata una nuova stored procedure nel database Northwind denominata GetProductsWithPriceGreaterThan e associata al metodo GetProductsWithPriceGreaterThan gestito , che si trova nella classe StoredProcedures, che si trova nell'assembly ManuallyCreatedDBObjects.

Dopo aver eseguito lo script precedente, aggiornare la cartella Stored procedure nel Esplora oggetti. Verrà visualizzata una nuova voce della stored procedure, GetProductsWithPriceGreaterThan con un'icona di blocco accanto a essa. Per testare questa stored procedure, immettere ed eseguire lo script seguente nella finestra di query:

exec GetProductsWithPriceGreaterThan 24.95

Come illustrato nella figura 32, il comando precedente visualizza informazioni per tali prodotti con un UnitPrice valore maggiore di $24,95.

Figura 32: l'oggetto ManuallyCreatedDBObjects.dll è elencato nella Esplora oggetti (fare clic per visualizzare l'immagine a dimensione intera)

Riepilogo

Microsoft SQL Server 2005 offre l'integrazione con Common Language Runtime (CLR), che consente di creare oggetti di database usando codice gestito. In precedenza, questi oggetti di database potevano essere creati solo usando T-SQL, ma ora è possibile creare questi oggetti usando linguaggi di programmazione .NET come C#. In questa esercitazione sono state create due stored procedure gestite e una funzione di User-Defined gestita.

Visual Studio SQL Server Tipo di progetto facilita la creazione, la compilazione e la distribuzione di oggetti di database gestiti. Offre inoltre un supporto avanzato per il debug. Tuttavia, SQL Server tipi di progetto sono disponibili solo nelle edizioni Professional e Team Systems di Visual Studio. Per gli utenti che usano Visual Web Developer o l'edizione Standard di Visual Studio, la creazione, la compilazione e i passaggi di distribuzione devono essere eseguiti manualmente, come illustrato nel passaggio 13.

Buon programmatori!

Altre informazioni

Per altre informazioni sugli argomenti descritti in questa esercitazione, vedere le risorse seguenti:

• Vantaggi e svantaggi delle funzioni di User-Defined
• Creazione di oggetti SQL Server 2005 nel codice gestito
• Procedura: Creare ed eseguire una stored procedure CLR SQL Server
• Procedura: Creare ed eseguire una funzione clr SQL Server User-Defined
• Procedura: Modificare lo Test.sql script per eseguire oggetti SQL
• Introduzione alle funzioni definite dall'utente
• Codice gestito e SQL Server 2005 (Video)
• Informazioni di riferimento su Transact-SQL
• Procedura dettagliata: Creazione di una stored procedure nel codice gestito

Informazioni sull'autore

Scott Mitchell, autore di sette libri ASP/ASP.NET e fondatore di 4GuysFromRolla.com, lavora con le tecnologie Web Microsoft dal 1998. Scott lavora come consulente indipendente, formatore e scrittore. Il suo ultimo libro è Sams Teach Yourself ASP.NET 2.0 in 24 ore. Può essere raggiunto all'indirizzo mitchell@4GuysFromRolla.com. o tramite il suo blog, disponibile all'indirizzo http://ScottOnWriting.NET.

Grazie speciale a

Questa serie di esercitazioni è stata esaminata da molti revisori utili. Il revisore responsabile di questa esercitazione è stato S ren Jacob Lauritsen. Oltre a esaminare questo articolo, S ren ha creato anche il progetto Visual C# Express Edition incluso in questo articolo per la compilazione manuale degli oggetti di database gestiti. Si è interessati a esaminare i prossimi articoli MSDN? In tal caso, rilasciami una riga in mitchell@4GuysFromRolla.com.

IndietroAvanti