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

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 gestite definite dall'utente 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 il raggruppamento di 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(UDF), un costrutto che verrà esaminato in modo più dettagliato nel passaggio 9.

Sql è progettato per l'uso di set di dati. Le SELECTistruzioni , UPDATEe DELETE si applicano intrinsecamente a tutti i record nella tabella corrispondente e sono limitate solo dalle relative WHERE clausole. Esistono tuttavia molte funzionalità del linguaggio progettate per l'uso di un record alla volta e per la modifica dei dati scalari. CURSOR consente di eseguire un ciclo di un set di record uno alla volta. Le funzioni di manipolazione delle stringhe, ad esempio LEFT, CHARINDEXe PATINDEX funzionano con i dati scalari. SQL include anche istruzioni del flusso di controllo come IF e WHILE.

Nelle versioni precedenti a Microsoft SQL Server 2005, le stored procedure e le funzioni definite dall'utente possono 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, le stored procedure e le funzioni definite dall'utente in un database SQL Server 2005 possono essere create usando codice gestito. Ovvero, è possibile creare una stored procedure o una funzione definita dall'utente come metodo in una classe Visual Basic. Ciò consente a queste stored procedure e funzioni definite dall'utente di usare le funzionalità in .NET Framework e dalle classi personalizzate.

In questa esercitazione verrà illustrato come creare stored procedure gestite e User-Defined Funzioni e come integrarle 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 possibilità di riutilizzare il codice e la logica esistenti sono i principali vantaggi. Tuttavia, è probabile che gli oggetti di database gestiti siano meno efficienti quando si stiano usando set di dati che non implicano 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 all'esterno di 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, perché tutti i file si trovavano all'interno di una directory e non richiedevano passaggi di configurazione aggiuntivi per testare l'esercitazione.

Per questa esercitazione, tuttavia, è possibile spostare il database Northwind da 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, una serie di passaggi è resa molto più semplice 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 nel computer è installata una versione non Express Edition di SQL Server 2005, è probabile che Management Studio sia già installato. Se nel computer sono presenti solo 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 di Esplora oggetti elenca le informazioni sull'istanza del database SQL Server 2005 Express Edition, inclusi i relativi database, informazioni di sicurezza, 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 nel file appropriato NORTHWND.MDF e fare clic su OK. A questo punto lo schermo dovrebbe essere simile alla figura 2.

Figura 2: Connettersi all'istanza di database appropriata (fare clic per visualizzare l'immagine a dimensione intera)

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 dei profili utente, ad esempio Documenti personali. Assicurarsi quindi 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 verrà chiusa e il Esplora oggetti dovrebbe ora elencare il database appena collegato. È probabile che il database Northwind abbia un nome come 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF. 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 funzioni definite dall'utente in SQL Server 2005, la stored procedure e la logica definita dall'utente verranno scritte come codice Visual Basic 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 tutti manualmente. È possibile creare il codice in qualsiasi editor di testo, compilarlo dalla riga di comando usando il compilatore Visual Basic (vbc.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 come usare il tipo di progetto SQL Server per creare una stored procedure gestita e una funzione definita dall'utente.

Nota

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

Iniziare aprendo Visual Studio. Scegliere Nuovo progetto dal menu File per visualizzare la finestra di dialogo Nuovo progetto (vedere la figura 4). Eseguire il drill-down al tipo di progetto database e quindi scegliere di creare un nuovo progetto SQL Server dal modello elencato a destra. Ho scelto di denominare 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 dimensione intera)

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

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

Figura 5: Associare il progetto SQL Server al database Northwind

Per eseguire il debug delle stored procedure gestite e delle funzioni definite dall'utente che verranno create all'interno di questo progetto, è necessario abilitare il supporto per il debug di 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 di SQL/CLR nella connessione (vedere la figura 6). Fare clic su Sì.

Figura 6: Abilitare il debug di SQL/CLR

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

È ora possibile aggiungere nuove stored procedure gestite e funzioni definite dall'utente 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.vbdella stored procedure il nome .

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

Verrà creato un nuovo file di classe Visual Basic con il contenuto seguente:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub  GetDiscontinuedProducts ()
        ' Add your code here
    End Sub
End Class

Si noti che la stored procedure viene implementata come Shared metodo all'interno di un Partial file di classe denominato StoredProcedures. Inoltre, il GetDiscontinuedProducts metodo viene decorato con l'attributoSqlProcedure , 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
Dim myCommand As 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.vb. Come illustrato nel passaggio 3, verrà creato un nuovo file di classe di Visual Basic 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 Shared Sub GetProductsWithPriceLessThan(ByVal price As SqlMoney)
    'Create the command
    Dim myCommand As 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)
End Sub

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 (price), la SqlCommand query s include un parametro (@MaxPrice) e un parametro viene aggiunto alla SqlCommand raccolta di s Parameters e assegnato il valore della price variabile.

Dopo aver aggiunto questo codice, ridistribuire il SQL Server Project. 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 inferiori a $25, come illustrato nella figura 14.

Figura 14: Vengono visualizzati i prodotti sotto $25 (fare clic per visualizzare l'immagine a dimensione intera)

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. Queste stored procedure gestite sono state richiamate anche da SQL Server Management Studio (vedere le figure 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'oggetto ProductsTableAdapter nell'oggetto NorthwindWithSprocs DataSet tipizzato, creato inizialmente nell'esercitazione Creazione di nuove stored procedure per l'esercitazione TableAdapters di DataSet tipizzato . Nel passaggio 7 si aggiungeranno i metodi corrispondenti al BLL.

Aprire il NorthwindWithSprocs set di dati tipizzato in Visual Studio e iniziare aggiungendo un nuovo metodo all'oggetto ProductsTableAdapter denominato GetDiscontinuedProducts. Per aggiungere un nuovo metodo a un oggetto 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é il database Northwind è stato spostato dalla App_Data cartella all'istanza del database SQL Server 2005 Express Edition, è fondamentale aggiornare il stringa di connessione corrispondente in Web.config 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 la query. Impossibile trovare la connessione NORTHWNDConnectionString per l'oggetto Web.config in una finestra di dialogo quando si tenta di aggiungere un nuovo metodo a TableAdapter. Per risolvere questo errore, fare clic su OK e quindi passare a Web.config e aggiornare il NORTHWNDConnectionString valore come descritto nel passaggio 2. Provare quindi ad aggiungere nuovamente il metodo a TableAdapter. Questa volta dovrebbe funzionare senza errori.

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

Figura 15: Scegliere l'opzione Usa stored procedure esistente (fare clic per visualizzare l'immagine a dimensione intera)

La schermata successiva richiede la stored procedure che verrà richiamata dal metodo . Scegliere la GetDiscontinuedProducts stored procedure gestita dall'elenco a discesa e premere Avanti.

Figura 16: Selezionare la GetDiscontinuedProducts stored procedure gestita (fare clic per visualizzare l'immagine a dimensione intera)

Viene quindi chiesto di specificare se la stored procedure restituisce righe, un singolo valore o nulla. Poiché GetDiscontinuedProducts restituisce il set di righe di prodotto non più disponibili, 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 dimensione intera)

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 GetDiscontinuedProductsfare clic per visualizzare l'immagine a dimensione intera

Ripetere questi passaggi per creare metodi denominati FillByPriceLessThan e GetProductsWithPriceLessThan in ProductsTableAdapter per la GetProductsWithPriceLessThan stored procedure gestita.

La figura 19 mostra uno screenshot del dataset Designer dopo aver aggiunto i metodi a ProductsTableAdapter per le GetDiscontinuedProducts stored procedure gestite e GetProductsWithPriceLessThan .

Figura 19: ProductsTableAdapter Include i nuovi metodi aggiunti in questo passaggio (fare clic per visualizzare l'immagine a dimensione intera)

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

Ora che è stato aggiornato il livello di accesso ai dati per includere i metodi per chiamare le stored procedure gestite aggiunte nei passaggi 4 e 5, è necessario aggiungere i metodi corrispondenti al livello della logica di business. Aggiungere i due metodi seguenti alla ProductsBLLWithSprocs classe :

<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetDiscontinuedProducts() As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetDiscontinuedProducts()
End Function
<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsWithPriceLessThan(ByVal priceLessThan As Decimal) _
    As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetProductsWithPriceLessThan(priceLessThan)
End Function

Entrambi i metodi chiamano semplicemente il metodo DAL corrispondente e restituiscono l'istanza ProductsDataTable . Il DataObjectMethodAttribute markup precedente a ogni metodo determina 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 presentazione

Con i livelli di accesso ai dati e la logica di business aumentano 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 trascinare un controllo GridView nella Designer dalla casella degli strumenti. Impostare la proprietà gridView su ID e, dallo smart tag, associarla a un nuovo ObjectDataSource denominato DiscontinuedProductsDataSource.DiscontinuedProducts Configurare ObjectDataSource per eseguire il ProductsBLLWithSprocs pull dei dati dal metodo della GetDiscontinuedProducts classe .

Figura 20: Configurare ObjectDataSource per l'uso della classe (fare clic per visualizzare l'immagineProductsBLLWithSprocs a dimensione intera)

Figura 21: Scegliere il GetDiscontinuedProducts metodo dall'elenco Drop-Down nella scheda SELECT (fare clic per visualizzare l'immagine a dimensione intera)

Poiché questa griglia verrà usata solo per visualizzare 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 campo BoundField o CheckBoxField per ogni campo dati in ProductsDataTable. Rimuovere tutti questi campi ad eccezione ProductName di e Discontinued, a questo punto il markup dichiarativo di 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>

Dedicare qualche minuto alla visualizzazione di questa pagina tramite un browser. Quando viene visitata la pagina, ObjectDataSource chiama il ProductsBLLWithSprocs metodo della GetDiscontinuedProducts classe . Come illustrato nel passaggio 7, questo metodo chiama il metodo della classe GetDiscontinuedProducts DALProductsDataTable, che richiama la GetDiscontinuedProducts stored procedure. Questa stored procedure è una stored procedure gestita ed esegue il codice creato nel passaggio 3, restituendo i prodotti sospesi.

I risultati restituiti dalla stored procedure gestita vengono inseriti in un ProductsDataTable pacchetto da DAL e quindi restituiti al BLL, che 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 sospesi sono elencati (fare clic per visualizzare l'immagine a dimensione intera)

Per altre procedure, aggiungere un controllo TextBox e un altro controllo GridView alla pagina. Fare in modo che GridView visualizzi i prodotti inferiori alla quantità immessa nel controllo TextBox chiamando il ProductsBLLWithSprocs metodo della GetProductsWithPriceLessThan classe .

Passaggio 9: Creazione e chiamata di funzioni definite dall'utente T-SQL

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

La funzione definita dall'utente seguente calcola il valore stimato dell'inventario per un determinato prodotto. A tale scopo, accettare 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 per UnitPrice .UnitsInStock Per gli elementi sospesi, 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 l'aggiunta di questa funzione definita dall'utente al database, è possibile trovarla tramite Management Studio espandendo la cartella Programmabilità, quindi Funzioni e quindi Funzioni scalari.once this UDF has been added the database, it can be found through Management Studio by expanding the Programmability folder, then Functions, and then Scalar-value Functions. Può essere usato in una SELECT query come segue:

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

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

Figura 23: Ogni valore di inventario del prodotto è elencato (fare clic per visualizzare l'immagine a dimensione intera)

Le funzioni definite dall'utente possono anche restituire dati tabulari. Ad esempio, è possibile creare una funzione definita dall'utente che restituisce 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 funzione definita dall'utente udf_GetProductsByCategoryID accetta un @CategoryID parametro di input e restituisce i risultati della query specificata SELECT . Dopo la creazione, è possibile fare riferimento a questa funzione definita dall'utente FROM 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 funzione definita dall'utente udf_GetProductsByCategoryID al database Northwind; La figura 24 mostra l'output della query precedente SELECT quando viene visualizzato tramite Management Studio. Le funzioni definite dall'utente 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 a dimensione intera)

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 definita dall'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.vbUDF 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.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function udf_ComputeInventoryValue_Managed() As SqlString
        ' Add your code here
        Return New SqlString("Hello")
    End Function
End Class

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 Shared Function udf_ComputeInventoryValue_Managed _
    (UnitPrice As SqlMoney, UnitsInStock As SqlInt16, Discontinued As SqlBoolean) _
    As SqlMoney
    Dim inventoryValue As SqlMoney = 0
    If Not UnitPrice.IsNull AndAlso Not UnitsInStock.IsNull Then
        inventoryValue = UnitPrice * UnitsInStock
        If Discontinued = True Then
            inventoryValue = inventoryValue * New SqlMoney(0.5)
        End If
    End If
    Return inventoryValue
End Function

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.vb e immettere il codice seguente nel file (è possibile usare Visual Studio, Blocco note o qualsiasi editor di testo per eseguire questa operazione):

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub GetProductsWithPriceGreaterThan(ByVal price As SqlMoney)
        'Create the command
        Dim myCommand As 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)
    End Sub
End Class

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.vb file e usare il compilatore C# (csc.exe) per compilare il file di classe in un assembly:

vbc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.vb

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

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

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

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

Nota

Anziché compilare il GetProductsWithPriceGreaterThan.vb file di classe dalla riga di comando, in alternativa è possibile usare Visual Basic Express Edition o creare un progetto libreria di classi separato in Visual Studio Standard Edition. S ren Jacob Lauritsen ha fornito un progetto visual Basic Express Edition 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

Sebbene sia stato aggiunto l'assembly al database Northwind, è ancora necessario associare una stored procedure al GetProductsWithPriceGreaterThan metodo nell'assembly. A questo 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

In questo modo viene creata una nuova stored procedure nel database Northwind denominata GetProductsWithPriceGreaterThan e la associa 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 nella Esplora oggetti. Verrà visualizzata una nuova voce di stored procedure, GetProductsWithPriceGreaterThan che include un'icona di blocco accanto a essa. Per testare questa stored procedure, immettere ed eseguire lo script seguente nella finestra della query:

exec GetProductsWithPriceGreaterThan 24.95

Come illustrato nella figura 32, il comando precedente visualizza le 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 full-size)

Riepilogo

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

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

Programmazione felice!

Altre informazioni

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

• Vantaggi e svantaggi delle funzioni 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, ha lavorato con le tecnologie Microsoft Web dal 1998. Scott lavora come consulente indipendente, allenatore e scrittore. Il suo ultimo libro è Sams Teach Yourself ASP.NET 2,0 in 24 Ore. Può essere raggiunto a mitchell@4GuysFromRolla.com. o tramite il suo blog, che può essere trovato in http://ScottOnWriting.NET.

Grazie speciali

Questa serie di esercitazioni è stata esaminata da molti revisori utili. Il revisore principale per 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. Interessati a esaminare i prossimi articoli MSDN? In tal caso, lasciami una riga in mitchell@4GuysFromRolla.com.

Indietro