Dodawanie dodatkowych kolumn tabeli DataTable (VB)

Autor : Scott Mitchell

Pobierz plik PDF

W przypadku używania Kreatora TableAdapter do tworzenia Typed DataSet, odpowiednia DataTable zawiera kolumny zwracane przez główne zapytanie bazy danych. Jednak czasami tabela DataTable musi zawierać dodatkowe kolumny. W tym samouczku uczymy się, dlaczego zaleca się używanie procedur składowanych, gdy potrzebujemy dodatkowych kolumn w DataTable.

Wprowadzenie

Podczas dodawania TableAdapter do Typizowanego Zestawu Danych odpowiedni schemat tabeli DataTable jest określany przez główne zapytanie TableAdapter. Jeśli na przykład główne zapytanie zwraca pola danych A, B i C, tabela DataTable będzie zawierać trzy odpowiadające im kolumny o nazwach A, B i C. Oprócz głównego zapytania narzędzie TableAdapter może zawierać dodatkowe zapytania, które zwracają, być może, podzbiór danych na podstawie określonego parametru. Na przykład oprócz ProductsTableAdapter głównego zapytania, które zwraca informacje o wszystkich produktach, zawiera również metody takie jak GetProductsByCategoryID(categoryID) i GetProductByProductID(productID), które zwracają określone informacje o produkcie na podstawie podanego parametru.

Model, w którym schemat DataTable jest zgodny z głównym zapytaniem TableAdapter, działa dobrze, jeśli wszystkie metody TableAdapter zwracają tyle samo lub mniej pól danych niż określone w zapytaniu głównym. Jeśli metoda TableAdapter musi zwrócić dodatkowe pola danych, należy odpowiednio rozwinąć schemat tabeli DataTable. W tutorialu Master/Detail Using a Bulleted List of Master Records with a Details DataList (Szczegóły datalist) dodaliśmy metodę do CategoriesTableAdapter zwracającą pola danych CategoryID, CategoryName i Description zdefiniowane w głównym zapytaniu oraz dodatkowe pole danych NumberOfProducts, które wskazywało liczbę produktów związanych z każdą kategorią. Ręcznie dodaliśmy nową kolumnę do CategoriesDataTable, aby przechwycić dane z pola NumberOfProducts stosując tę nową metodę.

Jak omówiono w samouczku Przesyłanie plików, należy przyłożyć dużą wagę do TableAdapters, które używają instrukcji SQL ad hoc i posiadają metody, których pola danych nie są precyzyjnie zgodne z głównym zapytaniem. Jeśli kreator konfiguracji tableAdapter zostanie uruchomiony ponownie, zaktualizuje wszystkie metody tableAdapter, aby ich lista pól danych była zgodna z głównym zapytaniem. W związku z tym wszystkie metody z dostosowanymi listami kolumn zostaną przywrócone do głównej listy kolumn zapytania i nie zwracają oczekiwanych danych. Ten problem nie występuje podczas korzystania z procedur przechowywanych.

W tym samouczku przyjrzymy się sposobom rozszerzania schematu tabeli DataTable w celu uwzględnienia dodatkowych kolumn. Ze względu na niestabilność TableAdapter podczas korzystania z instrukcji SQL typu ad hoc, w tym samouczku będziemy używać procedur składowanych. Zapoznaj się z samouczkami Tworzenie nowych procedur składowanych dla TableAdapterów typu Typed DataSet i Używanie istniejących procedur składowanych dla TableAdapterów typu Typed DataSet, aby uzyskać więcej informacji na temat konfigurowania klasy TableAdapter do korzystania z procedur składowanych.

Krok 1. DodawaniePriceQuartilekolumny doProductsDataTable

W samouczku „Tworzenie nowych procedur składowanych dla typowanych zestawów danych TableAdapter” utworzyliśmy typowany zestaw danych o nazwie . Ten zestaw danych zawiera obecnie dwie tabele danych: ProductsDataTable i EmployeesDataTable. Element ProductsTableAdapter ma następujące trzy metody:

• GetProducts — zapytanie główne, które zwraca wszystkie rekordy z Products tabeli
• GetProductsByCategoryID(categoryID) — zwraca wszystkie produkty o określonym identyfikatorze categoryID.
• GetProductByProductID(productID) - zwraca określony produkt o określonym identyfikatorze productID.

Główne zapytanie i dwie dodatkowe metody zwracają ten sam zestaw pól danych, a mianowicie wszystkie kolumny z Products tabeli. Nie ma skorelowanych podzapytań ani JOIN ściągających powiązane dane z tabel Categories lub Suppliers. W związku z tym kolumna ProductsDataTable zawiera odpowiednią kolumnę dla każdego pola w Products tabeli.

Na potrzeby tego samouczka dodajmy metodę do ProductsTableAdapter, nazwaną GetProductsWithPriceQuartile, która zwraca wszystkie produkty. Oprócz standardowych pól danych produktu, GetProductsWithPriceQuartile będzie również zawierać pole danych PriceQuartile, które wskazuje, do którego kwartyla należy cena produktu. Na przykład te produkty, których ceny znajdują się w najdroższych 25% będą miały PriceQuartile wartość 1, podczas gdy te, których ceny spadną w dolnej 25% będą miały wartość 4. Zanim jednak zaczniemy martwić się o utworzenie procedury składowanej w celu zwrócenia tych informacji, najpierw musimy zaktualizować ProductsDataTable aby uwzględnić kolumnę w celu przechowywania wyników PriceQuartile podczas używania metody GetProductsWithPriceQuartile.

Otwórz NorthwindWithSprocs DataSet i kliknij prawym przyciskiem myszy na ProductsDataTable. Wybierz pozycję Dodaj z menu kontekstowego, a następnie wybierz pozycję Kolumna.

Rysunek 1. Dodawanie nowej kolumny do elementu ProductsDataTable (kliknij, aby wyświetlić obraz pełnowymiarowy)

Spowoduje to dodanie nowej kolumny do tabeli DataTable o nazwie Column1 typu System.String. Musimy zaktualizować nazwę tej kolumny na PriceQuartile i jej typ System.Int32 , ponieważ będzie ona używana do przechowywania liczby z zakresu od 1 do 4. Wybierz nowo dodaną kolumnę w obiekcie ProductsDataTable, a następnie w oknie Właściwości, ustaw właściwość Name na PriceQuartile i właściwość DataType na System.Int32.

Rysunek 2. Ustawianie nowych kolumn Name i DataType właściwości (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Jak pokazano na rysunku 2, istnieją dodatkowe właściwości, które można ustawić, takie jak to, czy wartości w kolumnie muszą być unikatowe, czy kolumna jest kolumną automatycznego zwiększania, czy wartości NULL w bazie danych są dozwolone, itd. Pozostaw te wartości ustawione na wartości domyślne.

Krok 2. TworzenieGetProductsWithPriceQuartilemetody

Teraz, gdy element ProductsDataTable został zaktualizowany w celu uwzględnienia PriceQuartile kolumny, możemy przystąpić do tworzenia GetProductsWithPriceQuartile metody. Zacznij od kliknięcia prawym przyciskiem myszy kontrolki TableAdapter i wybrania pozycji Dodaj zapytanie z menu kontekstowego. Spowoduje to wyświetlenie kreatora konfiguracji zapytań TableAdapter, który najpierw zapyta nas, czy chcemy użyć instrukcji ad-hoc SQL, czy nowej lub istniejącej procedury składowanej. Ponieważ nie mamy jeszcze procedury składowanej, która zwraca dane dotyczące kwartylu cenowego, pozwólmy TableAdapterowi utworzyć dla nas tę procedurę składowaną. Wybierz opcję Utwórz nową procedurę składowaną, a następnie kliknij przycisk Dalej.

Rysunek 3. Poleć Kreatorowi TableAdapter, aby utworzył procedurę przechowywaną za nas (kliknij, aby zobaczyć obraz w pełnym rozmiarze)

Na kolejnym ekranie, pokazanym na rysunku 4, kreator pyta nas, jakiego typu zapytanie chcemy dodać. GetProductsWithPriceQuartile Ponieważ metoda zwróci wszystkie kolumny i rekordy z Products tabeli, wybierz opcję SELECT, która zwraca wiersze, a następnie kliknij przycisk Dalej.

Rysunek 4. Zapytanie będzie instrukcją zwracającą SELECT wiele wierszy (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Następnie zostaniemy poproszeni o SELECT zapytanie. Wprowadź następujące zapytanie w kreatorze:

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

Powyższe zapytanie używa nowej NTILE funkcji programu SQL Server 2005, aby podzielić wyniki na cztery grupy, w których grupy są określane według UnitPrice wartości posortowanych w kolejności malejącej.

Niestety konstruktor zapytań nie wie, jak przeanalizować OVER słowo kluczowe i wyświetli błąd podczas analizowania powyższego zapytania. W związku z tym wprowadź powyższe zapytanie bezpośrednio w polu tekstowym w kreatorze bez użycia konstruktora zapytań.

Uwaga / Notatka

Aby uzyskać więcej informacji na temat funkcji NTILE i innych funkcji klasyfikacji w SQL Server 2005, zobacz ROW_NUMBER (Transact-SQL) i sekcję Funkcje klasyfikacji z SQL Server 2005 Books Online.

Po wprowadzeniu SELECT zapytania i kliknięciu przycisku Dalej, kreator poprosi nas o podanie nazwy procedury przechowywania, którą utworzy. Nadaj nowej procedurze Products_SelectWithPriceQuartile składowanej nazwę i kliknij przycisk Dalej.

Rysunek 5. Nadaj nazwę procedurze Products_SelectWithPriceQuartileskładowanej (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Na koniec zostanie wyświetlony monit o nadanie nazwy metod TableAdapter. Pozostaw zaznaczone pola wyboru Fill a DataTable (Wypełnij tabelę danych) i Return a DataTable (Zwracanie tabeli danych) oraz nadaj metodom FillWithPriceQuartile nazwę i GetProductsWithPriceQuartile.

Rysunek 6. Nazwij metody tableAdapter i kliknij przycisk Zakończ (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Po określeniu zapytania SELECT oraz nazwaniu procedury składowanej i metod TableAdapter, kliknij przycisk Zakończ, aby ukończyć pracę kreatora. W tym momencie kreator może wyświetlić jedno lub dwa ostrzeżenia z informacją, że konstrukcja lub instrukcja SQL OVER nie jest obsługiwana. Te ostrzeżenia można zignorować.

Po ukończeniu pracy kreatora narzędzie TableAdapter powinno zawierać metody FillWithPriceQuartile oraz GetProductsWithPriceQuartile, a baza danych powinna zawierać procedurę składowaną nazwaną Products_SelectWithPriceQuartile. Poświęć chwilę, aby sprawdzić, czy TableAdapter rzeczywiście zawiera tę nową metodę i czy procedura składowana została poprawnie dodana do bazy danych. Jeśli podczas sprawdzania bazy danych nie widzisz procedury składowanej, spróbuj kliknąć prawym przyciskiem myszy folder Procedury składowane i wybrać polecenie Odśwież.

Rysunek 7. Sprawdzanie, czy nowa metoda została dodana do klasy TableAdapter

Rysunek 8. Upewnij się, że baza danych zawiera procedurę Products_SelectWithPriceQuartile składowaną (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Uwaga / Notatka

Jedną z zalet korzystania z procedur składowanych zamiast instrukcji ad hoc SQL jest to, że ponowne uruchomienie Kreatora konfiguracji tableAdapter nie spowoduje modyfikacji list kolumn procedur składowanych. Zweryfikuj to, klikając prawym przyciskiem myszy tabelę TableAdapter, wybierając opcję Konfiguruj z menu kontekstowego, aby uruchomić kreatora, a następnie klikając przycisk Zakończ, aby go ukończyć. Następnie przejdź do bazy danych i wyświetl procedurę Products_SelectWithPriceQuartile składowaną. Zwróć uwagę, że lista kolumn nie została zmodyfikowana. Gdybyśmy używali instrukcji ad-hoc SQL, to ponowne uruchomienie kreatora konfiguracji TableAdapter przywróciłoby listę kolumn kwerendy tak, aby dopasowała się do listy kolumn głównej kwerendy, usuwając w ten sposób instrukcję NTILE z zapytania używanego przez metodę GetProductsWithPriceQuartile.

Po wywołaniu metody warstwy dostępu do danych GetProductsWithPriceQuartile, TableAdapter wykonuje procedurę składowaną Products_SelectWithPriceQuartile i dodaje wiersz do ProductsDataTable dla każdego zwróconego rekordu. Pola danych zwracane przez procedurę składowaną są mapowane na kolumny ProductsDataTable. Ponieważ z procedury składowanej zwracane jest PriceQuartile pole danych, jego wartość jest przypisywana do ProductsDataTable kolumny s PriceQuartile .

W przypadku tych metod TableAdapter, których zapytania nie zwracają pola danych PriceQuartile, wartość kolumny PriceQuartile jest wartością określoną przez jej właściwość DefaultValue. Jak pokazano na rysunku 2, ta wartość jest ustawiona na DBNull, wartość domyślną. Jeśli wolisz inną wartość domyślną, po prostu ustaw właściwość DefaultValue zgodnie z tym. Upewnij się, że wartość DefaultValue jest prawidłowa, biorąc pod uwagę kolumnę DataType (tj. kolumnę System.Int32 dla PriceQuartile).

W tym momencie wykonaliśmy kroki niezbędne do dodania dodatkowej kolumny do tabeli DataTable. Aby sprawdzić, czy ta dodatkowa kolumna działa zgodnie z oczekiwaniami, utwórzmy stronę ASP.NET wyświetlającą nazwę, cenę i kwartyl każdego produktu. Zanim to zrobimy, musimy zaktualizować warstwę logiki biznesowej, aby uwzględniała metodę, która wywołuje metodę GetProductsWithPriceQuartile w DAL. Następnie zaktualizujemy usługę BLL w kroku 3, a następnie utworzymy stronę ASP.NET w kroku 4.

Krok 3. Rozszerzanie warstwy logiki biznesowej

Zanim użyjemy nowej GetProductsWithPriceQuartile metody z warstwy prezentacji, najpierw należy dodać odpowiednią metodę do usługi BLL. ProductsBLLWithSprocs Otwórz plik klasy i dodaj następujący kod:

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

Podobnie jak inne metody pobierania danych w metodzie ProductsBLLWithSprocs, GetProductsWithPriceQuartile metoda po prostu wywołuje odpowiadającą GetProductsWithPriceQuartile metodę DAL i zwraca wyniki.

Krok 4. Wyświetlanie informacji kwartylu cen na stronie internetowej ASP.NET

Po zakończeniu dodawania BLL możemy utworzyć stronę ASP.NET, która pokazuje kwartyl ceny dla każdego produktu. Otwórz stronę AddingColumns.aspx w folderze AdvancedDAL, a następnie przeciągnij kontrolkę GridView z Narzędzi do projektanta i ustaw jej właściwość ID na Products. Za pomocą inteligentnej etykiety GridView powiąż go z nowym obiektem ObjectDataSource o nazwie ProductsDataSource. Skonfiguruj obiekt ObjectDataSource, aby używać metody ProductsBLLWithSprocs klasy GetProductsWithPriceQuartile. Ponieważ będzie to siatka tylko do odczytu, ustaw listy rozwijane na kartach UPDATE, INSERT i DELETE na wartość (Brak).

Rysunek 9. Konfigurowanie obiektu ObjectDataSource do używania ProductsBLLWithSprocs klasy (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Rysunek 10. Pobieranie informacji o produkcie GetProductsWithPriceQuartile z metody (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Po ukończeniu kreatora Konfigurowanie źródła danych program Visual Studio automatycznie doda pole BoundField lub CheckBoxField do elementu GridView dla każdego pola danych zwróconego przez metodę. Jedno z tych pól danych to PriceQuartile, czyli kolumna dodana do ProductsDataTable kroku 1.

Edytuj pola kontrolki GridView, usuwając wszystkie pola poza ProductName, UnitPrice i PriceQuartile BoundFields. Skonfiguruj UnitPrice BoundField, aby sformatować jego wartość jako walutę, oraz UnitPrice i PriceQuartile BoundFields, aby były odpowiednio wyrównane do prawej oraz do środka. Na koniec zaktualizuj pozostałe właściwości BoundFields HeaderText odpowiednio do właściwości Product, Price i Price Quartile. Zaznacz również pole wyboru Włącz sortowanie z tagu inteligentnego GridView.

Po tych modyfikacjach znacznik deklaratywny GridView i ObjectDataSource powinien wyglądać następująco:

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

Rysunek 11 przedstawia tę stronę po odwiedzeniu w przeglądarce. Należy pamiętać, że początkowo produkty są uporządkowane według ich ceny w kolejności malejącej, a każdy produkt ma przypisaną odpowiednią PriceQuartile wartość. Oczywiście te dane można sortować według innych kryteriów, gdzie wartość kolumny kwartylu cenowego nadal odzwierciedla pozycję produktu względem ceny (patrz Rysunek 12).

Rysunek 11. Produkty są uporządkowane według ich cen (kliknij, aby wyświetlić obraz pełnowymiarowy)

Rysunek 12. Produkty są uporządkowane według ich nazw (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Uwaga / Notatka

Za pomocą kilku wierszy kodu możemy rozszerzyć obiekt GridView, aby pokolorować wiersze produktów według ich PriceQuartile wartości. Możemy pokolorować te produkty w pierwszym kwartylu kolorem jasnozielonym, te w drugim kwartylu kolorem jasnożółtym i tak dalej. Zachęcam do podjęcia chwilę, aby dodać tę funkcję. Jeśli potrzebujesz odświeżenia wiedzy o formatowaniu kontrolki GridView, zapoznaj się z samouczkiem Custom Formatting Based Upon Data (Formatowanie niestandardowe oparte na danych).

Alternatywne podejście — tworzenie kolejnego TableAdaptera

Jak pokazano w tym samouczku, podczas dodawania metody do klasy TableAdapter, która zwraca pola danych inne niż te, które zostały zapisane przez główne zapytanie, możemy dodać odpowiednie kolumny do tabeli DataTable. Takie podejście działa jednak dobrze tylko wtedy, gdy w tabeli TableAdapter istnieje niewielka liczba metod, które zwracają różne pola danych, a jeśli te alternatywne pola danych nie różnią się zbytnio od zapytania głównego.

Zamiast dodawać kolumny do tabeli DataTable, możesz zamiast tego dodać kolejny element TableAdapter do zestawu Danych, który zawiera metody z pierwszego obiektu TableAdapter zwracające różne pola danych. Na potrzeby tego samouczka, zamiast dodawać kolumnę PriceQuartile do ProductsDataTable (gdzie jest używana tylko przez metodę GetProductsWithPriceQuartile), moglibyśmy dodać dodatkowy TableAdapter do zestawu danych o nazwie ProductsWithPriceQuartileTableAdapter, który używa procedury składowanej Products_SelectWithPriceQuartile jako głównego zapytania. Strony ASP.NET, które potrzebowałyby uzyskać informacji o produkcie z kwartylem cen, używałyby elementu ProductsWithPriceQuartileTableAdapter, podczas gdy te, które tego nie potrzebowały, mogą nadal korzystać z elementu ProductsTableAdapter.

Dodając nowy TableAdapter, tabele DataTable pozostają nienaruszone, a ich kolumny dokładnie odzwierciedlają pola danych zwracane przez metody TableAdapter'a. Jednak dodatkowe elementy TableAdapters mogą wprowadzać powtarzające się zadania i funkcje. Jeśli na przykład te strony ASP.NET, które wyświetlały kolumnę PriceQuartile, również były potrzebne do zapewnienia obsługi wstawiania, aktualizowania i usuwania, należy prawidłowo skonfigurować właściwości ProductsWithPriceQuartileTableAdapter, InsertCommand, i UpdateCommand. Chociaż te właściwości będą odzwierciedlać cechy ProductsTableAdapter, ta konfiguracja wprowadza dodatkowy krok. Ponadto istnieją dwa sposoby aktualizowania, usuwania lub dodawania produktu do bazy danych — za pomocą ProductsTableAdapter klas i ProductsWithPriceQuartileTableAdapter .

Pobieranie dla tego samouczka obejmuje klasę ProductsWithPriceQuartileTableAdapter w NorthwindWithSprocs DataSet, która ilustruje to alternatywne podejście.

Podsumowanie

W większości scenariuszy wszystkie metody w tabeli TableAdapter zwracają ten sam zestaw pól danych, ale istnieją sytuacje, w których określona metoda lub dwie mogą wymagać zwrócenia dodatkowego pola. Na przykład w samouczku Master/Detail Using a Bulleted List of Master Records with a Details DataList dodaliśmy metodę do CategoriesTableAdapter , która oprócz pól danych kwerendy głównej zwracała NumberOfProducts pole, które zgłaszało liczbę produktów skojarzonych z każdą kategorią. W tym samouczku przyjrzeliśmy się dodaniu metody w ProductsTableAdapter, która zwróciła pole PriceQuartile oprócz pól danych głównego zapytania. Aby przechwycić dodatkowe pola danych zwracane przez metody tableAdapter, musimy dodać odpowiednie kolumny do tabeli DataTable.

Jeśli planujesz ręczne dodawanie kolumn do tabeli DataTable, zaleca się użycie procedur składowanych przez narzędzie TableAdapter. Jeśli TableAdapter używa instrukcji SQL ad hoc, za każdym razem, gdy kreator konfiguracji TableAdapter zostanie uruchomiony, wszystkie listy pól danych metod są przywracane do pól danych zwracanych przez główne zapytanie. Ten problem nie obejmuje procedur składowanych, dlatego są one zalecane i były używane w tym samouczku.

Szczęśliwe programowanie!

Informacje o autorze

Scott Mitchell, autor siedmiu książek ASP/ASP.NET i założyciel 4GuysFromRolla.com, współpracuje z technologiami internetowymi firmy Microsoft od 1998 roku. Scott pracuje jako niezależny konsultant, trener i pisarz. Jego najnowsza książka to Sams Teach Yourself ASP.NET 2.0 w ciągu 24 godzin. Można go uzyskać pod adresem mitchell@4GuysFromRolla.com.

Specjalne podziękowania

Ta seria samouczków została omówiona przez wielu przydatnych recenzentów. Główni recenzenci tego samouczka to Randy Schmidt, Jacky Goor, Bernadette Leigh i Hilton Giesenow. Chcesz przejrzeć nadchodzące artykuły MSDN? Jeśli tak, napisz do mnie na adres mitchell@4GuysFromRolla.com.

PoprzedniNastępny