Lägga till ytterligare datatabellkolumner (VB)

av Scott Mitchell

Ladda ned PDF

När du använder guiden TableAdapter för att skapa en Typed DataSet innehåller motsvarande DataTable de kolumner som returneras av huvuddatabasfrågan. Men det finns tillfällen då DataTable behöver inkludera ytterligare kolumner. I den här självstudien lär vi oss varför lagrade procedurer rekommenderas när vi behöver ytterligare DataTable-kolumner.

Inledning

När du lägger till en TableAdapter i en Typed DataSet bestäms motsvarande DataTable-schema av TableAdapters huvudfråga. Om huvudfrågan till exempel returnerar datafälten A, B och C har DataTable tre motsvarande kolumner med namnet A, B och C. Förutom huvudfrågan kan en TableAdapter innehålla ytterligare frågor som returnerar kanske en delmängd av data baserat på någon parameter. Förutom huvudfrågan, som returnerar information om alla produkter, innehåller den till exempel ProductsTableAdapter även metoder som GetProductsByCategoryID(categoryID) och GetProductByProductID(productID), som returnerar specifik produktinformation baserat på en angiven parameter.

Modellen med datatabellschemat återspeglar TableAdapters huvudfråga fungerar bra om alla TableAdapter-metoder returnerar samma eller färre datafält än de som anges i huvudfrågan. Om en TableAdapter-metod behöver returnera ytterligare datafält bör vi expandera DataTable-schemat i enlighet med detta. I självstudien Master/Detail Using a Bulleted List of Master Records with a Details DataList (Huvud-/detaljlista med hjälp av en punktlista över huvudposter med en detaljerad datalista) lade vi till en metod till CategoriesTableAdapter som returnerade datafälten CategoryID, CategoryName, och Description som definierades i den huvudsakliga frågan, samt NumberOfProducts, ett ytterligare datafält som rapporterade antalet produkter associerade med varje kategori. Vi lade manuellt till en ny kolumn i CategoriesDataTable för att samla in datafältvärdet för NumberOfProducts från den här nya metoden.

Som beskrivs i självstudien Ladda upp filer måste du vara mycket försiktig med TableAdapters som använder ad hoc SQL-instruktioner och har metoder vars datafält inte exakt matchar huvudfrågan. Om guiden TableAdapter-konfiguration körs igen uppdateras alla TableAdapter-metoder så att deras datafältlista matchar huvudfrågan. Därför återgår alla metoder med anpassade kolumnlistor till huvudfrågans kolumnlista och returnerar inte förväntade data. Det här problemet uppstår inte när du använder lagrade procedurer.

I den här självstudien ska vi titta på hur du utökar ett DataTable-schema till att omfatta ytterligare kolumner. På grund av TableAdapters känslighet vid användning av ad hoc SQL-instruktioner kommer vi i den här självstudien att använda lagrade procedurer. Mer information om hur du konfigurerar en TableAdapter för att använda lagrade procedurer finns i självstudierna Skapa nya lagrade procedurer för Typed DataSets TableAdapters och Använda befintliga lagrade procedurer för Typed DataSets TableAdapters.

Steg 1: Lägga till enPriceQuartilekolumn iProductsDataTable

I självstudien Skapa nya lagrade procedurer för Typed DataSet s TableAdapters skapade vi en Typed DataSet med namnet NorthwindWithSprocs. Den här datauppsättningen innehåller för närvarande två DataTables: ProductsDataTable och EmployeesDataTable. Har ProductsTableAdapter följande tre metoder:

• GetProducts – huvudfrågan, som returnerar alla poster från Products tabellen
• GetProductsByCategoryID(categoryID) – returnerar alla produkter med angivet categoryID.
• GetProductByProductID(productID) – returnerar den specifika produkten med angivet productID.

Huvudfrågan och de två ytterligare metoderna returnerar alla samma uppsättning datafält, nämligen alla kolumner från Products tabellen. Det finns inga korrelerade underfrågor eller JOIN som hämtar relaterade data från tabellerna Categories eller Suppliers . Därför ProductsDataTable har en motsvarande kolumn för varje fält i Products tabellen.

I den här handledningen ska vi lägga till en metod i ProductsTableAdapter som heter GetProductsWithPriceQuartile och som returnerar alla produkter. Förutom standarddatafälten GetProductsWithPriceQuartile för produkten innehåller det även ett PriceQuartile datafält som anger under vilken kvartil produktens pris faller. Till exempel kommer de produkter vars priser är i de dyraste 25% att ha ett PriceQuartile värde på 1, medan de vars priser faller i de nedre 25% kommer att ha ett värde på 4. Innan vi oroar oss för att skapa den lagrade proceduren för att returnera den här informationen måste vi dock först uppdatera ProductsDataTable för att inkludera en kolumn för att lagra PriceQuartile resultatet när GetProductsWithPriceQuartile metoden används.

NorthwindWithSprocs Öppna DataSet och högerklicka på ProductsDataTable. Välj Lägg till på snabbmenyn och välj sedan Kolumn.

Bild 1: Lägg till en ny kolumn i ProductsDataTable (Klicka om du vill visa en bild i full storlek)

Då läggs en ny kolumn till i DataTable med namnet Column1 av typen System.String. Vi måste uppdatera den här kolumnens namn till PriceQuartile och dess typ till System.Int32, då det kommer att användas för att lagra ett tal mellan 1 och 4. Välj den nyligen tillagda kolumnen i ProductsDataTable och i fönstret Egenskaper anger du Name egenskapen till PriceQuartile och egenskapen DataType till System.Int32.

Ange egenskaperna för Namn och Datatyp hos den nya kolumnen

Bild 2: Ange nya kolumner Name och DataType egenskaper (Klicka om du vill visa en bild i full storlek)

Som bild 2 visar finns det ytterligare egenskaper som kan anges, till exempel om värdena i kolumnen måste vara unika, om kolumnen är en automatisk inkrementell kolumn, om databasvärden NULL tillåts eller inte och så vidare. Låt dessa värden vara inställda på standardvärdena.

Steg 2: SkapaGetProductsWithPriceQuartilemetoden

Nu när ProductsDataTable har uppdaterats för att inkludera PriceQuartile kolumnen är vi redo att skapa GetProductsWithPriceQuartile metoden. Börja med att högerklicka på TableAdapter och välja Lägg till fråga från snabbmenyn. Då visas guiden TableAdapter Query Configuration, som först frågar oss om vi vill använda ad hoc SQL-instruktioner eller en ny eller befintlig lagrad procedur. Eftersom vi ännu inte har en lagrad procedur som returnerar priskvartildata låter vi TableAdapter skapa den här lagrade proceduren åt oss. Välj alternativet Skapa ny lagrad procedur och klicka på Nästa.

Bild 3: Instruera guiden TableAdapter att skapa den lagrade proceduren för oss (klicka om du vill visa en bild i full storlek)

På den efterföljande skärmen, som visas i bild 4, frågar guiden oss vilken typ av fråga som ska läggas till. GetProductsWithPriceQuartile Eftersom metoden returnerar alla kolumner och poster från Products tabellen väljer du alternativet VÄLJ som returnerar rader och klickar på Nästa.

Bild 4: Vår fråga är en SELECT instruktion som returnerar flera rader (klicka om du vill visa en bild i full storlek)

Därefter uppmanas vi att ange SELECT frågan. Ange följande sökfråga i guiden:

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

Ovanstående fråga använder sql Server 2005 s nya NTILE funktion för att dela upp resultatet i fyra grupper där grupperna bestäms av de UnitPrice värden som sorteras i fallande ordning.

Query Builder vet tyvärr inte hur nyckelordet OVER parsas och visar ett fel när ovanstående fråga parsas. Ange därför ovanstående fråga direkt i textrutan i guiden utan att använda Query Builder.

Anmärkning

Mer information om andra rankningsfunktioner för NTILE och SQL Server 2005 finns i ROW_NUMBER (Transact-SQL) och avsnittet Ranking Functions från SQL Server 2005 Books Online.

När du har angett frågan SELECT och klickat på Nästa ber guiden oss att ange ett namn för den lagrade procedur som skapas. Namnge den nya lagrade proceduren Products_SelectWithPriceQuartile och klicka på Nästa.

Bild 5: Namnge den lagrade proceduren Products_SelectWithPriceQuartile (Klicka om du vill visa en bild i full storlek)

Slutligen uppmanas vi att namnge TableAdapter-metoderna. Låt kryssrutorna Fyll en datatabell och Returnera en datatabell vara markerade och namnge metoderna FillWithPriceQuartile och GetProductsWithPriceQuartile.

Bild 6: Namnge TableAdapter-metoderna och klicka på Slutför (klicka om du vill visa en bild i full storlek)

Med den SELECT angivna frågan och den lagrade proceduren och TableAdapter-metoderna namngivna klickar du på Slutför för att slutföra guiden. Nu kan du få en varning eller två från guiden som säger att OVER SQL-konstruktionen eller -instruktionen inte stöds. Dessa varningar kan ignoreras.

När du har slutfört guiden bör TableAdapter innehålla FillWithPriceQuartile metoderna och GetProductsWithPriceQuartile och databasen ska innehålla en lagrad procedur med namnet Products_SelectWithPriceQuartile. Ta en stund att kontrollera att TableAdapter verkligen innehåller den här nya metoden och att den lagrade proceduren har lagts till korrekt i databasen. Om du inte ser den lagrade proceduren när du kontrollerar databasen kan du högerklicka på mappen Lagrade procedurer och välja Uppdatera.

Bild 7: Kontrollera att en ny metod har lagts till i TableAdapter

Bild 8: Kontrollera att databasen innehåller den Products_SelectWithPriceQuartile lagrade proceduren (klicka om du vill visa en bild i full storlek)

Anmärkning

En av fördelarna med att använda lagrade procedurer i stället för ad hoc SQL-instruktioner är att om du kör tableAdapter Configuration-guiden ändras inte kolumnlistorna för lagrade procedurer. Kontrollera detta genom att högerklicka på TableAdapter, välja alternativet Konfigurera på snabbmenyn för att starta guiden och sedan klicka på Slutför för att slutföra den. Gå sedan till databasen och visa den Products_SelectWithPriceQuartile lagrade proceduren. Observera att dess kolumnlista inte har ändrats. Om vi hade använt ad hoc SQL-instruktioner och kört om TableAdapter Configuration wizard, skulle frågans kolumnlista ha återställts för att matcha huvudfrågekolumnlistan, vilket skulle ha tagit bort NTILE-instruktionen från den fråga som används av GetProductsWithPriceQuartile-metoden.

När Data Access Layer-metoden GetProductsWithPriceQuartile anropas kör TableAdapter den lagrade proceduren Products_SelectWithPriceQuartile och lägger till en rad i ProductsDataTable för varje returnerad post. De datafält som returneras av den lagrade proceduren mappas till ProductsDataTable kolumnerna. Eftersom ett PriceQuartile datafält returneras från den lagrade proceduren tilldelas dess värde till ProductsDataTable kolumnen s PriceQuartile .

För de TableAdapter-metoder vars frågor inte returnerar ett PriceQuartile datafält PriceQuartile är kolumnens värde det värde som anges av dess DefaultValue egenskap. Som bild 2 visar är det här värdet inställt på DBNull, standardvärdet. Ange bara egenskapen DefaultValue om du föredrar ett annat standardvärde. Kontrollera bara att DefaultValue värdet är giltigt med tanke på kolumnerna DataType (dvs. System.Int32 för PriceQuartile kolumnen).

Nu har vi utfört de steg som krävs för att lägga till ytterligare en kolumn i en DataTable. För att verifiera att den här ytterligare kolumnen fungerar som förväntat kan vi skapa en ASP.NET sida som visar varje produkts namn, pris och priskvartil. Innan vi gör det måste vi dock först uppdatera affärslogiklagret för att inkludera en metod som anropar dal-metoden GetProductsWithPriceQuartile . Vi uppdaterar BLL:n härnäst i steg 3 och skapar sedan sidan ASP.NET i steg 4.

Steg 3: Utöka affärslogiklagret

Innan vi använder den nya GetProductsWithPriceQuartile metoden från presentationsskiktet bör vi först lägga till en motsvarande metod i BLL:n. ProductsBLLWithSprocs Öppna klassfilen och lägg till följande kod:

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

Precis som de andra datahämtningsmetoderna ProductsBLLWithSprocs i GetProductsWithPriceQuartileanropar metoden helt enkelt DAL:s motsvarande GetProductsWithPriceQuartile metod och returnerar dess resultat.

Steg 4: Visa priskvartilinformation på en ASP.NET webbsida

När BLL-tillägget är klart är vi redo att skapa en ASP.NET sida som visar priskvartilen för varje produkt. Öppna sidan AddingColumns.aspx i AdvancedDAL mappen och dra en GridView från verktygslådan till designern och ange dess ID egenskap till Products. Från GridViews smarta tagg binder du den till en ny ObjectDataSource med namnet ProductsDataSource. Konfigurera ObjectDataSource för att använda ProductsBLLWithSprocs-klassens GetProductsWithPriceQuartile-metod. Eftersom detta kommer att vara ett skrivskyddat rutnät anger du rullgardinsmenyerna i flikarna för UPDATE, INSERT och DELETE till (Ingen).

Bild 9: Konfigurera ObjectDataSource att använda ProductsBLLWithSprocs klassen (Klicka om du vill visa en bild i full storlek)

Bild 10: Hämta produktinformation från GetProductsWithPriceQuartile metoden (Klicka om du vill visa en bild i full storlek)

När du har slutfört guiden Konfigurera datakälla lägger Visual Studio automatiskt till ett BoundField- eller CheckBoxField till GridView för vart och ett av de datafält som returneras av metoden. Ett av dessa datafält är PriceQuartile, vilket är kolumnen som vi lade till ProductsDataTable i steg 1.

Redigera Fälten i GridView och ta bort alla utom ProductName, UnitPriceoch PriceQuartile BoundFields. Konfigurera UnitPrice BoundField att formatera dess värde som en valuta, och ha UnitPrice och PriceQuartile BoundFields höger- respektive mittjusterade. Uppdatera slutligen de återstående BoundFields-egenskaperna HeaderText till Produkt, Pris respektive Priskvartil. Markera också kryssrutan Aktivera sortering från GridViews smarta tagg.

Efter dessa ändringar bör GridView och ObjectDataSources deklarativa markering se ut så här:

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

Bild 11 visar den här sidan när den besöks via en webbläsare. Observera att produkterna till en början sorteras efter priset i fallande ordning och att varje produkt har tilldelats ett lämpligt PriceQuartile värde. Naturligtvis kan dessa data sorteras efter andra kriterier med kolumnvärdet Price Quartile som fortfarande återspeglar produktens rangordning med avseende på pris (se bild 12).

Bild 11: Produkterna beställs efter sina priser (Klicka för att visa en bild i full storlek)

Bild 12: Produkterna sorteras efter deras namn (Klicka om du vill visa en bild i full storlek)

Anmärkning

Med några rader kod kan vi utöka GridView så att den färgar produktraderna baserat på deras PriceQuartile värde. Vi kan färga dessa produkter i den första kvartilen en ljusgrön, de i den andra kvartilen en ljusgul och så vidare. Jag rekommenderar att du tar en stund att lägga till den här funktionen. Om du behöver en genomgång av hur man formaterar en GridView, se självstudien Anpassad formatering baserat på data.

En alternativ metod – Skapa en annan TableAdapter

Som vi såg i den här självstudien kan vi lägga till motsvarande kolumner i DataTable när vi lägger till en metod i en TableAdapter som returnerar andra datafält än de som anges av huvudfrågan. En sådan metod fungerar dock bara bra om det finns ett litet antal metoder i TableAdapter som returnerar olika datafält och om dessa alternativa datafält inte varierar för mycket från huvudfrågan.

I stället för att lägga till kolumner i DataTable kan du i stället lägga till en annan TableAdapter till datauppsättningen som innehåller metoderna från den första TableAdapter som returnerar olika datafält. I den här självstudien kan vi i stället för att lägga till PriceQuartile kolumnen i ProductsDataTable (där den endast används av GetProductsWithPriceQuartile metoden) ha lagt till ytterligare en TableAdapter till datauppsättningen med namnet ProductsWithPriceQuartileTableAdapter som använde den Products_SelectWithPriceQuartile lagrade proceduren som huvudfråga. ASP.NET-sidor som behövde hämta produktinformation inklusive priskvartilen skulle använda ProductsWithPriceQuartileTableAdapter, medan de som inte gjorde det kunde fortsätta att använda ProductsTableAdapter.

Genom att lägga till en ny TableAdapter förblir DataTables obefläckade och deras kolumner speglar exakt de datafält som returneras av deras TableAdapter-metoder. Ytterligare TableAdapters kan dock introducera repetitiva uppgifter och funktioner. Om till exempel de ASP.NET sidor som visade PriceQuartile kolumnen också behövde tillhandahålla stöd för infoga, uppdatera och ta bort måste ProductsWithPriceQuartileTableAdapterInsertCommandegenskaperna , UpdateCommandoch DeleteCommand vara korrekt konfigurerade. Även om dessa egenskaper skulle spegla ProductsTableAdapter s, introducerar den här konfigurationen ett extra steg. Dessutom finns det nu två sätt att uppdatera, ta bort eller lägga till en produkt i databasen – via klasserna ProductsTableAdapter och ProductsWithPriceQuartileTableAdapter .

Nedladdningen för den här handledningen innehåller en ProductsWithPriceQuartileTableAdapter klass i NorthwindWithSprocs DataSet som illustrerar det här alternativet.

Sammanfattning

I de flesta scenarier returnerar alla metoder i en TableAdapter samma uppsättning datafält, men det finns tillfällen då en viss metod eller två kan behöva returnera ytterligare ett fält. I självstudiekursen Master/Detail Using a Bulleted List of Master Records with a Details DataList lade vi till en metod till CategoriesTableAdapter, som förutom huvudfrågans datafält, returnerade ett NumberOfProducts-fält som rapporterade antalet produkter som är associerade med varje kategori. I den här självstudien tittade vi på att lägga till en metod i ProductsTableAdapter som returnerade ett PriceQuartile fält utöver huvudfrågans datafält. För att samla in ytterligare datafält som returneras av TableAdapter-metoderna måste vi lägga till motsvarande kolumner i DataTable.

Om du planerar att lägga till kolumner i DataTable manuellt rekommenderar vi att TableAdapter använder lagrade procedurer. Om TableAdaptern använder ad hoc-SQL-instruktioner, återställs alla metoders datafältlistor till de datafält som returneras av huvudfrågan varje gång TableAdapter-konfigurationsguiden körs. Det här problemet gäller inte lagrade procedurer, vilket är anledningen till att de rekommenderas och användes i den här självstudien.

Lycka till med programmerandet!

Om författaren

Scott Mitchell, författare till sju ASP/ASP.NET-böcker och grundare av 4GuysFromRolla.com, har arbetat med Microsofts webbtekniker sedan 1998. Scott arbetar som oberoende konsult, tränare och författare. Hans senaste bok är Sams Teach Yourself ASP.NET 2.0 på 24 timmar. Han kan nås på mitchell@4GuysFromRolla.com.

Särskilt tack till

Den här självstudieserien granskades av många användbara granskare. Huvudgranskare för den här handledningen var Randy Schmidt, Jacky Goor, Bernadette Leigh och Hilton Giesenow. Vill du granska mina kommande MSDN-artiklar? Om så är fallet, hör av dig på mitchell@4GuysFromRolla.com.

FöregåendeNästa