Aggiunta di colonne al DataTable (C#)

di Scott Mitchell

Scaricare il PDF

Quando si utilizza la Creazione Guidata di TableAdapter per creare un DataSet tipizzato, la tabella DataTable corrispondente contiene le colonne restituite dalla query di database principale. In alcuni casi, tuttavia, DataTable deve includere colonne aggiuntive. In questa esercitazione viene illustrato perché le stored procedure sono consigliate quando sono necessarie colonne DataTable aggiuntive.

Introduzione

Quando si aggiunge un oggetto TableAdapter a un dataset tipizzato, lo schema di DataTable corrispondente viene determinato dalla query principale di TableAdapter. Ad esempio, se la query principale restituisce i campi dati A, B e C, DataTable avrà tre colonne corrispondenti denominate A, B e C. Oltre alla query principale, un oggetto TableAdapter può includere query aggiuntive che restituiscono, ad esempio, un subset dei dati in base ad alcuni parametri. 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 in cui lo schema del DataTable rispecchia la query principale del TableAdapter funziona bene se tutti i metodi del TableAdapter restituiscono gli stessi campi dati o meno rispetto a quelli 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, abbiamo aggiunto un metodo al CategoriesTableAdapter che ha restituito i campi dati CategoryID, CategoryName, e Description definiti nella query principale, più NumberOfProducts, un campo dati aggiuntivo che ha segnalato il numero di prodotti associati a ogni categoria. È stata aggiunta manualmente una nuova colonna a CategoriesDataTable per acquisire il valore del NumberOfProducts campo dati da questo nuovo metodo.

Come illustrato nell'esercitazione Caricamento di file , è necessario prestare particolare attenzione agli oggetti TableAdapter 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 rieseguata, aggiornerà tutti i metodi di TableAdapter in modo che il relativo elenco di 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 fragilità di TableAdapter quando si usano istruzioni SQL ad hoc, in questa esercitazione si useranno stored procedure. Vedere il tutorial Creazione di nuove stored procedure per i TableAdapter di DataSet tipizzato per ulteriori informazioni sulla configurazione di un oggetto TableAdapter per l'uso di stored procedure.

Passaggio 1: Aggiunta di unaPriceQuartilecolonna aProductsDataTable

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

• GetProducts : query principale, che restituisce tutti i record dalla Products tabella
• GetProductsByCategoryID(categoryID) : restituisce tutti i prodotti con l'ID categoria specificato.
• GetProductByProductID(productID) : restituisce il prodotto specifico con l'ID prodotto 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 esistono sottoquery correlate né JOIN che estraggono dati correlati dalle tabelle Categories o Suppliers. Pertanto, il ProductsDataTable ha una colonna corrispondente per ogni campo nella tabella Products.

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

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

Figura 1: Aggiungere una nuova colonna a (ProductsDataTable a dimensione intera)

Verrà aggiunta una nuova colonna alla tabella DataTable denominata Column1 di tipo System.String. È necessario aggiornare il nome di questa colonna in PriceQuartile e il relativo tipo a System.Int32 perché verrà usato per contenere un numero compreso tra 1 e 4. Selezionare la colonna appena aggiunta in ProductsDataTable e, nella finestra Proprietà, impostare la Name proprietà su PriceQuartile e la DataType proprietà su System.Int32.

Figura 2: Impostare le proprietà della nuova colonna NameDataType (Clicca per vedere l'immagine a grandezza intera)

Come illustrato nella figura 2, è possibile impostare proprietà aggiuntive, ad esempio se i valori nella colonna devono essere univoci, se la colonna è una colonna con 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 la ProductsDataTable è stata aggiornata per includere la colonna PriceQuartile, siamo pronti per creare il metodo GetProductsWithPriceQuartile. Per iniziare, fare clic con il pulsante destro del mouse su TableAdapter e scegliere Aggiungi query dal menu di scelta rapida. Viene aperta la procedura guidata di configurazione query del TableAdapter, che ci chiede inizialmente se vogliamo utilizzare istruzioni SQL ad hoc oppure una stored procedure nuova o esistente. Poiché non abbiamo ancora una procedura memorizzata che restituisca i dati dei quartili di prezzo, facciamo in modo che il TableAdapter crei questa procedura memorizzata per noi. Selezionare l'opzione Crea una nuova procedura memorizzata e fare clic su Avanti.

Figura 3: Instruire il Wizard TableAdapter per creare la stored procedure per noi (fare clic per visualizzare l'immagine a schermo intero)

Nella schermata successiva, illustrata nella Figura 4, la procedura guidata ci chiede che tipo di query 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 a dimensione intera)

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 la nuova NTILE funzione di SQL Server 2005 per dividere i risultati in quattro gruppi in cui i gruppi sono determinati dai UnitPrice valori ordinati in ordine decrescente.

Sfortunatamente, il Generatore di query non sa come analizzare la parola chiave OVER e visualizzerà un errore quando analizza la query sopra indicata. Immettere quindi la query precedente direttamente nella casella di testo della procedura guidata senza usare Generatore query.

Annotazioni

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

Dopo aver immesso la SELECT query e aver fatto clic su Avanti, la procedura guidata ci chiede di specificare un nome per la stored procedure che verrà creata. Assegna un nome alla nuova stored procedure Products_SelectWithPriceQuartile e fai clic su Avanti.

Figura 5: Assegnare un nome alla stored procedure Products_SelectWithPriceQuartile (fare clic per visualizzare l'immagine a dimensione intera)

Infine, viene richiesto di denominare i metodi TableAdapter. Lasciare selezionate entrambe le caselle di controllo Fill a DataTable e Return a DataTable, quindi denominare i metodi FillWithPriceQuartile e GetProductsWithPriceQuartile.

Figura 6: Denominare i metodi di TableAdapter e fare clic su Fine (fare clic per visualizzare l'immagine a dimensione intera)

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

Al termine della procedura guidata, il TableAdapter dovrebbe includere i metodi FillWithPriceQuartile e GetProductsWithPriceQuartile, e il database dovrebbe includere una stored procedure chiamata Products_SelectWithPriceQuartile. Prenditi un momento 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 a TableAdapter

Figura 8: Verificare che il database contenga la stored procedure (Products_SelectWithPriceQuartile a dimensione intera)

Annotazioni

Uno dei vantaggi dell'uso di stored procedure anziché di istruzioni SQL ad hoc consiste nel fatto che la ripetizione della configurazione guidata TableAdapter non modificherà 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 scegliendo Fine per completarla. Passa quindi al database e visualizza la procedura memorizzata Products_SelectWithPriceQuartile. Si noti che l'elenco di colonne non è stato modificato. Se avessimo utilizzato istruzioni SQL ad hoc, la procedura guidata di configurazione del TableAdapter avrebbe ripristinato l'elenco delle colonne della query per farlo corrispondere all'elenco delle colonne della query principale, rimuovendo così l'istruzione NTILE dalla query utilizzata dal metodo GetProductsWithPriceQuartile.

Quando viene richiamato il metodo del livello di GetProductsWithPriceQuartile accesso ai dati, TableAdapter esegue la Products_SelectWithPriceQuartile procedura memorizzata e aggiunge una riga a ProductsDataTable per ogni record restituito. I campi dati restituiti dalla stored procedure vengono mappati alle colonne ProductsDataTable. 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 PriceQuartile valore della colonna è il 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 la DefaultValue proprietà di conseguenza. Assicurati che il valore DefaultValue sia valido in considerazione della colonna DataType (ad esempio, System.Int32 per la colonna PriceQuartile).

A questo punto sono stati eseguiti i passaggi necessari per aggiungere una colonna aggiuntiva a un oggetto 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, però, è necessario aggiornare il livello di logica aziendale per includere un metodo che chiama il metodo GetProductsWithPriceQuartile della DAL. Il BLL verrà aggiornato successivamente nel passaggio 3 e quindi verrà creata la pagina ASP.NET nel passaggio 4.

Passaggio 3: Aumento del livello di logica di business

Prima di usare il nuovo GetProductsWithPriceQuartile metodo dal livello presentazione, è necessario 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 NorthwindWithSprocs.ProductsDataTable GetProductsWithPriceQuartile()
{
    return Adapter.GetProductsWithPriceQuartile();
}

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

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

Con l'aggiunta BLL completa 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 Toolbox nel Designer, impostandone la proprietà ID su Products. Dallo smart tag di GridView, associarlo a un nuovo ObjectDataSource denominato ProductsDataSource. Configurare ObjectDataSource per l'uso del ProductsBLLWithSprocs metodo della classe .GetProductsWithPriceQuartile 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 (ProductsBLLWithSprocs a dimensione intera)

Figura 10: Recuperare le informazioni sul prodotto dal metodo (GetProductsWithPriceQuartile a dimensione intera)

Dopo aver completato la procedura guidata Configura origine dati, Visual Studio aggiungerà automaticamente un oggetto 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 tranne ProductName, UnitPrice e PriceQuartile BoundFields. Configurare il valore di UnitPrice BoundField per formattarlo come valuta e impostare UnitPrice BoundFields allineati a destra e PriceQuartile BoundFields allineati al centro, rispettivamente. Infine, aggiornare le proprietà rimanenti dei BoundFields, HeaderText rispettivamente in Prodotto, Prezzo e Quartile di Prezzo. Seleziona anche la casella di controllo Abilita ordinamento dallo smart tag di 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)

Annotazioni

Con alcune righe di codice è possibile aumentare GridView in modo che colori le righe del prodotto in base al relativo PriceQuartile valore. Potremmo colorare quei prodotti nel primo quartile di un verde leggero, quelli nel secondo quartile di un giallo leggero e così via. Ti invito a dedicare un po' di tempo per aggiungere questa funzionalità. Se hai bisogno di un ripasso sulla formattazione di un controllo GridView, consulta l'esercitazione Formattazione personalizzata basata sui dati.

Approccio alternativo: creazione di un altro tableAdapter

Come illustrato in questa esercitazione, quando si aggiunge un metodo a un TableAdapter che restituisce campi dati diversi da quelli definiti dalla query principale, è possibile aggiungere colonne corrispondenti a DataTable. Questo approccio, tuttavia, funziona correttamente 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 dalla 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, anziché 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. Le pagine ASP.NET che avevano bisogno di ottenere informazioni sul prodotto con il quartile di prezzo avrebbero usato il ProductsWithPriceQuartileTableAdapter, mentre quelle che non ne avevano bisogno avrebbero continuato a usare il ProductsTableAdapter.

Aggiungendo un nuovo TableAdapter, le tabelle DataTable rimangono intatte e le relative colonne rispecchiano con precisione i campi dati restituiti dai relativi metodi TableAdapter. Tuttavia, i tableadapter aggiuntivi possono introdurre attività e funzionalità ripetitive. Ad esempio, se anche le pagine di ASP.NET che visualizzano la PriceQuartile colonna devono fornire supporto per l'inserimento, l'aggiornamento e l'eliminazione, è necessario che ProductsWithPriceQuartileTableAdapterle InsertCommand relative proprietà , UpdateCommande DeleteCommand siano configurate correttamente. Anche se queste proprietà rispecchiano le ProductsTableAdapter proprietà, questa configurazione introduce un passaggio aggiuntivo. Inoltre, esistono ora 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 in NorthwindWithSprocs DataSet che illustra questo approccio alternativo.

Riassunto

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 al CategoriesTableAdapter che, oltre ai campi dati della query principale, restituiva un campo NumberOfProducts che indicava il numero di prodotti associati a ogni categoria. In questa esercitazione è stato esaminato l'aggiunta di un metodo nell'oggetto ProductsTableAdapter 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 utilizzare stored procedure in TableAdapter. 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, ha lavorato 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 a mitchell@4GuysFromRolla.com.

Grazie speciale a

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

PrecedenteAvanti