Tworzenie nowych procedur składowanych dla elementów TableAdapter typizowanego zestawu danych (C#)

Autor : Scott Mitchell

Pobierz plik PDF

We wcześniejszych samouczkach utworzyliśmy instrukcje SQL w naszym kodzie i przekazaliśmy instrukcje do bazy danych do wykonania. Alternatywną metodą jest użycie procedur składowanych, w których instrukcje SQL są wstępnie zdefiniowane w bazie danych. Z tego samouczka dowiesz się, jak kreator TableAdapter generuje dla nas nowe procedury składowane.

Wprowadzenie

W tych samouczkach warstwa dostępu do danych (DAL) używa typów zestawów danych. Zgodnie z opisem w samouczku Tworzenie warstwy dostępu do danych, typizowane zestawy danych składają się z silnie typizowanych tabel danych (DataTables) i elementów TableAdapters. DataTables reprezentują logiczne jednostki w systemie, podczas gdy TableAdapters pośredniczą w dostępie do bazowej bazy danych, wykonując pracę związaną z dostępem do danych. Obejmuje to wypełnianie tabel DataTable z danymi, wykonywanie zapytań, które zwracają dane skalarne, oraz wstawianie, aktualizowanie i usuwanie rekordów z bazy danych.

Polecenia SQL wykonywane przez klasy TableAdapters mogą być instrukcjami ad hoc SQL, takimi jak SELECT columnList FROM TableName, lub procedurami składowanymi. Klasy TableAdapters w naszej architekturze używają instrukcji SQL ad hoc. Wielu deweloperów i administratorów baz danych preferuje jednak procedury składowane za pośrednictwem instrukcji ad hoc SQL ze względów bezpieczeństwa, konserwacji i skalowalności. Inni zagorzali preferują stosowanie instrukcji ad-hoc SQL ze względu na ich elastyczność. We własnej pracy wolę procedury składowane od zapytań SQL tworzonych na bieżąco, ale zdecydowałem się użyć tych ostatnich, aby uprościć wcześniejsze samouczki.

Podczas definiowania TableAdapter lub dodawania nowych metod kreator TableAdapter ułatwia tworzenie nowych procedur składowanych lub używanie istniejących procedur składowanych, tak jak w przypadku używania instrukcji SQL ad hoc. W tym samouczku sprawdzimy, jak kreator tableAdapter automatycznie generuje procedury składowane. W następnym samouczku przyjrzymy się, jak skonfigurować metody TableAdapter do używania istniejących lub ręcznie utworzonych procedur składowanych.

Uwaga / Notatka

Zobacz wpis na blogu Roba Howarda Don't Use Stored Procedures Yet? oraz wpis na blogu Fransa BoumyStored Procedures are Bad, M Kay? na żywą debatę na temat zalet i wad procedur składowanych i ad hoc SQL.

Podstawy procedur składowanych

Funkcje są konstrukcją wspólną dla wszystkich języków programowania. Funkcja jest kolekcją instrukcji wykonywanych podczas wywoływanej funkcji. Funkcje mogą akceptować parametry wejściowe i mogą opcjonalnie zwracać wartość. Procedury składowane to konstrukcje bazy danych, które współdzielą wiele podobieństw do funkcji w językach programowania. Procedura składowana składa się z zestawu instrukcji języka T-SQL wykonywanych podczas wywoływanej procedury składowanej. Procedura składowana może przyjmować zero do wielu parametrów wejściowych i może zwracać wartości skalarne, parametry wyjściowe lub, najczęściej, zestawy wyników z SELECT zapytań.

Uwaga / Notatka

Procedury składowane są często określane jako "sprocs" (skrót od procedur) lub "SPs" (procedur składowanych).

Procedury składowane są tworzone przy użyciu instrukcji CREATE PROCEDURE języka T-SQL. Na przykład poniższy skrypt języka T-SQL tworzy procedurę składowaną o nazwie GetProductsByCategoryID, która przyjmuje jeden parametr o nazwie @CategoryID i zwraca ProductID, ProductName, UnitPrice i Discontinued pola tych kolumn w tabeli Products, które mają zgodną wartość CategoryID.

CREATE PROCEDURE GetProductsByCategoryID
(
    @CategoryID int
)
AS
SELECT ProductID, ProductName, UnitPrice, Discontinued
FROM Products
WHERE CategoryID = @CategoryID

Po utworzeniu tej procedury składowanej można ją wywołać przy użyciu następującej składni:

EXEC GetProductsByCategory categoryID

Uwaga / Notatka

W następnym samouczku przeanalizujemy tworzenie procedur składowanych za pomocą środowiska IDE programu Visual Studio. Na potrzeby tego samouczka zezwolimy jednak kreatorowi TableAdapter na automatyczne generowanie procedur składowanych.

Oprócz zwykłego zwracania danych procedury składowane są często używane do wykonywania wielu poleceń bazy danych w zakresie pojedynczej transakcji. Procedura składowana o nazwie DeleteCategory, na przykład, może przyjąć @CategoryID parametr i wykonać dwie DELETE instrukcje: po pierwsze, jedną, aby usunąć powiązane produkty i drugą, usuwając określoną kategorię. Instrukcje w procedurze składowanej nie są automatycznie opakowane w transakcję. Aby upewnić się, że procedura składowana złożona z wielu poleceń jest traktowana jako operacja atomowa, należy wydać dodatkowe polecenia języka T-SQL. Zobaczymy, jak opakować polecenia procedury składowanej w ramach transakcji w kolejnym samouczku.

W przypadku korzystania z procedur składowanych w architekturze metody warstwy dostępu do danych wywołują określoną procedurę składowaną, a nie wydając instrukcji ad hoc SQL. Centralizuje to lokalizację wykonywanych instrukcji SQL (w bazie danych), a nie zdefiniowaną w ramach architektury aplikacji. Ta centralizacja prawdopodobnie ułatwia znajdowanie, analizowanie i dostrajanie zapytań oraz zapewnia znacznie jaśniejszy obraz miejsca i sposobu użycia bazy danych.

Aby uzyskać więcej informacji na temat podstaw procedur składowanych, zapoznaj się z zasobami w sekcji Dalsza lektura na końcu tego samouczka.

Krok 1. Tworzenie zaawansowanych scenariuszy warstwy dostępu do danych stron sieci Web

Zanim rozpoczniemy dyskusję na temat tworzenia DAL przy użyciu procedur przechowywanych, najpierw poświęćmy chwilę na utworzenie stron ASP.NET w naszym projekcie witryny internetowej, które będą nam potrzebne do tego celu i w kolejnych kilku samouczkach. Zacznij od dodania nowego folderu o nazwie AdvancedDAL. Następnie dodaj następujące strony ASP.NET do tego folderu, aby skojarzyć każdą stronę ze stroną wzorcową Site.master :

• Default.aspx
• NewSprocs.aspx
• ExistingSprocs.aspx
• JOINs.aspx
• AddingColumns.aspx
• ComputedColumns.aspx
• EncryptingConfigSections.aspx
• ManagedFunctionsAndSprocs.aspx

Rysunek 1. Dodaj strony ASP.NET na potrzeby samouczków dotyczących zaawansowanego korzystania z warstwy dostępu do danych

Podobnie jak w innych folderach, Default.aspx w folderze AdvancedDAL wyświetli listę samouczków w swojej sekcji. Pamiętaj, że kontrolka SectionLevelTutorialListing.ascx użytkownika udostępnia tę funkcję. W związku z tym dodaj tę kontrolkę użytkownika Default.aspx przeciągając ją z Eksploratora rozwiązań na stronę w widoku Projektu.

Rysunek 2. Dodawanie kontrolki SectionLevelTutorialListing.ascx użytkownika do Default.aspx (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Na koniec dodaj te strony jako wpisy do Web.sitemap pliku. W szczególności dodaj następujący znacznik po zakończeniu pracy z danymi <siteMapNode>wsadowymi:

<siteMapNode url="~/AdvancedDAL/Default.aspx" 
    title="Advanced DAL Scenarios" 
    description="Explore a number of advanced Data Access Layer scenarios.">
    
    <siteMapNode url="~/AdvancedDAL/NewSprocs.aspx" 
        title="Creating New Stored Procedures for TableAdapters" 
        description="Learn how to have the TableAdapter wizard automatically 
            create and use stored procedures." />
    <siteMapNode url="~/AdvancedDAL/ExistingSprocs.aspx" 
        title="Using Existing Stored Procedures for TableAdapters" 
        description="See how to plug existing stored procedures into a 
            TableAdapter." />
    <siteMapNode url="~/AdvancedDAL/JOINs.aspx" 
        title="Returning Data Using JOINs" 
        description="Learn how to augment your DataTables to work with data 
            returned from multiple tables via a JOIN query." />
    <siteMapNode url="~/AdvancedDAL/AddingColumns.aspx" 
        title="Adding DataColumns to a DataTable" 
        description="Master adding new columns to an existing DataTable." />
    <siteMapNode url="~/AdvancedDAL/ComputedColumns.aspx" 
        title="Working with Computed Columns" 
        description="Explore how to work with computed columns when using 
            Typed DataSets." />
    <siteMapNode url="~/AdvancedDAL/EncryptingConfigSections.aspx" 
        title="Protected Connection Strings in Web.config" 
        description="Protect your connection string information in 
            Web.config using encryption." />
    <siteMapNode url="~/AdvancedDAL/ManagedFunctionsAndSprocs.aspx" 
        title="Creating Managed SQL Functions and Stored Procedures" 
        description="See how to create SQL functions and stored procedures 
            using managed code." />
</siteMapNode>

Po zaktualizowaniu Web.sitemap programu, poświęć chwilę, aby wyświetlić witrynę internetową samouczków za pośrednictwem przeglądarki. Menu po lewej stronie zawiera teraz elementy dla zaawansowanych samouczków dotyczących scenariuszy DAL.

Rysunek 3: Mapa witryny teraz zawiera wpisy dotyczące samouczków zaawansowanych scenariuszy DAL

Krok 2. Konfigurowanie obiektu TableAdapter w celu utworzenia nowych procedur składowanych

Aby zademonstrować tworzenie warstwy dostępu do danych, która używa procedur składowanych zamiast instrukcji ad hoc SQL, utwórzmy nowy typowy zestaw danych w ~/App_Code/DAL folderze o nazwie NorthwindWithSprocs.xsd. Ponieważ szczegółowo omówiliśmy ten proces w poprzednich samouczkach, szybko przejdziemy przez kroki opisane tutaj. Jeśli utkniesz lub potrzebujesz dodatkowych instrukcji krok po kroku dotyczących tworzenia i konfigurowania Typed DataSet, zapoznaj się z samouczkiem Tworzenie warstwy dostępu do danych.

Dodaj nowy zestaw danych do projektu, klikając prawym przyciskiem myszy DAL folder, wybierając polecenie Dodaj nowy element i wybierając szablon Zestaw danych, jak pokazano na rysunku 4.

Rysunek 4. Dodawanie nowego typu zestawu danych do projektu o nazwie NorthwindWithSprocs.xsd (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Spowoduje to utworzenie nowego Typed DataSet, otwarcie jego designera, utworzenie nowego TableAdapter i uruchomienie TableAdapter Configuration Wizard. Pierwszy krok Kreatora Konfiguracji TableAdaptera prosi nas o wybranie bazy danych do pracy. Parametry połączenia z bazą danych Northwind powinny być wymienione na liście rozwijanej. Wybierz to, a następnie kliknij przycisk Dalej.

Na tym następnym ekranie możemy wybrać sposób uzyskiwania dostępu do bazy danych przez narzędzie TableAdapter. W poprzednich samouczkach wybraliśmy pierwszą opcję Użyj instrukcji SQL. Na potrzeby tego samouczka wybierz drugą opcję Utwórz nowe procedury składowane, a następnie kliknij przycisk Dalej.

Rysunek 5: Nakazanie TableAdapter utworzenia nowych procedur składowanych (Kliknij, aby zobaczyć obraz w pełnym rozmiarze)

Podobnie jak w przypadku korzystania z instrukcji SQL ad hoc, w poniższym kroku jesteśmy proszeni o podanie SELECT instrukcji dla głównego zapytania w TableAdapterze. Jednak zamiast używać tutaj wprowadzonej instrukcji SELECT do bezpośredniego wykonania zapytania ad hoc, kreator TableAdapter utworzy procedurę składowaną, zawierającą to zapytanie SELECT.

Użyj następującego SELECT zapytania dla tego obiektu TableAdapter:

SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products

Rysunek 6. Wprowadź SELECT zapytanie (kliknij, aby wyświetlić obraz pełnowymiarowy)

Uwaga / Notatka

Powyższe zapytanie różni się nieco od głównego zapytania ProductsTableAdapter w typowym zestawie Northwind danych. Pamiętaj, że element ProductsTableAdapter w zestawie Northwind danych typu zawiera dwie skorelowane podzapytania, aby pobrać nazwę kategorii oraz nazwę firmy dla każdej kategorii produktów i dostawcy. W nadchodzącym samouczku dotyczącym aktualizowania obiektu TableAdapter do korzystania ze złączeń JOIN zajmiemy się dodawaniem tych powiązanych danych do tego TableAdaptera.

Poświęć chwilę, aby kliknąć przycisk Zaawansowane opcje. W tym miejscu można określić, czy kreator powinien również generować instrukcje wstawiania, aktualizacji i usuwania dla klasy TableAdapter, czy używać optymistycznej współbieżności, oraz czy tabela danych powinna zostać odświeżona po wstawianiu i aktualizacjach. Opcja Generuj instrukcje Wstaw, Aktualizuj i Usuń jest domyślnie zaznaczona. Pozostaw to zaznaczone. W tym samouczku pozostaw opcje niezaznaczone dla użycia opcji optymistycznej współbieżności.

W przypadku automatycznego tworzenia procedur składowanych przez kreatora TableAdapter pojawia się, że opcja Odśwież tabelę danych jest ignorowana. Niezależnie od tego, czy to pole wyboru jest zaznaczone, wynikowe procedury składowane do wstawiania i aktualizowania pobierają właśnie wstawiony lub właśnie zaktualizowany rekord, jak zobaczymy w Kroku 3.

Rysunek 7: Pozostaw zaznaczoną opcję generowania instrukcji Wstaw, Aktualizuj i Usuń

Uwaga / Notatka

Jeśli opcja Użyj optymistycznej współbieżności jest zaznaczona, kreator doda dodatkowe warunki do klauzuli WHERE, które uniemożliwią aktualizowanie danych, jeśli w innych polach zaszły zmiany. Zapoznaj się z samouczkiem Implementacja optymistycznej współbieżności, aby uzyskać więcej informacji na temat korzystania z wbudowanej funkcji sterowania współbieżnością optymistyczną w TableAdapterze.

Po wprowadzeniu tego SELECT zapytania i potwierdzeniu, że jest zaznaczona opcja Generuj instrukcje Wstaw, Aktualizuj i Usuń, kliknij przycisk Dalej. Następny ekran, pokazany na rysunku 8, prosi o wprowadzenie nazw procedur składowanych, które kreator utworzy do selekcjonowania, wstawiania, aktualizowania i usuwania danych. Zmień nazwy tych procedur składowanych na Products_Select, Products_Insert, Products_Updatei Products_Delete.

Rysunek 8. Zmiana nazwy procedur składowanych (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Aby zobaczyć kod T-SQL, którego użyje kreator TableAdapter do utworzenia czterech procedur składowanych, kliknij przycisk Podgląd skryptu SQL. W oknie dialogowym Podgląd skryptu SQL można zapisać skrypt w pliku lub skopiować go do schowka.

Rysunek 9. Podgląd skryptu SQL używanego do generowania procedur składowanych

Po nazewnictwie procedur składowanych kliknij przycisk Dalej, aby nazwać odpowiednie metody TableAdapter. Podobnie jak w przypadku korzystania z instrukcji SQL ad hoc, możemy utworzyć metody, które wypełniają istniejącą tabelę DataTable lub zwracają nową. Możemy również określić, czy obiekt TableAdapter powinien zawierać wzorzec DB-Direct do wstawiania, aktualizowania i usuwania rekordów. Pozostaw zaznaczone wszystkie trzy pola wyboru, ale zmień nazwę metody Return a DataTable na GetProducts (jak pokazano na rysunku 10).

Rysunek 10. Nadaj metodom Fill nazwę i GetProducts (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Kliknij przycisk Dalej, aby wyświetlić podsumowanie kroków, które wykona kreator. Zakończ pracę kreatora, klikając przycisk Zakończ. Po zakończeniu pracy kreatora nastąpi powrót do projektanta zestawu danych, który powinien teraz zawierać element ProductsDataTable.

Rysunek 11. Projektant zestawu danych przedstawia nowo dodane ProductsDataTable (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Krok 3. Badanie nowo utworzonych procedur składowanych

Kreator TableAdapter używany w kroku 2 automatycznie utworzył procedury składowane do wybierania, wstawiania, aktualizowania i usuwania danych. Te procedury składowane można wyświetlić lub zmodyfikować za pomocą programu Visual Studio, przechodząc do Eksploratora serwera i przechodząc do szczegółów w folderze Procedury składowane bazy danych. Jak pokazano na rysunku 12, baza danych Northwind zawiera cztery nowe procedury składowane: Products_Delete, Products_Insert, Products_Select i Products_Update.

Rysunek 12. Cztery procedury składowane utworzone w kroku 2 można znaleźć w folderze Procedury składowane bazy danych

Uwaga / Notatka

Jeśli nie widzisz Eksploratora serwera, przejdź do menu Widok i wybierz opcję Eksplorator serwera. Jeśli nie widzisz procedur składowanych związanych z produktem dodanych w kroku 2, spróbuj kliknąć prawym przyciskiem myszy folder Procedury składowane i wybrać polecenie Odśwież.

Aby wyświetlić lub zmodyfikować procedurę składowaną, kliknij dwukrotnie jego nazwę w Eksploratorze serwera lub, alternatywnie, kliknij prawym przyciskiem myszy procedurę składowaną i wybierz polecenie Otwórz. Rysunek 13 przedstawia procedurę składowaną Products_Delete w stanie otwarcia.

Rysunek 13. Procedury składowane można otwierać i modyfikować z poziomu programu Visual Studio (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Zawartość procedur składowanych Products_Delete i Products_Select jest dość prosta. Z drugiej strony, procedury składowane Products_Insert i Products_Update uzasadniają dokładniejszą analizę, ponieważ obie wykonują instrukcję SELECT po ich instrukcjach INSERT i UPDATE. Na przykład następujący kod SQL tworzy procedurę Products_Insert składowaną:

ALTER PROCEDURE dbo.Products_Insert
(
    @ProductName nvarchar(40),
    @SupplierID int,
    @CategoryID int,
    @QuantityPerUnit nvarchar(20),
    @UnitPrice money,
    @UnitsInStock smallint,
    @UnitsOnOrder smallint,
    @ReorderLevel smallint,
    @Discontinued bit
)
AS
    SET NOCOUNT OFF;
INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], 
    [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) 
VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, 
    @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
    
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, 
    UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued 
FROM Products 
WHERE (ProductID = SCOPE_IDENTITY())

Procedura składowana przyjmuje jako parametry wejściowe kolumny Products zwrócone przez zapytanie SELECT określone w kreatorze TableAdapter, a te wartości są używane w instrukcji INSERT. Po instrukcji INSERT, zapytanie SELECT służy do zwracania wartości kolumny Products (w tym ProductID) z nowo dodanego rekordu. Ta funkcja odświeżania jest użyteczna podczas dodawania nowego rekordu przy użyciu wzorca aktualizacji zbiorczej, ponieważ automatycznie aktualizuje właściwości nowo dodanych wystąpień ProductRowProductID z wartościami autoinkrementacji przypisanymi przez bazę danych.

Poniższy kod ilustruje tę funkcję. Zawiera element ProductsTableAdapter i ProductsDataTable utworzony dla NorthwindWithSprocs typu zestawu danych. Nowy produkt jest dodawany do bazy danych poprzez utworzenie wystąpienia ProductsRow, wprowadzenie jego wartości oraz wywołanie metody Update TableAdapter, przekazując ProductsDataTable element. Wewnętrznie metoda TableAdapter Update wylicza ProductsRow instancje w przekazanej tabeli DataTable (w tym przykładzie jest tylko jedna — ta właśnie dodana) i wykonuje odpowiednie polecenie wstawiania, aktualizowania lub usuwania. W takim przypadku jest wykonywana procedura składowana Products_Insert, która dodaje nowy rekord do tabeli Products i zwraca szczegóły nowo dodanego rekordu. Wartość ProductsRow wystąpienia ProductID jest następnie aktualizowana. Po zakończeniu Update metody możemy uzyskać dostęp do nowo dodanej wartości rekordu ProductID za pośrednictwem ProductsRow właściwości s ProductID .

// Create the ProductsTableAdapter and ProductsDataTable
NorthwindWithSprocsTableAdapters.ProductsTableAdapter productsAPI = 
    new NorthwindWithSprocsTableAdapters.ProductsTableAdapter();
NorthwindWithSprocs.ProductsDataTable products = 
    new NorthwindWithSprocs.ProductsDataTable();
// Create a new ProductsRow instance and set its properties
NorthwindWithSprocs.ProductsRow product = products.NewProductsRow();
product.ProductName = "New Product";
product.CategoryID = 1;  // Beverages
product.Discontinued = false;
// Add the ProductsRow instance to the DataTable
products.AddProductsRow(product);
// Update the DataTable using the Batch Update pattern
productsAPI.Update(products);
// At this point, we can determine the value of the newly-added record's ProductID
int newlyAddedProductIDValue = product.ProductID;

Podobnie, procedura Products_Update składowana zawiera instrukcję SELECT po instrukcji UPDATE.

ALTER PROCEDURE dbo.Products_Update
(
    @ProductName nvarchar(40),
    @SupplierID int,
    @CategoryID int,
    @QuantityPerUnit nvarchar(20),
    @UnitPrice money,
    @UnitsInStock smallint,
    @UnitsOnOrder smallint,
    @ReorderLevel smallint,
    @Discontinued bit,
    @Original_ProductID int,
    @ProductID int
)
AS
    SET NOCOUNT OFF;
UPDATE [Products] 
SET [ProductName] = @ProductName, [SupplierID] = @SupplierID, 
    [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, 
    [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, 
    [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel, 
    [Discontinued] = @Discontinued 
WHERE (([ProductID] = @Original_ProductID));
    
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, 
    UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued 
FROM Products 
WHERE (ProductID = @ProductID)

Należy pamiętać, że ta procedura składowana zawiera dwa parametry wejściowe dla ProductID: @Original_ProductID i @ProductID. Ta funkcja umożliwia korzystanie ze scenariuszy, w których klucz podstawowy może zostać zmieniony. Na przykład w bazie danych pracowników każdy rekord pracownika może używać numeru ubezpieczenia społecznego pracownika jako klucza podstawowego. Aby zmienić numer ubezpieczenia społecznego istniejącego pracownika, należy podać zarówno nowy numer ubezpieczenia społecznego, jak i oryginalny. Products W przypadku tabeli takie funkcje nie są potrzebne, ponieważ kolumna ProductID jest kolumną IDENTITY i nigdy nie powinna być zmieniana. W rzeczywistości UPDATE instrukcja w Products_Update procedurze składowanej nie zawiera kolumny ProductID na liście kolumn. Dlatego, chociaż @Original_ProductID jest używany w instrukcji UPDATE w klauzuli WHERE, jest on zbędny dla tabeli Products i może zostać zastąpiony przez parametr @ProductID. Podczas modyfikowania parametrów procedury składowanej ważne jest również zaktualizowanie metod TableAdapter korzystających z tej procedury składowanej.

Krok 4. Modyfikowanie parametrów procedury składowanej i aktualizowanie obiektu TableAdapter

Ponieważ parametr @Original_ProductID jest zbędny, całkowicie usuńmy go z procedury składowanej Products_Update. Otwórz procedurę Products_Update składowaną, usuń parametr @Original_ProductID, a w klauzuli WHERE instrukcji UPDATE zmień nazwę parametru użytej z @Original_ProductID na @ProductID. Po wprowadzeniu tych zmian język T-SQL w procedurze składowanej powinien wyglądać następująco:

ALTER PROCEDURE dbo.Products_Update
(
    @ProductName nvarchar(40),
    @SupplierID int,
    @CategoryID int,
    @QuantityPerUnit nvarchar(20),
    @UnitPrice money,
    @UnitsInStock smallint,
    @UnitsOnOrder smallint,
    @ReorderLevel smallint,
    @Discontinued bit,
    @ProductID int
)
AS
    SET NOCOUNT OFF;
UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID, 
    [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, 
    [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, 
    [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel, 
    [Discontinued] = @Discontinued 
WHERE (([ProductID] = @ProductID));
    
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, 
    UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued 
FROM Products 
WHERE (ProductID = @ProductID)

Aby zapisać te zmiany w bazie danych, kliknij ikonę Zapisz na pasku narzędzi lub naciśnij Ctrl+S. W tym momencie procedura składowana Products_Update nie oczekuje parametru wejściowego @Original_ProductID, ale TableAdapter jest skonfigurowany do przekazywania takiego parametru. Parametry, które TableAdapter wyśle do Products_Update procedury składowanej, można zobaczyć, wybierając TableAdapter w Projektancie DataSet, przechodząc do okna właściwości i klikając wielokropek w kolekcji UpdateCommandParameters. Spowoduje to wyświetlenie okna dialogowego Edytor kolekcji parametrów pokazanego na rysunku 14.

Rysunek 14: Edytor kolekcji parametrów wyświetla parametry przekazywane do procedury składowanej Products_Update

Możesz usunąć ten parametr stąd, po prostu wybierając parametr @Original_ProductID z listy członków i klikając przycisk Usuń.

Alternatywnie możesz odświeżyć parametry używane dla wszystkich metod, klikając prawym przyciskiem myszy element TableAdapter w Projektancie i wybierając polecenie Konfiguruj. Spowoduje to wyświetlenie kreatora konfiguracji TableAdapter, zawierającego listę procedur składowanych używanych do wybierania, wstawiania, aktualizowania i usuwania oraz parametrów, które procedury składowane spodziewają się otrzymać. Jeśli klikniesz listę rozwijaną Aktualizacji, zobaczysz oczekiwane parametry wejściowe dla Products_Update procedur składowanych, które nie zawierają już @Original_ProductID (zobacz Rysunek 15). Po prostu kliknij przycisk Zakończ, aby automatycznie zaktualizować kolekcję parametrów używaną przez obiekt TableAdapter.

Rysunek 15. Możesz też użyć Kreatora konfiguracji programu TableAdapter, aby odświeżyć kolekcje parametrów metod (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Krok 5. Dodawanie dodatkowych metod tableAdapter

Jak pokazano w kroku 2, podczas tworzenia nowego TableAdapter można łatwo wygenerować automatycznie odpowiednie procedury składowane. To samo dotyczy dodawania dodatkowych metod w TableAdapterze. Aby to zilustrować, dodajmy metodę GetProductByProductID(productID) do utworzonej ProductsTableAdapter w kroku 2. Ta metoda będzie przyjmować jako dane wejściowe ProductID wartość i zwracać szczegóły dotyczące określonego produktu.

Zacznij od kliknięcia prawym przyciskiem myszy kontrolki TableAdapter i wybrania pozycji Dodaj zapytanie z menu kontekstowego.

Rysunek 16. Dodawanie nowego zapytania do kontrolki TableAdapter

Spowoduje to rozpoczęcie kreatora konfiguracji zapytań TableAdapter, który najpierw wyświetli monit dotyczący sposobu, w jaki TableAdapter powinien uzyskiwać dostęp do bazy danych. Aby utworzyć nową procedurę składowaną, wybierz opcję Utwórz nową procedurę składowaną i kliknij przycisk Dalej.

Rysunek 17. Wybierz opcję Utwórz nową procedurę składowaną (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Na następnym ekranie zostanie wyświetlony monit o zidentyfikowanie typu zapytania do wykonania, czy zwróci zestaw wierszy, czy pojedynczą wartość skalarną, czy też wykona instrukcję UPDATE, INSERTlub DELETE . GetProductByProductID(productID) Ponieważ metoda zwróci wiersz, pozostaw zaznaczoną opcję SELECT, która zwraca zaznaczoną opcję wiersza i naciśnij przycisk Dalej.

Rysunek 18. Wybierz pozycję SELECT, która zwraca opcję wiersza (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Na następnym ekranie zostanie wyświetlone główne zapytanie TableAdapter, które zawiera tylko nazwę procedury składowanej (dbo.Products_Select). Zastąp nazwę procedury składowanej następującą SELECT instrukcją, która zwraca wszystkie pola produktu dla określonego produktu:

SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID

Rysunek 19. Zastąp nazwę procedury składowanej zapytaniem SELECT (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Na następnym ekranie zostaniesz poproszony o podanie nazwy dla nowej procedury składowanej. Wprowadź nazwę Products_SelectByProductID i kliknij przycisk Dalej.

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

Ostatni krok kreatora umożliwia zmianę wygenerowanych nazw metod oraz wskazanie, czy należy użyć wzorca Uzupełnij DataTable, Zwróć DataTable, czy obu jednocześnie. W przypadku tej metody pozostaw zaznaczone obie opcje, ale zmień nazwy metod na FillByProductID i GetProductByProductID. Kliknij przycisk Dalej, aby wyświetlić podsumowanie kroków, które wykona kreator, a następnie kliknij przycisk Zakończ, aby ukończyć pracę kreatora.

Rysunek 21: Zmień nazwy metod TableAdapter na FillByProductID oraz GetProductByProductID (Kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Po ukończeniu pracy kreatora narzędzie TableAdapter ma dostępną nową metodę, GetProductByProductID(productID) która po wywołaniu wykona właśnie utworzoną Products_SelectByProductID procedurę składowaną. Poświęć chwilę, aby wyświetlić tę nową procedurę składowaną z Eksploratora serwera, nawigując do folderu Procedury składowane i otwierając Products_SelectByProductID (jeśli jej nie widzisz, kliknij prawym przyciskiem myszy na folder Procedury składowane i wybierz opcję Odśwież).

Należy pamiętać, że procedura składowana SelectByProductID przyjmuje @ProductID jako parametr wejściowy i wykonuje instrukcję SELECT, którą wprowadziliśmy w kreatorze.

ALTER PROCEDURE dbo.Products_SelectByProductID
(
    @ProductID int
)
AS
    SET NOCOUNT ON;
SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID

Krok 6. Tworzenie klasy warstwy logiki biznesowej

W całej serii samouczków staraliśmy się zachować architekturę warstwową, w której warstwa prezentacji wykonała wszystkie wywołania warstwy logiki biznesowej (BLL). Aby stosować się do tej decyzji projektowej, najpierw musimy utworzyć klasę BLL dla nowego typu zestawu danych, zanim będziemy mogli uzyskać dostęp do danych produktów z warstwy prezentacji.

Utwórz nowy plik klasy o nazwie ProductsBLLWithSprocs.cs w folderze ~/App_Code/BLL i dodaj do niego następujący kod:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using NorthwindWithSprocsTableAdapters;
[System.ComponentModel.DataObject]
public class ProductsBLLWithSprocs
{
    private ProductsTableAdapter _productsAdapter = null;
    protected ProductsTableAdapter Adapter
    {
        get
        {
            if (_productsAdapter == null)
                _productsAdapter = new ProductsTableAdapter();
            return _productsAdapter;
        }
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Select, true)]
    public NorthwindWithSprocs.ProductsDataTable GetProducts()
    {
        return Adapter.GetProducts();
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Select, false)]
    public NorthwindWithSprocs.ProductsDataTable GetProductByProductID(int productID)
    {
        return Adapter.GetProductByProductID(productID);
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Insert, true)]
    public bool AddProduct
        (string productName, int? supplierID, int? categoryID, 
         string quantityPerUnit, decimal? unitPrice, short? unitsInStock, 
         short? unitsOnOrder, short? reorderLevel, bool discontinued)
    {
        // Create a new ProductRow instance
        NorthwindWithSprocs.ProductsDataTable products = 
            new NorthwindWithSprocs.ProductsDataTable();
        NorthwindWithSprocs.ProductsRow product = products.NewProductsRow();
        product.ProductName = productName;
        if (supplierID == null) 
            product.SetSupplierIDNull(); 
        else 
            product.SupplierID = supplierID.Value;
        if (categoryID == null) 
            product.SetCategoryIDNull(); 
        else 
            product.CategoryID = categoryID.Value;
        if (quantityPerUnit == null) 
            product.SetQuantityPerUnitNull(); 
        else 
            product.QuantityPerUnit = quantityPerUnit;
        if (unitPrice == null) 
            product.SetUnitPriceNull(); 
        else 
            product.UnitPrice = unitPrice.Value;
        if (unitsInStock == null) 
            product.SetUnitsInStockNull(); 
        else 
            product.UnitsInStock = unitsInStock.Value;
        if (unitsOnOrder == null) 
            product.SetUnitsOnOrderNull(); 
        else 
            product.UnitsOnOrder = unitsOnOrder.Value;
        if (reorderLevel == null)
            product.SetReorderLevelNull(); 
        else 
            product.ReorderLevel = reorderLevel.Value;
        product.Discontinued = discontinued;
        // Add the new product
        products.AddProductsRow(product);
        int rowsAffected = Adapter.Update(products);
        // Return true if precisely one row was inserted, otherwise false
        return rowsAffected == 1;
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Update, true)]
    public bool UpdateProduct
        (string productName, int? supplierID, int? categoryID, string quantityPerUnit,
        decimal? unitPrice, short? unitsInStock, short? unitsOnOrder, 
        short? reorderLevel, bool discontinued, int productID)
    {
        NorthwindWithSprocs.ProductsDataTable products = 
            Adapter.GetProductByProductID(productID);
        if (products.Count == 0)
            // no matching record found, return false
            return false;
        NorthwindWithSprocs.ProductsRow product = products[0];
        product.ProductName = productName;
        if (supplierID == null) 
            product.SetSupplierIDNull(); 
        else 
            product.SupplierID = supplierID.Value;
        if (categoryID == null) 
            product.SetCategoryIDNull(); 
        else 
            product.CategoryID = categoryID.Value;
        if (quantityPerUnit == null) 
            product.SetQuantityPerUnitNull(); 
        else 
            product.QuantityPerUnit = quantityPerUnit;
        if (unitPrice == null) 
            product.SetUnitPriceNull(); 
        else 
            product.UnitPrice = unitPrice.Value;
        if (unitsInStock == null) 
            product.SetUnitsInStockNull(); 
        else 
            product.UnitsInStock = unitsInStock.Value;
        if (unitsOnOrder == null) 
            product.SetUnitsOnOrderNull(); 
        else 
            product.UnitsOnOrder = unitsOnOrder.Value;
        if (reorderLevel == null) 
            product.SetReorderLevelNull(); 
        else 
            product.ReorderLevel = reorderLevel.Value;
        product.Discontinued = discontinued;
        // Update the product record
        int rowsAffected = Adapter.Update(product);
        // Return true if precisely one row was updated, otherwise false
        return rowsAffected == 1;
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Delete, true)]
    public bool DeleteProduct(int productID)
    {
        int rowsAffected = Adapter.Delete(productID);
        // Return true if precisely one row was deleted, otherwise false
        return rowsAffected == 1;
    }
}

Ta klasa naśladuje ProductsBLL semantykę klasy z wcześniejszych samouczków, ale używa obiektów ProductsTableAdapter i ProductsDataTable z zestawu danych NorthwindWithSprocs. Na przykład, zamiast mieć instrukcję using NorthwindTableAdapters na początku pliku klasy, tak jak w przypadku ProductsBLL, klasa ProductsBLLWithSprocs używa using NorthwindWithSprocsTableAdapters. Podobnie obiekty ProductsDataTable i ProductsRow, używane w tej klasie, są poprzedzone przestrzenią nazw NorthwindWithSprocs. Klasa ProductsBLLWithSprocs udostępnia dwie metody dostępu do danych, GetProducts, GetProductByProductID, oraz metody dodawania, aktualizowania i usuwania pojedynczego wystąpienia produktu.

Krok 7. Praca z zestawemNorthwindWithSprocsdanych z warstwy prezentacji

W tym momencie utworzyliśmy DAL, który używa procedur składowanych do uzyskiwania dostępu do danych bazy danych i ich modyfikowania. Utworzyliśmy również podstawowe BLL z metodami pobierania wszystkich produktów lub określonego produktu wraz z metodami dodawania, aktualizowania i usuwania produktów. Aby zaokrąglić ten samouczek, utwórzmy stronę ASP.NET, która używa klasy BLL do wyświetlania ProductsBLLWithSprocs , aktualizowania i usuwania rekordów.

NewSprocs.aspx Otwórz stronę w folderze AdvancedDAL i przeciągnij element GridView z narzędzi do interfejsu projektanta, nazywając go Products. W tagu inteligentnym GridView wybierz powiązanie go z nowym obiektem ObjectDataSource o nazwie ProductsDataSource. Skonfiguruj obiekt ObjectDataSource do używania klasy, jak pokazano na rysunku ProductsBLLWithSprocs 22.

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

Lista rozwijana na karcie SELECT ma dwie opcje: GetProducts i GetProductByProductID. Ponieważ chcemy wyświetlić wszystkie produkty w GridView, wybierz metodę GetProducts. Listy rozwijane na kartach UPDATE, INSERT i DELETE mają tylko jedną metodę. Upewnij się, że każda z tych list rozwijanych ma wybraną odpowiednią metodę, a następnie kliknij przycisk Zakończ.

Po zakończeniu pracy kreatora ObjectDataSource program Visual Studio doda pola BoundFields i CheckBoxField do kontrolki GridView dla pól danych produktu. Włącz funkcje wbudowane w kontrolkę GridView, takie jak edytowanie i usuwanie, poprzez zaznaczenie opcji Włącz edytowanie i Włącz usuwanie w etykiecie inteligentnej.

Rysunek 23. Strona zawiera kontrolkę GridView z włączoną obsługą edycji i usuwania (kliknij, aby wyświetlić obraz pełnowymiarowy)

Jak wspomniano w poprzednich samouczkach, po ukończeniu kreatora ObjectDataSource program Visual Studio ustawia właściwość OldValuesParameterFormatString na oryginalną wartość {0}. Należy przywrócić jego wartość {0} domyślną, aby funkcje modyfikacji danych działały prawidłowo, biorąc pod uwagę parametry oczekiwane przez metody w naszej usłudze BLL. W związku z tym należy ustawić OldValuesParameterFormatString właściwość na {0} lub całkowicie usunąć właściwość ze składni deklaratywnej.

Po ukończeniu pracy Kreatora konfigurowania źródła danych, włączeniu obsługi edytowania i usuwania w GridView oraz zwróceniu właściwości ObjectDataSource do OldValuesParameterFormatString wartości domyślnej, znaczniki deklaratywne strony powinny wyglądać podobnie jak poniższe.

<asp:GridView ID="Products" runat="server" AutoGenerateColumns="False" 
    DataKeyNames="ProductID" DataSourceID="ProductsDataSource">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
        <asp:BoundField DataField="ProductID" HeaderText="ProductID" 
            InsertVisible="False" ReadOnly="True" 
            SortExpression="ProductID" />
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
            SortExpression="ProductName" />
        <asp:BoundField DataField="SupplierID" HeaderText="SupplierID" 
            SortExpression="SupplierID" />
        <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" 
            SortExpression="CategoryID" />
        <asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" 
            SortExpression="QuantityPerUnit" />
        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" 
            SortExpression="UnitPrice" />
        <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" 
            SortExpression="UnitsInStock" />
        <asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder" 
            SortExpression="UnitsOnOrder" />
        <asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel" 
            SortExpression="ReorderLevel" />
        <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" 
            SortExpression="Discontinued" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ProductsDataSource" runat="server" 
    DeleteMethod="DeleteProduct" InsertMethod="AddProduct" 
    SelectMethod="GetProducts" TypeName="ProductsBLLWithSprocs" 
    UpdateMethod="UpdateProduct">
    <DeleteParameters>
        <asp:Parameter Name="productID" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
        <asp:Parameter Name="productName" Type="String" />
        <asp:Parameter Name="supplierID" Type="Int32" />
        <asp:Parameter Name="categoryID" Type="Int32" />
        <asp:Parameter Name="quantityPerUnit" Type="String" />
        <asp:Parameter Name="unitPrice" Type="Decimal" />
        <asp:Parameter Name="unitsInStock" Type="Int16" />
        <asp:Parameter Name="unitsOnOrder" Type="Int16" />
        <asp:Parameter Name="reorderLevel" Type="Int16" />
        <asp:Parameter Name="discontinued" Type="Boolean" />
        <asp:Parameter Name="productID" Type="Int32" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Name="productName" Type="String" />
        <asp:Parameter Name="supplierID" Type="Int32" />
        <asp:Parameter Name="categoryID" Type="Int32" />
        <asp:Parameter Name="quantityPerUnit" Type="String" />
        <asp:Parameter Name="unitPrice" Type="Decimal" />
        <asp:Parameter Name="unitsInStock" Type="Int16" />
        <asp:Parameter Name="unitsOnOrder" Type="Int16" />
        <asp:Parameter Name="reorderLevel" Type="Int16" />
        <asp:Parameter Name="discontinued" Type="Boolean" />
    </InsertParameters>
</asp:ObjectDataSource>

W tym momencie możemy uporządkować element GridView, dostosowując interfejs edycji w celu uwzględnienia walidacji oraz przekształcenia kolumn CategoryID i SupplierID na listy rozwijane itd. Możemy również dodać potwierdzenie po stronie klienta do przycisku Usuń i zachęcam do podjęcia czasu na wdrożenie tych ulepszeń. Ponieważ te tematy zostały omówione w poprzednich samouczkach, nie omówimy ich ponownie tutaj.

Niezależnie od tego, czy ulepszysz widok GridView, czy nie, przetestuj podstawowe funkcje strony w przeglądarce. Jak pokazano na rysunku 24, na stronie produkty są wyświetlane w widoku GridView, który umożliwia edytowanie i usuwanie wierszy.

Rysunek 24. Produkty można wyświetlać, edytować i usuwać z widoku GridView (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Podsumowanie

Klasy TableAdapters w typizowanym zestawie danych mogą uzyskiwać dostęp do danych z bazy danych przy użyciu instrukcji ad hoc SQL lub procedur składowanych. Podczas pracy z procedurami składowanymi można użyć istniejących procedur składowanych lub można poinstruować kreatora TableAdapter, aby utworzył nowe procedury składowane na podstawie zapytania SELECT. W tym samouczku zbadaliśmy, jak procedury składowane mogą być automatycznie utworzone dla nas.

Co prawda automatyczne generowanie procedur składowanych pozwala zaoszczędzić czas, istnieją jednak przypadki, w których procedura składowana stworzona przez czarodzieja nie odpowiada temu, co sami byśmy stworzyli. Jednym z przykładów jest procedura składowana Products_Update, która oczekiwała zarówno @Original_ProductID i @ProductID jako parametrów wejściowych, mimo że parametr @Original_ProductID był zbyteczny.

W wielu scenariuszach procedury składowane mogły już zostać utworzone lub możemy chcieć utworzyć je ręcznie, aby mieć dokładnszy stopień kontroli nad poleceniami procedury składowanej. W obu przypadkach chcielibyśmy poinstruować TableAdapter, aby korzystał z istniejących procedur składowanych dla jego metod. Zobaczymy, jak to zrobić w następnym samouczku.

Szczęśliwe programowanie!

Dalsza lektura

Aby uzyskać więcej informacji na temat tematów omówionych w tym samouczku, zapoznaj się z następującymi zasobami:

• Tworzenie i obsługa procedur składowanych
• Procedury składowane: przegląd
• Utwórz procedurę składowaną

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łówny recenzent tego samouczka to Hilton Geisenow. Chcesz przejrzeć nadchodzące artykuły MSDN? Jeśli tak, napisz do mnie na adres mitchell@4GuysFromRolla.com.

Następny