Aggiunta di colonne DataTable aggiuntive (VB)

di Scott Mitchell

Scarica il PDF

Quando si usa la Creazione guidata TableAdapter per creare un DataSet tipizzato, la tabella dati corrispondente contiene le colonne restituite dalla query di database principale. Tuttavia, esistono occasioni in cui DataTable deve includere colonne aggiuntive. Questa esercitazione illustra perché le stored procedure sono consigliate quando sono necessarie colonne di DataTable aggiuntive.

Introduzione

Quando si aggiunge una tabellaAdapter a un oggetto DataSet tipizzato, lo schema di DataTable corrispondente viene determinato dalla query principale di TableAdapter. Ad esempio, se la query principale restituisce campi dati A, B e C, DataTable avrà tre colonne corrispondenti denominate A, B e C. Oltre alla query principale, un TableAdapter può includere query aggiuntive che restituiscono, ad esempio, un subset dei dati in base a un parametro. Ad esempio, oltre alla ProductsTableAdapter query principale, che restituisce informazioni su tutti i prodotti, contiene anche metodi come GetProductsByCategoryID(categoryID) e GetProductByProductID(productID), che restituiscono informazioni specifiche sul prodotto in base a un parametro fornito.

Il modello di avere lo schema di DataTable riflette la query principale di TableAdapter se tutti i metodi TableAdapter restituiscono gli stessi o meno campi dati specificati nella query principale. Se un metodo TableAdapter deve restituire campi dati aggiuntivi, è necessario espandere di conseguenza lo schema di DataTable. Nell'esercitazione Master/Detail Using a Bulleted List of Master Records with a Details DataList è stato aggiunto un metodo ai CategoriesTableAdapter campi dati che hanno restituito i CategoryIDcampi dati , CategoryNamee Description definiti nella query principale e NumberOfProducts, un campo dati aggiuntivo che ha segnalato il numero di prodotti associati a ogni categoria. È stata aggiunta manualmente una nuova colonna all'oggetto CategoriesDataTable per acquisire il NumberOfProducts valore del campo dati da questo nuovo metodo.

Come illustrato nell'esercitazione Caricamento file , è necessario prestare molta attenzione a TableAdapters che usano istruzioni SQL ad hoc e hanno metodi i cui campi dati non corrispondono esattamente alla query principale. Se la configurazione guidata TableAdapter viene riesegua, aggiornerà tutti i metodi TableAdapter in modo che l'elenco dei campi dati corrisponda alla query principale. Di conseguenza, tutti i metodi con elenchi di colonne personalizzati ripristinano l'elenco di colonne della query principale e non restituiscono i dati previsti. Questo problema non si verifica quando si usano stored procedure.

In questa esercitazione verrà illustrato come estendere uno schema di DataTable per includere colonne aggiuntive. A causa della brittleness dell'oggetto TableAdapter quando si usano istruzioni SQL ad hoc, in questa esercitazione verranno usate stored procedure. Per altre informazioni sulla configurazione di tableadapter di TableAdapter s Typed, vedere l'esercitazione Creazione di nuove stored procedure di DataSet tipizzato per altre informazioni sulla configurazione di tableAdapter per l'uso di stored procedure.

Passaggio 1: Aggiunta di unaPriceQuartilecolonna all'oggettoProductsDataTable

Nell'esercitazione Creazione di nuove stored procedure per l'esercitazione TableAdapters di DataSet tipizzato è stato creato un dataset tipizzato denominato NorthwindWithSprocs. Questo DataSet contiene attualmente due tabelle dati: ProductsDataTable e EmployeesDataTable. Include ProductsTableAdapter i tre metodi seguenti:

• GetProducts - la query principale, che restituisce tutti i record della Products tabella
• GetProductsByCategoryID(categoryID) - restituisce tutti i prodotti con l'ID categoria specificato.
• GetProductByProductID(productID) - restituisce il prodotto specifico con l'ID product specificato.

La query principale e i due metodi aggiuntivi restituiscono tutti lo stesso set di campi dati, ovvero tutte le colonne della Products tabella. Non sono presenti sottoquerie correlate o JOIN il pull di dati correlati dalle Categories tabelle o Suppliers . Pertanto, l'oggetto ProductsDataTable ha una colonna corrispondente per ogni campo della Products tabella.

Per questa esercitazione, consente di aggiungere un metodo all'oggetto ProductsTableAdapter denominato GetProductsWithPriceQuartile che restituisce tutti i prodotti. Oltre ai campi dati del prodotto standard, GetProductsWithPriceQuartile includerà anche un PriceQuartile campo dati che indica in quale quartile il prezzo del prodotto scende. Ad esempio, tali prodotti i cui prezzi si trovano nel più costoso 25% avranno un valore pari a 1, mentre quelli i cui prezzi rientrano nel 25% inferiore avranno un PriceQuartile valore pari a 4. Prima di preoccuparsi di creare la stored procedure per restituire queste informazioni, è tuttavia prima necessario aggiornare l'oggetto ProductsDataTable per includere una colonna per contenere i PriceQuartile risultati quando viene usato il GetProductsWithPriceQuartile metodo.

Aprire DataSet e fare clic con il NorthwindWithSprocs pulsante destro del mouse su ProductsDataTable. Scegliere Aggiungi dal menu di scelta rapida e quindi selezionare Colonna.

Figura 1: Aggiungere una nuova colonna all'oggetto ProductsDataTable (Fare clic per visualizzare l'immagine full-size)

Verrà aggiunta una nuova colonna alla tabella DataTable denominata Column1 di tipo System.String. È necessario aggiornare il nome della colonna a PriceQuartile e il relativo tipo System.Int32 perché verrà usato per contenere un numero compreso tra 1 e 4. Selezionare la colonna appena aggiunta nell'oggetto ProductsDataTable e, dal Finestra Proprietà, impostare la proprietà su PriceQuartile e la NameDataType proprietà su System.Int32.

Figura 2: Impostare la nuova colonna e NameDataType le proprietà (fare clic per visualizzare l'immagine a dimensioni complete)

Come illustrato nella figura 2, sono disponibili proprietà aggiuntive che possono essere impostate, ad esempio se i valori della colonna devono essere univoci, se la colonna è una colonna di incremento automatico, se sono consentiti o meno valori di database NULL e così via. Lasciare questi valori impostati sulle impostazioni predefinite.

Passaggio 2: Creazione delGetProductsWithPriceQuartilemetodo

Ora che l'oggetto ProductsDataTable è stato aggiornato per includere la PriceQuartile colonna, è possibile creare il GetProductsWithPriceQuartile metodo. Iniziare facendo clic con il pulsante destro del mouse su TableAdapter e scegliendo Aggiungi query dal menu di scelta rapida. In questo modo viene visualizzata la procedura guidata Di configurazione query TableAdapter, che richiede innanzitutto se si vogliono usare istruzioni SQL ad hoc o una stored procedure nuova o esistente. Poiché non è ancora disponibile una stored procedure che restituisce i dati del quartile di prezzo, consentire all'utente di creare questa stored procedure. Selezionare l'opzione Crea nuova stored procedure e fare clic su Avanti.

Figura 3: Indicare alla Procedura guidata TableAdapter di creare la stored procedure per l'utente (fare clic per visualizzare l'immagine full-size)

Nella schermata successiva, illustrata nella figura 4, la procedura guidata richiede il tipo di query da aggiungere. Poiché il GetProductsWithPriceQuartile metodo restituirà tutte le colonne e i record della Products tabella, selezionare l'opzione SELECT che restituisce le righe e fare clic su Avanti.

Figura 4: la query sarà un'istruzione SELECT che restituisce più righe (fare clic per visualizzare l'immagine full-size)

Verrà quindi richiesta la SELECT query. Immettere la query seguente nella procedura guidata:

SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued,
       NTILE(4) OVER (ORDER BY UnitPrice DESC) as PriceQuartile
FROM Products

La query precedente usa SQL Server 2005 s nuova NTILE funzione per dividere i risultati in quattro gruppi in cui i gruppi sono determinati dai UnitPrice valori ordinati in ordine decrescente.

Sfortunatamente, Query Builder non sa come analizzare la OVER parola chiave e visualizzerà un errore durante l'analisi della query precedente. Immettere quindi la query precedente direttamente nella casella di testo nella procedura guidata senza usare Query Builder.

Nota

Per altre informazioni su NTILE e SQL Server 2005 s altre funzioni di classificazione, vedere ROW_NUMBER (Transact-SQL) e la sezione Funzioni di classificazione dalla documentazione online SQL Server 2005.

Dopo aver immesso la SELECT query e facendo clic su Avanti, la procedura guidata chiede di specificare un nome per la stored procedure creata. Assegnare un nome alla nuova stored procedure Products_SelectWithPriceQuartile e fare clic su Avanti.

Figura 5: Assegnare un nome alla stored procedure Products_SelectWithPriceQuartile (fare clic per visualizzare l'immagine full-size)

Infine, viene richiesto di assegnare un nome ai metodi TableAdapter. Lasciare selezionata la casella di controllo Fill a DataTable e Return a DataTable selezionata e denominare i metodi FillWithPriceQuartile e GetProductsWithPriceQuartile.

Figura 6: Assegnare un nome ai metodi tableAdapter e fare clic su Fine (fare clic per visualizzare l'immagine a dimensioni complete)

Con la query specificata e i SELECT metodi TableAdapter e TableAdapter denominati, fare clic su Fine per completare la procedura guidata. A questo punto è possibile che venga visualizzato un avviso o due dalla procedura guidata dicendo che il costrutto o l'istruzione OVER SQL non è supportato. Questi avvisi possono essere ignorati.

Al termine della procedura guidata, TableAdapter deve includere i FillWithPriceQuartile metodi e GetProductsWithPriceQuartile e il database deve includere una stored procedure denominata Products_SelectWithPriceQuartile. Per verificare che TableAdapter contenga effettivamente questo nuovo metodo e che la stored procedure sia stata aggiunta correttamente al database. Quando si controlla il database, se la stored procedure non viene visualizzata, provare a fare clic con il pulsante destro del mouse sulla cartella stored procedure e scegliere Aggiorna.

Figura 7: Verificare che un nuovo metodo sia stato aggiunto alla tabellaAdapter

Figura 8: Verificare che il database contenga la stored procedure (fare clic per visualizzare l'immagineProducts_SelectWithPriceQuartile a dimensioni complete)

Nota

Uno dei vantaggi dell'uso di stored procedure anziché istruzioni SQL ad hoc consiste nel non modificare gli elenchi di colonne delle stored procedure. Verificare questa operazione facendo clic con il pulsante destro del mouse su TableAdapter, scegliendo l'opzione Configura dal menu di scelta rapida per avviare la procedura guidata e quindi facendo clic su Fine per completarla. Passare quindi al database e visualizzare la Products_SelectWithPriceQuartile stored procedure. Si noti che l'elenco di colonne non è stato modificato. Se si usassero istruzioni SQL ad hoc, la procedura guidata TableAdapter Configuration avrebbe ripristinato l'elenco di colonne della query per corrispondere all'elenco di colonne di query principale, rimuovendo così l'istruzione NTILE dalla query usata dal GetProductsWithPriceQuartile metodo .

Quando viene richiamato il metodo Livello di accesso GetProductsWithPriceQuartile dati, TableAdapter esegue la Products_SelectWithPriceQuartile stored procedure e aggiunge una riga a ProductsDataTable per ogni record restituito. I campi dati restituiti dalla stored procedure vengono mappati alle ProductsDataTable colonne di s. Poiché è presente un PriceQuartile campo dati restituito dalla stored procedure, il relativo valore viene assegnato alla ProductsDataTable colonna s PriceQuartile .

Per i metodi TableAdapter le cui query non restituiscono un PriceQuartile campo dati, il valore della colonna è il PriceQuartile valore specificato dalla relativa DefaultValue proprietà. Come illustrato nella figura 2, questo valore è impostato su DBNull, il valore predefinito. Se si preferisce un valore predefinito diverso, è sufficiente impostare di conseguenza la DefaultValue proprietà. Assicurarsi che il DefaultValue valore sia valido in base alla colonna ( DataType ad esempio per System.Int32 la PriceQuartile colonna).

A questo punto sono stati eseguiti i passaggi necessari per aggiungere una colonna aggiuntiva a una tabella DataTable. Per verificare che questa colonna aggiuntiva funzioni come previsto, creare una pagina ASP.NET che visualizzi il nome, il prezzo e il quartile di prezzo di ogni prodotto. Prima di eseguire questa operazione, tuttavia, è necessario aggiornare il livello della logica di business per includere un metodo che chiama il metodo dal GetProductsWithPriceQuartile . Il BLL verrà aggiornato nel passaggio 3 e quindi verrà creata la pagina ASP.NET nel passaggio 4.

Passaggio 3: Aumento del livello della logica di business

Prima di usare il nuovo GetProductsWithPriceQuartile metodo dal livello presentazione, è necessario innanzitutto aggiungere un metodo corrispondente al BLL. Aprire il file di ProductsBLLWithSprocs classe e aggiungere il codice seguente:

<System.ComponentModel.DataObjectMethodAttribute_
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsWithPriceQuartile() As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetProductsWithPriceQuartile()
End Function

Analogamente agli altri metodi di recupero dati in ProductsBLLWithSprocs, il GetProductsWithPriceQuartile metodo chiama semplicemente il metodo corrispondente GetProductsWithPriceQuartile dal e restituisce i risultati.

Passaggio 4: Visualizzazione delle informazioni sui quartile dei prezzi in una pagina Web ASP.NET

Con l'aggiunta BLL, siamo pronti per creare una pagina ASP.NET che mostra il quartile di prezzo per ogni prodotto. Aprire la AddingColumns.aspx pagina nella AdvancedDAL cartella e trascinare gridView dalla casella degli strumenti nella Designer, impostandone la ID proprietà su Products. Dallo smart tag gridView, associarlo a un nuovo ObjectDataSource denominato ProductsDataSource. Configurare ObjectDataSource per l'uso del ProductsBLLWithSprocs metodo della GetProductsWithPriceQuartile classe . Poiché si tratta di una griglia di sola lettura, impostare gli elenchi a discesa nelle schede UPDATE, INSERT e DELETE su (Nessuno).

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

Figura 10: Recuperare le informazioni sul GetProductsWithPriceQuartile prodotto dal metodo (fare clic per visualizzare l'immagine a dimensione intera)

Al termine della procedura guidata Configura origine dati, Visual Studio aggiungerà automaticamente un BoundField o CheckBoxField a GridView per ognuno dei campi dati restituiti dal metodo . Uno di questi campi dati è PriceQuartile, ovvero la colonna aggiunta a ProductsDataTable nel passaggio 1.

Modificare i campi di GridView, rimuovendo tutti gli elementi , ProductNameUnitPricee PriceQuartile BoundFields. UnitPrice Configurare BoundField per formattare il relativo valore come valuta e impostare UnitPrice rispettivamente i valori e PriceQuartile BoundFields allineati a destra e al centro. Infine, aggiornare le proprietà BoundFields rimanenti HeaderText rispettivamente a Product, Price e Price Quartile. Selezionare anche la casella di controllo Abilita ordinamento dallo smart tag gridView.

Dopo queste modifiche, il markup dichiarativo di GridView e ObjectDataSource dovrebbe essere simile al seguente:

<asp:GridView ID="Products" runat="server" AllowSorting="True"
    AutoGenerateColumns="False" DataKeyNames="ProductID" 
    DataSourceID="ProductsDataSource">
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="Product" 
            SortExpression="ProductName" />
        <asp:BoundField DataField="UnitPrice" DataFormatString="{0:c}" 
            HeaderText="Price" HtmlEncode="False" 
            SortExpression="UnitPrice">
            <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
        <asp:BoundField DataField="PriceQuartile" HeaderText="Price Quartile" 
            SortExpression="PriceQuartile">
            <ItemStyle HorizontalAlign="Center" />
        </asp:BoundField>
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ProductsDataSource" runat="server" 
    OldValuesParameterFormatString="original_{0}"
    SelectMethod="GetProductsWithPriceQuartile" 
    TypeName="ProductsBLLWithSprocs">
</asp:ObjectDataSource>

La figura 11 mostra questa pagina quando viene visitata tramite un browser. Si noti che, inizialmente, i prodotti vengono ordinati in base al loro prezzo in ordine decrescente con ogni prodotto assegnato un valore appropriato PriceQuartile . Naturalmente questi dati possono essere ordinati in base ad altri criteri con il valore della colonna Price Quartile che riflette ancora la classificazione del prodotto rispetto al prezzo (vedere la figura 12).

Figura 11: I prodotti sono ordinati in base ai prezzi (fare clic per visualizzare l'immagine a dimensione intera)

Figura 12: I prodotti sono ordinati in base ai nomi (fare clic per visualizzare l'immagine a dimensione intera)

Nota

Con alcune righe di codice è possibile aumentare GridView in modo che colori le righe del prodotto in base al relativo PriceQuartile valore. Potremmo colorarli nel primo quartile un verde chiaro, quelli nel secondo quartile un giallo chiaro e così via. Ti invito a dedicare qualche minuto ad aggiungere questa funzionalità. Se è necessario un aggiornamento per la formattazione di un controllo GridView, vedere l'esercitazione Formattazione personalizzata basata sui dati .

Un approccio alternativo: creazione di un altro tableAdapter

Come illustrato in questa esercitazione, quando si aggiunge un metodo a un oggetto TableAdapter che restituisce campi dati diversi da quelli definiti dalla query principale, è possibile aggiungere colonne corrispondenti a DataTable. Un approccio di questo tipo, tuttavia, funziona bene solo se nel TableAdapter sono presenti un numero ridotto di metodi che restituiscono campi dati diversi e se tali campi dati alternativi non variano troppo rispetto alla query principale.

Anziché aggiungere colonne a DataTable, è possibile aggiungere un altro TableAdapter al DataSet che contiene i metodi del primo TableAdapter che restituiscono campi dati diversi. Per questa esercitazione, invece di aggiungere la PriceQuartile colonna a ProductsDataTable (in cui viene usata solo dal GetProductsWithPriceQuartile metodo ), è stato possibile aggiungere un oggetto TableAdapter aggiuntivo al DataSet denominato ProductsWithPriceQuartileTableAdapter che ha usato la Products_SelectWithPriceQuartile stored procedure come query principale. ASP.NET pagine necessarie per ottenere informazioni sul prodotto con il quartile di prezzo userebbero ProductsWithPriceQuartileTableAdapter, mentre quelle che non potevano continuare a usare .ProductsTableAdapter

Aggiungendo un nuovo TableAdapter, le tabelle DataTable rimangono invariate e le relative colonne rispecchiano con precisione i campi dati restituiti dai relativi metodi TableAdapter. Tuttavia, altri TableAdapter possono introdurre attività e funzionalità ripetitive. Ad esempio, se anche le pagine ASP.NET che visualizzavano la PriceQuartile colonna erano necessarie per fornire supporto per inserimento, aggiornamento ed eliminazione, sarebbe necessario che InsertCommandle ProductsWithPriceQuartileTableAdapter relative proprietà , UpdateCommande DeleteCommand siano configurate correttamente. Anche se queste proprietà rispecchiano le ProductsTableAdapter proprietà, questa configurazione introduce un passaggio aggiuntivo. Esistono inoltre due modi per aggiornare, eliminare o aggiungere un prodotto al database, tramite le ProductsTableAdapter classi e ProductsWithPriceQuartileTableAdapter .

Il download per questa esercitazione include una ProductsWithPriceQuartileTableAdapter classe nel NorthwindWithSprocs DataSet che illustra questo approccio alternativo.

Riepilogo

Nella maggior parte degli scenari, tutti i metodi in un oggetto TableAdapter restituiranno lo stesso set di campi dati, ma in alcuni casi potrebbe essere necessario restituire un campo aggiuntivo. Ad esempio, nell'esercitazione Master/Detail Using a Bulleted List of Master Records with a Details DataList è stato aggiunto un metodo a che, oltre ai CategoriesTableAdapter campi dati della query principale, ha restituito un NumberOfProducts campo che ha segnalato il numero di prodotti associati a ogni categoria. In questa esercitazione è stato esaminato l'aggiunta ProductsTableAdapter di un metodo in che ha restituito un PriceQuartile campo oltre ai campi dati della query principale. Per acquisire campi dati aggiuntivi restituiti dai metodi di TableAdapter, è necessario aggiungere colonne corrispondenti a DataTable.

Se si prevede di aggiungere manualmente colonne a DataTable, è consigliabile che TableAdapter usi stored procedure. Se TableAdapter usa istruzioni SQL ad hoc, ogni volta che la Configurazione guidata TableAdapter viene eseguita tutti gli elenchi di campi dati dei metodi ripristinano i campi dati restituiti dalla query principale. Questo problema non si estende alle stored procedure, motivo per cui sono consigliate e usate in questa esercitazione.

Buon programmatori!

Informazioni sull'autore

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

Grazie speciale a

Questa serie di esercitazioni è stata esaminata da molti revisori utili. I revisori principali di questa esercitazione erano Randy Schmidt, Jacky Goor, Bernadette Leigh e Hilton Giesenow. Si è interessati a esaminare i prossimi articoli MSDN? In tal caso, rilasciami una riga in mitchell@4GuysFromRolla.com.

IndietroAvanti