Creazione di nuove stored procedure per i TableAdapter del set di dati tipizzato (C#)
Nelle esercitazioni precedenti sono state create istruzioni SQL nel codice e sono state passate le istruzioni al database da eseguire. Un approccio alternativo consiste nell'usare stored procedure, in cui le istruzioni SQL sono predefinite nel database. In questa esercitazione si apprenderà come fare in modo che la Creazione guidata TableAdapter generi nuove stored procedure.
Introduzione
Il livello di accesso ai dati (DAL) per queste esercitazioni usa set di dati tipizzato. Come illustrato nell'esercitazione Creazione di un livello di accesso ai dati , i set di dati tipiti sono costituiti da tabelle DataTable e TableAdapter fortemente tipizzate. Le tabelle DataTable rappresentano le entità logiche nel sistema mentre l'interfaccia TableAdapters con il database sottostante per eseguire il lavoro di accesso ai dati. Ciò include il popolamento delle tabelle DataTable con dati, l'esecuzione di query che restituiscono dati scalari e l'inserimento, l'aggiornamento e l'eliminazione di record dal database.
I comandi SQL eseguiti dagli oggetti TableAdapter possono essere istruzioni SQL ad hoc, ad esempio SELECT columnList FROM TableName, o stored procedure. Gli oggetti TableAdapter nell'architettura usano istruzioni SQL ad hoc. Molti sviluppatori e amministratori di database, tuttavia, preferiscono stored procedure rispetto alle istruzioni SQL ad hoc per motivi di sicurezza, gestibilità e aggiornabilità. Altri preferiscono ardentmente istruzioni SQL ad hoc per la loro flessibilità. Nel mio lavoro si prediligo le stored procedure rispetto alle istruzioni SQL ad hoc, ma si è scelto di usare istruzioni SQL ad hoc per semplificare le esercitazioni precedenti.
Quando si definisce un oggetto TableAdapter o si aggiungono nuovi metodi, la procedura guidata tableAdapter semplifica la creazione di nuove stored procedure o l'uso di stored procedure esistenti come avviene per l'uso di istruzioni SQL ad hoc. In questa esercitazione verrà illustrato come creare automaticamente le stored procedure generate automaticamente dalla creazione guidata di TableAdapter. Nell'esercitazione successiva verrà illustrato come configurare i metodi di TableAdapter per l'uso di stored procedure esistenti o create manualmente.
Nota
Vedere il post di blog di Rob Howard Don't Use Stored procedure Yet? and Frans Bouma s blog entry Stored procedure are Bad, M Kay? for a live debate on the pros and cons of stored procedure and ad hoc SQL.
Nozioni fondamentali sulle stored procedure
Le funzioni sono un costrutto comune a tutti i linguaggi di programmazione. Una funzione è una raccolta di istruzioni eseguite quando viene chiamata la funzione . Le funzioni possono accettare parametri di input e possono facoltativamente restituire un valore. Le stored procedure sono costrutti di database che condividono molte analogie con le funzioni nei linguaggi di programmazione. Una stored procedure è costituita da un set di istruzioni T-SQL eseguite quando viene chiamata la stored procedure. Una stored procedure può accettare zero a molti parametri di input e può restituire valori scalari, parametri di output o, più comunemente, set di risultati dalle SELECT query.
Nota
Le stored procedure sono spesso denominate sprocs o SPS.
Le stored procedure vengono create usando l'istruzione CREATE PROCEDURE T-SQL. Ad esempio, lo script T-SQL seguente crea una stored procedure denominata GetProductsByCategoryID che accetta un singolo parametro denominato @CategoryID e restituisce i ProductIDcampi , ProductName, UnitPricee Discontinued di tali colonne nella Products tabella con un valore corrispondente CategoryID :
CREATE PROCEDURE GetProductsByCategoryID
(
@CategoryID int
)
AS
SELECT ProductID, ProductName, UnitPrice, Discontinued
FROM Products
WHERE CategoryID = @CategoryID
Dopo aver creato questa stored procedure, è possibile chiamarla usando la sintassi seguente:
EXEC GetProductsByCategory categoryID
Nota
Nell'esercitazione successiva verrà esaminata la creazione di stored procedure tramite l'IDE di Visual Studio. Per questa esercitazione, tuttavia, la procedura guidata TableAdapter genererà automaticamente le stored procedure.
Oltre a restituire semplicemente i dati, le stored procedure vengono spesso usate per eseguire più comandi di database nell'ambito di una singola transazione. Una stored procedure denominata DeleteCategory, ad esempio, può accettare un @CategoryID parametro ed eseguire due DELETE istruzioni: prima, una per eliminare i prodotti correlati e una seconda eliminando la categoria specificata. Il wrapping di più istruzioni all'interno di una stored procedure non viene eseguito automaticamente all'interno di una transazione. È necessario eseguire altri comandi T-SQL per assicurarsi che i comandi multipli della stored procedure vengano considerati come un'operazione atomica. Verrà illustrato come eseguire il wrapping dei comandi di una stored procedure nell'ambito di una transazione nell'esercitazione successiva.
Quando si usano stored procedure all'interno di un'architettura, i metodi del livello di accesso ai dati richiamano una stored procedure specifica anziché eseguire un'istruzione SQL ad hoc. In questo modo viene centralizzata la posizione delle istruzioni SQL eseguite (nel database) invece di definirla all'interno dell'architettura dell'applicazione. Questa centralizzazione semplifica probabilmente l'individuazione, l'analisi e l'ottimizzazione delle query e offre un quadro molto più chiaro su dove e come viene usato il database.
Per altre informazioni sui concetti fondamentali della stored procedure, vedere le risorse nella sezione Altre informazioni alla fine di questa esercitazione.
Passaggio 1: Creazione delle pagine Web degli scenari avanzati del livello di accesso ai dati
Prima di iniziare la discussione sulla creazione di un'operatore DAL usando stored procedure, è possibile creare prima di tutto le pagine di ASP.NET nel progetto del sito Web che saranno necessarie per questo e le esercitazioni successive. Per iniziare, aggiungere una nuova cartella denominata AdvancedDAL. Aggiungere quindi le pagine di ASP.NET seguenti a tale cartella, assicurandosi di associare ogni pagina alla Site.master pagina master:
Default.aspxNewSprocs.aspxExistingSprocs.aspxJOINs.aspxAddingColumns.aspxComputedColumns.aspxEncryptingConfigSections.aspxManagedFunctionsAndSprocs.aspx
Figura 1: Aggiungere le pagine ASP.NET per le esercitazioni avanzate sui livelli di accesso ai dati
Come nelle altre cartelle, Default.aspx nella AdvancedDAL cartella verranno elencate le esercitazioni nella relativa sezione. Tenere presente che il SectionLevelTutorialListing.ascx controllo utente fornisce questa funzionalità. Aggiungere quindi questo controllo utente a Default.aspx trascinandolo dal Esplora soluzioni nella visualizzazione Struttura della pagina.
Figura 2: Aggiungere il SectionLevelTutorialListing.ascx controllo utente a Default.aspx (fare clic per visualizzare l'immagine a dimensione intera)
Infine, aggiungere queste pagine come voci al Web.sitemap file. In particolare, aggiungere il markup seguente dopo l'uso dei dati <siteMapNode>in batch :
<siteMapNode url="~/AdvancedDAL/Default.aspx"
title="Advanced DAL Scenarios"
description="Explore a number of advanced Data Access Layer scenarios.">
<siteMapNode url="~/AdvancedDAL/NewSprocs.aspx"
title="Creating New Stored Procedures for TableAdapters"
description="Learn how to have the TableAdapter wizard automatically
create and use stored procedures." />
<siteMapNode url="~/AdvancedDAL/ExistingSprocs.aspx"
title="Using Existing Stored Procedures for TableAdapters"
description="See how to plug existing stored procedures into a
TableAdapter." />
<siteMapNode url="~/AdvancedDAL/JOINs.aspx"
title="Returning Data Using JOINs"
description="Learn how to augment your DataTables to work with data
returned from multiple tables via a JOIN query." />
<siteMapNode url="~/AdvancedDAL/AddingColumns.aspx"
title="Adding DataColumns to a DataTable"
description="Master adding new columns to an existing DataTable." />
<siteMapNode url="~/AdvancedDAL/ComputedColumns.aspx"
title="Working with Computed Columns"
description="Explore how to work with computed columns when using
Typed DataSets." />
<siteMapNode url="~/AdvancedDAL/EncryptingConfigSections.aspx"
title="Protected Connection Strings in Web.config"
description="Protect your connection string information in
Web.config using encryption." />
<siteMapNode url="~/AdvancedDAL/ManagedFunctionsAndSprocs.aspx"
title="Creating Managed SQL Functions and Stored Procedures"
description="See how to create SQL functions and stored procedures
using managed code." />
</siteMapNode>
Dopo l'aggiornamento Web.sitemap, dedicare qualche minuto alla visualizzazione del sito Web delle esercitazioni tramite un browser. Il menu a sinistra include ora gli elementi per le esercitazioni avanzate sugli scenari DAL.
Figura 3: La mappa del sito include ora le voci per le esercitazioni avanzate sugli scenari DAL
Passaggio 2: Configurazione di un tableAdapter per creare nuove stored procedure
Per illustrare la creazione di un livello di accesso ai dati che usa stored procedure anziché istruzioni SQL ad hoc, è possibile creare un nuovo dataset tipizzato nella ~/App_Code/DAL cartella denominata NorthwindWithSprocs.xsd. Poiché questo processo è stato descritto in dettaglio nelle esercitazioni precedenti, si procederà rapidamente nei passaggi descritti qui. Se ci si blocca o sono necessarie altre istruzioni dettagliate per la creazione e la configurazione di un set di dati tipizzato, fare riferimento all'esercitazione Creazione di un livello di accesso ai dati .
Aggiungere un nuovo oggetto DataSet al progetto facendo clic con il pulsante destro del DAL mouse sulla cartella, scegliendo Aggiungi nuovo elemento e selezionando il modello DataSet, come illustrato nella figura 4.
Figura 4: Aggiungere un nuovo set di dati tipizzato al progetto denominato NorthwindWithSprocs.xsd (fare clic per visualizzare l'immagine a dimensione intera)
Verrà creato il nuovo Set di dati tipizzato, ne verrà aperto il Designer, verrà creato un nuovo TableAdapter e verrà avviata la Configurazione guidata TableAdapter. Il primo passaggio della Configurazione guidata TableAdapter richiede di selezionare il database da usare. Il stringa di connessione al database Northwind deve essere elencato nell'elenco a discesa. Selezionare questa opzione e fare clic su Avanti.
Da questa schermata successiva è possibile scegliere il modo in cui l'oggetto TableAdapter deve accedere al database. Nelle esercitazioni precedenti è stata selezionata la prima opzione Usare istruzioni SQL. Per questa esercitazione, selezionare la seconda opzione Crea nuove stored procedure e fare clic su Avanti.
Figura 5: Indicare a TableAdapter di creare nuove stored procedure (fare clic per visualizzare l'immagine a dimensione intera)
Analogamente all'uso di istruzioni SQL ad hoc, nel passaggio seguente viene chiesto di fornire l'istruzione SELECT per la query principale di TableAdapter. Invece di usare l'istruzione SELECT immessa qui per eseguire direttamente una query ad hoc, la procedura guidata di TableAdapter creerà una stored procedure contenente questa SELECT query.
Usare la query seguente SELECT per questo TableAdapter:
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
Figura 6: Immettere la query (fare clic per visualizzare l'immagineSELECT a dimensione intera)
Nota
La query precedente è leggermente diversa dalla query principale di ProductsTableAdapter nell'oggetto Northwind DataSet tipizzato. Tenere presente che ProductsTableAdapter nell'oggetto Northwind DataSet tipizzato sono incluse due sottoquery correlate per riportare il nome della categoria e il nome della società per ogni categoria e fornitore del prodotto. Nell'prossima esercitazione Sull'aggiornamento di TableAdapter per l'uso di JOINs si esaminerà l'aggiunta di questi dati correlati a questo TableAdapter.
Fare clic sul pulsante Opzioni avanzate. Da qui è possibile specificare se la procedura guidata deve anche generare istruzioni di inserimento, aggiornamento ed eliminazione per TableAdapter, se usare la concorrenza ottimistica e se la tabella dati deve essere aggiornata dopo inserimenti e aggiornamenti. L'opzione Genera istruzioni Insert, Update e Delete è selezionata per impostazione predefinita. Lasciare selezionata. Per questa esercitazione lasciare deselezionate le opzioni Usa concorrenza ottimistica.
Quando le stored procedure vengono create automaticamente dalla procedura guidata TableAdapter, viene visualizzato che l'opzione Aggiorna tabella dati viene ignorata. Indipendentemente dal fatto che questa casella di controllo sia selezionata, le stored procedure di inserimento e aggiornamento risultanti recuperano il record appena inserito o appena aggiornato, come illustrato nel passaggio 3.
Figura 7: Lasciare selezionata l'opzione Genera istruzioni Insert, Update and Delete
Nota
Se viene selezionata l'opzione Usa concorrenza ottimistica, la procedura guidata aggiungerà condizioni aggiuntive alla WHERE clausola che impedisce l'aggiornamento dei dati se sono state apportate modifiche in altri campi. Per altre informazioni sull'uso della funzionalità di controllo di concorrenza ottimistica predefinita di TableAdapter, vedere l'esercitazione Implementazione della concorrenza ottimistica .
Dopo aver immesso la query e confermato che l'opzione SELECT Genera istruzioni Inserisci, Aggiorna ed Elimina viene selezionata, fare clic su Avanti. Questa schermata successiva, illustrata nella figura 8, richiede i nomi delle stored procedure create dalla procedura guidata per la selezione, l'inserimento, l'aggiornamento e l'eliminazione dei dati. Modificare questi nomi di stored procedure in Products_Select, Products_Insert, Products_Updatee Products_Delete.
Figura 8: Rinominare le stored procedure (fare clic per visualizzare l'immagine full-size)
Per visualizzare la procedura guidata T-SQL, la procedura guidata TableAdapter userà per creare le quattro stored procedure, fare clic sul pulsante Anteprima script SQL. Nella finestra di dialogo Anteprima script SQL è possibile salvare lo script in un file o copiarlo negli Appunti.
Figura 9: Visualizzare in anteprima lo script SQL usato per generare le stored procedure
Dopo aver denominato le stored procedure, fare clic su Avanti per assegnare al nome i metodi corrispondenti di TableAdapter. Analogamente all'uso di istruzioni SQL ad hoc, è possibile creare metodi che riempiono una tabella dati esistente o restituiscono una nuova. È anche possibile specificare se TableAdapter deve includere il modello di DB-Direct per l'inserimento, l'aggiornamento e l'eliminazione di record. Lasciare selezionata tutte e tre le caselle di controllo, ma rinominare il metodo GetProducts Return a DataTable (come illustrato nella figura 10).
Figura 10: Assegnare un nome ai metodi Fill e GetProducts (Fare clic per visualizzare l'immagine full-size)
Fare clic su Avanti per visualizzare un riepilogo dei passaggi che verranno eseguiti dalla procedura guidata. Completare la procedura guidata facendo clic sul pulsante Fine. Al termine della procedura guidata, verrà restituito al Designer di DataSet, che dovrebbe ora includere .ProductsDataTable
Figura 11: La Designer del dataset mostra l'immagine a dimensioni complete (fare clic per visualizzare l'immagine a dimensioni complete)ProductsDataTable
Passaggio 3: Esame delle stored procedure appena create
La procedura guidata TableAdapter usata nel passaggio 2 crea automaticamente le stored procedure per la selezione, l'inserimento, l'aggiornamento e l'eliminazione dei dati. Queste stored procedure possono essere visualizzate o modificate tramite Visual Studio passando a Esplora server e eseguendo il drill-down nella cartella stored procedure del database. Come illustrato nella figura 12, il database Northwind contiene quattro nuove stored procedure: Products_Delete, Products_Insert, Products_Selecte Products_Update.
Figura 12: Le quattro stored procedure create nel passaggio 2 sono disponibili nella cartella stored procedure del database
Nota
Se esplora server non viene visualizzato, passare al menu Visualizza e scegliere l'opzione Esplora server. Se non vengono visualizzate le stored procedure correlate al prodotto aggiunte dal passaggio 2, provare a fare clic con il pulsante destro del mouse sulla cartella Stored Procedure e scegliere Aggiorna.
Per visualizzare o modificare una stored procedure, fare doppio clic sul nome in Esplora server o, in alternativa, fare clic con il pulsante destro del mouse sulla stored procedure e scegliere Apri. La figura 13 mostra la Products_Delete stored procedure, quando si apre.
Figura 13: le stored procedure possono essere aperte e modificate da Visual Studio (fare clic per visualizzare l'immagine a dimensioni complete)
Il contenuto delle Products_Delete stored procedure e Products_Select è piuttosto semplice. Le Products_Insert stored procedure e Products_Update , d'altra parte, garantiscono un'ispezione più attenta man mano che eseguono un'istruzione SELECT dopo le relative INSERT istruzioni e UPDATE . Ad esempio, il codice SQL seguente costituisce la Products_Insert stored procedure:
ALTER PROCEDURE dbo.Products_Insert
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit
)
AS
SET NOCOUNT OFF;
INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit],
[UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued])
VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice,
@UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE (ProductID = SCOPE_IDENTITY())
La stored procedure accetta come parametri di input le Products colonne restituite dalla SELECT query specificata nella procedura guidata tableAdapter e questi valori vengono usati in un'istruzione INSERT . Dopo l'istruzione INSERT , viene usata una SELECT query per restituire i Products valori di colonna (incluso il ProductID) del record appena aggiunto. Questa funzionalità di aggiornamento è utile quando si aggiunge un nuovo record usando il modello di aggiornamento batch perché aggiorna automaticamente le proprietà delle istanze ProductID appena aggiunte ProductRow con i valori incrementati automaticamente assegnati dal database.
Il codice seguente illustra questa funzionalità. Contiene un ProductsTableAdapter oggetto e ProductsDataTable creato per l'oggetto NorthwindWithSprocs DataSet tipizzato. Un nuovo prodotto viene aggiunto al database creando un'istanza ProductsRow , fornendo i relativi valori e chiamando il metodo TableAdapter Update , passando l'oggetto ProductsDataTable. Internamente, il metodo TableAdapter Update enumera le ProductsRow istanze in DataTable passato (in questo esempio è presente solo una , quella appena aggiunta) ed esegue il comando di inserimento, aggiornamento o eliminazione appropriato. In questo caso, la Products_Insert stored procedure viene eseguita, che aggiunge un nuovo record alla Products tabella e restituisce i dettagli del record appena aggiunto. Il valore dell'istanza ProductsRowProductID viene quindi aggiornato. Al termine del Update metodo, è possibile accedere al valore del ProductID record appena aggiunto tramite la ProductsRow proprietà s.ProductID
// Create the ProductsTableAdapter and ProductsDataTable
NorthwindWithSprocsTableAdapters.ProductsTableAdapter productsAPI =
new NorthwindWithSprocsTableAdapters.ProductsTableAdapter();
NorthwindWithSprocs.ProductsDataTable products =
new NorthwindWithSprocs.ProductsDataTable();
// Create a new ProductsRow instance and set its properties
NorthwindWithSprocs.ProductsRow product = products.NewProductsRow();
product.ProductName = "New Product";
product.CategoryID = 1; // Beverages
product.Discontinued = false;
// Add the ProductsRow instance to the DataTable
products.AddProductsRow(product);
// Update the DataTable using the Batch Update pattern
productsAPI.Update(products);
// At this point, we can determine the value of the newly-added record's ProductID
int newlyAddedProductIDValue = product.ProductID;
La Products_Update stored procedure include in modo analogo un'istruzione SELECT dopo l'istruzione UPDATE .
ALTER PROCEDURE dbo.Products_Update
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit,
@Original_ProductID int,
@ProductID int
)
AS
SET NOCOUNT OFF;
UPDATE [Products]
SET [ProductName] = @ProductName, [SupplierID] = @SupplierID,
[CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit,
[UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock,
[UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel,
[Discontinued] = @Discontinued
WHERE (([ProductID] = @Original_ProductID));
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE (ProductID = @ProductID)
Si noti che questa stored procedure include due parametri di input per ProductID: @Original_ProductID e @ProductID. Questa funzionalità consente scenari in cui la chiave primaria potrebbe essere modificata. Ad esempio, in un database dipendente, ogni record dipendente potrebbe usare il numero di previdenza sociale del dipendente come chiave primaria. Per modificare un numero di previdenza sociale esistente, è necessario specificare sia il nuovo numero di previdenza sociale che quello originale. Per la Products tabella, tale funzionalità non è necessaria perché la ProductID colonna è una IDENTITY colonna e non deve mai essere modificata. Infatti, l'istruzione UPDATE nella stored procedure non include la colonna nell'elenco Products_UpdateProductID di colonne. Pertanto, mentre @Original_ProductID viene usato nella clausola s WHERE dell'istruzioneUPDATE, è superfluo per la Products tabella e può essere sostituito dal @ProductID parametro . Quando si modificano i parametri di una stored procedure, è importante aggiornare anche i metodi TableAdapter che usano tale stored procedure.
Passaggio 4: Modifica dei parametri di una stored procedure e aggiornamento di TableAdapter
Poiché il parametro è superfluo, rimuovere tutto dalla @Original_ProductIDProducts_Update stored procedure. Aprire la Products_Update stored procedure, eliminare il @Original_ProductID parametro e, nella WHERE clausola dell'istruzione UPDATE , modificare il nome del parametro usato da @Original_ProductID a @ProductID. Dopo aver apportato queste modifiche, il T-SQL all'interno della stored procedure dovrebbe essere simile al seguente:
ALTER PROCEDURE dbo.Products_Update
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit,
@ProductID int
)
AS
SET NOCOUNT OFF;
UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID,
[CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit,
[UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock,
[UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel,
[Discontinued] = @Discontinued
WHERE (([ProductID] = @ProductID));
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE (ProductID = @ProductID)
Per salvare queste modifiche al database, fare clic sull'icona Salva nella barra degli strumenti o premere CTRL+S. A questo punto, la Products_Update stored procedure non prevede un @Original_ProductID parametro di input, ma TableAdapter è configurato per passare tale parametro. È possibile visualizzare i parametri che tableAdapter invierà alla stored procedure selezionando TableAdapter nella Designer DataSet, passando alla Products_Update Finestra Proprietà e facendo clic sui puntini di sospensione nell'insieme UpdateCommand sParameters. Viene visualizzata la finestra di dialogo Raccolta parametri Editor visualizzata nella figura 14.
Figura 14: Raccolta parametri Editor Elenchi i parametri usati passati alla Products_Update stored procedure
È possibile rimuovere questo parametro da qui selezionando semplicemente il @Original_ProductID parametro dall'elenco dei membri e facendo clic sul pulsante Rimuovi.
In alternativa, è possibile aggiornare i parametri usati per tutti i metodi facendo clic con il pulsante destro del mouse sul TableAdapter nella Designer e scegliendo Configura. Verrà visualizzata la procedura guidata Di configurazione TableAdapter, elencando le stored procedure usate per la selezione, l'inserimento, l'aggiornamento e l'eliminazione, insieme ai parametri previsti per la ricezione delle stored procedure. Se si fa clic sull'elenco a discesa Aggiorna è possibile visualizzare i parametri di input previsti nelle Products_Update stored procedure, che ora non include @Original_ProductID più (vedere La figura 15). Fare clic su Fine per aggiornare automaticamente la raccolta di parametri utilizzata dall'oggetto TableAdapter.
Figura 15: è possibile usare in alternativa la Configurazione guidata TableAdapter per aggiornare le raccolte di parametri dei metodi (fare clic per visualizzare l'immagine a dimensioni complete)
Passaggio 5: Aggiunta di metodi tableAdapter aggiuntivi
Come illustrato nel passaggio 2, quando si crea un nuovo TableAdapter è facile generare automaticamente le stored procedure corrispondenti. Lo stesso vale quando si aggiungono altri metodi a un TableAdapter. Per illustrare questa operazione, è possibile aggiungere un GetProductByProductID(productID) metodo al ProductsTableAdapter metodo creato nel passaggio 2. Questo metodo accetta come input un ProductID valore e restituisce i dettagli sul prodotto specificato.
Iniziare facendo clic con il pulsante destro del mouse sul TableAdapter e scegliendo Aggiungi query dal menu di scelta rapida.
Figura 16: Aggiungere una nuova query all'oggetto TableAdapter
Verrà avviata la procedura guidata Configurazione query TableAdapter, che richiede prima di tutto come l'oggetto TableAdapter deve accedere al database. Per creare una nuova stored procedure, scegliere l'opzione Crea una nuova stored procedure e fare clic su Avanti.
Figura 17: scegliere l'opzione Crea una nuova stored procedure (Fare clic per visualizzare l'immagine a dimensioni complete)
La schermata successiva chiede di identificare il tipo di query da eseguire, se restituirà un set di righe o un singolo valore scalare o eseguire un'istruzione UPDATE, INSERTo DELETE . Poiché il GetProductByProductID(productID) metodo restituirà una riga, lasciare selezionata l'opzione SELECT che restituisce l'opzione di riga selezionata e premere Avanti.
Figura 18: scegliere l'opzione SELECT che restituisce l'opzione di riga (Fare clic per visualizzare l'immagine full-size)
La schermata successiva visualizza la query principale di TableAdapter, che elenca solo il nome della stored procedure (dbo.Products_Select). Sostituire il nome della stored procedure con l'istruzione seguente SELECT , che restituisce tutti i campi del prodotto per un prodotto specificato:
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID
Figura 19: Sostituire il nome della stored procedure con una query (fare clic per visualizzare l'immagineSELECT a dimensioni complete)
La schermata successiva chiede di assegnare un nome alla stored procedure che verrà creata. Immettere il nome Products_SelectByProductID e fare clic su Avanti.
Figura 20: Assegnare un nome alla nuova stored procedure Products_SelectByProductID (fare clic per visualizzare l'immagine full-size)
Il passaggio finale della procedura guidata consente di modificare i nomi dei metodi generati e di indicare se usare il modello Fill a DataTable, Return a DataTable pattern o entrambi. Per questo metodo, lasciare controllate entrambe le opzioni, ma rinominare i metodi in FillByProductID e GetProductByProductID. Fare clic su Avanti per visualizzare un riepilogo dei passaggi che verranno eseguiti dalla procedura guidata e quindi fare clic su Fine per completare la procedura guidata.
Figura 21: Rinominare i metodi tableAdapter in FillByProductID e GetProductByProductID (fare clic per visualizzare l'immagine a dimensioni complete)
Dopo aver completato la procedura guidata, TableAdapter ha un nuovo metodo disponibile, GetProductByProductID(productID) che, quando richiamato, eseguirà la Products_SelectByProductID stored procedure appena creata. Per visualizzare questa nuova stored procedure da Esplora server, passare alla cartella Stored Procedure e aprire Products_SelectByProductID (se non viene visualizzata, fare clic con il pulsante destro del mouse sulla cartella Stored Procedure e scegliere Aggiorna).
Si noti che la SelectByProductID stored procedure accetta @ProductID come parametro di input ed esegue l'istruzione SELECT immessa nella procedura guidata.
ALTER PROCEDURE dbo.Products_SelectByProductID
(
@ProductID int
)
AS
SET NOCOUNT ON;
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID
Passaggio 6: Creazione di una classe livello di logica di business
Durante la serie di esercitazioni abbiamo cercato di mantenere un'architettura a livelli in cui il livello presentazione ha eseguito tutte le chiamate al livello di logica di business (BLL). Per rispettare questa decisione di progettazione, è prima necessario creare una classe BLL per il nuovo DataSet tipizzato prima di poter accedere ai dati del prodotto dal livello presentazione.
Creare un nuovo file di classe denominato ProductsBLLWithSprocs.cs nella ~/App_Code/BLL cartella e aggiungerlo al codice seguente:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using NorthwindWithSprocsTableAdapters;
[System.ComponentModel.DataObject]
public class ProductsBLLWithSprocs
{
private ProductsTableAdapter _productsAdapter = null;
protected ProductsTableAdapter Adapter
{
get
{
if (_productsAdapter == null)
_productsAdapter = new ProductsTableAdapter();
return _productsAdapter;
}
}
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Select, true)]
public NorthwindWithSprocs.ProductsDataTable GetProducts()
{
return Adapter.GetProducts();
}
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetProductByProductID(int productID)
{
return Adapter.GetProductByProductID(productID);
}
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Insert, true)]
public bool AddProduct
(string productName, int? supplierID, int? categoryID,
string quantityPerUnit, decimal? unitPrice, short? unitsInStock,
short? unitsOnOrder, short? reorderLevel, bool discontinued)
{
// Create a new ProductRow instance
NorthwindWithSprocs.ProductsDataTable products =
new NorthwindWithSprocs.ProductsDataTable();
NorthwindWithSprocs.ProductsRow product = products.NewProductsRow();
product.ProductName = productName;
if (supplierID == null)
product.SetSupplierIDNull();
else
product.SupplierID = supplierID.Value;
if (categoryID == null)
product.SetCategoryIDNull();
else
product.CategoryID = categoryID.Value;
if (quantityPerUnit == null)
product.SetQuantityPerUnitNull();
else
product.QuantityPerUnit = quantityPerUnit;
if (unitPrice == null)
product.SetUnitPriceNull();
else
product.UnitPrice = unitPrice.Value;
if (unitsInStock == null)
product.SetUnitsInStockNull();
else
product.UnitsInStock = unitsInStock.Value;
if (unitsOnOrder == null)
product.SetUnitsOnOrderNull();
else
product.UnitsOnOrder = unitsOnOrder.Value;
if (reorderLevel == null)
product.SetReorderLevelNull();
else
product.ReorderLevel = reorderLevel.Value;
product.Discontinued = discontinued;
// Add the new product
products.AddProductsRow(product);
int rowsAffected = Adapter.Update(products);
// Return true if precisely one row was inserted, otherwise false
return rowsAffected == 1;
}
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Update, true)]
public bool UpdateProduct
(string productName, int? supplierID, int? categoryID, string quantityPerUnit,
decimal? unitPrice, short? unitsInStock, short? unitsOnOrder,
short? reorderLevel, bool discontinued, int productID)
{
NorthwindWithSprocs.ProductsDataTable products =
Adapter.GetProductByProductID(productID);
if (products.Count == 0)
// no matching record found, return false
return false;
NorthwindWithSprocs.ProductsRow product = products[0];
product.ProductName = productName;
if (supplierID == null)
product.SetSupplierIDNull();
else
product.SupplierID = supplierID.Value;
if (categoryID == null)
product.SetCategoryIDNull();
else
product.CategoryID = categoryID.Value;
if (quantityPerUnit == null)
product.SetQuantityPerUnitNull();
else
product.QuantityPerUnit = quantityPerUnit;
if (unitPrice == null)
product.SetUnitPriceNull();
else
product.UnitPrice = unitPrice.Value;
if (unitsInStock == null)
product.SetUnitsInStockNull();
else
product.UnitsInStock = unitsInStock.Value;
if (unitsOnOrder == null)
product.SetUnitsOnOrderNull();
else
product.UnitsOnOrder = unitsOnOrder.Value;
if (reorderLevel == null)
product.SetReorderLevelNull();
else
product.ReorderLevel = reorderLevel.Value;
product.Discontinued = discontinued;
// Update the product record
int rowsAffected = Adapter.Update(product);
// Return true if precisely one row was updated, otherwise false
return rowsAffected == 1;
}
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Delete, true)]
public bool DeleteProduct(int productID)
{
int rowsAffected = Adapter.Delete(productID);
// Return true if precisely one row was deleted, otherwise false
return rowsAffected == 1;
}
}
Questa classe simula la semantica della ProductsBLL classe dalle esercitazioni precedenti, ma usa gli ProductsTableAdapter oggetti e ProductsDataTable da NorthwindWithSprocs DataSet. Ad esempio, anziché avere un'istruzione using NorthwindTableAdapters all'inizio del file di classe, come ProductsBLL fausing NorthwindWithSprocsTableAdapters, la ProductsBLLWithSprocs classe usa . Analogamente, gli ProductsDataTable oggetti e ProductsRow usati in questa classe sono preceduti dallo NorthwindWithSprocs spazio dei nomi. La ProductsBLLWithSprocs classe fornisce due metodi GetProducts di accesso ai dati e GetProductByProductIDmetodi per aggiungere, aggiornare ed eliminare una singola istanza del prodotto.
Passaggio 7: Uso diNorthwindWithSprocsDataSet dal livello di presentazione
A questo punto è stato creato un DAL che usa stored procedure per accedere e modificare i dati del database sottostanti. Abbiamo anche creato un BLL rudimentario con metodi per recuperare tutti i prodotti o un particolare prodotto insieme ai metodi per aggiungere, aggiornare ed eliminare prodotti. Per completare questa esercitazione, creare una pagina ASP.NET che usa la classe BLL per ProductsBLLWithSprocs visualizzare, aggiornare ed eliminare i record.
Aprire la NewSprocs.aspx pagina nella AdvancedDAL cartella e trascinare GridView dalla casella degli strumenti nella Designer, assegnando la denominazione Productsa . Dallo smart tag GridView scegliere di associarlo a un nuovo OggettoDataSource denominato ProductsDataSource. Configurare ObjectDataSource per usare la ProductsBLLWithSprocs classe, come illustrato nella figura 22.
Figura 22: Configurare ObjectDataSource per usare la classe (fare clic per visualizzare l'immagineProductsBLLWithSprocs full-size)
L'elenco a discesa nella scheda SELECT include due opzioni GetProducts e GetProductByProductID. Poiché si desidera visualizzare tutti i prodotti in GridView, scegliere il GetProducts metodo. Gli elenchi a discesa nelle schede UPDATE, INSERT e DELETE hanno un solo metodo. Assicurarsi che ognuno di questi elenchi a discesa disponga del metodo appropriato selezionato e quindi fare clic su Fine.
Al termine della procedura guidata ObjectDataSource, Visual Studio aggiungerà BoundFields e un controllo CheckBoxField ai campi dati del prodotto. Attivare la modifica predefinita di GridView ed eliminare le funzionalità controllando le opzioni Abilita modifica e Abilita eliminazione presenti nello smart tag.
Figura 23: la pagina contiene un controllo GridView con modifica ed eliminazione del supporto abilitato (fare clic per visualizzare l'immagine full-size)
Come illustrato nelle esercitazioni precedenti, al completamento della procedura guidata di ObjectDataSource, Visual Studio imposta la OldValuesParameterFormatString proprietà su original_{0}. È necessario ripristinare il valore predefinito di per consentire alle funzionalità di {0} modifica dei dati di funzionare correttamente in base ai parametri previsti dai metodi nel BLL. Assicurarsi pertanto di impostare la proprietà su {0} o rimuovere completamente la OldValuesParameterFormatString proprietà dalla sintassi dichiarativa.
Dopo aver completato la Configurazione guidata origine dati, attivare la modifica ed eliminare il supporto in GridView e restituire la proprietà ObjectDataSource OldValuesParameterFormatString al relativo valore predefinito, il markup dichiarativo della pagina dovrebbe essere simile al seguente:
<asp:GridView ID="Products" runat="server" AutoGenerateColumns="False"
DataKeyNames="ProductID" DataSourceID="ProductsDataSource">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="ProductID" HeaderText="ProductID"
InsertVisible="False" ReadOnly="True"
SortExpression="ProductID" />
<asp:BoundField DataField="ProductName" HeaderText="ProductName"
SortExpression="ProductName" />
<asp:BoundField DataField="SupplierID" HeaderText="SupplierID"
SortExpression="SupplierID" />
<asp:BoundField DataField="CategoryID" HeaderText="CategoryID"
SortExpression="CategoryID" />
<asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit"
SortExpression="QuantityPerUnit" />
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice"
SortExpression="UnitPrice" />
<asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock"
SortExpression="UnitsInStock" />
<asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder"
SortExpression="UnitsOnOrder" />
<asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel"
SortExpression="ReorderLevel" />
<asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued"
SortExpression="Discontinued" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ProductsDataSource" runat="server"
DeleteMethod="DeleteProduct" InsertMethod="AddProduct"
SelectMethod="GetProducts" TypeName="ProductsBLLWithSprocs"
UpdateMethod="UpdateProduct">
<DeleteParameters>
<asp:Parameter Name="productID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="productName" Type="String" />
<asp:Parameter Name="supplierID" Type="Int32" />
<asp:Parameter Name="categoryID" Type="Int32" />
<asp:Parameter Name="quantityPerUnit" Type="String" />
<asp:Parameter Name="unitPrice" Type="Decimal" />
<asp:Parameter Name="unitsInStock" Type="Int16" />
<asp:Parameter Name="unitsOnOrder" Type="Int16" />
<asp:Parameter Name="reorderLevel" Type="Int16" />
<asp:Parameter Name="discontinued" Type="Boolean" />
<asp:Parameter Name="productID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="productName" Type="String" />
<asp:Parameter Name="supplierID" Type="Int32" />
<asp:Parameter Name="categoryID" Type="Int32" />
<asp:Parameter Name="quantityPerUnit" Type="String" />
<asp:Parameter Name="unitPrice" Type="Decimal" />
<asp:Parameter Name="unitsInStock" Type="Int16" />
<asp:Parameter Name="unitsOnOrder" Type="Int16" />
<asp:Parameter Name="reorderLevel" Type="Int16" />
<asp:Parameter Name="discontinued" Type="Boolean" />
</InsertParameters>
</asp:ObjectDataSource>
A questo punto è possibile riordinare GridView personalizzando l'interfaccia di modifica in modo da includere la convalida, il rendering delle CategoryID colonne e SupplierID come DropDownList e così via. È anche possibile aggiungere una conferma lato client al pulsante Elimina e ti consigliamo di prendere il tempo per implementare questi miglioramenti. Poiché questi argomenti sono stati trattati nelle esercitazioni precedenti, tuttavia, non verranno illustrati di nuovo qui.
Indipendentemente dal fatto che si migliora GridView o meno, testare le funzionalità principali della pagina in un browser. Come illustrato nella figura 24, la pagina elenca i prodotti in Un controllo GridView che fornisce funzionalità di modifica per riga ed eliminazione.
Figura 24: i prodotti possono essere visualizzati, modificati ed eliminati da GridView (fare clic per visualizzare l'immagine full-size)
Riepilogo
I TableAdapter in un dataset tipizzato possono accedere ai dati dal database usando istruzioni SQL ad hoc o tramite stored procedure. Quando si usano stored procedure, è possibile usare stored procedure esistenti o la procedura guidata TableAdapter può essere incaricata di creare nuove stored procedure in base a una SELECT query. In questa esercitazione è stato illustrato come creare automaticamente le stored procedure.
Quando le stored procedure generate automaticamente consentono di risparmiare tempo, esistono alcuni casi in cui la stored procedure creata dalla procedura guidata non è allineata a ciò che sarebbe stato creato autonomamente. Un esempio è la Products_Update stored procedure, che prevede parametri di @Original_ProductID input e @ProductID sia se il @Original_ProductID parametro è superfluo.
In molti scenari, le stored procedure potrebbero essere già state create oppure è possibile crearle manualmente in modo da avere un grado più fine di controllo sui comandi della stored procedure. In entrambi i casi, si vuole indicare a TableAdapter di usare stored procedure esistenti per i relativi metodi. Verrà illustrato come eseguire questa operazione nell'esercitazione successiva.
Programmazione felice!
Altre informazioni
Per altre informazioni sugli argomenti illustrati in questa esercitazione, vedere le risorse seguenti:
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 Hilton Geisenow. Interessati a esaminare i prossimi articoli MSDN? In tal caso, lasciami una riga in mitchell@4GuysFromRolla.com.
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per