Używanie zapytań sparametryzowanych z kontrolką SqlDataSource (VB)

Autor : Scott Mitchell

Pobierz plik PDF

W tym samouczku przyjrzymy się kontrolce SqlDataSource i dowiesz się, jak definiować zapytania sparametryzowane. Parametry można określić zarówno deklaratywnie, jak i programowo, i można je pobierać z wielu lokalizacji, takich jak ciąg zapytania, stan sesji, inne kontrolki i inne.

Wprowadzenie

W poprzednim samouczku pokazano, jak używać kontrolki SqlDataSource do pobierania danych bezpośrednio z bazy danych. Korzystając z kreatora Konfigurowanie źródła danych, możemy wybrać bazę danych, a następnie wybrać kolumny, które mają zostać zwrócone z tabeli lub widoku; wprowadź niestandardową instrukcję SQL; lub użyj procedury składowanej. Niezależnie od tego, czy wybieranie kolumn z tabeli lub widoku, czy wprowadzanie niestandardowej instrukcji SQL, właściwość s kontrolki SelectCommand SqlDataSource jest przypisywana do wynikowej instrukcji ad hoc SQL SELECT i jest SELECT to instrukcja wykonywana podczas wywoływania metody SqlDataSource Select() (programowo lub automatycznie z kontrolki internetowej danych).

Instrukcje SQL SELECT używane w poprzednich pokazach samouczka nie zawierały WHERE klauzul. W instrukcji klauzula SELECTWHERE może służyć do ograniczania zwracanych wyników. Aby na przykład wyświetlić nazwy produktów kosztujących ponad 50,00 USD, możemy użyć następującego zapytania:

SELECT ProductName
FROM Products
WHERE UnitPrice > 50.00

Zazwyczaj wartości używane w WHERE klauzuli są określane przez niektóre źródła zewnętrzne, takie jak wartość ciągu zapytania, zmienna sesji lub dane wejściowe użytkownika z kontrolki sieci Web na stronie. W idealnym przypadku takie dane wejściowe są określane przy użyciu parametrów. W przypadku SQL Server firmy Microsoft parametry są oznaczane za pomocą @parameterNamepolecenia , jak w:

SELECT ProductName
FROM Products
WHERE UnitPrice > @Price

Usługa SqlDataSource obsługuje zapytania parametryzowane zarówno dla SELECT instrukcji, jak i INSERTinstrukcji , UPDATEi DELETE . Ponadto wartości parametrów można automatycznie pobierać z różnych źródeł, do których można programowo przypisać ciąg zapytania, stan sesji, kontrolki na stronie itd. W tym samouczku zobaczymy, jak definiować sparametryzowane zapytania, a także jak określać wartości parametrów zarówno deklaratywnie, jak i programowo.

Uwaga

W poprzednim samouczku porównaliśmy element ObjectDataSource, który był naszym narzędziem do wyboru w ramach pierwszych 46 samouczków z bazą danych SqlDataSource, zwracając na nie uwagi na ich podobieństwa koncepcyjne. Podobieństwa te rozszerzają się również na parametry. Parametry obiektu ObjectDataSource zamapowane na parametry wejściowe metod w warstwie logiki biznesowej. W przypadku elementu SqlDataSource parametry są definiowane bezpośrednio w zapytaniu SQL. Obie kontrolki mają kolekcje parametrów dla ich Select()metod , Insert(), Update()i Delete() i mogą mieć te wartości parametrów wypełnione ze wstępnie zdefiniowanych źródeł (wartości ciągu zapytania, zmiennych sesji itd.) lub przypisane programowo.

Tworzenie zapytania parametrycznego

Kreator konfigurowania źródła danych kontrolki SqlDataSource oferuje trzy możliwości definiowania polecenia do wykonania w celu pobrania rekordów bazy danych:

• Wybierając kolumny z istniejącej tabeli lub widoku,
• Wprowadzając niestandardową instrukcję SQL lub
• Wybierając procedurę składowaną

Podczas wybierania kolumn z istniejącej tabeli lub widoku należy określić parametry WHERE klauzuli za pomocą okna dialogowego Dodawanie WHERE klauzuli. Podczas tworzenia niestandardowej instrukcji SQL można jednak wprowadzić parametry bezpośrednio do klauzuli WHERE (przy użyciu polecenia @parameterName , aby oznaczyć każdy parametr). Procedura składowana składa się z co najmniej jednej instrukcji SQL, a te instrukcje można sparametryzować. Parametry używane w instrukcjach SQL muszą jednak zostać przekazane jako parametry wejściowe do procedury składowanej.

Ponieważ tworzenie zapytania sparametryzowanego zależy od sposobu określenia parametru SelectCommand SqlDataSource, przyjrzyjmy się wszystkim trzem metodom. Aby rozpocząć, otwórz ParameterizedQueries.aspx stronę w folderzeSqlDataSource, przeciągnij kontrolkę SqlDataSource z przybornika do Projektant i ustaw jej ID wartość na Products25BucksAndUnderDataSource. Następnie kliknij link Konfiguruj źródło danych z tagu inteligentnego kontrolki. Wybierz bazę danych do użycia (NORTHWINDConnectionString), a następnie kliknij przycisk Dalej.

Krok 1. Dodawanie klauzuli WHERE podczas wybierania kolumn z tabeli lub widoku

Podczas wybierania danych, które mają być zwracane z bazy danych za pomocą kontrolki SqlDataSource, kreator Konfigurowanie źródła danych umożliwia nam po prostu wybranie kolumn do zwrócenia z istniejącej tabeli lub widoku (zobacz Rysunek 1). W ten sposób automatycznie tworzy instrukcję SQL SELECT , która jest wysyłana do bazy danych po wywołaniu metody SqlDataSource Select() . Tak jak w poprzednim samouczku, wybierz tabelę Products z listy rozwijanej i zaznacz ProductIDkolumny , ProductNamei UnitPrice .

Rysunek 1. Wybieranie kolumn do zwrócenia z tabeli lub widoku (kliknij, aby wyświetlić obraz pełnowymiarowy)

Aby dołączyć klauzulę WHERE do instrukcji SELECT , kliknij WHERE przycisk, który powoduje wyświetlenie okna dialogowego Dodawanie WHERE klauzuli (zobacz Rysunek 2). Aby dodać parametr, aby ograniczyć wyniki zwracane przez SELECT zapytanie, najpierw wybierz kolumnę, aby filtrować dane według. Następnie wybierz operator, który ma być używany do filtrowania (=, <, <= >, , itd.). Na koniec wybierz źródło wartości parametru, na przykład ze stanu zapytania lub sesji. Po skonfigurowaniu parametru kliknij przycisk Dodaj, aby uwzględnić go w zapytaniu SELECT .

W tym przykładzie zwróćmy tylko te wyniki, w których UnitPrice wartość jest mniejsza lub równa 25,00 USD. W związku z tym wybierz UnitPrice z listy rozwijanej Kolumna i <= z listy rozwijanej Operator. W przypadku korzystania z zakodowanej wartości parametru (takiej jak $25.00) lub jeśli wartość parametru ma być określona programowo, wybierz pozycję Brak z listy rozwijanej Źródło. Następnie wprowadź zakodowaną wartość parametru w polu tekstowym Wartość 25.00 i ukończ proces, klikając przycisk Dodaj.

Rysunek 2. Ogranicz wyniki zwrócone z okna dialogowego Dodawanie WHERE klauzuli (kliknij, aby wyświetlić obraz pełnowymiarowy)

Po dodaniu parametru kliknij przycisk OK, aby powrócić do Kreatora konfigurowania źródła danych. Instrukcja SELECT w dolnej części kreatora powinna teraz zawierać klauzulę WHERE z parametrem o nazwie @UnitPrice:

SELECT [ProductID], [ProductName], [UnitPrice]
FROM [Products]
WHERE ([UnitPrice] <= @UnitPrice)

Uwaga

Jeśli określisz wiele warunków w klauzuli WHERE w oknie dialogowym Dodawanie WHERE klauzuli, kreator łączy je z operatorem AND . Jeśli musisz dołączyć element OR do klauzuli WHERE (na przykład WHERE UnitPrice <= @UnitPrice OR Discontinued = 1), musisz skompilować instrukcję SELECT za pomocą niestandardowego ekranu instrukcji SQL.

Zakończ konfigurowanie elementu SqlDataSource (kliknij przycisk Dalej, a następnie zakończ), a następnie sprawdź deklaratywne znaczniki sqlDataSource. Znacznik zawiera <SelectParameters> teraz kolekcję, która określa źródła parametrów w elemecie SelectCommand.

<asp:SqlDataSource ID="Products25BucksAndUnderDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
    SelectCommand=
        "SELECT [ProductID], [ProductName], [UnitPrice]
        FROM [Products] WHERE ([UnitPrice] <= @UnitPrice)">
    <SelectParameters>
        <asp:Parameter DefaultValue="25.00" Name="UnitPrice" Type="Decimal" />
    </SelectParameters>
</asp:SqlDataSource>

Po wywołaniu metody sqlDataSource Select() wartość parametru (25.00) jest stosowana do parametru @UnitPrice w SelectCommand obiekcie przed wysłaniem do bazy danych.UnitPrice Wynikiem netto jest to, że z tabeli zwracane Products są tylko te produkty mniejsze lub równe 25,00 USD. Aby to potwierdzić, dodaj element GridView do strony, powiąż go ze źródłem danych, a następnie wyświetl stronę za pośrednictwem przeglądarki. Powinny być widoczne tylko te produkty, które są mniejsze lub równe 25,00 USD, jak potwierdza Rysunek 3.

Rysunek 3. Wyświetlane są tylko te produkty mniejsze niż lub równe 25,00 USD (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Krok 2. Dodawanie parametrów do niestandardowej instrukcji SQL

Podczas dodawania niestandardowej instrukcji SQL można jawnie wprowadzić WHERE klauzulę lub określić wartość w komórce Filtr konstruktora zapytań. Aby to zademonstrować, pokażmy tylko te produkty w siatce, której ceny są mniejsze niż określony próg. Zacznij od dodania kontrolki ParameterizedQueries.aspx TextBox do strony, aby zebrać tę wartość progową od użytkownika. Ustaw właściwość TextBox ID na MaxPricewartość . Dodaj kontrolkę Sieć Web przycisku i ustaw jej Text właściwość na Display Matching Products .

Następnie przeciągnij element GridView na stronę i z tagu inteligentnego wybierz pozycję , aby utworzyć nowy element SqlDataSource o nazwie ProductsFilteredByPriceDataSource. W kreatorze Konfigurowanie źródła danych przejdź do ekranu Określanie niestandardowej instrukcji SQL lub procedury składowanej (zobacz Rysunek 4) i wprowadź następujące zapytanie:

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice <= @MaximumPrice OR @MaximumPrice = -1.0

Po wprowadzeniu zapytania (ręcznie lub za pośrednictwem Konstruktora zapytań) kliknij przycisk Dalej.

Rysunek 4. Zwracanie tylko tych produktów mniejszych niż lub równych wartości parametru (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Ponieważ zapytanie zawiera parametry, następny ekran w kreatorze monituje nas o źródło wartości parametrów. Wybierz pozycję Kontrolka z listy rozwijanej Źródło parametrów i MaxPrice (wartość kontrolki ID TextBox) z listy rozwijanej ControlID. Możesz również wprowadzić opcjonalną wartość domyślną, która ma być używana w przypadku, gdy użytkownik nie wprowadził żadnego tekstu do kontrolki MaxPrice TextBox. W tym czasie nie wprowadzaj wartości domyślnej.

Rysunek 5. Właściwość MaxPrice TextBox jest Text używana jako źródło parametrów (kliknij, aby wyświetlić obraz pełnowymiarowy)

Ukończ kreatora Konfigurowanie źródła danych, klikając przycisk Dalej, a następnie zakończ. Znaczniki deklaratywne dla elementów GridView, TextBox, Button i SqlDataSource są następujące:

Maximum price:
$<asp:TextBox ID="MaxPrice" runat="server" Columns="5" />
 
<asp:Button ID="DisplayProductsLessThanButton" runat="server"
    Text="Display Matching Products" />
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"
    DataSourceID="ProductsFilteredByPriceDataSource" EnableViewState="False">
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="Product"
            SortExpression="ProductName" />
        <asp:BoundField DataField="UnitPrice" HeaderText="Price"
            HtmlEncode="False" DataFormatString="{0:c}"
            SortExpression="UnitPrice" />
    </Columns>
</asp:GridView>
<asp:SqlDataSource ID="ProductsFilteredByPriceDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
    SelectCommand=
        "SELECT ProductName, UnitPrice 
        FROM Products WHERE UnitPrice <= @MaximumPrice">
    <SelectParameters>
        <asp:ControlParameter ControlID="MaxPrice" Name="MaximumPrice"
            PropertyName="Text" />
    </SelectParameters>
</asp:SqlDataSource>

Należy pamiętać, że parametr w sekcji SqlDataSource <SelectParameters> to ControlParameter, który zawiera dodatkowe właściwości, takie jak ControlID i PropertyName. Po wywołaniu ControlParameter metody sqlDataSource Select() metoda pobiera wartość z określonej właściwości kontrolki sieci Web i przypisuje ją do odpowiedniego parametru SelectCommandw obiekcie . W tym przykładzie MaxPrice właściwość Text jest używana jako wartość parametru @MaxPrice .

Poświęć chwilę, aby wyświetlić tę stronę za pośrednictwem przeglądarki. Podczas pierwszej wizyty na stronie lub gdy kontrolka MaxPrice TextBox nie ma wartości, w siatce GridView nie są wyświetlane żadne rekordy.

Rysunek 6. Brak rekordów wyświetlanych, gdy MaxPrice pole tekstowe jest puste (kliknij, aby wyświetlić obraz w pełnym rozmiarze)

Przyczyną, dla którego nie są wyświetlane żadne produkty, jest to, że domyślnie pusty ciąg wartości parametru jest konwertowany na wartość bazy danych NULL . Ponieważ porównanie [UnitPrice] <= NULL zawsze jest obliczane jako Fałsz, żadne wyniki nie są zwracane.

Wprowadź wartość w polu tekstowym, na przykład 5.00, i kliknij przycisk Wyświetl pasujące produkty. Po wystąpieniu zwrotnym źródło danych SqlDataSource informuje obiekt GridView, że jedno ze źródeł parametrów uległo zmianie. W związku z tym element GridView ponownie połączy się z usługą SqlDataSource, wyświetlając te produkty poniżej lub równe 5,00 USD.

Rysunek 7. Wyświetlane są produkty mniejsze niż lub równe 5,00 USD (kliknij, aby wyświetlić obraz w pełnym rozmiarze)

Początkowo wyświetlanie wszystkich produktów

Zamiast wyświetlać żadnych produktów po pierwszym załadowaniu strony, możemy chcieć wyświetlić wszystkie produkty. Jednym ze sposobów wyświetlania listy wszystkich produktów zawsze, gdy MaxPrice pole TextBox jest puste, jest ustawienie wartości domyślnej parametru na nieco szalenie wysoką wartość, na przykład 1000000, ponieważ jest mało prawdopodobne, że firma Northwind Traders będzie mieć zapasy, których cena jednostkowa przekracza 1000 000 USD. Jednak takie podejście jest krótkowzroczne i może nie działać w innych sytuacjach.

W poprzednich samouczkach — parametry deklaratywne i filtrowanie wzorca/szczegółów za pomocą listy rozwijanej, z jaką mieliśmy do czynienia z podobnym problemem. Nasze rozwiązanie miało na celu umieszczenie tej logiki w warstwie logiki biznesowej. W szczególności usługa BLL zbadała wartość przychodzącą, a jeśli była NULL to wartość zarezerwowana, wywołanie zostało przekierowane do metody DAL, która zwróciła wszystkie rekordy. Jeśli wartość przychodząca była normalną wartością filtrowania, wywołanie zostało wykonane do metody DAL, która wykonała instrukcję SQL, która użyła klauzuli sparametryzowanej WHERE z podaną wartością.

Niestety, pomijamy architekturę w przypadku korzystania z usługi SqlDataSource. Zamiast tego musimy dostosować instrukcję SQL, aby inteligentnie pobrać wszystkie rekordy, jeśli @MaximumPrice parametr ma NULL wartość zarezerwowaną lub wartość zarezerwowaną. W tym ćwiczeniu załóżmy, że jeśli @MaximumPrice parametr jest równy -1.0, zwracane są wszystkie rekordy (-1.0 działa jako wartość zarezerwowana, ponieważ żaden produkt nie może mieć wartości ujemnej UnitPrice ). Aby to osiągnąć, możemy użyć następującej instrukcji SQL:

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice <= @MaximumPrice OR @MaximumPrice = -1.0

Ta WHERE klauzula @MaximumPrice zwraca wszystkie rekordy, jeśli parametr jest -1.0równy . Jeśli wartość parametru to nie -1.0, zwracane są tylko te produkty, których UnitPrice wartość jest mniejsza lub równa wartości parametru @MaximumPrice . Ustawiając domyślną wartość parametru @MaximumPrice na -1.0, podczas pierwszego ładowania strony (lub za każdym razem, gdy MaxPrice pole TextBox jest puste), @MaximumPrice będzie mieć wartość -1.0 i zostaną wyświetlone wszystkie produkty.

Rysunek 8. Teraz wszystkie produkty są wyświetlane, gdy MaxPrice pole tekstowe jest puste (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Istnieje kilka zastrzeżeń do zanotowania przy użyciu tego podejścia. Najpierw należy pamiętać, że typ danych parametru jest wywnioskowany przez jego użycie w zapytaniu SQL. Jeśli zmienisz klauzulę WHERE z @MaximumPrice = -1.0 na @MaximumPrice = -1, środowisko uruchomieniowe traktuje parametr jako liczbę całkowitą. Jeśli następnie spróbujesz przypisać element MaxPrice TextBox do wartości dziesiętnej (na przykład 5,00 ), wystąpi błąd, ponieważ nie może przekonwertować wartości 5.00 na liczbę całkowitą. Aby rozwiązać ten problem, upewnij się, że używasz @MaximumPrice = -1.0 klauzuli WHERE lub, jeszcze lepiej, ustaw ControlParameter właściwość obiektu Type na Wartość Dziesiętna .

Po drugie, dodając element OR @MaximumPrice = -1.0 do WHERE klauzuli , aparat zapytań nie może użyć indeksu UnitPrice (zakładając, że istnieje), co powoduje skanowanie tabeli. Może to mieć wpływ na wydajność, jeśli w Products tabeli istnieje wystarczająco duża liczba rekordów. Lepszym rozwiązaniem jest przeniesienie tej logiki do procedury składowanej, w której IF instrukcja wykonuje SELECT zapytanie z Products tabeli bez WHERE klauzuli, gdy wszystkie rekordy muszą zostać zwrócone lub jedna, której WHERE klauzula zawiera tylko UnitPrice kryteria, aby można było użyć indeksu.

Krok 3. Tworzenie i używanie sparametryzowanych procedur składowanych

Procedury składowane mogą zawierać zestaw parametrów wejściowych, które mogą być następnie używane w instrukcjach SQL zdefiniowanych w procedurze składowanej. Podczas konfigurowania elementu SqlDataSource do używania procedury składowanej, która akceptuje parametry wejściowe, można określić te wartości parametrów przy użyciu tych samych technik, co w przypadku instrukcji ad hoc JĘZYKA SQL.

Aby zilustrować użycie procedur składowanych w usłudze SqlDataSource, utwórzmy nową procedurę składowaną w bazie danych Northwind o nazwie , która akceptuje parametr o nazwie GetProductsByCategory@CategoryID i zwraca wszystkie kolumny produktów, których CategoryID kolumna jest zgodna @CategoryIDz parametrem . Aby utworzyć procedurę składowaną, przejdź do Eksploratora serwera i przejdź do NORTHWND.MDF szczegółów bazy danych. (Jeśli eksplorator serwera nie jest widoczny, wyświetl go, przechodząc do menu Widok i wybierając opcję Eksplorator serwera).

NORTHWND.MDF W bazie danych kliknij prawym przyciskiem myszy folder Procedury składowane, wybierz polecenie Dodaj nową procedurę składowaną i wprowadź następującą składnię:

CREATE PROCEDURE dbo.GetProductsByCategory
(
      @CategoryID int
)
AS
SELECT *
FROM Products
WHERE CategoryID = @CategoryID

Kliknij ikonę Zapisz (lub Ctrl+S), aby zapisać procedurę składowaną. Procedurę składowaną można przetestować, klikając ją prawym przyciskiem myszy w folderze Procedury składowane i wybierając polecenie Wykonaj. Spowoduje to wyświetlenie monitu o parametry procedury składowanej (@CategoryIDw tym przypadku), po którym wyniki zostaną wyświetlone w oknie Dane wyjściowe.

@CategoryID z 1" />

Rysunek 9. GetProductsByCategory Procedura składowana wykonywana przy użyciu wartości @CategoryID 1 (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Użyjmy tej procedury składowanej, aby wyświetlić wszystkie produkty w kategorii Napoje w siatce. Dodaj nowy obiekt GridView do strony i powiąż go z nową bazą danych SqlDataSource o nazwie BeverageProductsDataSource. Przejdź do ekranu Określanie niestandardowej instrukcji SQL lub procedury składowanej, wybierz przycisk radiowy Procedura składowana i wybierz GetProductsByCategory procedurę składowaną z listy rozwijanej.

Rysunek 10. Wybierz procedurę GetProductsByCategory składowaną z listy Drop-Down (kliknij, aby wyświetlić obraz pełnowymiarowy)

Ponieważ procedura składowana akceptuje parametr wejściowy (@CategoryID), kliknięcie przycisku Dalej powoduje wyświetlenie monitu o określenie źródła dla tej wartości parametru. Napoje CategoryID mają wartość 1, więc pozostaw listę rozwijaną Źródło parametrów na wartość Brak i wprowadź wartość 1 w polu tekstowym DefaultValue.

Rysunek 11. Użyj wartości Hard-Coded 1, aby zwrócić produkty w kategorii Napoje (kliknij, aby wyświetlić obraz pełnowymiarowy)

Jak pokazuje poniższy znacznik deklaratywny, w przypadku korzystania z procedury składowanej właściwość SqlDataSource SelectCommand jest ustawiona na nazwę procedury składowanej, a SelectCommandType właściwość jest ustawiona na StoredProcedure, wskazując, że SelectCommand jest to nazwa procedury składowanej, a nie ad hoc instrukcji SQL.

<asp:SqlDataSource ID="BeverageProductsDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
    SelectCommand="GetProductsByCategory" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:Parameter DefaultValue="1" Name="CategoryID" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

Przetestuj stronę w przeglądarce. Wyświetlane są tylko te produkty należące do kategorii Napoje, chociaż wszystkie pola produktu są wyświetlane, ponieważ GetProductsByCategory procedura składowana zwraca wszystkie kolumny z Products tabeli. Oczywiście możemy ograniczyć lub dostosować pola wyświetlane w siatce z okna dialogowego Edytowanie kolumn kontrolki GridView.

Rysunek 12. Wszystkie napoje są wyświetlane (kliknij, aby wyświetlić obraz pełnowymiarowy)

Krok 4. Programowe wywoływanie instrukcji Select() elementu SqlDataSource

Przykłady, które widzieliśmy w poprzednim samouczku, i w tym samouczku do tej pory powiązaliśmy kontrolki SqlDataSource bezpośrednio z kontrolką GridView. Dane kontrolki SqlDataSource mogą być jednak uzyskiwane programowo i wyliczane w kodzie. Może to być szczególnie przydatne, gdy trzeba wykonać zapytanie o dane, aby je sprawdzić, ale nie trzeba ich wyświetlać. Zamiast pisać cały standardowy kod ADO.NET, aby połączyć się z bazą danych, określić polecenie i pobrać wyniki, możesz pozwolić usłudze SqlDataSource obsłużyć ten kod monotonny.

Aby programowo zilustrować pracę z danymi usługi SqlDataSource, wyobraź sobie, że szef zwrócił się do Ciebie z żądaniem utworzenia strony internetowej, która wyświetla nazwę losowo wybranej kategorii i skojarzonych z nią produktów. Oznacza to, że gdy użytkownik odwiedzi tę stronę, chcemy losowo wybrać kategorię z Categories tabeli, wyświetlić nazwę kategorii, a następnie wyświetlić listę produktów należących do tej kategorii.

Aby to osiągnąć, potrzebujemy dwóch kontrolek SqlDataSource, które mają pobrać losową kategorię z Categories tabeli, a drugą, aby pobrać produkty kategorii. Utworzymy element SqlDataSource, który pobiera losowy rekord kategorii w tym kroku; Krok 5 obejmuje tworzenie elementu SqlDataSource, który pobiera produkty kategorii.

Zacznij od dodania elementu SqlDataSource do ParameterizedQueries.aspx elementu i ustawienia elementu ID na RandomCategoryDataSource. Skonfiguruj go tak, aby używał następującego zapytania SQL:

SELECT TOP 1 CategoryID, CategoryName
FROM Categories
ORDER BY NEWID()

ORDER BY NEWID() Zwraca rekordy posortowane w kolejności losowej (zobacz Używanie NEWID() do losowego sortowania rekordów). SELECT TOP 1 Zwraca pierwszy rekord z zestawu wyników. Razem to zapytanie zwraca CategoryID wartości kolumn i CategoryName z pojedynczej, losowo wybranej kategorii.

Aby wyświetlić wartość kategorii CategoryName , dodaj kontrolkę Sieć Web etykiet do strony, ustaw jej ID właściwość na CategoryNameLabel, a następnie wyczyść jej Text właściwość. Aby programowo pobrać dane z kontrolki SqlDataSource, musimy wywołać jej Select() metodę. MetodaSelect() oczekuje pojedynczego parametru wejściowego typu DataSourceSelectArguments, który określa sposób, w jaki dane mają być przesyłane przed zwróceniem. Może to obejmować instrukcje dotyczące sortowania i filtrowania danych oraz są używane przez kontrolki sieci Web danych podczas sortowania lub stronicowania danych z kontrolki SqlDataSource. W naszym przykładzie nie potrzebujemy jednak, aby dane były modyfikowane przed zwróceniem, a w związku z tym przekażemy DataSourceSelectArguments.Empty obiekt .

Metoda Select() zwraca obiekt, który implementuje IEnumerableelement . Zwrócony dokładny typ zależy od wartości właściwości kontrolki DataSourceModeSqlDataSource. Zgodnie z opisem w poprzednim samouczku tę właściwość można ustawić na wartość DataSet lub DataReader. Jeśli ustawiono DataSetwartość , Select() metoda zwraca obiekt DataView . Jeśli jest ustawiona na DataReaderwartość , zwraca obiekt implementujący IDataReaderobiekt . Ponieważ właściwość RandomCategoryDataSource SqlDataSource jest DataSourceMode ustawiona na DataSet (wartość domyślna), będziemy pracować z obiektem DataView.

Poniższy kod ilustruje sposób pobierania rekordów z obiektu RandomCategoryDataSource SqlDataSource jako elementu DataView oraz odczytywania CategoryName wartości kolumny z pierwszego wiersza widoku danych:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles Me.Load
    ' Get the data from the SqlDataSource as a DataView
    Dim randomCategoryView As DataView = CType _
        (RandomCategoryDataSource.Select(DataSourceSelectArguments.Empty), DataView)
    If randomCategoryView.Count > 0 Then
        ' Assign the CategoryName value to the Label
        CategoryNameLabel.Text = String.Format( _
            "Here are Products in the {0} Category...", _
            randomCategoryView(0)("CategoryName").ToString())
    End If
End Sub

randomCategoryView(0) Funkcja zwraca pierwszy DataRowView element w widoku DataView. randomCategoryView(0)("CategoryName") Zwraca wartość kolumny CategoryName w tym pierwszym wierszu. Należy pamiętać, że element DataView jest luźno wpisany. Aby odwołać się do określonej wartości kolumny, musimy przekazać nazwę kolumny jako ciąg ( CategoryName, w tym przypadku). Rysunek 13 przedstawia komunikat wyświetlany podczas CategoryNameLabel wyświetlania strony. Oczywiście wyświetlana rzeczywista nazwa kategorii jest losowo wybierana przez RandomCategoryDataSource element SqlDataSource podczas każdej wizyty na stronie (w tym ogłaszania zwrotnego).

Rysunek 13. Wyświetlana jest losowo wybrana nazwa kategorii (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Uwaga

Gdyby właściwość s kontrolki DataSourceMode SqlDataSource została ustawiona na DataReaderwartość , wartość zwracana z Select() metody musiałaby zostać rzutowana na IDataReaderwartość . Aby odczytać CategoryName wartość kolumny z pierwszego wiersza, użyjemy kodu takiego jak:

If randomCategoryReader.Read() Then
   Dim categoryName as String = randomCategoryReader("CategoryName').ToString()
   ...
End If

Po losowym wybraniu kategorii SqlDataSource możemy dodać obiekt GridView, który wyświetla listę produktów kategorii.

Uwaga

Zamiast używać kontrolki Etykieta w sieci Web do wyświetlania nazwy kategorii, możemy dodać element FormView lub DetailsView do strony, powiązanie jej z usługą SqlDataSource. Jednak użycie etykiety pozwoliło nam zbadać, jak programowo wywołać instrukcję sqlDataSource Select() i pracować z jej wynikowymi danymi w kodzie.

Krok 5. Programowe przypisywanie wartości parametrów

We wszystkich przykładach, które widzieliśmy do tej pory w tym samouczku, użyto zakodowanej wartości parametru lub jednej pobranej z jednego ze wstępnie zdefiniowanych źródeł parametrów (wartości ciągu zapytania, kontrolki sieci Web na stronie itd.). Jednak parametry kontrolki SqlDataSource można również ustawić programowo. Aby ukończyć nasz bieżący przykład, potrzebujemy elementu SqlDataSource, który zwraca wszystkie produkty należące do określonej kategorii. Ta usługa SqlDataSource będzie mieć CategoryID parametr, którego wartość musi być ustawiona na CategoryID podstawie wartości kolumny zwróconej przez usługę RandomCategoryDataSource SqlDataSource w procedurze obsługi zdarzeń Page_Load .

Zacznij od dodania elementu GridView do strony i powiązania go z nową usługą SqlDataSource o nazwie ProductsByCategoryDataSource. Podobnie jak w kroku 3, skonfiguruj usługę SqlDataSource tak, aby wywołała procedurę GetProductsByCategory składowaną. Pozostaw listę rozwijaną Źródło parametrów ustawioną na Wartość Brak, ale nie wprowadź wartości domyślnej, ponieważ ustawimy tę wartość domyślną programowo.

Rysunek 14. Nie określaj źródła parametru lub wartości domyślnej (kliknij, aby wyświetlić obraz pełnowymiarowy)

Po ukończeniu pracy kreatora SqlDataSource wynikowy znacznik deklaratywny powinien wyglądać podobnie do następującego:

<asp:SqlDataSource ID="ProductsByCategoryDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
    SelectCommand="GetProductsByCategory" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:Parameter Name="CategoryID" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

Parametr można przypisać DefaultValueCategoryID programowo w procedurze obsługi zdarzeń Page_Load :

' Assign the ProductsByCategoryDataSource's
' CategoryID parameter's DefaultValue property
ProductsByCategoryDataSource.SelectParameters("CategoryID").DefaultValue = _
    randomCategoryView(0)("CategoryID").ToString()

Po dodaniu strona zawiera element GridView, który pokazuje produkty skojarzone z losowo wybraną kategorią.

Rysunek 15. Nie określaj źródła parametrów lub wartości domyślnej (kliknij, aby wyświetlić obraz pełnowymiarowy)

Podsumowanie

Usługa SqlDataSource umożliwia deweloperom stron definiowanie sparametryzowanych zapytań, których wartości parametrów mogą być zakodowane wstępnie, ściągane ze wstępnie zdefiniowanych źródeł parametrów lub przypisywane programowo. W tym samouczku pokazano, jak utworzyć sparametryzowane zapytanie z kreatora Konfigurowanie źródła danych zarówno dla zapytań AD hoc SQL, jak i procedur składowanych. Przyjrzeliśmy się również używaniu zakodowanych w kodzie źródeł parametrów, kontrolki internetowej jako źródła parametrów i programowego określania wartości parametru.

Podobnie jak w przypadku obiektu ObjectDataSource, usługa SqlDataSource udostępnia również możliwości modyfikowania danych bazowych. W następnym samouczku przyjrzymy się sposobom definiowania INSERTinstrukcji , UPDATEi DELETE za pomocą usługi SqlDataSource. Po dodaniu tych instrukcji możemy użyć wbudowanych funkcji wstawiania, edytowania i usuwania funkcji związanych z kontrolkami GridView, DetailsView i FormView.

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 do niego dotrzeć pod adresem mitchell@4GuysFromRolla.com. Lub za pośrednictwem swojego bloga, który można znaleźć na stronie http://ScottOnWriting.NET.

Specjalne podziękowania

Ta seria samouczków została sprawdzona przez wielu pomocnych recenzentów. Recenzenci na potrzeby tego samouczka to Scott Clyde, Randell Schmidt i Ken Pespisa. Chcesz przejrzeć nadchodzące artykuły MSDN? Jeśli tak, upuść mi wiersz pod adresem mitchell@4GuysFromRolla.com.

WsteczDalej