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

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 pokazano, jak utworzyć zarządzane procedury składowane i zarządzane funkcje zdefiniowane przez użytkownika przy użyciu kodu Visual Basic lub C#. Widzimy również, jak te wersje programu Visual Studio umożliwiają debugowanie takich zarządzanych obiektów bazy danych.

Wprowadzenie

Bazy danych, takie jak microsoft s 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 obejmuje 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. Inną jest funkcja zdefiniowana przez użytkownika (UDF), konstrukcja, którą szczegółowo przeanalizujemy w kroku 9.

Podstawowym elementem programu SQL jest praca z zestawami danych. Instrukcje SELECT, UPDATEi DELETE mają z natury 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 mogą być definiowane tylko jako kolekcja instrukcji języka T-SQL. SQL Server 2005 został jednak zaprojektowany w celu zapewnienia integracji ze środowiskiem Uruchomieniowym języka wspólnego (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 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 C#. Dzięki temu te procedury składowane i funkcje zdefiniowane przez użytkownika umożliwiają korzystanie z funkcji w .NET Framework i z własnych klas niestandardowych.

W tym samouczku dowiesz się, jak utworzyć zarządzane procedury składowane i User-Defined Functions oraz jak je zintegrować z naszą bazą danych Northwind. Zacznijmy!

Uwaga

Obiekty zarządzanej bazy danych oferują pewne korzyści w stosunku do ich odpowiedników SQL. Bogactwo języka i znajomość języka 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 uzyskać bardziej szczegółową dyskusję na temat 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 microsoft SQL Server 2005 Express Edition w folderze aplikacji App_Data internetowej. Umieszczenie bazy danych w App_Data uproszczonym rozpowszechnianiu i uruchamianiu tych samouczków, ponieważ wszystkie pliki znajdowały się w jednym katalogu i nie wymagały dodatkowych kroków konfiguracji w celu przetestowania samouczka.

Na potrzeby tego samouczka przenieśmy jednak bazę danych Northwind z App_Data i jawnie zarejestrujmy ją przy użyciu wystąpienia bazy danych SQL Server 2005 Express Edition. Chociaż możemy wykonać kroki tego samouczka z bazą danych w App_Data folderze, wiele kroków jest znacznie prostszych przez jawne zarejestrowanie bazy danych przy użyciu wystąpienia bazy danych 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 internetowej App_Data 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 SQL Server 2005 Express Edition. Można to zrobić z poziomu SQL Server Management Studio. Jeśli na komputerze zainstalowano wersję inną niż Express Edition SQL Server 2005, prawdopodobnie masz już zainstalowany program Management Studio. Jeśli masz tylko SQL Server 2005 Express Edition na komputerze, poświęć chwilę, aby pobrać i zainstalować program 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 dla nazwy serwera, wybierz pozycję Uwierzytelnianie systemu Windows na liście rozwijanej Uwierzytelnianie, a następnie kliknij przycisk 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 SQL Server 2005 Express Edition, w tym 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 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 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 profilu użytkownika, takich jak Moje dokumenty. Dlatego 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. 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 SQL Server projektu w programie Visual Studio

Aby utworzyć zarządzane procedury składowane lub funkcje zdefiniowane przez użytkownika w SQL Server 2005 r., napiszemy procedurę składowaną i logikę UDF jako kod języka C# w klasie. Po zapisaniu kodu należy skompilować tę klasę do zestawu (.dllpliku), zarejestrować zestaw przy użyciu bazy danych 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 C# (csc.exe), zarejestrować go w bazie danych przy użyciu CREATE ASSEMBLY polecenia lub programu Management Studio, a następnie dodać procedurę składowaną lub obiekt UDF za pomocą podobnych środków. Na szczęście wersje Professional and Team Systems programu Visual Studio zawierają SQL Server typ projektu, który automatyzuje te zadania. W tym samouczku omówimy użycie SQL Server typ projektu w celu utworzenia zarządzanej procedury składowanej i funkcji UDF.

Uwaga

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

Zacznij 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 SQL Server. Wybrałem nazwę tego projektu ManagedDatabaseConstructs i umieściłem go w rozwiązaniu o nazwie Tutorial75.

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

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

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

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

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 SQL Server z nową bazą danych (tak jak na rysunku 5), program Visual Studio pyta 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 SQL Server został dodany do rozwiązania. Zawiera folder o nazwie Test Scripts z plikiem o nazwie Test.sql, który jest używany 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 najpierw uwzględnimy naszą 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 SQL Server Project.

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

Wartość NORTHWNDConnectionString w Web.config pliku aktualnie 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 SQL Server 2005 Express Edition, musimy odpowiednio zaktualizować NORTHWNDConnectionString wartość. Web.config Otwórz plik w witrynie internetowej i zmień NORTHWNDConnectionString wartość tak, aby parametry połączenia odczytał: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. Po tej zmianie <connectionStrings> sekcja w Web.config pliku 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

Zgodnie z opisem w poprzednim samouczku podczas debugowania obiektu SQL Server z aplikacji klienckiej, takiej jak witryna internetowa ASP.NET, musimy wyłączyć buforowanie połączeń. Pokazana powyżej 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 programie SQL Server Project. 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 zawierającego 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 User-Defined Functions.

Zacznijmy od dodania procedury składowanej, która po prostu zwraca wszystkie wycofane produkty. Nadaj nowej nazwie plik GetDiscontinuedProducts.csprocedury składowanej .

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

Spowoduje to utworzenie nowego pliku klasy języka C# z następującą zawartością:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetDiscontinuedProducts()
    {
        // Put your code here
    }
};

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

Poniższy kod tworzy SqlCommand obiekt i ustawia go CommandText na SELECT zapytanie, które zwraca 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
SqlCommand myCommand = 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 obiekty zarządzanej 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 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 zestawach. Logika proceduralna obejmuje pracę z zestawami danych w oparciu o wiersz po wierszu 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 w celu zademonstrowania kroków niezbędnych do tworzenia i wdrażania zarządzanych procedur składowanych.

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

Po zakończeniu tego kodu możemy wdrożyć go w bazie danych Northwind. Wdrożenie SQL Server Project 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 SQL Server 2005, poziom zgodności bazy danych musi być ustawiony na 90. Domyślnie nowe bazy danych SQL Server 2005 mają poziom zgodności 90. Jednak bazy danych utworzone przy użyciu platformy Microsoft SQL Server 2000 mają domyślny poziom zgodności 80. Ponieważ baza danych Northwind była początkowo bazą danych firmy Microsoft SQL Server 2000, jej poziom zgodności jest obecnie ustawiony na 80 i dlatego należy go zwiększyć do 90, aby zarejestrować zarządzane obiekty 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 w pełnym rozmiarze)

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

Wróć do SQL Server Management Studio, kliknij prawym przyciskiem myszy bazę danych Northwind w Eksplorator obiektów, a następnie wybierz polecenie Odśwież. Następnie przejdź do folderu Programmability, a następnie rozwiń folder Zestawy. Jak pokazano na rysunku ManagedDatabaseConstructs 10, baza danych Northwind zawiera teraz zestaw wygenerowany przez 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 w ManagedDatabaseConstructs zestawie. Po wykonaniu GetDiscontinuedProducts procedury składowanej metoda jest wykonywana GetDiscontinuedProducts z kolei. 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

Jeszcze jedną przeszkodę musimy przezwyciężyć, zanim będziemy mogli wywołać zarządzaną procedurę składowaną: baza danych jest skonfigurowana tak, aby zapobiec 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 .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 wartość 0 (kliknij, aby wyświetlić obraz w pełnym rozmiarze)

Należy pamiętać, że każde ustawienie konfiguracji na rysunku 12 ma cztery wartości na liście: wartości minimalne i maksymalne oraz konfigurację i wartości uruchamiania. Aby zaktualizować wartość konfiguracji dla ustawienia włączonego środowiska clr, 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. Aby ta zmiana konfiguracji wpływała na musimy wykonać RECONFIGURE polecenie , co spowoduje ustawienie wartości przebiegu na bieżącą wartość konfiguracji. Wystarczy wprowadzić RECONFIGURE w oknie zapytania i kliknąć ikonę Wykonaj na pasku narzędzi. Jeśli uruchomisz exec sp_configure polecenie teraz, powinna zostać wyświetlona wartość 1 dla ustawienia clr enabled config i wartości uruchamiania.

Po zakończeniu konfiguracji z włączoną funkcją clr możemy uruchomić zarządzaną GetDiscontinuedProducts procedurę składowaną. W oknie zapytania wprowadź i wykonaj polecenie execGetDiscontinuedProducts. Wywołanie procedury składowanej powoduje wykonanie odpowiedniego kodu zarządzanego w metodzie GetDiscontinuedProducts . Ten kod wysyła SELECT zapytanie, aby zwrócić wszystkie wycofane produkty i zwrócić te dane do aplikacji wywołującej, która jest 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 typowanego zestawu danych TableAdapters ) utworzyliśmy procedurę składowaną o nazwie , która zaakceptowała parametr wejściowy o nazwie GetProductsByCategoryID@CategoryID. Następnie procedura składowana zwróciła 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.cs. Jak pokazano w kroku 3, spowoduje to utworzenie nowego pliku klasy języka C# z metodą o nazwie GetProductsWithPriceLessThan umieszczonej partial w klasie StoredProcedures.

Zaktualizuj definicję metody , GetProductsWithPriceLessThan 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 static void GetProductsWithPriceLessThan(SqlMoney price)
{
    // Create the command
    SqlCommand myCommand = 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);
}

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

Po dodaniu tego kodu ponownie wdróż projekt SQL Server. Następnie wróć do 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 mniej 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 i GetProductsWithPriceLessThan zarządzaliśmy procedurami składowanymi 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 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 ProductsTableAdapterNorthwindWithSprocs zestawu danych typowych, które zostały początkowo utworzone w samouczku Tworzenie nowych procedur składowanych dla zestawu danych Typed DataSet s TableAdapters . W kroku 7 dodamy odpowiednie metody do usługi 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 klasy TableAdapter, kliknij prawym przyciskiem myszy nazwę tableAdapter w Projektant i wybierz opcję Dodaj zapytanie z menu kontekstowego.

Uwaga

Ponieważ przenieśliśmy bazę danych Northwind z App_Data folderu do wystąpienia bazy danych SQL Server 2005 Express Edition, konieczne jest zaktualizowanie odpowiednich parametry połączenia w Web.config w celu odzwierciedlenia tej zmiany. W kroku 2 omówiliśmy aktualizowanie NORTHWNDConnectionString wartości w elemecie Web.config. Jeśli nie pamiętasz o aktualizacji, zostanie wyświetlony komunikat o błędzie Nie można dodać zapytania. Nie można odnaleźć połączenia NORTHWNDConnectionString dla obiektu Web.config w oknie dialogowym podczas próby dodania nowej metody do klasy 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 tabeli TableAdapter. Tym razem powinna działać bez błędu.

Dodanie nowej metody uruchamia kreatora konfiguracji zapytań TableAdapter, którego używaliśmy wiele razy w poprzednich samouczkach. Pierwszy krok prosi nas o określenie, w jaki sposób tableAdapter powinien uzyskać dostęp do bazy danych: za pośrednictwem instrukcji AD-hoc SQL lub za pośrednictwem nowej lub istniejącej procedury składowanej. Ponieważ już utworzyliśmy i zarejestrowaliśmy GetDiscontinuedProducts zarządzaną 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 pełnowymiarowy)

Na następnym ekranie zostanie wyświetlony monit o procedurę składowaną, która zostanie wywołana. 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 pełnowymiarowy)

Następnie poproszono nas o określenie, czy procedura składowana zwraca wiersze, pojedynczą wartość, czy nic. Ponieważ GetDiscontinuedProducts zwraca zestaw wycofanych wierszy produktów, 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 i nazw wynikowych metod. Pozostaw zaznaczone pola wyboru i nazwij metody FillByDiscontinued i GetDiscontinuedProducts. Kliknij przycisk Zakończ, aby zakończyć kreatora.

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

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

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

Rysunek 19. Dołączanie ProductsTableAdapter nowych metod dodanych w tym kroku (kliknij, aby wyświetlić obraz pełnowymiarowy)

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 do klasy następujące dwie metody ProductsBLLWithSprocs :

[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetDiscontinuedProducts()
{
    return Adapter.GetDiscontinuedProducts();
}
[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable 
    GetProductsWithPriceLessThan(decimal priceLessThan)
{
    return Adapter.GetProductsWithPriceLessThan(priceLessThan);
}

Obie metody po prostu wywołają odpowiednią metodę DAL i zwracają ProductsDataTable wystąpienie. Znaczniki DataObjectMethodAttribute powyżej każdej metody powodują, że te metody należy uwzględnić 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 włączenia obsługi wywoływania GetDiscontinuedProducts i GetProductsWithPriceLessThan zarządzanych procedur składowanych możemy teraz wyświetlić te wyniki procedur składowanych za pośrednictwem strony ASP.NET.

ManagedFunctionsAndSprocs.aspx Otwórz stronę w AdvancedDAL folderze i z przybornika przeciągnij element GridView na Projektant. Ustaw właściwość GridView ID na DiscontinuedProducts i z jego 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 pełnowymiarowy)

Rysunek 21. Wybierz metodę GetDiscontinuedProducts z listy Drop-Down 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 elemecie ProductsDataTable. Poświęć chwilę, aby usunąć wszystkie te pola z wyjątkiem ProductName elementów i Discontinued, w tym momencie znaczniki deklaratywne GridView i ObjectDataSource powinny wyglądać podobnie do następujących:

<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święć 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 klasy GetDiscontinuedProducts 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 obiektu DAL, a następnie zwracane do biblioteki BLL, która następnie zwraca je do warstwy prezentacji, gdzie są one powiązane z obiektem GridView i wyświetlane. Zgodnie z oczekiwaniami siatka zawiera listę tych produktów, które zostały wycofane.

Rysunek 22. Wymienione są wycofane produkty (kliknij, aby wyświetlić obraz pełnowymiarowy)

Aby uzyskać dalszą praktykę, dodaj pole TextBox i inny element GridView do strony. Aby ten element GridView wyświetlał produkty mniejsze niż kwota wprowadzona do pola TextBox przez wywołanie ProductsBLLWithSprocs metody klasy s GetProductsWithPriceLessThan .

Krok 9. Tworzenie i wywoływanie funkcji UDF języka T-SQL

User-Defined Funkcje lub funkcje zdefiniowane przez użytkownika są obiektami bazy danych, które ściśle naśladują semantyka funkcji w językach programowania. Podobnie jak funkcja w języku C#, 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ę szybkim typom funkcji zdefiniowanych przez użytkownika, począwszy od funkcji UDF, która zwraca typ danych skalarnych.

Poniższa funkcja UDF oblicza szacowaną wartość spisu dla określonego produktu. W ten sposób przyjmuje trzy parametry wejściowe — UnitPricewartości , UnitsInStocki Discontinued dla określonego produktu — i zwraca wartość typu money. Oblicza szacowaną wartość spisu przez pomnożenie UnitPrice wartości przez UnitsInStockelement . W przypadku wycofanych elementów 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 UDF do bazy danych można ją znaleźć za pośrednictwem programu Management Studio, rozwijając folder Programmability, a następnie funkcje, a następnie funkcje skalarne. Można go użyć w zapytaniu w SELECT następujący sposób:

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

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

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

Funkcje zdefiniowane przez użytkownika mogą również zwracać dane tabelaryczne. Na przykład możemy utworzyć funkcję UDF, 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 UDF 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 udf_GetProductsByCategoryID funkcję UDF do bazy danych Northwind; Rysunek 24 przedstawia dane wyjściowe powyższego SELECT zapytania po wyświetleniu za pośrednictwem programu Management Studio. Funkcje zdefiniowane przez użytkownika zwracające dane tabelaryczne można znaleźć w folderze Eksplorator obiektów Table-value Functions.

Rysunek 24. Lista 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, zapoznaj się z tematem Wprowadzenie do usługi User-Defined Functions. Zapoznaj się również z zaletami i wadami funkcji User-Defined.

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. 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 udf_ComputeInventoryValue funkcję UDF w kodzie zarządzanym.

Aby dodać zarządzaną funkcję UDF do ManagedDatabaseConstructs projektu, kliknij prawym przyciskiem myszy nazwę projektu w Eksplorator rozwiązań i wybierz polecenie Dodaj nowy element. Wybierz szablon User-Defined w oknie dialogowym Dodawanie nowego elementu i nadaj nowej nazwie udf_ComputeInventoryValue_Managed.csplik UDF .

Rysunek 25. Dodawanie nowej zarządzanej ManagedDatabaseConstructs funkcji UDF do projektu (kliknij, aby wyświetlić obraz pełnowymiarowy)

Szablon funkcji User-Defined 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 udekorowana przy użyciu atrybutuSqlFunction, który flaguje metodę jako zarządzaną funkcję UDF.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString udf_ComputeInventoryValue_Managed()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};

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 UDF języka T-SQL udf_ComputeInventoryValue .

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlMoney udf_ComputeInventoryValue_Managed
    (SqlMoney UnitPrice, SqlInt16 UnitsInStock, SqlBoolean Discontinued)
{
    SqlMoney inventoryValue = 0;
    if (!UnitPrice.IsNull && !UnitsInStock.IsNull)
    {
        inventoryValue = UnitPrice * UnitsInStock;
        if (Discontinued == true)
            inventoryValue = inventoryValue * new SqlMoney(0.5);
    }
    return inventoryValue;
}

Należy pamiętać, że parametry wejściowe metody UDF są odpowiadającymi im typami SQL: dla pola , i dla SqlBooleanUnitsInStockDiscontinued. SqlInt16UnitPriceSqlMoney Te typy danych odzwierciedlają typy zdefiniowane w Products tabeli: kolumna UnitPrice ma typ , UnitsInStock kolumnę typu moneysmallinti kolumnę 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 wartości s, które wykonujemy za pośrednictwem SqlMoney właściwości obiektuIsNull. Jeśli obie UnitPrice wartości i UnitsInStock zawierają wartości inneNULL niż, obliczymy wartość inventoryValue , aby być produktem tych dwóch wartości. Następnie, jeśli Discontinued ma wartość true, połówek wartości.

Uwaga

Obiekt SqlMoney umożliwia pomnożanie tylko dwóch SqlMoney wystąpień. Nie zezwala SqlMoney na mnożnik wystąpienia przez literał zmiennoprzecinkowa liczba. W związku z tym, aby pomnożyć go o połowę inventoryValue przez nowe SqlMoney wystąpienie, które ma wartość 0,5.

Krok 11. Wdrażanie zarządzanej funkcji UDF

Teraz, po utworzeniu zarządzanej funkcji UDF, jesteśmy gotowi wdrożyć ją w bazie danych Northwind. Jak pokazano w kroku 4, obiekty zarządzane w projekcie 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 SQL Server Management Studio i odśwież folder Funkcje z wartością skalarną. 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 UDF utworzona w kroku 10, która została właśnie wdrożona.

Aby przetestować tę zarządzaną funkcję UDF, 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 UDF zamiast funkcji UDF języka T-SQL udf_ComputeInventoryValue , ale dane wyjściowe są takie same. Wróć do rysunku 23, aby zobaczyć zrzut ekranu przedstawiający dane wyjściowe funkcji UDF.

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

W samouczku Debugowanie procedur składowanych omówiliśmy trzy opcje debugowania SQL Server za pomocą programu Visual Studio: debugowanie bezpośredniej bazy danych, debugowanie aplikacji i debugowanie z projektu SQL Server. Nie można debugować zarządzanych obiektów bazy danych za pomocą debugowania bezpośredniej bazy danych, ale można debugować z poziomu aplikacji klienckiej i bezpośrednio z projektu SQL Server. Aby debugowanie działało, jednak baza danych 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 pełnowymiarowy)

Najpierw przyjrzyjmy się debugowaniu zarządzanych obiektów bazy danych z SQL Server Project. Ponieważ nasze rozwiązanie obejmuje dwa projekty — ManagedDatabaseConstructs projekt SQL Server wraz z naszą witryną internetową — w celu debugowania z projektu SQL Server, musimy poinstruować program Visual Studio, aby ManagedDatabaseConstructs uruchamiał projekt SQL Server po rozpoczęciu 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 . Aby na przykład przetestować GetProductsWithPriceLessThan zarządzaną 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 Test.sqldo programu rozpocznij debugowanie, przechodząc do menu Debugowanie i wybierając pozycję Rozpocznij debugowanie lub naciskając klawisz F5 lub zieloną ikonę odtwarzania na pasku narzędzi. Spowoduje to skompilowanie projektów w ramach rozwiązania, 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 metodę GetProductsWithPriceLessThan , zbadać wartości parametrów wejściowych itd.

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

Aby obiekt bazy danych SQL był debugowany za pośrednictwem aplikacji klienckiej, konieczne jest skonfigurowanie bazy 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 ASP.NET aplikacji i bazy danych ustaw witrynę internetową ASP.NET jako projekt startowy i rozpocznij debugowanie. Jeśli odwiedzasz stronę, która wywołuje jeden z zarządzanych obiektów, które mają punkt przerwania, aplikacja zostanie zatrzymana, a 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

SQL Server Projects ułatwiają tworzenie, kompilowanie i wdrażanie obiektów zarządzanej bazy danych. Niestety, SQL Server Projects 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 w zestawie,
3. Rejestrowanie zestawu przy użyciu bazy danych SQL Server 2005 i
4. Utwórz obiekt bazy danych w SQL Server, który wskazuje 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.cs i wprowadź następujący kod w pliku (w tym celu możesz użyć programu Visual Studio, Notatnika lub dowolnego edytora tekstów):

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetProductsWithPriceGreaterThan(SqlMoney price)
    {
        // Create the command
        SqlCommand myCommand = 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);
    }
};

Ten kod jest prawie identyczny z tą metodą utworzoną GetProductsWithPriceLessThan w kroku 5. Jedyną różnicą są nazwy metod, WHERE klauzula i nazwa parametru używana w zapytaniu. W metodzie klauzula GetProductsWithPriceLessThanWHERE brzmi: WHERE UnitPrice < @MaxPrice. W tym miejscu użyjemy polecenia GetProductsWithPriceGreaterThan: WHERE UnitPrice > @MinPrice .

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

csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs

Jeśli folder zawierający csc.exe nie w systemie PATHs , musisz 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\csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs

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

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

Uwaga

Zamiast kompilować GetProductsWithPriceGreaterThan.cs plik klasy z poziomu wiersza polecenia, możesz też użyć programu Visual C# Express Edition lub utworzyć oddzielny projekt biblioteki klas w programie Visual Studio Standard Edition. S ren Jacob Lauritsen uprzejmie dostarczył taki projekt Visual C# Express Edition z kodem dla GetProductsWithPriceGreaterThan procedury składowanej oraz dwie zarządzane procedury składowane i UDF utworzone w krokach 3, 5 i 10. Projekt S ren zawiera 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 SQL Server 2005. Można to wykonać za pomocą języka T-SQL, za pomocą polecenia CREATE ASSEMBLYlub za pomocą SQL Server Management Studio. Skoncentrujmy się na korzystaniu z programu Management Studio.

W programie Management Studio rozwiń folder Programmability w bazie danych Northwind. Jeden z jego podfolderów to Zestawy. Aby ręcznie dodać nowy zestaw do bazy danych, kliknij prawym przyciskiem myszy folder Zestawy i wybierz pozycję 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 pełnowymiarowy)

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

Mimo że dodaliśmy zestaw do bazy danych Northwind, musimy jeszcze skojarzyć 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 można było tworzyć 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 C#. W tym samouczku utworzyliśmy dwie zarządzane procedury składowane i zarządzaną funkcję User-Defined.

Program Visual Studio SQL Server typ projektu ułatwia tworzenie, kompilowanie i wdrażanie zarządzanych obiektów bazy danych. Ponadto oferuje rozbudowaną obsługę debugowania. Jednak SQL Server typy projektów 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 User-Defined
• Tworzenie obiektów SQL Server 2005 w kodzie zarządzanym
• Instrukcje: tworzenie i uruchamianie procedury składowanej clR SQL Server
• Instrukcje: tworzenie i uruchamianie funkcji SQL Server User-Defined CLR
• Instrukcje: edytowanie skryptu w celu uruchamiania Test.sql obiektów SQL
• Wprowadzenie do funkcji zdefiniowanych przez użytkownika
• Kod zarządzany i 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 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 przejrzyona przez wielu przydatnych recenzentów. Głównym recenzentem tego samouczka był S ren Jacob Lauritsen. Oprócz zapoznania się z tym artykułem, S ren utworzył 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 adresemmitchell@4GuysFromRolla.com .

WsteczDalej