Aanvullende gegevenstabelkolommen toevoegen (VB)

door Scott Mitchell

PDF downloaden

Wanneer u de wizard TableAdapter gebruikt om een getypte gegevensset te maken, bevat de bijbehorende gegevenstabel de kolommen die worden geretourneerd door de hoofddatabasequery. Er zijn echter situaties waarin de Gegevenstabel extra kolommen moet bevatten. In deze zelfstudie leert u waarom opgeslagen procedures worden aanbevolen wanneer we extra Gegevenstabelkolommen nodig hebben.

Introductie

Wanneer u een TableAdapter toevoegt aan een getypte gegevensset, wordt het bijbehorende schema van de gegevenstabel bepaald door de hoofdquery van TableAdapter. Als de hoofdquery bijvoorbeeld gegevensvelden A, B en C retourneert, bevat de gegevenstabel drie bijbehorende kolommen met de naam A, B en C. Naast de hoofdquery kan een TableAdapter aanvullende query's bevatten die mogelijk een subset van de gegevens retourneren op basis van een bepaalde parameter. Naast de ProductsTableAdapter hoofdquery, die bijvoorbeeld informatie over alle producten retourneert, bevat het ook methoden zoals GetProductsByCategoryID(categoryID) en GetProductByProductID(productID), die specifieke productinformatie retourneren op basis van een opgegeven parameter.

Het model van het gegevenstabelschema weerspiegelt de hoofdquery van TableAdapters goed als alle TableAdapter-methoden dezelfde of minder gegevensvelden retourneren dan de velden die zijn opgegeven in de hoofdquery. Als een TableAdapter-methode aanvullende gegevensvelden moet retourneren, moeten we het schema van de gegevenstabel dienovereenkomstig uitbreiden. In de zelfstudie Master/Detail Using a Bulleted List of Master Records with a Details DataList voegden we een methode toe aan CategoriesTableAdapter die de gegevensvelden CategoryID, CategoryName en Description retourneerde die in de hoofdquery zijn gedefinieerd, plus NumberOfProducts, een extra gegevensveld dat het aantal producten rapporteerde dat aan elke categorie is gekoppeld. We hebben handmatig een nieuwe kolom toegevoegd aan de CategoriesDataTable kolom om de waarde van het NumberOfProducts gegevensveld van deze nieuwe methode vast te leggen.

Zoals besproken in de zelfstudie Bestanden uploaden , moet u goed letten op TableAdapters die ad-hoc SQL-instructies gebruiken en methoden hebben waarvan de gegevensvelden niet precies overeenkomen met de hoofdquery. Als de wizard TableAdapter-configuratie opnieuw wordt uitgevoerd, worden alle methoden van TableAdapter bijgewerkt, zodat de lijst met gegevensvelden overeenkomt met de hoofdquery. Daarom worden methoden met aangepaste kolomlijsten teruggezet naar de kolomlijst van de hoofdquery en worden de verwachte gegevens niet geretourneerd. Dit probleem treedt niet op bij het gebruik van opgeslagen procedures.

In deze zelfstudie bekijken we hoe u een DataTable-schema kunt uitbreiden om extra kolommen op te nemen. Vanwege de broosheid van de TableAdapter bij het gebruik van ad-hoc SQL-instructies, gebruiken we in deze zelfstudie opgeslagen procedures. Raadpleeg de zelfstudies Nieuwe opgeslagen procedures maken voor de Getypte DataSet's TableAdapters en Bestaande opgeslagen procedures gebruiken voor de Getypte DataSet's TableAdapters voor meer informatie over het configureren van een TableAdapter voor het gebruik van opgeslagen procedures.

Stap 1: EenPriceQuartilekolom toevoegen aan deProductsDataTable

In de zelfstudie Nieuwe opgeslagen procedures maken voor de Getypte DataSet's TableAdapters hebben we een getypte gegevensset gemaakt met de naam NorthwindWithSprocs. Deze DataSet bevat momenteel twee gegevenstabellen: ProductsDataTable en EmployeesDataTable. De ProductsTableAdapter heeft de volgende drie methoden:

• GetProducts - de hoofdquery, die alle records uit de Products tabel retourneert
• GetProductsByCategoryID(categoryID) - retourneert alle producten met de opgegeven categorie-id.
• GetProductByProductID(productID) - retourneert het specifieke product met de opgegeven product-id.

De hoofdquery en de twee extra methoden retourneren allemaal dezelfde set gegevensvelden, namelijk alle kolommen uit de Products tabel. Er zijn geen gecorreleerde subquery's of JOIN-elementen die gerelateerde gegevens ophalen uit de Categories- of Suppliers-tabellen. Daarom heeft de ProductsDataTable kolom een corresponderende kolom voor elk veld in de Products tabel.

Voor deze zelfstudie voegen we een methode toe aan de ProductsTableAdapter genaamd GetProductsWithPriceQuartile, die alle producten retourneert. Naast de standaardvelden voor productgegevens, zal GetProductsWithPriceQuartile ook een PriceQuartile gegevensveld bevatten dat aangeeft in welk kwartiel de prijs van het product valt. Zo zullen die producten waarvan de prijzen in de duurste 25% vallen, een waarde van 1 hebben, terwijl de producten waarvan de prijzen in de onderste 25% vallen, een waarde van 4 hebben. Voordat we ons zorgen maken over het maken van de opgeslagen procedure om deze informatie te retourneren, moeten we echter eerst de ProductsDataTable kolom bijwerken om de PriceQuartile resultaten op te slaan wanneer de GetProductsWithPriceQuartile methode wordt gebruikt.

Open de NorthwindWithSprocs DataSet en klik met de rechtermuisknop op de ProductsDataTable. Kies Toevoegen in het contextmenu en kies Vervolgens Kolom.

Afbeelding 1: Voeg een nieuwe kolom toe aan de ProductsDataTable afbeelding (klik om de volledige afbeelding weer te geven)

Hiermee voegt u een nieuwe kolom toe aan de gegevenstabel met de naam Kolom1 van het type System.String. We moeten de naam van deze kolom bijwerken naar PriceQuartile en het bijbehorende type System.Int32 , omdat deze wordt gebruikt voor het opslaan van een getal tussen 1 en 4. Selecteer de zojuist toegevoegde kolom in het ProductsDataTable venster Eigenschappen en stel in het venster Eigenschappen de Name eigenschap in op PriceQuartile en de DataType eigenschap op System.Int32.

Afbeelding 2: De nieuwe kolom en NameDataType eigenschappen instellen (klik om de afbeelding op volledige grootte weer te geven)

Zoals in afbeelding 2 wordt weergegeven, zijn er aanvullende eigenschappen die kunnen worden ingesteld, bijvoorbeeld of de waarden in de kolom uniek moeten zijn, als de kolom een kolom met automatische verhoging is, of databasewaarden NULL al dan niet zijn toegestaan, enzovoort. Laat deze waarden ingesteld op de standaardwaarden.

Stap 2: deGetProductsWithPriceQuartilemethode maken

Nu de ProductsDataTable kolom is bijgewerkt om de PriceQuartile kolom op te nemen, zijn we klaar om de GetProductsWithPriceQuartile methode te maken. Klik eerst met de rechtermuisknop op TableAdapter en kies Query toevoegen in het contextmenu. Hiermee wordt de wizard voor het configureren van TableAdapter-query's weergegeven, waarbij eerst wordt gevraagd of u ad-hoc SQL-instructies of een nieuwe of bestaande opgeslagen procedure wilt gebruiken. Omdat we nog geen opgeslagen procedure hebben die de prijskwartielgegevens retourneert, laten we de TableAdapter deze opgeslagen procedure voor ons maken. Selecteer de optie Nieuwe opgeslagen procedure maken en klik op Volgende.

Afbeelding 3: Instrueer de wizard TableAdapter om de opgeslagen procedure voor ons te maken (klik hier om de volledige afbeelding weer te geven)

In het volgende scherm, weergegeven in afbeelding 4, vraagt de wizard ons welk type query moet worden toegevoegd. Omdat de GetProductsWithPriceQuartile methode alle kolommen en records uit de Products tabel retourneert, selecteert u de optie SELECT die rijen retourneert en klikt u op Volgende.

Afbeelding 4: Onze query is een SELECT instructie die meerdere rijen retourneert (klik om de afbeelding op volledige grootte weer te geven)

Vervolgens wordt u om de SELECT query gevraagd. Voer de volgende query in de wizard in:

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

De bovenstaande query maakt gebruik van de nieuwe NTILE functie van SQL Server 2005 om de resultaten te verdelen in vier groepen waarbij de groepen worden bepaald door de UnitPrice waarden die in aflopende volgorde worden gesorteerd.

Helaas weet de opbouwfunctie voor query's niet hoe het OVER trefwoord moet worden geparseerd en wordt er een fout weergegeven bij het parseren van de bovenstaande query. Voer daarom de bovenstaande query rechtstreeks in het tekstvak in de wizard in zonder de opbouwfunctie voor query's te gebruiken.

Opmerking

Zie voor meer informatie over NTILE en SQL Server 2005 andere classificatiefuncties ROW_NUMBER (Transact-SQL) en de sectie Ranking Functions van sql Server 2005 Books Online.

Nadat u de SELECT query hebt ingevoerd en op Volgende hebt geklikt, vereist de wizard dat we een naam opgeven voor de opslagprocedure die wordt gemaakt. Geef de nieuwe opgeslagen procedure Products_SelectWithPriceQuartile een naam en klik op Volgende.

Afbeelding 5: Geef de opgeslagen procedure Products_SelectWithPriceQuartile een naam (klik om de afbeelding op volledige grootte weer te geven)

Ten slotte wordt u gevraagd om de TableAdapter-methoden een naam te geven. Laat de selectievakjes Gegevenstabel invullen en Gegevenstabel retourneren beide ingeschakeld en geef de methoden FillWithPriceQuartile en GetProductsWithPriceQuartile een naam.

Afbeelding 6: Geef de TableAdapter-methoden een naam en klik op Voltooien (klik om de afbeelding op volledige grootte weer te geven)

Klik op Voltooien om de wizard te voltooien met de opgegeven SELECT query, de benoemde opgeslagen procedure en TableAdapter-methoden. Op dit moment krijgt u mogelijk een waarschuwing of twee van de wizard waarin staat dat de OVER SQL-constructie of -instructie niet wordt ondersteund. Deze waarschuwingen kunnen worden genegeerd.

Nadat de wizard is voltooid, moet de TableAdapter de FillWithPriceQuartile en GetProductsWithPriceQuartile methoden bevatten en moet de database een opgeslagen procedure met de naam Products_SelectWithPriceQuartilebevatten. Neem even de tijd om te controleren of de TableAdapter inderdaad deze nieuwe methode bevat en of de opgeslagen procedure correct is toegevoegd aan de database. Als u de opgeslagen procedure niet ziet bij het controleren van de database, klikt u met de rechtermuisknop op de map Opgeslagen procedures en kiest u Vernieuwen.

Afbeelding 7: Controleer of er een nieuwe methode is toegevoegd aan de TableAdapter

Afbeelding 8: Zorg ervoor dat de database de Products_SelectWithPriceQuartile opgeslagen procedure bevat (klik om de volledige afbeelding weer te geven)

Opmerking

Een van de voordelen van het gebruik van opgeslagen procedures in plaats van ad-hoc SQL-instructies is dat het opnieuw uitvoeren van de wizard TableAdapter-configuratie de lijsten met opgeslagen procedures niet wijzigt. Controleer dit door met de rechtermuisknop op TableAdapter te klikken, de optie Configureren te kiezen in het contextmenu om de wizard te starten en vervolgens op Voltooien te klikken om deze te voltooien. Ga vervolgens naar de database en bekijk de Products_SelectWithPriceQuartile opgeslagen procedure. Houd er rekening mee dat de kolomlijst niet is gewijzigd. Als we ad-hoc SQL-instructies hadden gebruikt, zou het opnieuw uitvoeren van de wizard TableAdapter-configuratie deze querykolomlijst hebben teruggezet zodat deze overeenkomt met de kolomlijst van de hoofdquery. Daardoor zou de NTILE-instructie worden verwijderd uit de query die door de GetProductsWithPriceQuartile methode wordt gebruikt.

Wanneer de methode Data Access Layer GetProductsWithPriceQuartile wordt aangeroepen, voert TableAdapter de Products_SelectWithPriceQuartile opgeslagen procedure uit en voegt een rij toe aan de ProductsDataTable voor elke geretourneerde record. De gegevensvelden die door de opgeslagen procedure zijn geretourneerd, worden toegewezen aan de kolommen van ProductsDataTable. Omdat er een PriceQuartile gegevensveld is dat wordt geretourneerd door de opgeslagen procedure, wordt de waarde toegewezen aan de ProductsDataTable kolom s PriceQuartile .

Voor de TableAdapter-methoden waarvan de query's het gegevensveld PriceQuartile niet retourneren, is de waarde van de PriceQuartile-kolom de waarde die is opgegeven door zijn DefaultValue-eigenschap. Zoals in afbeelding 2 wordt weergegeven, is deze waarde ingesteld op DBNull, de standaardwaarde. Als u liever een andere standaardwaarde wilt, stelt u de DefaultValue eigenschap dienovereenkomstig in. Zorg ervoor dat de DefaultValue waarde geldig is, gegeven de kolom s DataType (bijvoorbeeld System.Int32 voor de PriceQuartile kolom).

Op dit moment hebben we de benodigde stappen uitgevoerd voor het toevoegen van een extra kolom aan een gegevenstabel. Om te controleren of deze extra kolom werkt zoals verwacht, laten we een ASP.NET-pagina maken waarin de naam, prijs en prijs kwartiel van elk product worden weergegeven. Voordat we dat doen, moeten we echter eerst de bedrijfslogicalaag bijwerken om een methode op te nemen waarmee de DAL-methode GetProductsWithPriceQuartile wordt aangeroepen. We werken de BLL vervolgens bij in stap 3 en maken vervolgens de pagina ASP.NET in stap 4.

Stap 3: De bedrijfslogicalaag uitbreiden

Voordat we de nieuwe GetProductsWithPriceQuartile methode uit de presentatielaag gebruiken, moeten we eerst een bijbehorende methode toevoegen aan de BLL. Open het ProductsBLLWithSprocs klassebestand en voeg de volgende code toe:

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

Net als de andere methoden ProductsBLLWithSprocsvoor het ophalen van gegevens, roept de GetProductsWithPriceQuartile methode gewoon de bijbehorende DAL-methode GetProductsWithPriceQuartile aan en retourneert de resultaten.

Stap 4: de prijskwartielinformatie weergeven in een ASP.NET webpagina

Nu de BLL-toevoeging is voltooid, zijn we klaar om een ASP.NET pagina te maken waarin het prijskwartiel voor elk product wordt weergegeven. Open de AddingColumns.aspx pagina in de AdvancedDAL map en sleep een GridView van de Werkset naar de Ontwerper, waarbij u de eigenschap ID instelt op Products. Bind deze vanuit de infolabel van GridView aan een nieuwe ObjectDataSource met de naam ProductsDataSource. Configureer de ObjectDataSource om de ProductsBLLWithSprocs klasse te gebruiken en de GetProductsWithPriceQuartile methode uit te voeren. Omdat dit een alleen-lezenraster is, stelt u de vervolgkeuzelijsten in de tabbladen UPDATE, INSERT en DELETE in op (Geen).

Afbeelding 9: De ObjectDataSource configureren voor het gebruik van de ProductsBLLWithSprocs klasse (klik om de volledige afbeelding weer te geven)

Afbeelding 10: Productgegevens ophalen uit de GetProductsWithPriceQuartile methode (klik om de afbeelding op volledige grootte weer te geven)

Nadat u de wizard Gegevensbron configureren hebt voltooid, voegt Visual Studio automatisch een BoundField- of CheckBoxField-veld toe aan de GridView voor elk van de gegevensvelden die door de methode worden geretourneerd. Een van deze gegevensvelden is PriceQuartile, de kolom die we in stap 1 aan ProductsDataTable hebben toegevoegd.

Bewerk de velden van GridView, verwijder alle, behalve de ProductName, UnitPriceen PriceQuartile BoundFields. Configureer het UnitPrice BoundField om de waarde als valuta op te maken en laat de UnitPrice en PriceQuartile BoundFields respectievelijk rechts en gecentreerd uitlijnen. Werk ten slotte de resterende BoundFields-eigenschappen HeaderText bij naar respectievelijk Product, Price en Price Quartile. Schakel ook het selectievakje 'Sorteren inschakelen' in vanuit de GridView-smarttag.

Na deze wijzigingen moeten de declaratieve markeringen van GridView en ObjectDataSource er als volgt uitzien:

<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>

In afbeelding 11 ziet u deze pagina wanneer deze wordt bezocht via een browser. Houd er rekening mee dat in eerste instantie de producten in aflopende volgorde worden besteld, waarbij aan elk product een geschikte PriceQuartile waarde is toegewezen. Natuurlijk kunnen deze gegevens worden gesorteerd op andere criteria, waarbij de kolomwaarde 'Prijskwartiel' nog steeds de rangorde van het product ten opzichte van de prijs weerspiegelt (zie afbeelding 12).

Afbeelding 11: De producten zijn besteld op basis van hun prijzen (klik om de volledige afbeelding weer te geven)

Afbeelding 12: De producten zijn geordend op basis van hun namen (klik om de volledige afbeelding weer te geven)

Opmerking

Met een paar regels code kunnen we de GridView uitbreiden zodat deze de productrijen op basis van hun PriceQuartile waarde heeft gekleurd. We zouden die producten in het eerste kwartiel lichtgroen kunnen kleuren, die in het tweede kwartiel lichtgeel, enzovoort. Ik moedig u aan deze functionaliteit toe te voegen. Als u een opfriscursus nodig hebt voor het formatteren van een GridView, raadpleegt u de zelfstudie Aangepaste opmaak op basis van gegevens.

Een alternatieve benadering: een andere TableAdapter maken

Zoals we in deze zelfstudie hebben gezien, kunnen we bij het toevoegen van een methode aan een TableAdapter die andere gegevensvelden retourneert dan de gegevensvelden die door de hoofdquery zijn gespeld, overeenkomende kolommen toevoegen aan de gegevenstabel. Een dergelijke benadering werkt echter alleen goed als er een klein aantal methoden in de TableAdapter zijn die verschillende gegevensvelden retourneren en als deze alternatieve gegevensvelden niet te veel verschillen van de hoofdquery.

In plaats van kolommen toe te voegen aan de DataTable, kunt u in plaats daarvan een andere TableAdapter toevoegen aan de DataSet die de methoden bevat uit de eerste TableAdapter die verschillende gegevensvelden retourneert. Voor deze handleiding konden we, in plaats van de kolom toe te voegen aan de PriceQuartile (waar ProductsDataTable alleen door de GetProductsWithPriceQuartile methode wordt gebruikt), ook een extra TableAdapter toevoegen aan de DataSet genaamd ProductsWithPriceQuartileTableAdapter, waarbij de Products_SelectWithPriceQuartile opgeslagen procedure wordt gebruikt als hoofdquery. ASP.NET-pagina's die productinformatie moesten ophalen met het prijskwartiel, zouden de ProductsWithPriceQuartileTableAdapter gebruiken, terwijl de pagina's die dat niet hoefden, het ProductsTableAdapter konden blijven gebruiken.

Door een nieuwe TableAdapter toe te voegen, blijven de DataTables ongetarneerd en zijn kolommen nauwkeurig afgestemd op de gegevensvelden die door hun TableAdapter-methoden worden geretourneerd. Aanvullende TableAdapters kunnen echter terugkerende taken en functionaliteit introduceren. Als de ASP.NET-pagina's waarop de PriceQuartile-kolom werd weergegeven bijvoorbeeld ook ondersteuning voor invoegen, bijwerken en verwijderen moesten bieden, dan moesten de eigenschappen ProductsWithPriceQuartileTableAdapter, InsertCommand en UpdateCommand van de DeleteCommand correct worden geconfigureerd. Hoewel deze eigenschappen de ProductsTableAdapter s spiegelen, introduceert deze configuratie een extra stap. Bovendien zijn er nu twee manieren om een product bij te werken, te verwijderen of toe te voegen aan de database, via de ProductsTableAdapter en ProductsWithPriceQuartileTableAdapter klassen.

De download voor deze zelfstudie bevat een ProductsWithPriceQuartileTableAdapter klasse in de NorthwindWithSprocs DataSet die deze alternatieve benadering illustreert.

Samenvatting

In de meeste scenario's retourneren alle methoden in een TableAdapter dezelfde set gegevensvelden, maar er zijn momenten waarop een bepaalde methode of twee mogelijk een extra veld moeten retourneren. In de zelfstudie Master-Detail met een opsomminglijst van hoofdrecords en een Details DataList hebben we bijvoorbeeld een methode toegevoegd aan de CategoriesTableAdapter, die naast de data velden van de hoofdquery, een NumberOfProducts-veld retourneerde waarin het aantal producten werd gerapporteerd dat aan elke categorie was gekoppeld. In deze zelfstudie hebben we gekeken naar het toevoegen van een methode in het ProductsTableAdapter veld dat naast de gegevensvelden van de hoofdquery een PriceQuartile veld heeft geretourneerd. Als u extra gegevensvelden wilt vastleggen die worden geretourneerd door de TableAdapter-methoden, moeten we overeenkomende kolommen toevoegen aan de gegevenstabel.

Als u van plan bent kolommen handmatig toe te voegen aan de DataTable, is het raadzaam dat TableAdapter opgeslagen procedures gebruikt. Als de TableAdapter gebruikmaakt van ad-hoc SQL-instructies, worden op elk moment dat de wizard TableAdapter-configuratie wordt uitgevoerd, alle gegevensveldlijsten van methoden teruggezet naar de gegevensvelden die worden geretourneerd door de hoofdquery. Dit probleem geldt niet voor opgeslagen procedures. Daarom worden ze aanbevolen en zijn ze gebruikt in deze zelfstudie.

Veel plezier met programmeren!

Over de auteur

Scott Mitchell, auteur van zeven ASP/ASP.NET-boeken en oprichter van 4GuysFromRolla.com, werkt sinds 1998 met Microsoft-webtechnologieën. Scott werkt als onafhankelijk consultant, trainer en schrijver. Zijn laatste boek is Sams Teach Yourself ASP.NET 2.0 in 24 uur. Hij kan worden bereikt op mitchell@4GuysFromRolla.com.

Speciale dank aan

Deze tutorialreeks is beoordeeld door veel behulpzame beoordelers. Hoofdrevisoren voor deze zelfstudie waren Randy Schmidt, Jacky Goor, Bernadette Leigh en Hilton Giesenow. Bent u geïnteresseerd in het bekijken van mijn aanstaande MSDN-artikelen? Zo ja, laat iets van je horen via mitchell@4GuysFromRolla.com.

VorigeVolgende