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

przez Scott Mitchell

Pobierz kod lub Pobierz plik PDF

Microsoft SQL Server 2005 integruje się z środowiskiem uruchomieniowym języka wspólnego .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#. Zobaczymy również, jak te wersje programu Visual Studio umożliwiają debugowanie takich obiektów zarządzanych baz danych.

Wprowadzenie

Bazy danych, takie jak Microsoft s SQL Server 2005, wykorzystują Transact-Structured Query Language (T-SQL) do wstawiania, modyfikowania i pobierania danych. Większość systemów baz danych zawiera konstrukcje do grupowania serii instrukcji SQL, które można następnie wykonać jako pojedynczą jednostkę wielokrotnego użytku. Procedury składowane są jednym z przykładów. Inna to funkcja zdefiniowana przez użytkownika(UDF), konstrukcja, która dokładniej sprawdzi się w kroku 9.

Na jego rdzeń program SQL został zaprojektowany do pracy z zestawami danych. SELECTInstrukcje, i zależą od UPDATE DELETE zastosowanych do wszystkich rekordów w odpowiedniej tabeli i są ograniczone tylko przez ich WHERE klauzule. Istnieje jednak wiele funkcji języka zaprojektowanych do pracy z jednym rekordem jednocześnie i do manipulowania danymi skalarnymi. CURSOR s Zezwalaj na zapętlenie zestawu rekordów w czasie. Funkcje manipulowania ciągami, takie jak LEFT , CHARINDEX i PATINDEX współdziałają z danymi skalarnymi. SQL zawiera również instrukcje przepływu sterowania, takie jak IF i WHILE .

Przed Microsoft SQL Server 2005, procedury składowane i UDF mogą być zdefiniowane tylko jako kolekcja instrukcji T-SQL. SQL Server 2005, jednak został zaprojektowany w celu zapewnienia integracji ze środowiskiem uruchomieniowym języka wspólnego (CLR), który jest używany przez wszystkie zestawy .NET. W związku z tym procedury składowane i UDF w bazie danych SQL Server 2005 można utworzyć za pomocą kodu zarządzanego. Oznacza to, że można utworzyć procedurę składowaną lub UDF jako metodę w klasie języka C#. Dzięki temu te procedury składowane i UDF mogą korzystać z funkcji w .NET Framework i z własnych klas niestandardowych.

W tym samouczku sprawdzimy, jak tworzyć zarządzane procedury składowane i funkcje zdefiniowane przez użytkownika oraz jak integrować je z naszą bazą danych Northwind. Zacznij korzystać z aplikacji.

Note

Obiekty zarządzanej bazy danych oferują pewne korzyści w porównaniu z ich odpowiednikami SQL. Najważniejsze zalety to zaawansowanie i znajomość języka oraz możliwość ponownego użycia istniejącego kodu i logiki. Mimo że obiekty zarządzanej bazy danych mogą być mniej wydajne podczas pracy z zestawami danych, które nie obejmują logiki proceduralnej. Dokładniejsze Omówienie zalet korzystania z kodu zarządzanego i języka T-SQL można znaleźć w sekcji zalety korzystania z kodu zarządzanego do tworzenia obiektów bazy danych.

Krok 1. przeniesienie bazy danych Northwind zApp_Data

We wszystkich naszych samouczkach wykorzystano plik bazy danych Microsoft SQL Server 2005 Express Edition w folderze aplikacji sieci Web App_Data . Umieszczenie bazy danych w App_Data uproszczonej dystrybucji i uruchamiania tych samouczków jako wszystkie pliki znajdują się w jednym katalogu i nie wymaga dodatkowych kroków konfiguracyjnych do przetestowania samouczka.

Na potrzeby tego samouczka należy jednak przenieść bazę danych Northwind z App_Data i jawnie ją zarejestrować z wystąpieniem bazy danych SQL Server 2005 Express Edition. Chociaż możemy wykonać kroki tego samouczka z bazą danych w App_Data folderze, kilka kroków jest znacznie prostsze przez jawne zarejestrowanie bazy danych za pomocą wystąpienia bazy danych SQL Server 2005 Express Edition.

Pobranie 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łasnych implementacji samouczków, Zamknij program Visual Studio i Przenieś NORTHWND.MDF NORTHWND_log.LDF pliki z folderu witryny sieci Web App_Data do folderu poza witryną sieci Web. Po przeniesieniu plików bazy danych do innego folderu musimy zarejestrować bazę danych Northwind z wystąpieniem bazy danych SQL Server 2005 Express Edition. Można to zrobić z poziomu SQL Server Management Studio. Jeśli na komputerze zainstalowano wersję nierolniczą SQL Server 2005, na pewno zainstalowano już Management Studio. Jeśli masz tylko SQL Server 2005 Express Edition na komputerze, poświęć chwilę na pobranie i zainstalowanie Microsoft SQL Server Management Studio Express.

Uruchom program SQL Server Management Studio. Jak pokazano na rysunku 1, Management Studio rozpocznie się z pytaniem o serwer, z którym ma zostać nawiązane połączenie. Wprowadź localhost\SQLExpress w polu Nazwa serwera, wybierz pozycję Uwierzytelnianie systemu Windows na liście rozwijanej uwierzytelnianie, a następnie kliknij przycisk Połącz.

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

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

Po zakończeniu połączenia w oknie Eksplorator obiektów zostaną wystawione informacje dotyczące wystąpienia bazy danych SQL Server 2005 Express Edition, w tym jego baz danych, informacje o zabezpieczeniach, opcje zarządzania itd.

Musimy dołączyć bazę danych Northwind do DataFiles folderu (lub wszędzie tam, gdzie można go przenieść) do wystąpienia bazy danych SQL Server 2005 Express Edition. Kliknij prawym przyciskiem myszy folder Databases, a następnie wybierz opcję Dołącz z menu kontekstowego. Spowoduje to wyświetlenie okna dialogowego dołączanie baz danych. Kliknij przycisk Dodaj, przejdź do odpowiedniego NORTHWND.MDF pliku i kliknij przycisk OK. W tym momencie ekran powinien wyglądać podobnie do rysunku 2.

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

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

Note

Podczas nawiązywania połączenia z wystąpieniem SQL Server 2005 Express Edition za pomocą Management Studio okno dialogowe Dołączanie baz danych nie pozwala na przechodzenie do szczegółów katalogów profilów użytkowników, takich jak Moje dokumenty. W związku z tym upewnij się, że NORTHWND.MDF pliki i są umieszczone NORTHWND_log.LDF w katalogu profilów nie użytkownika.

Kliknij przycisk OK, aby dołączyć bazę danych. Zostanie zamknięte okno dialogowe Dołączanie baz danych, a Eksplorator obiektów powinna teraz wyświetlić listę bezpośrednio dołączonej bazy danych. Prawdopodobnie 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 ją prawym przyciskiem myszy i wybierając polecenie Zmień nazwę.

Zmień nazwę bazy danych na Northwind

Rysunek 3. zmiana nazwy bazy danych na Northwind

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

Aby utworzyć zarządzane procedury składowane lub UDF w SQL Server 2005, należy napisać procedurę składowaną i logikę UDF jako kod C# w klasie. Po zapisaniu kodu będziemy musieli skompilować tę klasę do zestawu ( .dll pliku), zarejestrować zestaw z SQL Server bazą danych, a następnie utworzyć procedurę składowaną lub obiekt UDF w bazie danych, która wskazuje odpowiednią metodę w zestawie. Wszystkie te kroki można wykonać ręcznie. Możemy utworzyć kod w dowolnym edytorze tekstu, skompilować go z wiersza polecenia przy użyciu kompilatora języka C# ( csc.exe ), zarejestrować go w bazie danych przy użyciu CREATE ASSEMBLY polecenia lub z Management Studio, a następnie dodać procedurę składowaną lub metodę UDF przy użyciu podobnych metod. Na szczęście wersje Professional i Team Systems programu Visual Studio zawierają SQL Server typ projektu, który automatyzuje te zadania. W tym samouczku opisano, jak za pomocą typu projektu SQL Server utworzyć zarządzaną procedurę składowaną i funkcję UDF.

Note

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

Zacznij od otwarcia programu Visual Studio. Z menu plik wybierz polecenie Nowy projekt, aby wyświetlić okno dialogowe Nowy projekt (zobacz rysunek 4). Przejdź do szczegółów typu projektu bazy danych, a następnie z szablonów wymienionych po prawej stronie wybierz opcję utworzenia nowego projektu SQL Server. Wybrano opcję nazywania tego projektu ManagedDatabaseConstructs i umieszczenia go w rozwiązaniu o nazwie Tutorial75 .

Utwórz nowy projekt SQL Server

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

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

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

Skojarz projekt SQL Server z bazą danych Northwind

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

Aby debugować zarządzane procedury składowane i UDF, które zostaną utworzone w ramach tego projektu, musimy włączyć obsługę debugowania SQL/CLR dla tego połączenia. Za każdym razem, gdy kojarzy projekt SQL Server z nową bazą danych (jak pokazano na rysunku 5), program Visual Studio monituje nas, jeśli chcemy włączyć debugowanie SQL/CLR dla połączenia (zobacz rysunek 6). Kliknij przycisk Yes (Tak).

Włącz debugowanie SQL/CLR

Ilustracja 6. Włączanie debugowania SQL/CLR

W tym momencie nowy projekt SQL Server został dodany do rozwiązania. Zawiera folder o nazwie Test Scripts Test.sql , który jest używany do debugowania obiektów zarządzanej bazy danych utworzonych w projekcie. Zobaczmy debugowanie w kroku 12.

Teraz możemy dodawać nowe zarządzane procedury składowane i UDF do tego projektu, ale zanim wyślemy do niej ofertę istniejącej aplikacji sieci Web w rozwiązaniu. Z menu plik wybierz opcję Dodaj, a następnie wybierz istniejącą witrynę sieci Web. Przejdź do odpowiedniego folderu witryny sieci Web i kliknij przycisk OK. Jak pokazano na rysunku 7, spowoduje to zaktualizowanie rozwiązania w taki sposób, aby obejmowało dwa projekty: witrynę internetową i ManagedDatabaseConstructs projekt SQL Server.

Eksplorator rozwiązań obejmuje teraz dwa projekty

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

NORTHWNDConnectionStringWartość w Web.config obecnie odwołuje się do NORTHWND.MDF pliku w App_Data folderze. Ponieważ ta baza danych została usunięta z App_Data i jawnie zarejestrowana w wystąpieniu bazy danych SQL Server 2005 Express Edition, musimy odpowiednio zaktualizować NORTHWNDConnectionString wartość. Otwórz Web.config plik w witrynie sieci Web i Zmień NORTHWNDConnectionString wartość tak, aby parametry połączenia były odczytywane: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True . Po tej zmianie <connectionStrings> sekcja w 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>

Note

Zgodnie z opisem w poprzednim samouczkupodczas debugowania obiektu SQL Server z aplikacji klienckiej, takiej jak witryna sieci Web ASP.NET, musimy wyłączyć buforowanie połączeń. Pokazane powyżej parametry połączenia powodują wyłączenie puli połączeń ( Pooling=false ). Jeśli nie planujesz debugowania zarządzanymi procedurami składowanymi i UDF z witryny sieci Web 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ę przechowywaną jako metodę w projekcie SQL Server. W Eksplorator rozwiązań kliknij prawym przyciskiem myszy ManagedDatabaseConstructs nazwę projektu i wybierz polecenie Dodaj nowy element. Spowoduje to wyświetlenie okna dialogowego Dodawanie nowego elementu zawierającego listę typów obiektów zarządzanych baz danych, które można dodać do projektu. Jak pokazano na rysunku 8, obejmuje procedury składowane i funkcje zdefiniowane przez użytkownika, między innymi.

Zacznij od dodania procedury składowanej, która po prostu zwraca wszystkie produkty, które zostały wycofane. Nazwij nowy plik procedury składowanej GetDiscontinuedProducts.cs .

Dodaj nową procedurę składowaną o nazwie GetDiscontinuedProducts.cs

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

Spowoduje to utworzenie nowego pliku klasy C# o następującej 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 zauważyć, że procedura składowana jest zaimplementowana jako static Metoda w partial pliku klasy o nazwie StoredProcedures . Ponadto GetDiscontinuedProducts Metoda jest dekoracyjna SqlProcedure attribute , która oznacza metodę jako procedurę składowaną.

Poniższy kod tworzy SqlCommand obiekt i ustawia jego CommandText do SELECT zapytania, które zwraca wszystkie kolumny z Products tabeli dla produktów, których Discontinued pole ma wartość 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 , który reprezentuje kontekst obiektu wywołującego. SqlContextZapewnia dostęp do SqlPipe obiektu za pośrednictwem jego Pipe Właściwości. Ten SqlPipe obiekt jest używany do promowania informacji między bazą danych SQL Server a aplikacją wywołującą. Jak nazywa się to, ExecuteAndSend Metoda wykonuje obiekt przekazaną SqlCommand i wysyła wyniki z powrotem do aplikacji klienckiej.

Note

Obiekty zarządzanej bazy danych najlepiej nadają się do procedur składowanych i UDF, które używają logiki proceduralnej, a nie logiki opartej na zestawie. Logika proceduralna obejmuje pracę z zestawami danych w poszczególnych wierszach lub w pracy z danymi skalarnymi. GetDiscontinuedProductsJednak utworzona właśnie Metoda nie obejmuje logiki proceduralnej. W związku z tym najlepszym rozwiązaniem jest wdrożenie procedury składowanej T-SQL. Jest ona zaimplementowana jako zarządzana procedura składowana w celu przedstawienia kroków niezbędnych do utworzenia i wdrożenia zarządzanych procedur składowanych.

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

Po ukończeniu tego kodu jesteśmy gotowi do wdrożenia go w bazie danych Northwind. Wdrożenie projektu SQL Server kompiluje kod w zestawie, rejestruje zestaw z bazą 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 bardziej precyzyjnie wpisany w kroku 13. Kliknij prawym przyciskiem myszy ManagedDatabaseConstructs nazwę projektu w Eksplorator rozwiązań i wybierz opcję Wdróż. Jednak wdrożenie nie powiedzie się z powodu następującego błędu: nieprawidłowa składnia w sąsiedztwie "EXTERNAL". Może być konieczne ustawienie poziomu zgodności bieżącej bazy danych na wyższą wartość, aby włączyć tę funkcję. Zobacz pomoc dotyczącą procedury składowanej sp_dbcmptlevel .

Ten komunikat o błędzie występuje podczas próby zarejestrowania zestawu przy użyciu bazy danych Northwind. Aby zarejestrować zestaw z bazą danych SQL Server 2005, należy ustawić poziom zgodności bazy danych na 90. Domyślnie nowe bazy danych SQL Server 2005 mają poziom zgodności równy 90. Jednak bazy danych utworzone przy użyciu Microsoft SQL Server 2000 mają domyślny poziom zgodności równy 80. Ponieważ baza danych Northwind była początkowo bazą danych Microsoft SQL Server 2000, jej poziom zgodności jest obecnie ustawiony na 80 i dlatego należy go zwiększyć do 90 w celu zarejestrowania obiektów zarządzanych baz danych.

Aby zaktualizować poziom zgodności bazy danych, Otwórz nowe okno zapytania w Management Studio i wprowadź:

exec sp_dbcmptlevel 'Northwind', 90

Kliknij ikonę Execute (wykonaj) na pasku narzędzi, aby uruchomić powyższe zapytanie.

Aktualizowanie poziomu zgodności bazy danych Northwind

Ilustracja 9. aktualizowanie poziomu zgodności bazy danych Northwind (kliknij, aby wyświetlić obraz w pełnym rozmiarze)

Po zaktualizowaniu poziomu zgodności, wdróż ponownie projekt SQL Server. Ta godzina wdrożenia powinna 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 szczegółów folderu programowanie, a następnie rozwiń folder zestawy. Jak pokazano na rysunku 10, baza danych Northwind zawiera teraz zestaw wygenerowany przez ManagedDatabaseConstructs projekt.

Zestaw ManagedDatabaseConstructs jest teraz zarejestrowany w bazie danych Northwind

Ilustracja 10. ManagedDatabaseConstructs zestaw 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 na GetDiscontinuedProducts metodę w ManagedDatabaseConstructs zestawie. Po GetDiscontinuedProducts wykonaniu procedury składowanej jest wykonywana GetDiscontinuedProducts Metoda. Ponieważ jest to zarządzana procedura składowana, nie można jej edytować za pomocą Management Studio (w związku z tym ikona kłódki obok nazwy procedury składowanej).

Procedura składowana GetDiscontinuedProducts jest wymieniona w folderze procedury składowane

Ilustracja 11. GetDiscontinuedProducts procedura składowana znajduje się na liście w folderze procedury składowane

Nadal trzeba przezwyciężyć, zanim będziemy mogli wywoływać zarządzane procedury składowane: baza danych jest skonfigurowana tak, aby uniemożliwiać wykonywanie kodu zarządzanego. Sprawdź to, otwierając nowe okno zapytania i wykonując GetDiscontinuedProducts procedurę składowaną. Zostanie wyświetlony następujący komunikat o błędzie: wykonywanie kodu użytkownika w .NET Framework jest wyłączone. Włącz opcję konfiguracji z włączoną funkcją CLR.

Aby zbadać informacje o konfiguracji bazy danych Northwind, wprowadź i wykonaj polecenie exec sp_configure w oknie zapytania. Wskazuje to, że ustawienie CLR enabled ma obecnie wartość 0.

Ustawienie Enabled CLR ma obecnie wartość 0

Rysunek 12: ustawienie Enabled CLR ma obecnie wartość 0 (kliknij, aby wyświetlić obraz w pełnym rozmiarze)

Należy zauważyć, że każde ustawienie konfiguracji na rysunku 12 ma cztery wartości na liście: wartości minimalne i maksymalne oraz wartości konfiguracyjne i uruchomienia. Aby zaktualizować wartość konfiguracji dla ustawienia Enabled CLR, wykonaj następujące polecenie:

exec sp_configure 'clr enabled', 1

Po ponownym uruchomieniu zostanie exec sp_configure wyświetlona informacja, że powyższa instrukcja zaktualizował wartość konfiguracyjną ustawienia CLR z włączoną wartością 1, ale wartość Run jest nadal ustawiona na 0. Aby zmiana konfiguracji została uwzględniona, należy wykonać RECONFIGURE polecenie, które spowoduje ustawienie wartości Run na bieżącą wartość konfiguracji. Po prostu wprowadź RECONFIGURE w oknie zapytania i kliknij ikonę Execute (wykonaj) na pasku narzędzi. Jeśli uruchomisz exec sp_configure teraz, powinna zostać wyświetlona wartość 1 dla ustawienia konfiguracja i uruchomienia z włączoną funkcją CLR.

Po zakończeniu konfiguracji z włączoną obsługą środowiska CLR jesteśmy gotowi do uruchomienia zarządzanej GetDiscontinuedProducts procedury składowanej. W oknie zapytania wprowadź i wykonaj polecenie exec GetDiscontinuedProducts . Wywołanie procedury składowanej powoduje, że odpowiedni zarządzany kod w GetDiscontinuedProducts metodzie do wykonania. Ten kod emituje SELECT zapytanie, aby zwrócić wszystkie produkty, które są wycofane, i zwraca te dane do aplikacji wywołującej, która jest SQL Server Management Studio w tym wystąpieniu. Management Studio otrzymuje te wyniki i wyświetla je w oknie wyników.

Procedura składowana GetDiscontinuedProducts zwraca wszystkie wycofane produkty

Ilustracja 13. GetDiscontinuedProducts procedura składowana zwraca wszystkie wycofane produkty (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Krok 5. Tworzenie zarządzanych procedur składowanych akceptujących parametry wejściowe

Wiele zapytań i procedur składowanych utworzonych w ramach tych samouczków ma używane Parametry. Na przykład w przypadku tworzenia nowych procedur składowanych dla wpisanych zestawów danych TableAdapters samouczek został utworzony w procedurze składowanej o nazwie GetProductsByCategoryID , która zaakceptowała parametr wejściowy o nazwie @CategoryID . Procedura składowana zwraca wszystkie produkty CategoryID , których pole pasuje do wartości podanego @CategoryID parametru.

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

Aby dodać nową procedurę składowaną do projektu, kliknij prawym przyciskiem myszy ManagedDatabaseConstructs nazwę projektu i wybierz opcję dodania nowej procedury składowanej. Nazwij plik GetProductsWithPriceLessThan.cs. Jak opisano w kroku 3, spowoduje to utworzenie nowego pliku klasy C# z metodą o nazwie GetProductsWithPriceLessThan umieszczonej w partial klasie StoredProcedures .

Zaktualizuj GetProductsWithPriceLessThan definicję metody s tak, aby akceptowała SqlMoney parametr wejściowy o nazwie price i napisać kod do wykonania i zwrócić wyniki 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);
}

GetProductsWithPriceLessThanDefinicja i kod metody s przypominają definicję i kod GetDiscontinuedProducts metody utworzonej w kroku 3. Jedyne różnice polegają na tym, że GetProductsWithPriceLessThan Metoda akceptuje jako parametr wejściowy ( price ), SqlCommand zapytanie s zawiera parametr ( @MaxPrice ), a parametr jest dodawany do SqlCommand Parameters kolekcji s i ma przypisaną wartość price zmiennej.

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 pojawić się nowy wpis GetProductsWithPriceLessThan . W oknie zapytania wprowadź i wykonaj polecenie exec GetProductsWithPriceLessThan 25 , które spowoduje wyświetlenie listy wszystkich produktów mniejszych niż $25, jak pokazano na rysunku 14.

Produkty poniżej $25 są wyświetlane

Ilustracja 14. wyświetlane są produkty poniżej $25 (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

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

W tym momencie dodałem GetDiscontinuedProducts i GetProductsWithPriceLessThan zarządzanymi procedurami przechowywanymi do ManagedDatabaseConstructs projektu i zarejestrowali je za pomocą bazy danych Northwind SQL Server. Te zarządzane procedury składowane są również wywoływane z SQL Server Management Studio (zobacz rysunek s 13 i 14). Aby nasze aplikacje ASP.NET korzystały z tych zarządzanych procedur składowanych, należy jednak dodać je do warstw dostępu do danych i logiki biznesowej w architekturze. W tym kroku dodamy dwie nowe metody do ProductsTableAdapter NorthwindWithSprocs określonego zestawu danych, który został początkowo utworzony w nowych procedurach składowanych dla danego typu zestawu danych s TableAdapters . W kroku 7 dodamy odpowiednie metody do LOGIKI biznesowej.

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

Note

Ze względu na to, że baza danych Northwind została przeniesiona z App_Data folderu do wystąpienia bazy danych SQL Server 2005 Express Edition, należy koniecznie zaktualizować odpowiednie parametry połączenia w Web.config w celu odzwierciedlenia tej zmiany. W kroku 2 omawiamy aktualizację NORTHWNDConnectionString wartości w Web.config . Jeśli nie pamiętasz tej aktualizacji, zostanie wyświetlony komunikat o błędzie nie można dodać zapytania. Nie można znaleźć połączenia NORTHWNDConnectionString dla obiektu Web.config w oknie dialogowym podczas próby dodania nowej metody do TableAdapter. Aby rozwiązać ten problem, kliknij przycisk OK, a następnie przejdź do Web.config i zaktualizuj NORTHWNDConnectionString wartość zgodnie z opisem w kroku 2. Następnie spróbuj ponownie dodać metodę do TableAdapter. Tym razem należy to zrobić bez błędu.

Dodanie nowej metody powoduje uruchomienie Kreatora konfiguracji zapytania TableAdapter, którego użyto wielokrotnie w poprzednich samouczkach. Pierwszy krok prosi nas o określenie, w jaki sposób element TableAdapter powinien uzyskiwać dostęp do bazy danych: za pośrednictwem instrukcji SQL ad hoc lub za pośrednictwem nowej lub istniejącej procedury składowanej. Ponieważ została już utworzona i zarejestrowana GetDiscontinuedProducts zarządzana procedura składowana z bazą danych, wybierz opcję Użyj istniejącej procedury składowanej, a następnie kliknij przycisk Dalej.

Wybierz opcję Użyj istniejącej procedury składowanej

Ilustracja 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 komunikat z prośbą o procedurę składowaną, która zostanie wywołana przez metodę. Wybierz GetDiscontinuedProducts zarządzaną procedurę składowaną z listy rozwijanej, a następnie kliknij przycisk Dalej.

Wybierz zarządzaną procedurę składowaną GetDiscontinuedProducts

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

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), a następnie kliknij przycisk Dalej.

Wybierz opcję danych tabelarycznych

Ilustracja 17. Wybierz opcję danych tabelarycznych (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Końcowy ekran kreatora pozwala nam określić używane wzorce dostępu do danych oraz nazwy metod wynikowych. Pozostaw zaznaczone pola wyboru i nazwij metody FillByDiscontinued i GetDiscontinuedProducts . Kliknij przycisk Zakończ, aby zakończyć kreatora.

Nazwij metody FillByDiscontinued i GetDiscontinuedProducts

Ilustracja 18. Nazwij metody FillByDiscontinued i GetDiscontinuedProducts (kliknij, aby wyświetlić obraz w pełnym rozmiarze)

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

Rysunek 19 przedstawia zrzut ekranu projektanta obiektów DataSet po dodaniu metod do ProductsTableAdapter dla GetDiscontinuedProducts i GetProductsWithPriceLessThan zarządzanych procedur składowanych.

ProductsTableAdapter obejmuje nowe metody dodane w tym kroku

Ilustracja 19. ProductsTableAdapter zawiera 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, które zostały dodane 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 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łują odpowiednie metody DAL i zwracają ProductsDataTable wystąpienie. DataObjectMethodAttributeZnaczniki powyżej każdej metody powodują, że te metody są uwzględnione na liście rozwijanej na karcie Wybierz w Kreatorze konfiguracji źródła danych programu ObjectDataSource s.

Krok 8: wywoływanie zarządzanych procedur składowanych z warstwy prezentacji

Dzięki warstwom logiki biznesowej i dostępu do danych rozszerzane w celu uwzględnienia obsługi wywoływania GetDiscontinuedProducts i GetProductsWithPriceLessThan zarządzanych procedur składowanych można teraz wyświetlić te procedury składowane za pomocą strony ASP.NET.

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

Skonfiguruj element ObjectDataSource, aby używał klasy ProductsBLLWithSprocs

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

Wybierz metodę GetDiscontinuedProducts z listy rozwijanej na karcie Wybierz.

Ilustracja 21. Wybierz GetDiscontinuedProducts metodę z listy rozwijanej na karcie Wybierz (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Ponieważ ta siatka zostanie użyta do wyświetlania informacji o produkcie, Ustaw listę rozwijaną na kartach UPDATE, INSERT i DELETE na wartość (brak), a następnie kliknij przycisk Zakończ.

Po zakończeniu działania kreatora program Visual Studio automatycznie doda BoundField lub CheckBoxField dla każdego pola danych w ProductsDataTable . Poświęć chwilę, aby usunąć wszystkie te pola z wyjątkiem ProductName 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ę na wyświetlenie tej strony za pomocą przeglądarki. Po odwiedzeniu strony element ObjectDataSource wywołuje ProductsBLLWithSprocs metodę Class s GetDiscontinuedProducts . Zgodnie z opisem w kroku 7 ta metoda wywołuje do metody DAL s ProductsDataTable klasy s GetDiscontinuedProducts , która wywołuje GetDiscontinuedProducts procedurę 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 a ProductsDataTable przez dal, a następnie zwracane do logiki biznesowej, który następnie zwraca je do warstwy prezentacji, gdzie są one powiązane z elementem GridView i są wyświetlane. Zgodnie z oczekiwaniami siatka zawiera listę produktów, które zostały wycofane.

Wycofane produkty są wymienione na liście

Ilustracja 22. wycofane produkty są wymienione na liście (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Aby zapewnić dalsze rozwiązanie, Dodaj do strony pole tekstowe i inny element GridView. Czy w tym widoku GridView są wyświetlane produkty mniejsze od ilości wprowadzonej do pola tekstowego przez wywołanie ProductsBLLWithSprocs metody Class s GetProductsWithPriceLessThan .

Krok 9. Tworzenie i wywoływanie UDF T-SQL

Funkcje zdefiniowane przez użytkownika, lub UDF, to obiekty bazy danych ściśle naśladuje semantykę funkcji w językach programowania. Podobnie jak w przypadku funkcji w języku C#, UDF może zawierać zmienną liczbę parametrów wejściowych i zwracać wartość określonego typu. System UDF może zwracać dane skalarne — ciąg, liczbę całkowitą i tak dalej lub dane tabelaryczne. Zezwól na szybkie przeszukiwanie obu typów UDF, rozpoczynając od wartości UDF, która zwraca typ danych skalarnych.

Poniższy format UDF oblicza szacowaną wartość spisu dla określonego produktu. Robi to, pobierając trzy parametry wejściowe — UnitPrice UnitsInStock wartości, i Discontinued dla określonego produktu — i zwraca wartość typu money . Oblicza szacowaną wartość spisu przez pomnożenie UnitPrice przez UnitsInStock . W przypadku niewycofanych elementów ta wartość jest mniejsza.

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 pomocą Management Studio, rozszerzając folder programowalny, a następnie funkcje, a następnie funkcje skalarne Value. Może być używany w SELECT zapytaniu podobnym do tego:

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 pokazuje dane wyjściowe powyższego SELECT zapytania, gdy jest wyświetlany za pomocą Management Studio. Należy również zauważyć, że UDF jest wymieniony w folderze funkcje skalarne wartości w Eksplorator obiektów.

Wszystkie wartości spisu produktów są wymienione na liście

Ilustracja 23: wszystkie wartości spisu produktów są wyświetlane (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

UDF może również zwracać dane tabelaryczne. Można na przykład utworzyć ciąg UDF, który 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
)

udf_GetProductsByCategoryIDElement UDF akceptuje @CategoryID parametr wejściowy i zwraca wyniki określonego SELECT zapytania. Po utworzeniu tego elementu UDF można odwoływać się w FROM klauzuli (lub JOIN ) SELECT zapytania. Poniższy przykład zwróci ProductID ProductName wartości,, i 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, gdy jest wyświetlany za pomocą Management Studio. UDF, które zwracają dane tabelaryczne, można znaleźć w folderze funkcji tabeli Eksplorator obiektów s.

Identyfikator ProductID, ProductName i IDKategorii są wyświetlane dla każdego napoju

Ilustracja 24. ProductID , ProductName i CategoryID są wyświetlane dla każdego napoju (kliknij, aby wyświetlić obraz w pełnym rozmiarze)

Note

Aby uzyskać więcej informacji na temat tworzenia i używania UDF, zapoznaj się z wprowadzeniem do funkcji zdefiniowanych przez użytkownika. Sprawdź również zalety i wady funkcji zdefiniowanych przez użytkownika.

Krok 10. Tworzenie zarządzanego elementu UDF

udf_ComputeInventoryValueUDF i udf_GetProductsByCategoryID utworzone w powyższych przykładach to obiekty bazy danych T-SQL. SQL Server 2005 obsługuje również zarządzane UDF, które można dodać do projektu, podobnie ManagedDatabaseConstructs jak zarządzane procedury składowane z kroków 3 i 5. W tym kroku, Zezwól, aby zaimplementować udf_ComputeInventoryValue UDF w kodzie zarządzanym.

Aby dodać zarządzany system UDF 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 Dodaj nowy element i nazwij nowy plik UDF udf_ComputeInventoryValue_Managed.cs .

Dodawanie nowego zarządzanego elementu UDF do projektu ManagedDatabaseConstructs

Ilustracja 25. Dodawanie nowego zarządzanego elementu UDF do ManagedDatabaseConstructs projektu (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Zdefiniowany przez użytkownika szablon funkcji tworzy partial klasę o nazwie UserDefinedFunctions przy użyciu metody, której nazwa jest taka sama jak nazwa pliku klasy s ( udf_ComputeInventoryValue_Managed w tym wystąpieniu). Ta metoda jest dekoracyjna przy użyciu SqlFunction atrybutu, który oznacza metodę jako zarządzany system 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");
    }
};

udf_ComputeInventoryValueMetoda aktualnie zwraca SqlString obiekt i nie akceptuje żadnych parametrów wejściowych. Musimy zaktualizować definicję metody, aby akceptowała trzy parametry wejściowe — UnitPrice , UnitsInStock , i Discontinued -i zwraca SqlMoney obiekt. Logika obliczania wartości spisu jest taka sama jak w przypadku formatu UDF 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 mają odpowiednie typy SQL: SqlMoney dla pola, UnitPrice SqlInt16 dla UnitsInStock i SqlBoolean dla Discontinued . Te typy danych odzwierciedlają typy zdefiniowane w Products tabeli: UnitPrice kolumna jest typu money , UnitsInStock kolumny typu smallint i Discontinued kolumny typu bit .

Kod jest uruchamiany przez utworzenie SqlMoney wystąpienia o nazwie inventoryValue , do którego przypisano wartość 0. ProductsTabela umożliwia korzystanie z wartości bazy danych NULL w UnitsInPrice UnitsInStock kolumnach i. W związku z tym musimy najpierw sprawdzić, czy te wartości zawierają wartość NULL s, co jest zalecane przez SqlMoney IsNull WłaściwośćObject s. Jeśli oba UnitPrice i UnitsInStock zawierają wartości niebędące NULL wartościami, obliczy się, że jest to inventoryValue iloczyn dwóch. W przypadku wartości Discontinued true wartość jest mniejsza.

Note

SqlMoneyObiekt umożliwia SqlMoney jednocześnie mnożenie dwóch wystąpień. Nie zezwala na SqlMoney pomnożenie wystąpienia przez literał liczby zmiennoprzecinkowej. W związku z tym, aby zmniejszyć inventoryValue liczbę pomnożoną przez nowe SqlMoney wystąpienie o wartości 0,5.

Krok 11. wdrażanie zarządzanego formatu UDF

Po utworzeniu zarządzanego formatu UDF wszystko jest gotowe do wdrożenia w bazie danych Northwind. 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 o wartościach skalarnych. Powinny teraz być widoczne dwa wpisy:

  • dbo.udf_ComputeInventoryValue — Format UDF języka T-SQL utworzony w kroku 9 i
  • dbo.udf ComputeInventoryValue_Managed — zarządzany system UDF utworzony w kroku 10, który właśnie został wdrożony.

Aby przetestować zarządzany system UDF, wykonaj następujące zapytanie w 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ądzanego systemu udf ComputeInventoryValue_Managed UDF zamiast UDF w formacie T-SQL udf_ComputeInventoryValue , ale dane wyjściowe są takie same. Zapoznaj się z powrotem z rysunkiem 23, aby wyświetlić zrzut ekranu przedstawiający dane wyjściowe w formacie UDF s.

Krok 12. debugowanie obiektów zarządzanej bazy danych

W samouczku procedury składowane debugowania omówiono trzy opcje debugowania SQL Server za pośrednictwem programu Visual Studio: bezpośrednie debugowanie bazy danych, debugowanie aplikacji i debugowanie z SQL Server projekcie. Obiekty zarządzanej bazy danych nie mogą być debugowane za pośrednictwem bezpośredniego debugowania bazy danych, ale mogą być debugowane z 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. Należy przypomnieć, że po pierwszym utworzeniu ManagedDatabaseConstructs projektu Visual Studio prosi nas o włączenie debugowania SQL/CLR (patrz rysunek 6 w kroku 2). To ustawienie można zmodyfikować, klikając prawym przyciskiem myszy bazę danych w oknie Eksplorator serwera.

Upewnij się, że baza danych umożliwia debugowanie SQL/CLR

Ilustracja 26. Upewnij się, że baza danych umożliwia debugowanie SQL/CLR

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

Ustawianie punktu przerwania w metodzie GetProductsWithPriceLessThan

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

Pozwól, aby najpierw debugować obiekty zarządzanej bazy danych z projektu SQL Server. Ponieważ nasze rozwiązanie obejmuje dwa projekty — ManagedDatabaseConstructs projekt SQL Server wraz z naszą witryną sieci Web — w celu debugowania z SQL Server projektu musimy polecić programowi Visual Studio uruchomienie ManagedDatabaseConstructs projektu 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.

Gdy ManagedDatabaseConstructs projekt zostanie uruchomiony z debugera, wykonuje instrukcje SQL w Test.sql pliku, który znajduje się w Test Scripts folderze. Na przykład w celu przetestowania GetProductsWithPriceLessThan zarządzanej procedury składowanej 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 do Test.sql programu Rozpocznij debugowanie, przechodząc do menu Debuguj i wybierając polecenie 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 obiektów zarządzanej bazy danych w bazie danych Northwind, a następnie wykonanie Test.sql skryptu. W tym momencie punkt przerwania zostanie osiągnięty i możemy przejść przez GetProductsWithPriceLessThan metodę, przeanalizować wartości parametrów wejściowych i tak dalej.

Punkt przerwania w metodzie GetProductsWithPriceLessThan został trafiony

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

Aby obiekt bazy danych SQL był debugowany za pomocą aplikacji klienckiej, należy bezwzględnie skonfigurować bazę danych do obsługi debugowania aplikacji. Kliknij prawym przyciskiem myszy bazę danych w Eksplorator serwera i upewnij się, że opcja Debugowanie aplikacji jest zaznaczona. Ponadto należy 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 procedury składowane debugowania .

Po skonfigurowaniu aplikacji ASP.NET i bazy danych ustaw witrynę ASP.NET jako projekt startowy, a następnie Rozpocznij debugowanie. Jeśli zostanie wyświetlona strona, która wywołuje jeden z zarządzanych obiektów, które mają punkt przerwania, aplikacja zostanie zatrzymana, a kontrola zostanie przełączona do debugera, gdzie można przejść przez kod, jak pokazano na rysunku 28.

Krok 13. ręczne Kompilowanie i wdrażanie obiektów zarządzanych baz danych

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

  1. Utwórz plik zawierający kod źródłowy obiektu zarządzanej bazy danych,
  2. Kompiluj obiekt do zestawu,
  3. Zarejestruj zestaw z bazą 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, należy utworzyć 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 do pliku (możesz użyć programu Visual Studio, Notatnika lub dowolnego edytora tekstu):

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 niemal identyczny z GetProductsWithPriceLessThan metodą utworzoną w kroku 5. Jedyne różnice to nazwy metod, WHERE klauzule i nazwy parametrów używane w zapytaniu. W GetProductsWithPriceLessThan metodzie WHERE Odczytaj klauzulę: WHERE UnitPrice < @MaxPrice . W tym miejscu GetProductsWithPriceGreaterThan używamy: WHERE UnitPrice > @MinPrice .

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

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

Jeśli folder zawierający csc.exe program nie znajduje się w systemie s PATH , należy w pełni odwołać się do jego ścieżki, na przykład %WINDOWS%\Microsoft.NET\Framework\version\ :

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

Kompiluj GetProductsWithPriceGreaterThan.cs do zestawu

Ilustracja 29. Kompilowanie GetProductsWithPriceGreaterThan.cs do zestawu (kliknij, aby wyświetlić obraz w pełnym rozmiarze)

/tFlaga określa, że plik klasy C# powinien zostać skompilowany do biblioteki DLL (a nie plik wykonywalny). /outFlaga określa nazwę tworzonego zestawu.

Note

Zamiast kompilowania GetProductsWithPriceGreaterThan.cs pliku klasy z wiersza polecenia można użyć programu Visual C# Express Edition lub utworzyć oddzielny projekt biblioteki klas w programie Visual Studio Standard Edition. Polecenie S Ren Jacob Lauritsen, które udostępnia taki projekt Visual C# Express Edition, zawiera kod dla GetProductsWithPriceGreaterThan procedury składowanej oraz dwie zarządzane procedury składowane i UDF utworzone w krokach 3, 5 i 10. Program s Ren s zawiera również polecenia T-SQL, które są konieczne do dodania odpowiednich obiektów bazy danych.

Gdy kod jest kompilowany do zestawu, jest gotowy do zarejestrowania zestawu w bazie danych SQL Server 2005. Można to zrobić za pomocą T-SQL, za pomocą polecenia CREATE ASSEMBLY lub za pośrednictwem SQL Server Management Studio. Zezwól na korzystanie z Management Studio.

W obszarze Management Studio rozwiń folder programowanie w bazie danych Northwind. Jednym z jego podfolderów jest zestaw. 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 zestaw, który ManuallyCreatedDBObjects.dll właśnie został skompilowany, a następnie kliknij przycisk OK, aby dodać zestaw do bazy danych. Zestaw nie powinien być widoczny ManuallyCreatedDBObjects.dll w Eksplorator obiektów.

Dodaj zestaw ManuallyCreatedDBObjects.dll do bazy danych

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

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

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

Po dodaniu zestawu do bazy danych Northwind nie mamy jeszcze skojarzyć procedury składowanej 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ą zarządzaną GetProductsWithPriceGreaterThan (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. Powinna zostać wyświetlona nowa pozycja procedury składowanej — GetProductsWithPriceGreaterThan która 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 UnitPrice większej niż $24,95.

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

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

Podsumowanie

Microsoft SQL Server 2005 zapewnia integrację ze środowiskiem uruchomieniowym języka wspólnego (CLR), która umożliwia tworzenie obiektów bazy danych za pomocą kodu zarządzanego. Wcześniej te obiekty bazy danych można utworzyć tylko przy użyciu języka T-SQL, ale teraz można utworzyć te obiekty przy użyciu języków programowania .NET, takich jak C#. W tym samouczku zostały utworzone dwie zarządzane procedury składowane i zarządzana funkcja zdefiniowana przez użytkownika.

Program Visual Studio s SQL Server typ projektu ułatwia tworzenie, kompilowanie i wdrażanie obiektów zarządzanych baz danych. Ponadto oferuje zaawansowaną obsługę debugowania. Jednak typy projektów SQL Server są dostępne tylko w wersjach Professional i Team Systems programu Visual Studio. W przypadku korzystania z programu Visual Web Developer lub Standard Edition programu Visual Studio, kroki tworzenia, kompilowania i wdrażania należy przeprowadzić 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:

Informacje o autorze

Scott Mitchell, autor siedmiu grup ASP/ASP. NET Books i założyciel of 4GuysFromRolla.com, pracował z technologiami sieci Web firmy Microsoft od czasu 1998. Scott działa jako niezależny konsultant, trainer i składnik zapisywania. Jego Najnowsza książka to Sams ASP.NET 2,0 w ciągu 24 godzin. Można się z nią skontaktować mitchell@4GuysFromRolla.com . lub za pośrednictwem swojego blogu, który można znaleźć pod adresem http://ScottOnWriting.NET .

Specjalne podziękowania

Ta seria samouczków została sprawdzona przez wielu przydatnych recenzentów. Osoba dokonująca przeglądu potencjalnego klienta dla tego samouczka była Jacob Lauritsen. Oprócz przejrzenia tego artykułu polecenie S Ren spowodowało również utworzenie projektu Visual C# Express Edition zawartego w tym artykule s Download do ręcznego kompilowania zarządzanych obiektów bazy danych. Chcesz przeglądać moje nadchodzące artykuły MSDN? Jeśli tak, upuść mi linię na mitchell@4GuysFromRolla.com .