Tworzenie procedur składowanych i funkcji zdefiniowanych przez użytkownika z kodem zarządzanym (VB)

Autor: Scott Mitchell

Pobierz plik PDF

Program Microsoft SQL Server 2005 integruje się ze środowiskiem uruchomieniowym języka wspólnego platformy .NET, aby umożliwić deweloperom tworzenie obiektów bazy danych za pomocą kodu zarządzanego. W tym samouczku przedstawiono sposób tworzenia zarządzanych procedur składowanych i zarządzanych funkcji zdefiniowanych przez użytkownika przy użyciu kodu Visual Basic lub C#. Zobaczymy również, jak te wersje programu Visual Studio umożliwiają debugowanie takich zarządzanych obiektów bazy danych.

Wprowadzenie

Bazy danych, takie jak microsoft SQL Server 2005, używają języka Transact-Structured Query Language (T-SQL) do wstawiania, modyfikowania i pobierania danych. Większość systemów baz danych zawiera konstrukcje służące do grupowania serii instrukcji SQL, które następnie można wykonać jako pojedynczą jednostkę wielokrotnego użytku. Procedury składowane są jednym z przykładów. Innym jest funkcja zdefiniowana przez użytkownika (UDF, User-Defined Functions), konstrukcja, która zostanie szczegółowo zbadana w kroku 9.

Podstawowa funkcja SQL jest przeznaczona do pracy z zestawami danych. Instrukcje SELECT, UPDATEi DELETE mają z założenia zastosowanie do wszystkich rekordów w odpowiedniej tabeli i są ograniczone tylko przez ich WHERE klauzule. Istnieje jednak wiele funkcji językowych przeznaczonych do pracy z jednym rekordem naraz i manipulowania danymi skalarnymi. CURSOR s zezwalają na pętlę zestawu rekordów pojedynczo. Funkcje manipulowania ciągami, takie jak LEFT, CHARINDEXi PATINDEX działają z danymi skalarnymi. Język SQL zawiera również instrukcje przepływu sterowania, takie jak IF i WHILE.

Przed programem Microsoft SQL Server 2005 procedury składowane i funkcje zdefiniowane przez użytkownika można zdefiniować tylko jako kolekcję instrukcji języka T-SQL. Program SQL Server 2005 został jednak zaprojektowany w celu zapewnienia integracji ze środowiskiem uruchomieniowym Common Language Runtime (CLR), który jest środowiskiem uruchomieniowym używanym przez wszystkie zestawy platformy .NET. W związku z tym procedury składowane i funkcje zdefiniowane przez użytkownika w bazie danych programu SQL Server 2005 można utworzyć przy użyciu kodu zarządzanego. Oznacza to, że można utworzyć procedurę składowaną lub funkcję UDF jako metodę w klasie Visual Basic. Dzięki temu te procedury składowane i funkcje zdefiniowane przez użytkownika umożliwiają korzystanie z funkcji w programie .NET Framework i z własnych klas niestandardowych.

W tym samouczku dowiesz się, jak utworzyć zarządzane procedury składowane i funkcje zdefiniowane przez użytkownika oraz jak zintegrować je z naszą bazą danych Northwind. Zacznijmy!

Uwaga

Obiekty zarządzanej bazy danych oferują pewne korzyści w porównaniu z ich odpowiednikami SQL. Bogactwo języka i znajomość oraz możliwość ponownego użycia istniejącego kodu i logiki są głównymi zaletami. Jednak obiekty zarządzanej bazy danych mogą być mniej wydajne podczas pracy z zestawami danych, które nie obejmują dużej ilości logiki proceduralnej. Aby zapoznać się z bardziej szczegółowym omówieniem zalet korzystania z kodu zarządzanego i języka T-SQL, zapoznaj się z tematem Zalety używania kodu zarządzanego do tworzenia obiektów bazy danych.

Krok 1. Przenoszenie bazy danych Northwind z App_Data

Wszystkie nasze samouczki do tej pory używały pliku bazy danych programu Microsoft SQL Server 2005 Express Edition w folderze aplikacji App_Data internetowej. Umieszczenie bazy danych w App_Data uproszczonej dystrybucji i uruchomienie tych samouczków, ponieważ wszystkie pliki znajdowały się w jednym katalogu i nie wymagały dodatkowych kroków konfiguracji do przetestowania samouczka.

W tym samouczku przenieśmy jednak bazę danych Northwind z App_Data bazy danych Northwind i jawnie zarejestrujmy ją w wystąpieniu bazy danych programu SQL Server 2005 Express Edition. Chociaż możemy wykonać kroki tego samouczka z bazą danych w folderze App_Data , wiele kroków jest znacznie prostszych przez jawne zarejestrowanie bazy danych w wystąpieniu bazy danych programu SQL Server 2005 Express Edition.

Pobieranie dla tego samouczka zawiera dwa pliki bazy danych — NORTHWND.MDF i NORTHWND_log.LDF — umieszczone w folderze o nazwie DataFiles. Jeśli korzystasz z własnej implementacji samouczków, zamknij program Visual Studio i przenieś NORTHWND.MDF pliki i NORTHWND_log.LDF z folderu witryny App_Data internetowej do folderu poza witryną internetową. Po przeniesieniu plików bazy danych do innego folderu musimy zarejestrować bazę danych Northwind przy użyciu wystąpienia bazy danych programu SQL Server 2005 Express Edition. Można to zrobić w programie SQL Server Management Studio. Jeśli na komputerze zainstalowano program SQL Server 2005 w wersji innej niż Express, prawdopodobnie masz już zainstalowany program Management Studio. Jeśli na komputerze masz tylko program SQL Server 2005 Express Edition, pośmiń chwilę na pobranie i zainstalowanie programu Microsoft SQL Server Management Studio.

Uruchom program SQL Server Management Studio. Jak pokazano na rysunku 1, program Management Studio rozpoczyna się od pytania, z jakim serwerem ma nawiązać połączenie. Wprowadź wartość localhost\SQLExpress jako nazwę serwera, wybierz pozycję Uwierzytelnianie systemu Windows z listy rozwijanej Uwierzytelnianie, a następnie kliknij pozycję Połącz.

Rysunek 1. Nawiązywanie połączenia z odpowiednim wystąpieniem bazy danych

Po nawiązaniu połączenia okno Eksplorator obiektów wyświetli informacje o wystąpieniu bazy danych programu SQL Server 2005 Express Edition, w tym o jego bazach danych, informacjach o zabezpieczeniach, opcjach zarządzania itd.

Musimy dołączyć bazę danych Northwind w DataFiles folderze (lub wszędzie tam, gdzie mogła zostać przeniesiona) do wystąpienia bazy danych programu SQL Server 2005 Express Edition. Kliknij prawym przyciskiem myszy folder Bazy danych i wybierz opcję Dołącz z menu kontekstowego. Spowoduje to wyświetlenie okna dialogowego Dołączanie baz danych. Kliknij przycisk Dodaj, przejdź do szczegółów odpowiedniego NORTHWND.MDF pliku, a następnie kliknij przycisk OK. Na tym etapie ekran powinien wyglądać podobnie do rysunku 2.

Rysunek 2. Nawiązywanie połączenia z odpowiednim wystąpieniem bazy danych (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Uwaga

Podczas nawiązywania połączenia z wystąpieniem programu SQL Server 2005 Express Edition za pośrednictwem programu Management Studio okno dialogowe Dołączanie baz danych nie umożliwia przechodzenia do szczegółów katalogów profilów użytkownika, takich jak Moje dokumenty. W związku z tym pamiętaj, aby umieścić NORTHWND.MDF pliki i NORTHWND_log.LDF w katalogu profilu innego niż użytkownik.

Kliknij przycisk OK, aby dołączyć bazę danych. Okno dialogowe Dołączanie baz danych zostanie zamknięte, a Eksplorator obiektów powinna teraz wyświetlić listę właśnie dołączonej bazy danych. Istnieje prawdopodobieństwo, że baza danych Northwind ma nazwę taką jak 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF. Zmień nazwę bazy danych na Northwind, klikając prawym przyciskiem myszy bazę danych i wybierając polecenie Zmień nazwę.

Rysunek 3. Zmiana nazwy bazy danych na Northwind

Krok 2. Tworzenie nowego rozwiązania i projektu programu SQL Server w programie Visual Studio

Aby utworzyć zarządzane procedury składowane lub funkcje zdefiniowane przez użytkownika w programie SQL Server 2005, napiszemy procedurę składowaną i logikę zdefiniowaną przez użytkownika jako kod języka Visual Basic w klasie. Po zapisaniu kodu musimy skompilować tę klasę do zestawu ( .dll pliku), zarejestrować zestaw w bazie danych programu SQL Server, a następnie utworzyć procedurę składowaną lub obiekt UDF w bazie danych, która wskazuje odpowiednią metodę w zestawie. Te kroki można wykonać ręcznie. Możemy utworzyć kod w dowolnym edytorze tekstów, skompilować go z poziomu wiersza polecenia przy użyciu kompilatora Języka Visual Basic (vbc.exe), zarejestrować go w bazie danych przy użyciu polecenia lub programu Management Studio, a następnie dodać procedurę składowaną lub obiekt UDF za pomocą CREATE ASSEMBLY podobnych środków. Na szczęście wersje Professional and Team Systems programu Visual Studio zawierają typ projektu programu SQL Server, który automatyzuje te zadania. W tym samouczku omówimy użycie typu projektu programu SQL Server w celu utworzenia zarządzanej procedury składowanej i funkcji zdefiniowanej przez użytkownika.

Uwaga

Jeśli używasz programu Visual Web Developer lub wersji Standard programu Visual Studio, musisz zamiast tego użyć podejścia ręcznego. Krok 13 zawiera szczegółowe instrukcje dotyczące ręcznego wykonywania tych kroków. Zachęcam cię do przeczytania kroków od 2 do 12 przed przeczytaniem kroku 13, ponieważ te kroki obejmują ważne instrukcje konfiguracji programu SQL Server, które należy zastosować niezależnie od używanej wersji programu Visual Studio.

Rozpocznij od otwarcia programu Visual Studio. W menu Plik wybierz pozycję Nowy projekt, aby wyświetlić okno dialogowe Nowy projekt (zobacz Rysunek 4). Przejdź do szczegółów typu projektu Baza danych, a następnie w obszarze Szablony wymienione po prawej stronie wybierz opcję utworzenia nowego projektu programu SQL Server. Wybrano nazwę tego projektu ManagedDatabaseConstructs i umieszczono go w rozwiązaniu o nazwie Tutorial75.

Rysunek 4. Tworzenie nowego projektu programu SQL Server (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Kliknij przycisk OK w oknie dialogowym Nowy projekt, aby utworzyć projekt rozwiązania i programu SQL Server.

Projekt programu SQL Server jest powiązany z określoną bazą danych. W związku z tym po utworzeniu nowego projektu programu SQL Server natychmiast zostanie wyświetlony monit o określenie tych informacji. Rysunek 5 przedstawia okno dialogowe Nowa dokumentacja bazy danych, które zostało wypełnione, aby wskazać bazę danych Northwind zarejestrowaną w wystąpieniu bazy danych programu SQL Server 2005 Express Edition z powrotem w kroku 1.

Rysunek 5. Kojarzenie projektu programu SQL Server z bazą danych Northwind Database

Aby debugować zarządzane procedury składowane i funkcje zdefiniowane przez użytkownika, które utworzymy w tym projekcie, musimy włączyć obsługę debugowania SQL/CLR dla połączenia. Za każdym razem, gdy kojarzenie projektu programu SQL Server z nową bazą danych (tak jak na rysunku 5), program Visual Studio nas, czy chcemy włączyć debugowanie SQL/CLR w połączeniu (zobacz Rysunek 6). Kliknij przycisk Yes (Tak).

Rysunek 6. Włączanie debugowania SQL/CLR

W tym momencie nowy projekt programu SQL Server został dodany do rozwiązania. Zawiera on folder o nazwie z plikiem o nazwie Test Scripts Test.sql, który służy do debugowania zarządzanych obiektów bazy danych utworzonych w projekcie. Przyjrzymy się debugowaniu w kroku 12.

Teraz możemy dodać nowe zarządzane procedury składowane i funkcje zdefiniowane przez użytkownika do tego projektu, ale zanim zaczniemy najpierw uwzględniać istniejącą aplikację internetową w rozwiązaniu. W menu Plik wybierz opcję Dodaj i wybierz pozycję Istniejąca witryna sieci Web. Przejdź do odpowiedniego folderu witryny internetowej i kliknij przycisk OK. Jak pokazano na rysunku 7, rozwiązanie zostanie zaktualizowane tak, aby obejmowało dwa projekty: witrynę internetową i ManagedDatabaseConstructs projekt programu SQL Server.

Rysunek 7. Eksplorator rozwiązań zawiera teraz dwa projekty

Wartość NORTHWNDConnectionString w Web.config obecnie odwołuje się do NORTHWND.MDF pliku w folderze App_Data . Ponieważ usunęliśmy tę bazę danych i App_Data jawnie zarejestrowaliśmy ją w wystąpieniu bazy danych programu SQL Server 2005 Express Edition, musimy odpowiednio zaktualizować NORTHWNDConnectionString wartość. Web.config Otwórz plik w witrynie internetowej i zmień NORTHWNDConnectionString wartość, aby parametry połączenia odczytał: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. Po tej zmianie <connectionStrings> sekcja w pliku Web.config powinna wyglądać podobnie do następującej:

<connectionStrings>
    <add name="NORTHWNDConnectionString" connectionString=
        "Data Source=localhost\SQLExpress;Initial Catalog=Northwind;
            Integrated Security=True;Pooling=false"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Uwaga

Jak opisano w poprzednim samouczku, podczas debugowania obiektu programu SQL Server z aplikacji klienckiej, takiej jak witryna internetowa ASP.NET, musimy wyłączyć buforowanie połączeń. Powyższe parametry połączenia wyłącza buforowanie połączeń ( Pooling=false ). Jeśli nie planujesz debugowania zarządzanych procedur składowanych i funkcji zdefiniowanych przez użytkownika z witryny internetowej ASP.NET, włącz buforowanie połączeń.

Krok 3. Tworzenie zarządzanej procedury składowanej

Aby dodać zarządzaną procedurę składowaną do bazy danych Northwind, najpierw musimy utworzyć procedurę składowaną jako metodę w projekcie programu SQL Server. W Eksplorator rozwiązań kliknij prawym przyciskiem myszy ManagedDatabaseConstructs nazwę projektu i wybierz opcję dodania nowego elementu. Spowoduje to wyświetlenie okna dialogowego Dodawanie nowego elementu z listą typów zarządzanych obiektów bazy danych, które można dodać do projektu. Jak pokazano na rysunku 8, obejmuje to między innymi procedury składowane i funkcje zdefiniowane przez użytkownika.

Zacznijmy od dodania procedury składowanej, która po prostu zwraca wszystkie produkty, które zostały przerwane. Nadaj nowej nazwie nowy plik GetDiscontinuedProducts.vbprocedury składowanej .

Rysunek 8. Dodawanie nowej procedury składowanej o nazwie GetDiscontinuedProducts.vb (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Spowoduje to utworzenie nowego pliku klasy Visual Basic z następującą zawartością:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub  GetDiscontinuedProducts ()
        ' Add your code here
    End Sub
End Class

Należy pamiętać, że procedura składowana jest implementowana jako Shared metoda w Partial pliku klasy o nazwie StoredProcedures. Ponadto GetDiscontinuedProducts metoda jest ozdobiona atrybutem SqlProcedure, który oznacza metodę jako procedurę składowaną.

Poniższy kod tworzy SqlCommand obiekt i ustawia go CommandText na SELECT zapytanie zwracające wszystkie kolumny z Products tabeli dla produktów, których Discontinued pole jest równe 1. Następnie wykonuje polecenie i wysyła wyniki z powrotem do aplikacji klienckiej. Dodaj ten kod do GetDiscontinuedProducts metody .

' Create the command
Dim myCommand As New SqlCommand()
myCommand.CommandText = _
    "SELECT ProductID, ProductName, SupplierID, CategoryID, " & _
    "       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, " & _
    "       ReorderLevel, Discontinued " & _
    "FROM Products " & _
    "WHERE Discontinued = 1"
' Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand)

Wszystkie zarządzane obiekty bazy danych mają dostęp do SqlContext obiektu reprezentującego kontekst obiektu wywołującego. Obiekt SqlContext zapewnia dostęp do SqlPipe obiektu za pośrednictwem jego Pipe właściwości. Ten SqlPipe obiekt służy do promowania informacji między bazą danych programu SQL Server a aplikacją wywołującą. Jak wskazuje jej nazwa, ExecuteAndSend metoda wykonuje przekazany SqlCommand obiekt i wysyła wyniki z powrotem do aplikacji klienckiej.

Uwaga

Obiekty zarządzanej bazy danych najlepiej nadają się do procedur składowanych i funkcji zdefiniowanych przez użytkownika, które używają logiki proceduralnej, a nie logiki opartej na zestawie. Logika proceduralna obejmuje pracę z zestawami danych na podstawie wiersza lub pracą z danymi skalarnymi. Metoda GetDiscontinuedProducts , którą właśnie utworzyliśmy, nie obejmuje jednak logiki proceduralnej. W związku z tym najlepiej byłoby zaimplementować ją jako procedurę składowaną języka T-SQL. Jest on implementowany jako zarządzana procedura składowana, aby zademonstrować kroki niezbędne do tworzenia i wdrażania zarządzanych procedur składowanych.

Krok 4. Wdrażanie zarządzanej procedury składowanej

Po zakończeniu tego kodu jesteśmy gotowi do wdrożenia go w bazie danych Northwind. Wdrożenie projektu programu SQL Server kompiluje kod w zestawie, rejestruje zestaw w bazie danych i tworzy odpowiednie obiekty w bazie danych, łącząc je z odpowiednimi metodami w zestawie. Dokładny zestaw zadań wykonywanych przez opcję Wdróż jest dokładniej określony w kroku 13. Kliknij prawym przyciskiem myszy ManagedDatabaseConstructs nazwę projektu w Eksplorator rozwiązań i wybierz opcję Wdróż. Jednak wdrożenie kończy się niepowodzeniem z powodu następującego błędu: Nieprawidłowa składnia w pobliżu "EXTERNAL". Aby włączyć tę funkcję, może być konieczne ustawienie poziomu zgodności bieżącej bazy danych na wyższą wartość. Zobacz pomoc dotyczącą procedury sp_dbcmptlevelskładowanej .

Ten komunikat o błędzie występuje podczas próby zarejestrowania zestawu w bazie danych Northwind. Aby zarejestrować zestaw w bazie danych programu SQL Server 2005, poziom zgodności bazy danych musi być ustawiony na 90. Domyślnie nowe bazy danych programu SQL Server 2005 mają poziom zgodności 90. Jednak bazy danych utworzone przy użyciu programu Microsoft SQL Server 2000 mają domyślny poziom zgodności 80. Ponieważ baza danych Northwind początkowo była bazą danych programu Microsoft SQL Server 2000, jej poziom zgodności jest obecnie ustawiony na 80 i dlatego musi zostać zwiększony do 90, aby zarejestrować obiekty zarządzanej bazy danych.

Aby zaktualizować poziom zgodności bazy danych, otwórz okno Nowe zapytanie w programie Management Studio i wprowadź:

exec sp_dbcmptlevel 'Northwind', 90

Kliknij ikonę Wykonaj na pasku narzędzi, aby uruchomić powyższe zapytanie.

Rysunek 9. Aktualizowanie poziomu zgodności bazy danych Northwind Database (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Po zaktualizowaniu poziomu zgodności ponownie wdróż projekt programu SQL Server. Tym razem wdrożenie powinno zakończyć się bez błędu.

Wróć do programu SQL Server Management Studio, kliknij prawym przyciskiem myszy bazę danych Northwind w Eksplorator obiektów i wybierz polecenie Odśwież. Następnie przejdź do folderu Programmability, a następnie rozwiń folder Zestawy. Jak pokazano na rysunku 10, baza danych Northwind zawiera teraz zestaw wygenerowany przez ManagedDatabaseConstructs projekt.

Rysunek 10. Zestaw ManagedDatabaseConstructs jest teraz zarejestrowany w bazie danych Northwind

Rozwiń również folder Procedury składowane. Zostanie wyświetlona procedura składowana o nazwie GetDiscontinuedProducts. Ta procedura składowana została utworzona przez proces wdrażania i wskazuje metodę GetDiscontinuedProducts ManagedDatabaseConstructs w zestawie. Po wykonaniu GetDiscontinuedProducts procedury składowanej wykonuje ona z kolei metodę GetDiscontinuedProducts . Ponieważ jest to zarządzana procedura składowana, nie można jej edytować za pomocą programu Management Studio (stąd ikona blokady obok nazwy procedury składowanej).

Rysunek 11. GetDiscontinuedProducts Procedura składowana znajduje się w folderze Procedury składowane

Istnieje jeszcze jedna przeszkoda, której musimy przezwyciężyć, zanim będziemy mogli wywołać zarządzaną procedurę składowaną: baza danych jest skonfigurowana do zapobiegania wykonywaniu kodu zarządzanego. Sprawdź to, otwierając nowe okno zapytania i wykonując procedurę GetDiscontinuedProducts składowaną. Zostanie wyświetlony następujący komunikat o błędzie: Wykonanie kodu użytkownika w programie .NET Framework jest wyłączone. Włącz opcję konfiguracji "clr enabled".

Aby sprawdzić informacje o konfiguracji bazy danych Northwind, wprowadź i wykonaj polecenie exec sp_configure w oknie zapytania. Oznacza to, że ustawienie clr enabled jest obecnie ustawione na 0.

Rysunek 12. Ustawienie włączone clr jest obecnie ustawione na 0 (kliknij, aby wyświetlić obraz pełnowymiarowy)

Należy pamiętać, że każde ustawienie konfiguracji na rysunku 12 ma cztery wartości wymienione na liście: wartości minimalne i maksymalne oraz konfigurację i wartości uruchamiania. Aby zaktualizować wartość konfiguracji dla ustawienia clr enabled, wykonaj następujące polecenie:

exec sp_configure 'clr enabled', 1

Jeśli uruchomisz ponownie instrukcję exec sp_configure , zobaczysz, że powyższa instrukcja zaktualizowała wartość konfiguracji parametru clr enabled na 1, ale wartość uruchomienia jest nadal ustawiona na 0. Dla tej zmiany konfiguracji, aby mieć wpływ na musimy wykonać RECONFIGURE polecenie, które ustawi wartość przebiegu na bieżącą wartość konfiguracji. Po prostu wprowadź RECONFIGURE w oknie zapytania i kliknij ikonę Wykonaj na pasku narzędzi. Jeśli uruchomisz exec sp_configure polecenie teraz, powinna zostać wyświetlona wartość 1 dla ustawienia clr enabled ustawienia config i wartości uruchamiania.

Po zakończeniu konfiguracji z włączoną obsługą środowiska clr możemy uruchomić zarządzaną GetDiscontinuedProducts procedurę składowaną. W oknie zapytania wprowadź i wykonaj polecenie exec GetDiscontinuedProducts. Wywołanie procedury składowanej powoduje wykonanie odpowiedniego kodu zarządzanego w metodzie GetDiscontinuedProducts . Ten kod wysyła SELECT zapytanie, aby zwrócić wszystkie produkty, które zostały wycofane i zwrócić te dane do aplikacji wywołującej, czyli programu SQL Server Management Studio w tym wystąpieniu. Program Management Studio odbiera te wyniki i wyświetla je w oknie Wyniki.

Rysunek 13. GetDiscontinuedProducts Procedura składowana zwraca wszystkie wycofane produkty (kliknij, aby wyświetlić obraz pełnowymiarowy)

Krok 5. Tworzenie zarządzanych procedur składowanych, które akceptują parametry wejściowe

W wielu zapytaniach i procedurach składowanych utworzonych w tych samouczkach użyto parametrów. Na przykład w samouczku Creating New Stored Procedures for the Typed DataSet s TableAdapters (Tworzenie nowych procedur składowanych dla typowanych zestawów danych TableAdapters ) utworzyliśmy procedurę składowaną o nazwie , która zaakceptowała parametr wejściowy o nazwie GetProductsByCategoryID @CategoryID. Procedura składowana zwróciła następnie wszystkie produkty, których CategoryID pole pasuje do wartości podanego @CategoryID parametru.

Aby utworzyć zarządzaną procedurę składowaną, która akceptuje parametry wejściowe, po prostu określ te parametry w definicji metody . Aby to zilustrować, dodajmy kolejną zarządzaną procedurę składowaną do ManagedDatabaseConstructs projektu o nazwie GetProductsWithPriceLessThan. Ta zarządzana procedura składowana zaakceptuje parametr wejściowy określający cenę i zwróci wszystkie produkty, których UnitPrice pole jest mniejsze niż wartość parametru.

Aby dodać nową procedurę składowaną do projektu, kliknij prawym przyciskiem myszy ManagedDatabaseConstructs nazwę projektu i wybierz dodanie nowej procedury składowanej. Nazwij plik GetProductsWithPriceLessThan.vb. Jak pokazano w kroku 3, spowoduje to utworzenie nowego pliku klasy Visual Basic z metodą o nazwie GetProductsWithPriceLessThan umieszczonej Partial w klasie StoredProcedures.

Zaktualizuj definicję GetProductsWithPriceLessThan metody , aby akceptowała SqlMoney parametr wejściowy o nazwie price i napisz kod do wykonania i zwrócenia wyników zapytania:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub GetProductsWithPriceLessThan(ByVal price As SqlMoney)
    'Create the command
    Dim myCommand As New SqlCommand()
    myCommand.CommandText = _
        "SELECT ProductID, ProductName, SupplierID, CategoryID, " & _
        "       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, " & _
        "       ReorderLevel, Discontinued " & _
        "FROM Products " & _
        "WHERE UnitPrice < @MaxPrice"
    myCommand.Parameters.AddWithValue("@MaxPrice", price)
    ' Execute the command and send back the results
    SqlContext.Pipe.ExecuteAndSend(myCommand)
End Sub

Definicja GetProductsWithPriceLessThan metody i kod są ściśle podobne do definicji i kodu metody utworzonej GetDiscontinuedProducts w kroku 3. Jedyną różnicą jest to, że GetProductsWithPriceLessThan metoda przyjmuje jako parametr wejściowy (price), SqlCommand zapytanie zawiera parametr (@MaxPrice), a parametr jest dodawany do SqlCommand Parameters kolekcji i przypisuje wartość zmiennej price .

Po dodaniu tego kodu ponownie wdróż projekt programu SQL Server. Następnie wróć do programu SQL Server Management Studio i odśwież folder Procedury składowane. Powinien zostać wyświetlony nowy wpis . GetProductsWithPriceLessThan W oknie zapytania wprowadź i wykonaj polecenie exec GetProductsWithPriceLessThan 25, które wyświetli listę wszystkich produktów mniejszych niż $25, jak pokazano na rysunku 14.

Rysunek 14. Wyświetlane są produkty poniżej 25 USD (kliknij, aby wyświetlić obraz pełnowymiarowy)

Krok 6. Wywoływanie zarządzanej procedury składowanej z warstwy dostępu do danych

W tym momencie dodaliśmy GetDiscontinuedProducts procedury składowane i GetProductsWithPriceLessThan zarządzane do ManagedDatabaseConstructs projektu i zarejestrowaliśmy je w bazie danych Northwind SQL Server. Wywołaliśmy również te zarządzane procedury składowane z programu SQL Server Management Studio (zobacz Rysunek 13 i 14). Aby nasza aplikacja ASP.NET korzystała z tych zarządzanych procedur składowanych, musimy jednak dodać je do warstw dostępu do danych i logiki biznesowej w architekturze. W tym kroku dodamy dwie nowe metody do ProductsTableAdapter elementu w typowym zestawie NorthwindWithSprocs danych, który został początkowo utworzony w samouczku Creating New Stored Procedures for the Typed DataSet s TableAdapters (Tworzenie nowych procedur składowanych dla typowanych elementów TableAdapters zestawu danych). W kroku 7 dodamy odpowiednie metody do BLL.

Otwórz typowy zestaw NorthwindWithSprocs danych w programie Visual Studio i zacznij od dodania nowej metody do ProductsTableAdapter nazwy GetDiscontinuedProducts. Aby dodać nową metodę do tabeli TableAdapter, kliknij prawym przyciskiem myszy nazwę tableAdapter w Projektancie i wybierz opcję Dodaj zapytanie z menu kontekstowego.

Uwaga

Ponieważ baza danych Northwind została przeniesiona z App_Data folderu do wystąpienia bazy danych programu SQL Server 2005 Express Edition, konieczne jest zaktualizowanie odpowiedniego parametry połączenia w pliku Web.config w celu odzwierciedlenia tej zmiany. W kroku 2 omówiliśmy aktualizowanie NORTHWNDConnectionString wartości w pliku Web.config. Jeśli nie pamiętasz o wprowadzania tej aktualizacji, zostanie wyświetlony komunikat o błędzie Nie można dodać kwerendy. Nie można odnaleźć połączenia NORTHWNDConnectionString dla obiektu Web.config w oknie dialogowym podczas próby dodania nowej metody do obiektu TableAdapter. Aby rozwiązać ten błąd, kliknij przycisk OK, a następnie przejdź do Web.config i zaktualizuj wartość zgodnie z opisem NORTHWNDConnectionString w kroku 2. Następnie spróbuj ponownie dodać metodę do klasy TableAdapter. Tym razem powinna działać bez błędu.

Dodanie nowej metody powoduje uruchomienie kreatora konfiguracji zapytań TableAdapter, którego używaliśmy wiele razy w poprzednich samouczkach. Pierwszy krok prosi nas o określenie sposobu, w jaki tableAdapter powinien uzyskać dostęp do bazy danych: za pomocą instrukcji AD hoc SQL lub za pośrednictwem nowej lub istniejącej procedury składowanej. Ponieważ utworzyliśmy i zarejestrowaliśmy zarządzaną GetDiscontinuedProducts procedurę składowaną z bazą danych, wybierz opcję Użyj istniejącej procedury składowanej i naciśnij przycisk Dalej.

Rysunek 15. Wybierz opcję Użyj istniejącej procedury składowanej (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Na następnym ekranie zostanie wyświetlony monit o procedurę składowaną, która zostanie wywołana przez metodę . Wybierz zarządzaną procedurę GetDiscontinuedProducts składowaną z listy rozwijanej i naciśnij przycisk Dalej.

Rysunek 16. Wybierz zarządzaną procedurę GetDiscontinuedProducts składowaną (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Następnie zostanie wyświetlony monit o określenie, czy procedura składowana zwraca wiersze, pojedynczą wartość, czy nic. Ponieważ GetDiscontinuedProducts zwraca zestaw wycofanych wierszy produktu, wybierz pierwszą opcję (dane tabelaryczne) i kliknij przycisk Dalej.

Rysunek 17. Wybierz opcję Dane tabelaryczne (kliknij, aby wyświetlić obraz pełnowymiarowy)

Końcowy ekran kreatora umożliwia określenie używanych wzorców dostępu do danych oraz nazw wynikowych metod. Pozostaw zaznaczone oba pola wyboru i nadaj metodom FillByDiscontinued nazwę i GetDiscontinuedProducts. Kliknij przycisk Zakończ, aby zakończyć kreatora.

Rysunek 18. Nadaj metodom FillByDiscontinued nazwę i GetDiscontinuedProducts (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Powtórz te kroki, aby utworzyć metody o nazwie FillByPriceLessThan i GetProductsWithPriceLessThan w metodzie ProductsTableAdapter dla zarządzanej GetProductsWithPriceLessThan procedury składowanej.

Rysunek 19 przedstawia zrzut ekranu projektanta zestawu danych po dodaniu metod do ProductsTableAdapter procedur składowanych i GetDiscontinuedProducts GetProductsWithPriceLessThan zarządzanych.

Rysunek 19. Zawiera ProductsTableAdapter nowe metody dodane w tym kroku (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Krok 7. Dodawanie odpowiednich metod do warstwy logiki biznesowej

Po zaktualizowaniu warstwy dostępu do danych w celu uwzględnienia metod wywoływania zarządzanych procedur składowanych dodanych w krokach 4 i 5 musimy dodać odpowiednie metody do warstwy logiki biznesowej. Dodaj następujące dwie metody do ProductsBLLWithSprocs klasy:

<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetDiscontinuedProducts() As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetDiscontinuedProducts()
End Function
<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsWithPriceLessThan(ByVal priceLessThan As Decimal) _
    As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetProductsWithPriceLessThan(priceLessThan)
End Function

Obie metody po prostu wywołają odpowiednią metodę DAL i zwracają ProductsDataTable wystąpienie. Znacznik DataObjectMethodAttribute powyżej każdej metody powoduje, że te metody mają być uwzględnione na liście rozwijanej na karcie SELECT kreatora Konfigurowanie źródła danych ObjectDataSource.

Krok 8. Wywoływanie zarządzanych procedur składowanych z warstwy prezentacji

Dzięki rozszerzonej warstwie logiki biznesowej i dostępu do danych w celu uwzględnienia obsługi wywoływania GetDiscontinuedProducts procedur składowanych i GetProductsWithPriceLessThan zarządzanych możemy teraz wyświetlić wyniki tych procedur składowanych za pośrednictwem strony ASP.NET.

ManagedFunctionsAndSprocs.aspx Otwórz stronę w folderze AdvancedDAL i z przybornika przeciągnij kontrolkę GridView do projektanta. Ustaw właściwość GridView ID na DiscontinuedProducts i, z tagu inteligentnego, powiąż ją z nowym obiektem ObjectDataSource o nazwie DiscontinuedProductsDataSource. Skonfiguruj obiekt ObjectDataSource, aby ściągnąć dane z ProductsBLLWithSprocs metody klasy s GetDiscontinuedProducts .

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

Rysunek 21. Wybierz metodę GetDiscontinuedProducts z listy rozwijanej na karcie SELECT (Kliknij, aby wyświetlić obraz pełnowymiarowy)

Ponieważ ta siatka będzie używana do wyświetlania informacji o produkcie, ustaw listy rozwijane na kartach UPDATE, INSERT i DELETE na wartość (Brak), a następnie kliknij przycisk Zakończ.

Po ukończeniu pracy kreatora program Visual Studio automatycznie doda pole BoundField lub CheckBoxField dla każdego pola danych w obiekcie ProductsDataTable. Pośmiń chwilę, aby usunąć wszystkie te pola z wyjątkiem ProductName i Discontinued, w którym momencie znacznik deklaratywny GridView i ObjectDataSource powinien wyglądać podobnie do następującego:

<asp:GridView ID="DiscontinuedProducts" runat="server" 
    AutoGenerateColumns="False" DataKeyNames="ProductID" 
    DataSourceID="DiscontinuedProductsDataSource">
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
            SortExpression="ProductName" />
        <asp:CheckBoxField DataField="Discontinued" 
            HeaderText="Discontinued" 
            SortExpression="Discontinued" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="DiscontinuedProductsDataSource" runat="server" 
    OldValuesParameterFormatString="original_{0}"
    SelectMethod="GetDiscontinuedProducts" TypeName="ProductsBLLWithSprocs">
</asp:ObjectDataSource>

Pośmiń chwilę, aby wyświetlić tę stronę za pośrednictwem przeglądarki. Po odwiedzeniu strony obiekt ObjectDataSource wywołuje metodę ProductsBLLWithSprocs klasy s GetDiscontinuedProducts . Jak pokazano w kroku 7, ta metoda wywołuje metodę s ProductsDataTable GetDiscontinuedProducts klasy DAL, która wywołuje procedurę GetDiscontinuedProducts składowaną. Ta procedura składowana jest zarządzaną procedurą składowaną i wykonuje kod utworzony w kroku 3, zwracając wycofane produkty.

Wyniki zwrócone przez zarządzaną procedurę składowaną są pakowane do ProductsDataTable elementu przez dal, a następnie zwracane do biblioteki BLL, która następnie zwraca je do warstwy prezentacji, w której są one powiązane z kontrolką GridView i wyświetlane. Zgodnie z oczekiwaniami siatka zawiera listę tych produktów, które zostały wycofane.

Rysunek 22. Wycofane produkty są wyświetlane (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Aby dokładniej pracować, dodaj element TextBox i inny element GridView do strony. Aby ten element GridView wyświetlał produkty mniejsze niż kwota wprowadzona do kontrolki TextBox przez wywołanie ProductsBLLWithSprocs metody klasy s GetProductsWithPriceLessThan .

Krok 9. Tworzenie i wywoływanie funkcji zdefiniowanych przez użytkownika języka T-SQL

Funkcje zdefiniowane przez użytkownika lub funkcje zdefiniowane przez użytkownika to obiekty bazy danych, które ściśle naśladują semantyka funkcji w językach programowania. Podobnie jak funkcja w języku Visual Basic, funkcje zdefiniowane przez użytkownika mogą zawierać zmienną liczbę parametrów wejściowych i zwracać wartość określonego typu. Funkcja UDF może zwracać dane skalarne — ciąg, liczbę całkowitą itd. — lub dane tabelaryczne. Przyjrzyjmy się szybko obu typom funkcji zdefiniowanych przez użytkownika, zaczynając od funkcji zdefiniowanej przez użytkownika, która zwraca typ danych skalarnych.

Poniższa funkcja zdefiniowanej przez użytkownika oblicza szacowaną wartość spisu dla określonego produktu. Robi to, biorąc w ciągu trzech parametrów wejściowych — UnitPricewartości , UnitsInStocki Discontinued dla określonego produktu — i zwraca wartość typu money. Oblicza szacowaną wartość spisu przez pomnożenie UnitPrice wartości przez UnitsInStockwartość . W przypadku elementów wycofanych ta wartość jest o połowę niższa.

CREATE FUNCTION udf_ComputeInventoryValue
(
    @UnitPrice money,
    @UnitsInStock smallint,
    @Discontinued bit
)
RETURNS money
AS
BEGIN
    DECLARE @Value decimal
    SET @Value = ISNULL(@UnitPrice, 0) * ISNULL(@UnitsInStock, 0)
    IF @Discontinued = 1
        SET @Value = @Value * 0.5
    
    RETURN @Value
END

Po dodaniu tej funkcji zdefiniowanej przez użytkownika do bazy danych można ją znaleźć za pomocą programu Management Studio, rozwijając folder Programmability, funkcje, a następnie funkcje skalarne. Można go użyć w SELECT zapytaniu w następujący sposób:

SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue
    (UnitPrice, UnitsInStock, Discontinued) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

Dodano funkcję udf_ComputeInventoryValue UDF do bazy danych Northwind; Rysunek 23 przedstawia dane wyjściowe powyższego SELECT zapytania w przypadku wyświetlania za pośrednictwem programu Management Studio. Należy również pamiętać, że funkcja zdefiniowana przez użytkownika znajduje się w folderze Funkcje wartości skalarnej w Eksplorator obiektów.

Rysunek 23. Wartości spisu poszczególnych produktów są wyświetlane (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Funkcje zdefiniowane przez użytkownika mogą również zwracać dane tabelaryczne. Na przykład możemy utworzyć funkcję zdefiniowaną przez użytkownika, która zwraca produkty należące do określonej kategorii:

CREATE FUNCTION dbo.udf_GetProductsByCategoryID
(    
    @CategoryID int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ProductID, ProductName, SupplierID, CategoryID, 
           QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
           ReorderLevel, Discontinued
    FROM Products
    WHERE CategoryID = @CategoryID
)

Funkcja udf_GetProductsByCategoryID UDF akceptuje @CategoryID parametr wejściowy i zwraca wyniki określonego SELECT zapytania. Po utworzeniu tej funkcji zdefiniowanej przez użytkownika można odwoływać się do FROM klauzuli SELECT (lub JOIN) zapytania. Poniższy przykład zwraca ProductIDwartości , ProductNamei CategoryID dla każdego z napojów.

SELECT ProductID, ProductName, CategoryID
FROM dbo.udf_GetProductsByCategoryID(1)

Dodano funkcję udf_GetProductsByCategoryID UDF do bazy danych Northwind; Rysunek 24 przedstawia dane wyjściowe powyższego SELECT zapytania w przypadku wyświetlania za pośrednictwem programu Management Studio. Funkcje zdefiniowane przez użytkownika zwracające dane tabelaryczne można znaleźć w folderze funkcji table-value Eksplorator obiektów.

Rysunek 24. Wartości ProductID, ProductNamei CategoryID są wyświetlane dla każdego napoju (kliknij, aby wyświetlić obraz pełnowymiarowy)

Uwaga

Aby uzyskać więcej informacji na temat tworzenia i używania funkcji zdefiniowanych przez użytkownika, zobacz Wprowadzenie do funkcji zdefiniowanych przez użytkownika. Zapoznaj się również z zaletami i wadami funkcji zdefiniowanych przez użytkownika.

Krok 10. Tworzenie zarządzanej funkcji zdefiniowanej przez użytkownika

Funkcje udf_ComputeInventoryValue zdefiniowane przez użytkownika i udf_GetProductsByCategoryID utworzone w powyższych przykładach to obiekty bazy danych T-SQL. Program SQL Server 2005 obsługuje również zarządzane funkcje zdefiniowane przez użytkownika, które można dodać do ManagedDatabaseConstructs projektu tak samo jak zarządzane procedury składowane z kroków 3 i 5. W tym kroku zaimplementujmy funkcję zdefiniowanej przez udf_ComputeInventoryValue użytkownika w kodzie zarządzanym.

Aby dodać zarządzaną funkcję zdefiniowaną przez użytkownika do ManagedDatabaseConstructs projektu, kliknij prawym przyciskiem myszy nazwę projektu w Eksplorator rozwiązań i wybierz polecenie Dodaj nowy element. Wybierz szablon zdefiniowany przez użytkownika w oknie dialogowym Dodawanie nowego elementu i nadaj nowej nazwie plik udf_ComputeInventoryValue_Managed.vbUDF .

Rysunek 25. Dodawanie nowej zarządzanej funkcji zdefiniowanej ManagedDatabaseConstructs przez użytkownika do projektu (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Szablon funkcji zdefiniowanej przez użytkownika tworzy klasę Partial o nazwie UserDefinedFunctions z metodą, której nazwa jest taka sama jak nazwa pliku klasy (udf_ComputeInventoryValue_Managedw tym wystąpieniu). Ta metoda jest dekorowana przy użyciu atrybutu SqlFunction, który flaguje metodę jako zarządzaną funkcję zdefiniowanej przez użytkownika.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function udf_ComputeInventoryValue_Managed() As SqlString
        ' Add your code here
        Return New SqlString("Hello")
    End Function
End Class

Metoda udf_ComputeInventoryValue obecnie zwraca SqlString obiekt i nie akceptuje żadnych parametrów wejściowych. Musimy zaktualizować definicję metody, aby akceptowała trzy parametry wejściowe — UnitPrice, UnitsInStocki Discontinued — i zwraca SqlMoney obiekt. Logika obliczania wartości spisu jest taka sama jak w funkcji zdefiniowanej przez użytkownika języka T-SQL udf_ComputeInventoryValue .

<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function udf_ComputeInventoryValue_Managed _
    (UnitPrice As SqlMoney, UnitsInStock As SqlInt16, Discontinued As SqlBoolean) _
    As SqlMoney
    Dim inventoryValue As SqlMoney = 0
    If Not UnitPrice.IsNull AndAlso Not UnitsInStock.IsNull Then
        inventoryValue = UnitPrice * UnitsInStock
        If Discontinued = True Then
            inventoryValue = inventoryValue * New SqlMoney(0.5)
        End If
    End If
    Return inventoryValue
End Function

Należy pamiętać, że parametry wejściowe metody UDF są odpowiadającymi im typami SQL: SqlMoney dla UnitPrice pola , UnitsInStockSqlInt16 i SqlBoolean dla Discontinued. Te typy danych odzwierciedlają typy zdefiniowane w Products tabeli: kolumna UnitPrice jest typu money, UnitsInStock kolumna typu smallinti kolumna Discontinued typu bit.

Kod rozpoczyna się od utworzenia SqlMoney wystąpienia o nazwie inventoryValue , które ma przypisaną wartość 0. Tabela Products umożliwia używanie wartości bazy danych NULL w kolumnach UnitsInPrice i UnitsInStock . W związku z tym musimy najpierw sprawdzić, czy te wartości zawierają NULL s, co robimy za pośrednictwem SqlMoney właściwości s IsNull obiektu. Jeśli obie UnitPrice wartości i UnitsInStock zawierają wartości inneNULL , obliczamy wartość inventoryValue , aby być produktem tych dwóch wartości. Następnie, jeśli Discontinued ma wartość true, wartość zostanie zmniejszona o połowę.

Uwaga

Obiekt SqlMoney umożliwia mnożące tylko dwa SqlMoney wystąpienia. Nie zezwala na mnożące SqlMoney wystąpienie przez literał liczby zmiennoprzecinkowe. W związku z tym w celu pomnożenia go o połowę inventoryValue przez nowe SqlMoney wystąpienie, które ma wartość 0,5.

Krok 11. Wdrażanie zarządzanej funkcji zdefiniowanej przez użytkownika

Teraz, po utworzeniu zarządzanej funkcji zdefiniowanej przez użytkownika, możemy przystąpić do wdrażania jej w bazie danych Northwind. Jak pokazano w kroku 4, obiekty zarządzane w projekcie programu SQL Server są wdrażane przez kliknięcie prawym przyciskiem myszy nazwy projektu w Eksplorator rozwiązań i wybranie opcji Wdróż z menu kontekstowego.

Po wdrożeniu projektu wróć do programu SQL Server Management Studio i odśwież folder Funkcje o wartości skalarnej. Powinny zostać wyświetlone dwa wpisy:

• dbo.udf_ComputeInventoryValue — funkcja UDF języka T-SQL utworzona w kroku 9 i
• dbo.udf ComputeInventoryValue_Managed — zarządzana funkcja zdefiniowanej przez użytkownika utworzona w kroku 10, która została właśnie wdrożona.

Aby przetestować tę zarządzaną funkcję zdefiniowaną przez użytkownika, wykonaj następujące zapytanie z poziomu programu Management Studio:

SELECT ProductID, ProductName, 
       dbo.udf_ComputeInventoryValue_Managed(
                 UnitPrice, 
                 UnitsInStock, 
                 Discontinued
              ) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

To polecenie używa zarządzanej udf ComputeInventoryValue_Managed funkcji zdefiniowanej przez użytkownika zamiast funkcji zdefiniowanej przez użytkownika języka T-SQL udf_ComputeInventoryValue , ale dane wyjściowe są takie same. Wróć do rysunku 23, aby wyświetlić zrzut ekranu przedstawiający dane wyjściowe funkcji zdefiniowanej przez użytkownika.

Krok 12. Debugowanie zarządzanych obiektów bazy danych

W samouczku Debugowanie procedur składowanych omówiliśmy trzy opcje debugowania programu SQL Server za pomocą programu Visual Studio: debugowanie bezpośredniej bazy danych, debugowanie aplikacji i debugowanie z projektu programu SQL Server. Zarządzanych obiektów bazy danych nie można debugować za pomocą debugowania bezpośredniej bazy danych, ale można je debugować z poziomu aplikacji klienckiej i bezpośrednio z projektu programu SQL Server. Aby debugowanie działało, jednak baza danych programu SQL Server 2005 musi zezwalać na debugowanie sql/CLR. Pamiętaj, że podczas pierwszego utworzenia ManagedDatabaseConstructs projektu program Visual Studio zapytał nas, czy chcemy włączyć debugowanie SQL/CLR (zobacz Rysunek 6 w kroku 2). To ustawienie można zmodyfikować, klikając prawym przyciskiem myszy bazę danych w oknie Eksplorator serwera.

Rysunek 26. Upewnij się, że baza danych zezwala na debugowanie SQL/CLR

Załóżmy, że chcemy debugować zarządzaną procedurę GetProductsWithPriceLessThan składowaną. Zaczniemy od ustawienia punktu przerwania w kodzie GetProductsWithPriceLessThan metody .

Rysunek 27. Ustawianie punktu przerwania w metodzie GetProductsWithPriceLessThan (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Najpierw przyjrzyjmy się debugowaniu zarządzanych obiektów bazy danych z projektu programu SQL Server. Ponieważ nasze rozwiązanie zawiera dwa projekty — ManagedDatabaseConstructs projekt programu SQL Server wraz z naszą witryną internetową — w celu debugowania z projektu programu SQL Server musimy poinstruować program Visual Studio o uruchomieniu projektu programu SQL Server po rozpoczęciu ManagedDatabaseConstructs debugowania. Kliknij prawym przyciskiem myszy ManagedDatabaseConstructs projekt w Eksplorator rozwiązań i wybierz opcję Ustaw jako projekt startowy z menu kontekstowego.

Po uruchomieniu ManagedDatabaseConstructs projektu z debugera wykonuje instrukcje SQL w Test.sql pliku, który znajduje się w folderze Test Scripts . Na przykład aby przetestować zarządzaną GetProductsWithPriceLessThan procedurę składowaną, zastąp istniejącą Test.sql zawartość pliku następującą instrukcją, która wywołuje GetProductsWithPriceLessThan zarządzaną procedurę składowaną przekazującą @CategoryID wartość 14.95:

exec GetProductsWithPriceLessThan 14.95

Po wprowadzeniu powyższego skryptu w Test.sqlprogramie rozpocznij debugowanie, przechodząc do menu Debugowanie i wybierając pozycję Rozpocznij debugowanie lub naciskając F5 lub zieloną ikonę odtwarzania na pasku narzędzi. Spowoduje to skompilowanie projektów w rozwiązaniu, wdrożenie zarządzanych obiektów bazy danych w bazie danych Northwind, a następnie wykonanie skryptu Test.sql . W tym momencie punkt przerwania zostanie trafiony i możemy przejść przez GetProductsWithPriceLessThan metodę, zbadać wartości parametrów wejściowych itd.

Rysunek 28. Punkt przerwania w metodzie GetProductsWithPriceLessThan został trafiony (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Aby obiekt bazy danych SQL był debugowany za pośrednictwem aplikacji klienckiej, należy skonfigurować bazę danych do obsługi debugowania aplikacji. Kliknij prawym przyciskiem myszy bazę danych w Eksploratorze serwera i upewnij się, że opcja Debugowanie aplikacji jest zaznaczona. Ponadto musimy skonfigurować aplikację ASP.NET do integracji z debugerem SQL i wyłączyć buforowanie połączeń. Te kroki zostały szczegółowo omówione w kroku 2 samouczka Debugowanie procedur składowanych.

Po skonfigurowaniu aplikacji i bazy danych ASP.NET ustaw witrynę internetową ASP.NET jako projekt startowy i rozpocznij debugowanie. Jeśli odwiedzisz stronę, która wywołuje jeden z zarządzanych obiektów z punktem przerwania, aplikacja zostanie zatrzymana i kontrolka zostanie przekazana do debugera, gdzie można przejść przez kod, jak pokazano na rysunku 28.

Krok 13. Ręczne kompilowanie i wdrażanie obiektów zarządzanej bazy danych

Projekty programu SQL Server ułatwiają tworzenie, kompilowanie i wdrażanie zarządzanych obiektów bazy danych. Niestety projekty programu SQL Server są dostępne tylko w wersjach Professional and Team Systems programu Visual Studio. Jeśli używasz programu Visual Web Developer lub Standard Edition programu Visual Studio i chcesz używać obiektów zarządzanej bazy danych, musisz ręcznie je utworzyć i wdrożyć. Obejmuje to cztery kroki:

1. Utwórz plik zawierający kod źródłowy obiektu zarządzanej bazy danych.
2. Skompiluj obiekt do zestawu,
3. Rejestrowanie zestawu w bazie danych programu SQL Server 2005 i
4. Utwórz obiekt bazy danych w programie SQL Server wskazujący odpowiednią metodę w zestawie.

Aby zilustrować te zadania, utwórzmy nową zarządzaną procedurę składowaną, która zwraca te produkty, których UnitPrice wartość jest większa niż określona. Utwórz nowy plik na komputerze o nazwie GetProductsWithPriceGreaterThan.vb i wprowadź następujący kod do pliku (w tym celu możesz użyć programu Visual Studio, Notatnika lub dowolnego edytora tekstów):

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub GetProductsWithPriceGreaterThan(ByVal price As SqlMoney)
        'Create the command
        Dim myCommand As New SqlCommand()
        myCommand.CommandText = _
            "SELECT ProductID, ProductName, SupplierID, CategoryID, " & _
            "       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, " & _
            "       ReorderLevel, Discontinued " & _
            "FROM Products " & _
            "WHERE UnitPrice > @MinPrice"
        myCommand.Parameters.AddWithValue("@MinPrice", price)
        ' Execute the command and send back the results
        SqlContext.Pipe.ExecuteAndSend(myCommand)
    End Sub
End Class

Ten kod jest prawie identyczny z tą metodą utworzoną GetProductsWithPriceLessThan w kroku 5. Jedyną różnicą są nazwy metod, klauzula WHERE i nazwa parametru używanego w zapytaniu. Po powrocie do metody klauzula GetProductsWithPriceLessThan odczytuje WHERE : WHERE UnitPrice < @MaxPrice. GetProductsWithPriceGreaterThanW tym miejscu użyjemy polecenia : WHERE UnitPrice > @MinPrice .

Teraz musimy skompilować tę klasę do zestawu. W wierszu polecenia przejdź do katalogu, w którym zapisano GetProductsWithPriceGreaterThan.vb plik, i użyj kompilatora języka C# (csc.exe), aby skompilować plik klasy do zestawu:

vbc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.vb

Jeśli folder zawierający maszynę wirtualną bc.exe nie znajduje się w systemie s PATH, należy w pełni odwołać się do jego ścieżki , %WINDOWS%\Microsoft.NET\Framework\version\w następujący sposób:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.vb

Rysunek 29. Kompilowanie GetProductsWithPriceGreaterThan.vb w zestawie (kliknij, aby wyświetlić obraz pełnowymiarowy)

Flaga /t określa, że plik klasy Visual Basic powinien zostać skompilowany do biblioteki DLL (zamiast pliku wykonywalnego). Flaga /out określa nazwę wynikowego zestawu.

Uwaga

Zamiast kompilować GetProductsWithPriceGreaterThan.vb plik klasy z wiersza polecenia, możesz też użyć programu Visual Basic Express Edition lub utworzyć oddzielny projekt biblioteki klas w programie Visual Studio Standard Edition. S ren Jacob Lauritsen uprzejmie dostarczył taki projekt Visual Basic Express Edition z kodem procedury GetProductsWithPriceGreaterThan składowanej oraz dwie zarządzane procedury składowane i UDF utworzone w krokach 3, 5 i 10. Projekt S ren obejmuje również polecenia języka T-SQL potrzebne do dodania odpowiednich obiektów bazy danych.

Kod skompilowany w zestawie jest gotowy do zarejestrowania zestawu w bazie danych programu SQL Server 2005. Można to wykonać za pomocą języka T-SQL, za pomocą polecenia lub za pomocą programu CREATE ASSEMBLYSQL Server Management Studio. Skoncentrujmy się na używaniu programu Management Studio.

W programie Management Studio rozwiń folder Programmability w bazie danych Northwind. Jednym z jego podfolderów jest Zestawy. Aby ręcznie dodać nowy zestaw do bazy danych, kliknij prawym przyciskiem myszy folder Zestawy i wybierz polecenie Nowy zestaw z menu kontekstowego. Spowoduje to wyświetlenie okna dialogowego Nowy zestaw (zobacz Rysunek 30). Kliknij przycisk Przeglądaj, wybierz ManuallyCreatedDBObjects.dll właśnie skompilowany zestaw, a następnie kliknij przycisk OK, aby dodać zestaw do bazy danych. Zestaw nie powinien być widoczny ManuallyCreatedDBObjects.dll w Eksplorator obiektów.

Rysunek 30. Dodawanie ManuallyCreatedDBObjects.dll zestawu do bazy danych (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Rysunek 31. Element ManuallyCreatedDBObjects.dll znajduje się na liście w Eksplorator obiektów

Mimo że dodaliśmy zestaw do bazy danych Northwind, jeszcze skojarzyliśmy procedurę składowaną z GetProductsWithPriceGreaterThan metodą w zestawie. Aby to zrobić, otwórz nowe okno zapytania i wykonaj następujący skrypt:

CREATE PROCEDURE [dbo].[GetProductsWithPriceGreaterThan] 
( 
    @price money 
) 
WITH EXECUTE AS CALLER 
AS 
EXTERNAL NAME [ManuallyCreatedDBObjects].[StoredProcedures].[GetProductsWithPriceGreaterThan] 
GO

Spowoduje to utworzenie nowej procedury składowanej w bazie danych Northwind o nazwie GetProductsWithPriceGreaterThan i skojarzenie jej z metodą GetProductsWithPriceGreaterThan zarządzaną (która znajduje się w klasie StoredProcedures, która znajduje się w zestawie ManuallyCreatedDBObjects).

Po wykonaniu powyższego skryptu odśwież folder Procedury składowane w Eksplorator obiektów. Powinien zostać wyświetlony nowy wpis procedury składowanej — GetProductsWithPriceGreaterThan który ma obok niej ikonę blokady. Aby przetestować tę procedurę składowaną, wprowadź i wykonaj następujący skrypt w oknie zapytania:

exec GetProductsWithPriceGreaterThan 24.95

Jak pokazano na rysunku 32, powyższe polecenie wyświetla informacje dla tych produktów o wartości większej UnitPrice niż 24,95 USD.

Rysunek 32. Element ManuallyCreatedDBObjects.dll znajduje się na liście w Eksplorator obiektów (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Podsumowanie

Program Microsoft SQL Server 2005 zapewnia integrację ze środowiskiem Uruchomieniowym języka wspólnego (CLR), który umożliwia tworzenie obiektów bazy danych przy użyciu kodu zarządzanego. Wcześniej te obiekty bazy danych mogły być tworzone tylko przy użyciu języka T-SQL, ale teraz możemy utworzyć te obiekty przy użyciu języków programowania .NET, takich jak Visual Basic. W tym samouczku utworzyliśmy dwie zarządzane procedury składowane i zarządzaną funkcję zdefiniowaną przez użytkownika.

Typ projektu programu SQL Server programu Visual Studio ułatwia tworzenie, kompilowanie i wdrażanie zarządzanych obiektów bazy danych. Ponadto oferuje rozbudowaną obsługę debugowania. Jednak typy projektów programu SQL Server są dostępne tylko w wersjach Professional and Team Systems programu Visual Studio. W przypadku osób korzystających z programu Visual Web Developer lub Standard Edition programu Visual Studio kroki tworzenia, kompilacji i wdrażania należy wykonać ręcznie, jak pokazano w kroku 13.

Szczęśliwe programowanie!

Dalsze informacje

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

• Zalety i wady funkcji zdefiniowanych przez użytkownika
• Tworzenie obiektów programu SQL Server 2005 w kodzie zarządzanym
• Instrukcje: tworzenie i uruchamianie procedury składowanej programu SQL Server CLR
• Instrukcje: tworzenie i uruchamianie funkcji zdefiniowanej przez użytkownika programu SQL Server CLR
• Instrukcje: edytowanie skryptu w celu uruchamiania Test.sql obiektów SQL
• Wprowadzenie do funkcji zdefiniowanych przez użytkownika
• Kod zarządzany i program SQL Server 2005 (wideo)
• Dokumentacja języka Transact-SQL
• Przewodnik: tworzenie procedury składowanej w kodzie zarządzanym

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 24 godzinach. Można go uzyskać 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 omówiona przez wielu przydatnych recenzentów. Główny recenzent tego samouczka to S ren Jacob Lauritsen. Oprócz przejrzenia tego artykułu ren utworzono również projekt Visual C# Express Edition uwzględniony w tym artykule do ręcznego kompilowania obiektów zarządzanej bazy danych. Chcesz przejrzeć nadchodzące artykuły MSDN? Jeśli tak, upuść mi wiersz pod adresem mitchell@4GuysFromRolla.com.

Poprzednie