Vytvoření uložených procedur a uživatelsky definovaných funkcí spravovaným kódem (C#)

Scott Mitchell

Stáhnout PDF

Microsoft SQL Server 2005 se integruje s modulem .NET Common Language Runtime, aby vývojáři mohli vytvářet databázové objekty prostřednictvím spravovaného kódu. V tomto kurzu se dozvíte, jak vytvořit spravované uložené procedury a spravované uživatelem definované funkce pomocí kódu jazyka Visual Basic nebo C#. Vidíme také, jak tyto edice sady Visual Studio umožňují ladit takové spravované databázové objekty.

Úvod

Databáze jako Microsoft SQL Server 2005 používají transact-jazyk SQL (Structured Query Language) (T-SQL) pro vkládání, úpravy a načítání dat. Většina databázových systémů zahrnuje konstrukce pro seskupení řady příkazů SQL, které je pak možné spustit jako jednu opakovaně použitelnou jednotku. Uložené procedury jsou jedním z příkladů. Dalším je uživatelem definované funkce (UDF), konstruktor, který podrobněji prozkoumáme v kroku 9.

V jádru je SQL navržený pro práci se sadami dat. Příkazy SELECT, UPDATEa DELETE příkazy se ze své podstaty vztahují na všechny záznamy v odpovídající tabulce a jsou omezeny pouze jejich WHERE klauzulemi. Existuje však mnoho jazykových funkcí navržených pro práci s jedním záznamem najednou a pro manipulaci s skalárními daty. CURSOR umožňuje , aby se sada záznamů postupně smyčovala. Funkce pro manipulaci s řetězci, jako je LEFT, CHARINDEXa PATINDEX pracují s skalárními daty. SQL také obsahuje příkazy toku řízení jako IF a WHILE.

Před microsoft SQL Serverem 2005 bylo možné uložené procedury a funkce definované uživatelem definovat pouze jako kolekci příkazů T-SQL. SQL Server 2005 byl však navržen tak, aby poskytoval integraci s modulem CLR (Common Language Runtime), což je modul runtime používaný všemi sestaveními .NET. Uložené procedury a funkce definované uživatelem v databázi SQL Serveru 2005 je proto možné vytvořit pomocí spravovaného kódu. To znamená, že můžete vytvořit uloženou proceduru nebo funkci definovanou uživatelem jako metodu ve třídě jazyka C#. To umožňuje těmto uloženým procedurám a funkcím definovaným uživatelem využívat funkce v rozhraní .NET Framework a z vlastních tříd.

V tomto kurzu se podíváme, jak vytvořit spravované uložené procedury a uživatelem definované funkce a jak je integrovat do naší databáze Northwind. Pojďme začít!

Poznámka:

Spravované databázové objekty nabízejí oproti svým protějškům SQL určité výhody. Hlavními výhodami jsou jazyková richness a znalost a schopnost opakovaně používat existující kód a logiku. Spravované databázové objekty ale budou pravděpodobně méně efektivní při práci se sadami dat, které nezahrnují mnoho procedurální logiky. Podrobnější diskuzi o výhodách použití spravovaného kódu a T-SQL najdete v tématu Výhody použití spravovaného kódu k vytváření databázových objektů.

Krok 1: Přesun databáze Northwind mimo App_Data

Všechny naše kurzy dosud používaly soubor databáze Microsoft SQL Server 2005 Express Edition ve složce webové aplikace App_Data . Umístění databáze do App_Data zjednodušené distribuce a spuštění těchto kurzů, protože všechny soubory byly umístěny v jednom adresáři a k otestování kurzu nebyly potřeba žádné další kroky konfigurace.

V tomto kurzu ale pojďme přesunout databázi Northwind z instance databáze App_Data SQL Server 2005 Express Edition a explicitně ji zaregistrovat. I když můžeme provést kroky pro tento kurz s databází ve App_Data složce, řada kroků je mnohem jednodušší tím, že explicitně zaregistrujeme databázi v instanci databáze SQL Server 2005 Express Edition.

Stažení pro tento kurz obsahuje dva databázové soubory - NORTHWND.MDF a NORTHWND_log.LDF - umístěné ve složce s názvem DataFiles. Pokud sledujete společně s vlastní implementací kurzů, zavřete Visual Studio a přesuňte NORTHWND.MDF NORTHWND_log.LDF soubory ze složky webu App_Data do složky mimo web. Po přesunutí souborů databáze do jiné složky potřebujeme zaregistrovat databázi Northwind v instanci databáze SQL Server 2005 Express Edition. To lze provést z aplikace SQL Server Management Studio. Pokud máte v počítači nainstalovanou verzi SQL Server 2005, je pravděpodobné, že už máte nainstalovanou sadu Management Studio. Pokud máte na počítači jenom SQL Server 2005 Express Edition, stáhněte a nainstalujte Microsoft SQL Server Management Studio.

Spusťte SQL Server Management Studio. Jak ukazuje obrázek 1, Management Studio začne dotazem, ke kterému serveru se má připojit. Jako název serveru zadejte localhost\SQLExpress, v rozevíracím seznamu Ověřování zvolte Ověřování systému Windows a klikněte na Připojit.

Obrázek 1: Připojení k příslušné instanci databáze

Po připojení zobrazí okno Průzkumník objektů seznam informací o instanci databáze SQL Server 2005 Express Edition, včetně jeho databází, informací o zabezpečení, možností správy atd.

Potřebujeme připojit databázi Northwind ve DataFiles složce (nebo kamkoliv jste ji mohli přesunout) do instance databáze SQL Serveru 2005 Express Edition. Klikněte pravým tlačítkem na složku Databáze a v místní nabídce zvolte možnost Připojit. Tím se zobrazí dialogové okno Připojit databáze. Klikněte na tlačítko Přidat, přejděte k podrobnostem příslušného NORTHWND.MDF souboru a klikněte na TLAČÍTKO OK. V tomto okamžiku by měla obrazovka vypadat podobně jako na obrázku 2.

Obrázek 2: Připojení k příslušné instanci databáze (kliknutím zobrazíte obrázek plné velikosti)

Poznámka:

Při připojování k instanci SQL Server 2005 Express Edition prostřednictvím aplikace Management Studio dialogové okno Připojit databáze neumožňuje přejít k podrobnostem o adresářích profilů uživatelů, jako jsou dokumenty. Proto nezapomeňte umístit NORTHWND.MDF soubory do NORTHWND_log.LDF adresáře profilu uživatele.

Kliknutím na tlačítko OK připojte databázi. Dialogové okno Připojit databáze se zavře a Průzkumník objektů by teď měl vypsat právě připojenou databázi. Je pravděpodobné, že databáze Northwind má název jako 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF. Přejmenujte databázi na Northwind tak, že kliknete pravým tlačítkem myši na databázi a zvolíte Přejmenovat.

Obrázek 3: Přejmenování databáze na Northwind

Krok 2: Vytvoření nového řešení a projektu SQL Serveru v sadě Visual Studio

K vytvoření spravovaných uložených procedur nebo funkcí definovaných uživatelem v SQL Serveru 2005 napíšeme uloženou proceduru a logiku definovanou uživatelem jako kód jazyka C# ve třídě. Po napsání kódu budeme muset tuto třídu zkompilovat do sestavení ( .dll soubor), zaregistrovat sestavení v databázi SQL Serveru a pak vytvořit uloženou proceduru nebo objekt UDF v databázi, která odkazuje na odpovídající metodu v sestavení. Všechny tyto kroky je možné provést ručně. Kód můžeme vytvořit v libovolném textovém editoru, zkompilovat ho z příkazového řádku pomocí kompilátoru jazyka C# (csc.exe), zaregistrovat ho v databázi pomocí CREATE ASSEMBLY příkazu nebo ze sady Management Studio a přidat uloženou proceduru nebo objekt UDF podobným způsobem. Verze Professional a Team Systems sady Visual Studio naštěstí obsahují typ projektu SQL Serveru, který tyto úlohy automatizuje. V tomto kurzu si projdeme použití typu projektu SQL Serveru k vytvoření spravované uložené procedury a uživatelem definované uživatelem.

Poznámka:

Pokud používáte Visual Web Developer nebo edici Standard sady Visual Studio, budete muset místo toho použít ruční přístup. Krok 13 obsahuje podrobné pokyny k provedení těchto kroků ručně. Doporučuji vám přečíst kroky 2 až 12 před čtením kroku 13, protože tyto kroky zahrnují důležité pokyny ke konfiguraci SQL Serveru, které je nutné použít bez ohledu na to, jakou verzi sady Visual Studio používáte.

Začněte otevřením sady Visual Studio. V nabídce Soubor zvolte Nový projekt, aby se zobrazilo dialogové okno Nový projekt (viz obrázek 4). Přejděte k podrobnostem o typu databázového projektu a pak v šablonách uvedených vpravo zvolte, jestli chcete vytvořit nový projekt SQL Serveru. Rozhodl(a) jsem se pojmenovat tento projekt ManagedDatabaseConstructs a umístil(a) ho do řešení s názvem Tutorial75.

Obrázek 4: Vytvoření nového projektu SQL Serveru (kliknutím zobrazíte obrázek s plnou velikostí)

Kliknutím na tlačítko OK v dialogovém okně Nový projekt vytvořte řešení a projekt SQL Serveru.

Projekt SQL Serveru je svázaný s konkrétní databází. Po vytvoření nového projektu SQL Serveru se proto okamžitě zobrazí výzva k zadání těchto informací. Obrázek 5 ukazuje dialogové okno Nový odkaz na databázi, které bylo vyplněno tak, aby odkazovalo na databázi Northwind, kterou jsme zaregistrovali v instanci databáze SQL Server 2005 Express Edition zpět v kroku 1.

Obrázek 5: Přidružení projektu SQL Serveru k databázi Northwind

Abychom mohli ladit spravované uložené procedury a funkce definované uživatelem, které v rámci tohoto projektu vytvoříme, musíme povolit podporu ladění SQL/CLR pro připojení. Pokaždé, když přidružuje projekt SQL Serveru k nové databázi (jak jsme to udělali na obrázku 5), Visual Studio nás vyzve, jestli chceme povolit ladění SQL/CLR na připojení (viz obrázek 6). Klepněte na tlačítko Ano.

Obrázek 6: Povolení ladění SQL/CLR

V tuto chvíli se do řešení přidal nový projekt SQL Serveru. Obsahuje složku Test Scripts s názvem soubor s názvem Test.sql, který se používá k ladění spravovaných databázových objektů vytvořených v projektu. V kroku 12 se podíváme na ladění.

Do tohoto projektu teď můžeme přidat nové spravované uložené procedury a funkce definované uživatelem, ale než začneme napřed do řešení zahrnout naši stávající webovou aplikaci. V nabídce Soubor vyberte možnost Přidat a zvolte Existující web. Přejděte do příslušné složky webu a klikněte na tlačítko OK. Jak ukazuje obrázek 7, tím se řešení aktualizuje tak, aby zahrnovalo dva projekty: web a ManagedDatabaseConstructs projekt SQL Serveru.

Obrázek 7: Průzkumník řešení teď obsahuje dva projekty

Hodnota NORTHWNDConnectionString v Web.config současné době odkazuje na NORTHWND.MDF soubor ve App_Data složce. Vzhledem k tomu, že jsme tuto databázi App_Data odebrali a explicitně zaregistrovali v instanci databáze SQL Server 2005 Express Edition, musíme odpovídajícím způsobem aktualizovat NORTHWNDConnectionString hodnotu. Web.config Otevřete soubor na webu a změňte NORTHWNDConnectionString hodnotu tak, aby připojovací řetězec četl: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. Po této změně by váš <connectionStrings> oddíl Web.config měl vypadat nějak takto:

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

Poznámka:

Jak je popsáno v předchozím kurzu, při ladění objektu SQL Serveru z klientské aplikace, jako je například ASP.NET web, musíme zakázat sdružování připojení. Výše uvedený připojovací řetězec zakáže sdružování připojení ( Pooling=false ). Pokud neplánujete ladění spravovaných uložených procedur a funkcí definovaných uživatelem z webu ASP.NET, povolte sdružování připojení.

Krok 3: Vytvoření spravované uložené procedury

Abychom mohli přidat spravovanou uloženou proceduru do databáze Northwind, musíme nejprve vytvořit uloženou proceduru jako metodu v projektu SQL Serveru. V Průzkumník řešení klikněte pravým tlačítkem myši na ManagedDatabaseConstructs název projektu a zvolte přidání nové položky. Zobrazí se dialogové okno Přidat novou položku, ve kterém jsou uvedeny typy spravovaných databázových objektů, které lze přidat do projektu. Jak ukazuje obrázek 8, patří mezi ně uložené procedury a uživatelem definované funkce.

Začněme přidáním uložené procedury, která jednoduše vrátí všechny produkty, které byly ukončeny. Pojmenujte nový soubor GetDiscontinuedProducts.csuložené procedury .

Obrázek 8: Přidání nové uložené procedury s názvem GetDiscontinuedProducts.cs (kliknutím zobrazíte obrázek s plnou velikostí)

Tím se vytvoří nový soubor třídy C#s následujícím obsahem:

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
    }
};

Všimněte si, že uložená procedura je implementována static jako metoda v souboru partial třídy s názvem StoredProcedures. Metoda je navíc GetDiscontinuedProducts zdobena metodou SqlProcedure attribute, která označuje metodu jako uloženou proceduru.

Následující kód vytvoří SqlCommand objekt a nastaví ho CommandText na SELECT dotaz, který vrátí všechny sloupce z Products tabulky pro produkty, jejichž Discontinued pole se rovná 1. Potom příkaz spustí a výsledky odešle zpět do klientské aplikace. Přidejte tento kód 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);

Všechny spravované databázové objekty mají přístup k objektuSqlContext, který představuje kontext volajícího. Poskytuje SqlContext přístup k objektu SqlPipe prostřednictvím jeho Pipe vlastnosti. Tento SqlPipe objekt slouží k převozu informací mezi databází SQL Serveru a volající aplikací. Jak název napovídá, ExecuteAndSend metoda spustí předaný SqlCommand objekt a odešle výsledky zpět do klientské aplikace.

Poznámka:

Spravované databázové objekty jsou nejvhodnější pro uložené procedury a funkce definované uživatelem, které místo logiky založené na sadě používají procedurální logiku. Procedurální logika zahrnuje práci se sadami dat na řádku po řádcích nebo práci se skalárními daty. Metoda GetDiscontinuedProducts , kterou jsme právě vytvořili, ale neobsahuje žádnou procedurální logiku. Proto by se ideálně implementovala jako uložená procedura T-SQL. Implementuje se jako spravovaná uložená procedura, která demonstruje kroky potřebné k vytvoření a nasazení spravovaných uložených procedur.

Krok 4: Nasazení spravované uložené procedury

Po dokončení tohoto kódu jsme připraveni ho nasadit do databáze Northwind. Nasazení projektu SQL Serveru zkompiluje kód do sestavení, zaregistruje sestavení v databázi a vytvoří odpovídající objekty v databázi a propojí je s příslušnými metodami v sestavení. Přesná sada úloh provedených možností Nasazení je přesněji napsaná v kroku 13. Klikněte pravým tlačítkem myši na ManagedDatabaseConstructs název projektu v Průzkumník řešení a zvolte možnost Nasadit. Nasazení se však nezdaří s následující chybou: Nesprávná syntaxe blízko "EXTERNAL". Aby bylo možné tuto funkci povolit, možná budete muset nastavit úroveň kompatibility aktuální databáze na vyšší hodnotu. Podívejte se na nápovědu k uložené proceduře sp_dbcmptlevel.

K této chybové zprávě dochází při pokusu o registraci sestavení v databázi Northwind. Aby bylo možné zaregistrovat sestavení v databázi SQL Serveru 2005, musí být úroveň kompatibility databáze nastavena na hodnotu 90. Ve výchozím nastavení mají nové databáze SQL Serveru 2005 úroveň kompatibility 90. Databáze vytvořené pomocí microsoft SQL Serveru 2000 však mají výchozí úroveň kompatibility 80. Vzhledem k tomu, že databáze Northwind byla původně databází Microsoft SQL Server 2000, její úroveň kompatibility je nyní nastavena na 80, a proto je potřeba zvýšit na 90, aby bylo možné zaregistrovat spravované databázové objekty.

Pokud chcete aktualizovat úroveň kompatibility databáze, otevřete v sadě Management Studio okno Nový dotaz a zadejte:

exec sp_dbcmptlevel 'Northwind', 90

Kliknutím na ikonu Spustit na panelu nástrojů spusťte výše uvedený dotaz.

Obrázek 9: Aktualizace úrovně kompatibility databáze Northwind (kliknutím zobrazíte obrázek s plnou velikostí)

Po aktualizaci úrovně kompatibility znovu nasaďte projekt SQL Serveru. Tentokrát by se nasazení mělo dokončit bez chyby.

Vraťte se do aplikace SQL Server Management Studio, klikněte pravým tlačítkem myši na databázi Northwind v Průzkumník objektů a zvolte Aktualizovat. Dále přejděte k podrobnostem do složky Programovatelnost a potom rozbalte složku Sestavení. Jak ukazuje obrázek 10, databáze Northwind teď obsahuje sestavení vygenerované projektem ManagedDatabaseConstructs .

Obrázek 10: Sestavení ManagedDatabaseConstructs je nyní registrováno v databázi Northwind

Rozbalte také složku Uložené procedury. Tam uvidíte uloženou proceduru s názvem GetDiscontinuedProducts. Tato uložená procedura byla vytvořena procesem nasazení a odkazuje na metodu GetDiscontinuedProducts ManagedDatabaseConstructs v sestavení. Když je uložená GetDiscontinuedProducts procedura spuštěna, pak provede metodu GetDiscontinuedProducts . Vzhledem k tomu, že se jedná o spravovanou uloženou proceduru, nelze ji upravovat prostřednictvím sady Management Studio (proto ikona zámku vedle názvu uložené procedury).

Obrázek 11: Uložená procedura GetDiscontinuedProducts je uvedená ve složce Uložené procedury

Ještě existuje ještě jedna překážka, které musíme překonat, než můžeme volat spravovanou uloženou proceduru: databáze je nakonfigurovaná tak, aby zabránila spuštění spravovaného kódu. Ověřte to tak, že otevřete nové okno dotazu a spustíte uloženou proceduru GetDiscontinuedProducts . Zobrazí se následující chybová zpráva: Spuštění uživatelského kódu v rozhraní .NET Framework je zakázáno. Povolte možnost konfigurace s podporou clr.

Pokud chcete prozkoumat informace o konfiguraci databáze Northwind, zadejte a spusťte příkaz exec sp_configure v okně dotazu. To ukazuje, že nastavení s povolenou funkcí clr je aktuálně nastaveno na hodnotu 0.

Obrázek 12: Nastavení s povolenou funkcí clr je aktuálně nastaveno na 0 (kliknutím zobrazíte obrázek s plnou velikostí)

Všimněte si, že každé nastavení konfigurace na obrázku 12 má uvedené čtyři hodnoty: minimální a maximální hodnoty a hodnoty konfigurace a spuštění. Pokud chcete aktualizovat konfigurační hodnotu pro nastavení s povoleným clr, spusťte následující příkaz:

exec sp_configure 'clr enabled', 1

Pokud znovu spustíte exec sp_configure , uvidíte, že výše uvedený příkaz aktualizoval hodnotu konfigurace nastavení clr s povolenou hodnotou 1, ale že hodnota spuštění je stále nastavená na 0. Aby tato změna konfigurace ovlivnila, musíme spustit RECONFIGURE příkaz, který nastaví hodnotu spuštění na aktuální hodnotu konfigurace. Jednoduše zadejte RECONFIGURE do okna dotazu a klikněte na ikonu Spustit na panelu nástrojů. Pokud teď spustíte exec sp_configure , měla by se zobrazit hodnota 1 pro konfiguraci nastavení clr s povolenou konfigurací a spuštěním hodnot.

Po dokončení konfigurace s podporou clr jsme připraveni spustit spravovanou GetDiscontinuedProducts uloženou proceduru. V okně dotazu zadejte a spusťte příkaz exec GetDiscontinuedProducts. Vyvolání uložené procedury způsobí spuštění odpovídajícího spravovaného GetDiscontinuedProducts kódu v metodě. Tento kód vydá dotaz, SELECT který vrátí všechny produkty, které jsou ukončeny, a vrátí tato data do volající aplikace, což je SQL Server Management Studio v této instanci. Management Studio tyto výsledky obdrží a zobrazí je v okně Výsledky.

Obrázek 13: Uložená procedura GetDiscontinuedProducts vrátí všechny ukončené produkty (kliknutím zobrazíte obrázek v plné velikosti).

Krok 5: Vytvoření spravovaných uložených procedur, které přijímají vstupní parametry

Mnoho dotazů a uložených procedur, které jsme vytvořili v těchto kurzech, používaly parametry. Například v kurzu Vytváření nových uložených procedur pro Typed DataSet s TableAdapter jsme vytvořili uloženou proceduru s názvem GetProductsByCategoryID , která přijala vstupní parametr s názvem @CategoryID. Uložená procedura pak vrátila všechny produkty, jejichž CategoryID pole odpovídalo hodnotě zadaného @CategoryID parametru.

Chcete-li vytvořit spravovanou uloženou proceduru, která přijímá vstupní parametry, jednoduše zadejte tyto parametry v definici metody. Abychom to mohli ilustrovat, pojďme do projektu s názvem GetProductsWithPriceLessThanpřidat další spravovanou uloženou proceduru ManagedDatabaseConstructs . Tato spravovaná uložená procedura přijme vstupní parametr určující cenu a vrátí všechny produkty, jejichž UnitPrice pole je menší než hodnota parametru.

Pokud chcete do projektu přidat novou uloženou proceduru, klikněte pravým tlačítkem myši na ManagedDatabaseConstructs název projektu a zvolte přidání nové uložené procedury. Pojmenujte soubor GetProductsWithPriceLessThan.cs. Jak jsme viděli v kroku 3, vytvoří se nový soubor třídy C# s metodou pojmenovanou GetProductsWithPriceLessThan v rámci partial třídy StoredProcedures.

GetProductsWithPriceLessThan Aktualizujte definici metody tak, aby přijímala SqlMoney vstupní parametr s názvem price a napsal kód pro spuštění a vrácení výsledků dotazu:

[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);
}

Definice GetProductsWithPriceLessThan metody a kód se podobají definici a kódu GetDiscontinuedProducts metody vytvořené v kroku 3. Jedinými rozdíly jsou, že GetProductsWithPriceLessThan metoda přijímá jako vstupní parametr (price), SqlCommand dotaz s obsahuje parametr (@MaxPrice) a parametr je přidán do SqlCommand Parameters kolekce je a přiřazena hodnota price proměnné.

Po přidání tohoto kódu znovu nasaďte projekt SQL Serveru. Potom se vraťte do aplikace SQL Server Management Studio a aktualizujte složku Uložené procedury. Měla by se zobrazit nová položka. GetProductsWithPriceLessThan V okně dotazu zadejte a spusťte příkaz exec GetProductsWithPriceLessThan 25, který zobrazí seznam všech produktů menší než 25 USD, jak ukazuje obrázek 14.

Obrázek 14: Zobrazí se produkty pod $25 (kliknutím zobrazíte obrázek s plnou velikostí)

Krok 6: Volání spravované uložené procedury z vrstvy přístupu k datům

V tomto okamžiku jsme do ManagedDatabaseConstructs projektu přidali GetDiscontinuedProducts a GetProductsWithPriceLessThan spravovali uložené procedury a zaregistrovali je v databázi Northwind SQL Serveru. Tyto spravované uložené procedury jsme také vyvolali ze sady SQL Server Management Studio (viz obrázek 13 a 14). Abychom mohli naše ASP.NET aplikace používat tyto spravované uložené procedury, musíme je ale přidat do vrstev přístupu k datům a obchodní logiky v architektuře. V tomto kroku přidáme dvě nové metody do ProductsTableAdapter NorthwindWithSprocs typové datové sady, která byla původně vytvořena v vytváření nových uložených procedur pro kurz TableAdapter typed DataSet s. V kroku 7 přidáme odpovídající metody do BLL.

NorthwindWithSprocs Otevřete typovou datovou sadu v sadě Visual Studio a začněte přidáním nové metody do pojmenované ProductsTableAdapter GetDiscontinuedProductssady . Chcete-li přidat novou metodu do objektu TableAdapter, klikněte pravým tlačítkem myši na název TableAdapter v Návrháři a v místní nabídce zvolte možnost Přidat dotaz.

Poznámka:

Vzhledem k tomu, že jsme přesunuli databázi Northwind ze App_Data složky do instance databáze SQL Server 2005 Express Edition, je nezbytné, aby odpovídající připojovací řetězec v souboru Web.config byly aktualizovány tak, aby odrážely tuto změnu. V kroku 2 jsme probrali aktualizaci NORTHWNDConnectionString hodnoty v Web.configsouboru . Pokud jste zapomněli provést tuto aktualizaci, zobrazí se chybová zpráva, že se nepovedlo přidat dotaz. Při pokusu o přidání nové metody do objektu TableAdapter nelze najít připojení NORTHWNDConnectionString pro objekt Web.config v dialogovém okně. Chcete-li tuto chybu vyřešit, klepněte na tlačítko OK a potom přejděte na Web.config a aktualizujte NORTHWNDConnectionString hodnotu, jak je popsáno v kroku 2. Pak zkuste metodu znovu přidat do TableAdapter. Tentokrát by měl fungovat bez chyby.

Přidání nové metody spustí Průvodce konfigurací dotazu TableAdapter, který jsme použili mnohokrát v předchozích kurzech. První krok nás požádá, abychom určili, jak má Objekt TableAdapter přistupovat k databázi: prostřednictvím příkazu AD hoc SQL nebo prostřednictvím nové nebo existující uložené procedury. Vzhledem k tomu, že jsme již vytvořili a zaregistrovali spravovanou uloženou proceduru GetDiscontinuedProducts v databázi, zvolte možnost Použít existující uloženou proceduru a stiskněte tlačítko Další.

Obrázek 15: Volba možnosti Použít existující uloženou proceduru (Kliknutím zobrazíte obrázek v plné velikosti)

Další obrazovka nás vyzve k zadání uložené procedury, která metoda vyvolá. V rozevíracím seznamu zvolte spravovanou uloženou GetDiscontinuedProducts proceduru a stiskněte Další.

Obrázek 16: Výběr GetDiscontinuedProducts spravované uložené procedury (kliknutím zobrazíte obrázek s plnou velikostí)

Pak se zobrazí výzva, abychom určili, jestli uložená procedura vrací řádky, jednu hodnotu nebo nic. Vzhledem k tomu GetDiscontinuedProducts , že vrátí sadu ukončených řádků produktu, zvolte první možnost (tabulková data) a klikněte na Tlačítko Další.

Obrázek 17: Výběr možnosti tabulkových dat (kliknutím zobrazíte obrázek v plné velikosti)

Poslední obrazovka průvodce nám umožňuje určit použité vzory přístupu k datům a názvy výsledných metod. Nechte zaškrtnutá políčka a pojmenujte metody FillByDiscontinued a GetDiscontinuedProducts. Dokončete průvodce kliknutím na Dokončit.

Obrázek 18: Pojmenování metod FillByDiscontinued a GetDiscontinuedProducts (kliknutím zobrazíte obrázek s plnou velikostí)

Opakováním těchto kroků vytvořte metody pojmenované FillByPriceLessThan a GetProductsWithPriceLessThan v ProductsTableAdapter spravované uložené proceduře GetProductsWithPriceLessThan .

Obrázek 19 znázorňuje snímek obrazovky Návrháře datové sady po přidání metod do ProductsTableAdapter uložených procedur a GetProductsWithPriceLessThan spravovaných GetDiscontinuedProducts uložených procedur.

Obrázek 19: Zahrnutí ProductsTableAdapter nových metod přidaných v tomto kroku (kliknutím zobrazíte obrázek s plnou velikostí)

Krok 7: Přidání odpovídajících metod do vrstvy obchodní logiky

Teď, když jsme aktualizovali vrstvu přístupu k datům tak, aby zahrnovala metody pro volání spravovaných uložených procedur přidaných v krocích 4 a 5, musíme do vrstvy obchodní logiky přidat odpovídající metody. Do třídy přidejte následující dvě 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);
}

Obě metody jednoduše volají odpovídající metodu ProductsDataTable DAL a vrátí instanci. Revize DataObjectMethodAttribute nad jednotlivými metodami způsobí, že tyto metody budou zahrnuty do rozevíracího seznamu na kartě SELECT v Průvodci konfigurací zdroje dat ObjectDataSource.

Krok 8: Vyvolání spravovaných uložených procedur z prezentační vrstvy

S rozšířenou obchodní logikou a vrstvami přístupu k datům tak, aby zahrnovaly podporu volání GetDiscontinuedProducts a GetProductsWithPriceLessThan spravovaných uložených procedur, teď můžeme tyto uložené procedury zobrazit prostřednictvím ASP.NET stránky.

ManagedFunctionsAndSprocs.aspx Otevřete stránku ve AdvancedDAL složce a z panelu nástrojů přetáhněte objekt GridView do Návrháře. Nastavte Vlastnost GridView ID na DiscontinuedProducts a, z její inteligentní značky, vytvořit vazbu na nový ObjectDataSource pojmenovaný DiscontinuedProductsDataSource. Nakonfigurujte ObjectDataSource tak, aby načítá data z ProductsBLLWithSprocs metody třídy s GetDiscontinuedProducts .

Obrázek 20: Konfigurace ObjectDataSource pro použití ProductsBLLWithSprocs třídy (kliknutím zobrazíte obrázek s plnou velikostí)

Obrázek 21: Volba GetDiscontinuedProducts metody z rozevíracího seznamu na kartě SELECT (kliknutím zobrazíte obrázek s plnou velikostí)

Vzhledem k tomu, že se tato mřížka bude používat pouze k zobrazení informací o produktu, nastavte rozevírací seznamy na kartách UPDATE, INSERT a DELETE na (Žádné) a potom klikněte na Tlačítko Dokončit.

Po dokončení průvodce visual Studio automaticky přidá BoundField nebo CheckBoxField pro každé datové pole v souboru ProductsDataTable. Než odeberete všechna tato pole s výjimkou ProductName polí a Discontinuedv tomto okamžiku by deklarativní kód GridView a ObjectDataSource měly vypadat podobně jako následující:

<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>

Chvilku si tuto stránku prohlédněte v prohlížeči. Při návštěvě stránky ObjectDataSource volá metodu ProductsBLLWithSprocs třídy s GetDiscontinuedProducts . Jak jsme viděli v kroku 7, tato metoda volá metodu TŘÍDY DAL s ProductsDataTable GetDiscontinuedProducts , která vyvolá uloženou proceduru GetDiscontinuedProducts . Tato uložená procedura je spravovaná uložená procedura a spustí kód, který jsme vytvořili v kroku 3 a vrací ukončené produkty.

Výsledky vrácené spravovanou uloženou procedurou se zabalí do ProductsDataTable dal a pak se vrátí do BLL, který je pak vrátí do prezentační vrstvy, kde jsou svázané s GridView a zobrazeny. Podle očekávání obsahuje mřížka seznam produktů, které byly ukončeny.

Obrázek 22: V seznamu jsou uvedeny ukončené produkty (kliknutím zobrazíte obrázek s plnou velikostí)

Pro další praxi přidejte na stránku TextBox a další Objekt GridView. Mít tento GridView zobrazit produkty menší než množství zadané do TextBox voláním ProductsBLLWithSprocs třídy GetProductsWithPriceLessThan metody.

Krok 9: Vytvoření a volání UDF T-SQL

Uživatelem definované funkce neboli funkce definované uživatelem jsou databázové objekty, které úzce napodobují sémantiku funkcí v programovacích jazycích. Stejně jako funkce v jazyce C# můžou funkce UDF obsahovat proměnný počet vstupních parametrů a vrátit hodnotu určitého typu. Funkce definovaná uživatelem může vrátit skalární data – řetězec, celé číslo atd. nebo tabulková data. Pojďme se rychle podívat na oba typy funkcí definovaných uživatelem, počínaje uživatelem definovaným uživatelem, který vrací skalární datový typ.

Následující funkce definovaná uživatelem vypočítá odhadovanou hodnotu inventáře konkrétního produktu. Provádí to tak, že vezme tři vstupní parametry - UnitPrice, UnitsInStocka Discontinued hodnoty pro určitý produkt - a vrátí hodnotu typu money. Vypočítá odhadovanou hodnotu inventáře vynásobením UnitPrice UnitsInStockhodnoty hodnotou . U ukončených položek je tato hodnota halvována.

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

Jakmile se tato funkce definovaná uživatelem přidá do databáze, najdete ji prostřednictvím nástroje Management Studio rozbalením složky Programovatelnost, funkcemi a skalárními hodnotami. Dá se použít v SELECT dotazu takto:

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

Přidal(a) jsem uživatelem definovanou uživatele udf_ComputeInventoryValue do databáze Northwind; Obrázek 23 znázorňuje výstup výše uvedeného SELECT dotazu při prohlížení přes Management Studio. Všimněte si také, že funkce definované uživatelem jsou uvedené ve složce Funkce skalární hodnoty v Průzkumník objektů.

Obrázek 23: Zobrazí se hodnoty inventáře jednotlivých produktů (kliknutím zobrazíte obrázek s plnou velikostí).

Funkce definované uživatelem můžou také vracet tabulková data. Můžeme například vytvořit uživatelem definovanou uživatelem, který vrací produkty, které patří do konkrétní kategorie:

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
)

Funkce udf_GetProductsByCategoryID definovaná uživatelem @CategoryID přijímá vstupní parametr a vrací výsledky zadaného SELECT dotazu. Po vytvoření se na tuto funkci definovanou SELECT uživatelem dá odkazovat v FROM klauzuli (neboJOIN) dotazu. Následující příklad by vrátil ProductIDhodnotu , ProductNamea CategoryID hodnoty pro každý z nápojů.

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

Přidal(a) jsem uživatelem definovanou uživatele udf_GetProductsByCategoryID do databáze Northwind; Obrázek 24 znázorňuje výstup výše uvedeného SELECT dotazu při prohlížení přes Management Studio. Funkce definované uživatelem, které vracejí tabulková data, najdete ve složce Funkce tabulky Průzkumník objektů.

Obrázek 24: Položka ProductID, ProductNamea CategoryID jsou uvedeny pro každý nápoj (Kliknutím zobrazíte obrázek plné velikosti)

Poznámka:

Další informace o vytváření a používání funkcí definovaných uživatelem najdete v úvodu k uživatelem definovaným funkcím. Podívejte se také na výhody a nevýhody uživatelem definovaných funkcí.

Krok 10: Vytvoření spravovaného definovaného uživatelem

udf_GetProductsByCategoryID Uživatelem udf_ComputeInventoryValue vytvořené v předchozích příkladech jsou databázové objekty T-SQL. SQL Server 2005 také podporuje spravované funkce definované uživatelem, které je možné přidat do ManagedDatabaseConstructs projektu stejně jako spravované uložené procedury z kroků 3 a 5. V tomto kroku implementujme definovanou uživatelem ve spravovaném udf_ComputeInventoryValue kódu.

Pokud chcete do ManagedDatabaseConstructs projektu přidat spravovanou uživatelem definovanou uživatelem, klikněte pravým tlačítkem myši na název projektu v Průzkumník řešení a zvolte Přidat novou položku. V dialogovém okně Přidat novou položku vyberte šablonu definovanou uživatelem a pojmenujte nový soubor udf_ComputeInventoryValue_Managed.csUDF .

Obrázek 25: Přidání nové spravované funkce definované uživatelem ManagedDatabaseConstructs do projektu (kliknutím zobrazíte obrázek s plnou velikostí)

Šablona uživatelem definované funkce vytvoří partial třídu pojmenovanou UserDefinedFunctions metodou, jejíž název je stejný jako název souboru třídy (udf_ComputeInventoryValue_Managedv této instanci). Tato metoda je zdobena atributemSqlFunction, který označí metodu jako spravovanou uživatelem definovanou uživatelem.

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 v současné době vrací SqlString objekt a nepřijímá žádné vstupní parametry. Potřebujeme aktualizovat definici metody tak, aby přijímala tři vstupní parametry - UnitPriceUnitsInStock, a Discontinued - a vrací SqlMoney objekt. Logika pro výpočet hodnoty inventáře je stejná jako v 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;
}

Všimněte si, že vstupní parametry metody UDF mají odpovídající typy SQL: SqlMoney pro UnitPrice pole, SqlInt16 pro UnitsInStocka SqlBoolean pro Discontinued. Tyto datové typy odrážejí typy definované v Products tabulce: UnitPrice sloupec je typu money, UnitsInStock sloupec typu smallinta Discontinued sloupec typu bit.

Kód začíná vytvořením SqlMoney instance s názvem inventoryValue , která má přiřazenou hodnotu 0. Tabulka Products umožňuje hodnoty databáze NULL ve UnitsInPrice sloupcích a UnitsInStock sloupcích. Proto musíme nejprve zkontrolovat, jestli tyto hodnoty obsahují NULL hodnoty, které provádíme prostřednictvím vlastnosti objektu SqlMoney IsNull. Pokud obě UnitPrice hodnoty obsahují UnitsInStock jinéNULL než hodnoty, vypočítáme inventoryValue , aby to byl součin těchto dvou hodnot. Pokud Discontinued je to pravda, pak hodnotu na polovinu snížíme.

Poznámka:

Objekt SqlMoney umožňuje násobit pouze dvě SqlMoney instance. Nepovoluje SqlMoney vynásobení instance číslem s plovoucí desetinnou čárkou literálu. Proto ji vynásobíme inventoryValue novou SqlMoney instancí, která má hodnotu 0,5.

Krok 11: Nasazení spravovaného definovaného uživatelem

Teď, když je spravovaná definovaná funkce definovaná uživatelem vytvořená, jsme připraveni ji nasadit do databáze Northwind. Jak jsme viděli v kroku 4, spravované objekty v projektu SQL Serveru se nasadí tak, že kliknete pravým tlačítkem na název projektu v Průzkumník řešení a v místní nabídce zvolíte možnost Nasadit.

Po nasazení projektu se vraťte do aplikace SQL Server Management Studio a aktualizujte složku Skalar-valued Functions. Teď byste měli vidět dvě položky:

• dbo.udf_ComputeInventoryValue – definovaná uživatelem T-SQL vytvořená v kroku 9 a
• dbo.udf ComputeInventoryValue_Managed – spravovaná funkce definovaná uživatelem vytvořená v kroku 10, který byl právě nasazen.

Pokud chcete otestovat tuto spravovanou definovanou uživatelem, spusťte v nástroji Management Studio následující dotaz:

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

Tento příkaz místo funkce T-SQL udf_ComputeInventoryValue UDF používá spravovanou udf ComputeInventoryValue_Managed funkci definovanou uživatelem, ale výstup je stejný. Vraťte se na obrázek 23 a podívejte se na snímek obrazovky s výstupem UDF.

Krok 12: Ladění spravovaných databázových objektů

V kurzu Ladění uložených procedur jsme probrali tři možnosti ladění SQL Serveru prostřednictvím sady Visual Studio: Přímé ladění databáze, ladění aplikací a ladění z projektu SQL Serveru. Spravované databázové objekty nelze ladit prostřednictvím ladění přímé databáze, ale lze je ladit z klientské aplikace a přímo z projektu SQL Serveru. Aby však ladění fungovalo, musí databáze SQL Serveru 2005 povolit ladění SQL/CLR. Vzpomeňte si, že když jsme poprvé vytvořili projekt, ManagedDatabaseConstructs sada Visual Studio nás požádala, jestli jsme chtěli povolit ladění SQL/CLR (viz obrázek 6 v kroku 2). Toto nastavení lze změnit tak, že kliknete pravým tlačítkem myši na databázi z okna Průzkumník serveru.

Obrázek 26: Ujistěte se, že databáze umožňuje ladění SQL/CLR

Představte si, že chceme ladit spravovanou uloženou proceduru GetProductsWithPriceLessThan . Začneme nastavením zarážky v kódu GetProductsWithPriceLessThan metody.

Obrázek 27: Nastavení zarážky v GetProductsWithPriceLessThan metodě (kliknutím zobrazíte obrázek s plnou velikostí)

Pojďme se nejprve podívat na ladění spravovaných databázových objektů z projektu SQL Serveru. Vzhledem k tomu, že naše řešení obsahuje dva projekty – ManagedDatabaseConstructs projekt SQL Serveru společně s naším webem – abychom mohli ladit projekt SQL Serveru, musíme visual Studio dát sadě Visual Studio pokyn, aby ManagedDatabaseConstructs při spuštění ladění spustil projekt SQL Serveru. Klikněte pravým tlačítkem myši na ManagedDatabaseConstructs projekt v Průzkumník řešení a v místní nabídce zvolte možnost Nastavit jako spustit projekt.

ManagedDatabaseConstructs Při spuštění projektu z ladicího programu spustí příkazy SQL v Test.sql souboru, který se nachází ve Test Scripts složce. Pokud chcete například otestovat spravovanou uloženou proceduru GetProductsWithPriceLessThan , nahraďte existující Test.sql obsah souboru následujícím příkazem, který vyvolá spravovanou uloženou proceduru, která předává GetProductsWithPriceLessThan @CategoryID hodnotu 14,95:

exec GetProductsWithPriceLessThan 14.95

Jakmile zadáte výše uvedený skript do Test.sql, spusťte ladění tak, že přejdete do nabídky Ladění a zvolíte Spustit ladění, nebo stisknutím klávesy F5 nebo zelené ikony přehrávání na panelu nástrojů. Tím se sestaví projekty v rámci řešení, nasadí spravované databázové objekty do databáze Northwind a pak skript spustí Test.sql . V tomto okamžiku se zarážka dosáhne a můžeme procházet metodou GetProductsWithPriceLessThan , prozkoumat hodnoty vstupních parametrů atd.

Obrázek 28: Zarážka v GetProductsWithPriceLessThan metodě byla nalezena (kliknutím zobrazíte obrázek plné velikosti)

Aby bylo možné objekt databáze SQL ladit prostřednictvím klientské aplikace, je nezbytné, aby byla databáze nakonfigurovaná tak, aby podporovala ladění aplikací. Klikněte pravým tlačítkem myši na databázi v Průzkumníku serveru a ujistěte se, že je zaškrtnutá možnost Ladění aplikace. Kromě toho musíme nakonfigurovat ASP.NET aplikaci tak, aby se integrovali s ladicím programem SQL a zakázali sdružování připojení. Tyto kroky byly podrobně popsány v kroku 2 kurzu Ladění uložených procedur .

Jakmile nakonfigurujete ASP.NET aplikaci a databázi, nastavte web ASP.NET jako spouštěný projekt a spusťte ladění. Pokud navštívíte stránku, která volá jeden ze spravovaných objektů, které mají zarážku, aplikace se zastaví a řízení se přepne do ladicího programu, kde můžete procházet kód, jak je znázorněno na obrázku 28.

Krok 13: Ruční kompilace a nasazení spravovaných databázových objektů

Projekty SQL Serveru usnadňují vytváření, kompilaci a nasazování spravovaných databázových objektů. Projekty SQL Serveru jsou bohužel k dispozici pouze v edicích Professional a Team Systems sady Visual Studio. Pokud používáte Visual Web Developer nebo edice Standard sady Visual Studio a chcete používat spravované databázové objekty, budete je muset vytvořit a nasadit ručně. To zahrnuje čtyři kroky:

1. Vytvořte soubor, který obsahuje zdrojový kód spravovaného databázového objektu.
2. Zkompilujte objekt do sestavení,
3. Zaregistrujte sestavení v databázi SQL Serveru 2005 a
4. Vytvořte databázový objekt v SQL Serveru, který odkazuje na příslušnou metodu v sestavení.

Pro ilustraci těchto úloh vytvoříme novou spravovanou uloženou proceduru, která vrátí ty produkty, jejichž UnitPrice hodnota je větší než zadaná hodnota. Ve svém počítači vytvořte nový soubor s názvem GetProductsWithPriceGreaterThan.cs a do souboru zadejte následující kód (k tomu můžete použít Visual Studio, Poznámkový blok nebo libovolný textový editor):

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);
    }
};

Tento kód je téměř stejný jako GetProductsWithPriceLessThan kód metody vytvořené v kroku 5. Jedinými rozdíly jsou názvy metod, WHERE klauzule a název parametru použitý v dotazu. Zpět v GetProductsWithPriceLessThan metodě, klauzule WHERE přečte: WHERE UnitPrice < @MaxPrice. Zde, v GetProductsWithPriceGreaterThan, používáme: WHERE UnitPrice > @MinPrice .

Teď musíme tuto třídu zkompilovat do sestavení. Z příkazového řádku přejděte do adresáře, do kterého jste soubor uložili GetProductsWithPriceGreaterThan.cs , a pomocí kompilátoru jazyka C# (csc.exe) zkompilujte soubor třídy do sestavení:

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

Pokud složka, která csc.exe není v systému s PATH, budete muset plně odkazovat na jeho cestu, %WINDOWS%\Microsoft.NET\Framework\version\například takto:

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

Obrázek 29: Kompilace GetProductsWithPriceGreaterThan.cs do sestavení (kliknutím zobrazíte obrázek v plné velikosti)

Příznak /t určuje, že soubor třídy jazyka C# by se měl zkompilovat do knihovny DLL (místo spustitelného souboru). Příznak /out určuje název výsledného sestavení.

Poznámka:

Místo kompilace GetProductsWithPriceGreaterThan.cs souboru třídy z příkazového řádku můžete alternativně použít Visual C# Express Edition nebo vytvořit samostatný projekt knihovny tříd v sadě Visual Studio edice Standard. S ren Jacob Lauritsen laskavě poskytl takový projekt Visual C# Express Edition s kódem pro GetProductsWithPriceGreaterThan uloženou proceduru a dvě spravované uložené procedury a UDF vytvořené v krocích 3, 5 a 10. Projekt S ren obsahuje také příkazy T-SQL potřebné k přidání odpovídajících databázových objektů.

S kódem zkompilovaným do sestavení jsme připraveni zaregistrovat sestavení v databázi SQL Serveru 2005. To lze provést prostřednictvím T-SQL, pomocí příkazu CREATE ASSEMBLY, nebo prostřednictvím aplikace SQL Server Management Studio. Pojďme se zaměřit na používání sady Management Studio.

V sadě Management Studio rozbalte složku Programovatelnost v databázi Northwind. Jednou z jejích podsložek je sestavení. Chcete-li do databáze ručně přidat nové sestavení, klikněte pravým tlačítkem myši na složku Sestavení a v místní nabídce zvolte Nové sestavení. Zobrazí se dialogové okno Nové sestavení (viz obrázek 30). Klikněte na tlačítko Procházet, vyberte ManuallyCreatedDBObjects.dll sestavení, které jsme právě zkompilovali, a potom kliknutím na tlačítko OK přidejte sestavení do databáze. V Průzkumník objektů byste neměli vidět ManuallyCreatedDBObjects.dll sestavení.

Obrázek 30: Přidání ManuallyCreatedDBObjects.dll sestavení do databáze (kliknutím zobrazíte obrázek s plnou velikostí)

Obrázek 31: Je ManuallyCreatedDBObjects.dll uveden v Průzkumník objektů

Zatímco jsme přidali sestavení do databáze Northwind, ještě musíme přidružit uloženou proceduru GetProductsWithPriceGreaterThan k metodě v sestavení. Chcete-li toho dosáhnout, otevřete nové okno dotazu a spusťte následující skript:

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

Tím se vytvoří nová uložená procedura v databázi Northwind s názvem GetProductsWithPriceGreaterThan a přidruží ji ke spravované metodě GetProductsWithPriceGreaterThan (která je ve třídě StoredProcedures, která je v sestavení ManuallyCreatedDBObjects).

Po spuštění výše uvedeného skriptu aktualizujte složku Uložené procedury v Průzkumník objektů. Měla by se zobrazit nová položka uložené procedury – GetProductsWithPriceGreaterThan která má vedle ní ikonu zámku. Pokud chcete otestovat tuto uloženou proceduru, zadejte a spusťte v okně dotazu následující skript:

exec GetProductsWithPriceGreaterThan 24.95

Jak ukazuje obrázek 32, výše uvedený příkaz zobrazí informace pro tyto produkty s UnitPrice vyšší než 24,95 USD.

Obrázek 32: Obrázek ManuallyCreatedDBObjects.dll je uvedený v Průzkumník objektů (kliknutím zobrazíte obrázek s plnou velikostí).

Shrnutí

Microsoft SQL Server 2005 poskytuje integraci s modulem CLR (Common Language Runtime), který umožňuje vytváření databázových objektů pomocí spravovaného kódu. Dříve se tyto databázové objekty daly vytvořit pouze pomocí T-SQL, ale teď je můžeme vytvořit pomocí programovacích jazyků .NET, jako je C#. V tomto kurzu jsme vytvořili dvě spravované uložené procedury a spravovanou uživatelem definovanou funkci.

Typ projektu SQL Serveru sady Visual Studio usnadňuje vytváření, kompilaci a nasazování spravovaných databázových objektů. Kromě toho nabízí bohatou podporu ladění. Typy projektů SQL Serveru jsou ale k dispozici pouze v edicích Professional a Team Systems sady Visual Studio. Pro ty, kteří používají Visual Web Developer nebo edice Standard sady Visual Studio, je potřeba provést kroky vytvoření, kompilace a nasazení ručně, jak jsme viděli v kroku 13.

Šťastné programování!

Další čtení

Další informace o tématech probíraných v tomto kurzu najdete v následujících zdrojích informací:

• Výhody a nevýhody uživatelem definovaných funkcí
• Vytváření objektů SQL Serveru 2005 ve spravovaném kódu
• Postupy: Vytvoření a spuštění uložené procedury CLR SQL Serveru
• Postupy: Vytvoření a spuštění uživatelem definované funkce CLR SQL Serveru
• Postupy: Úprava Test.sql skriptu pro spouštění objektů SQL
• Úvod do uživatelem definovaných funkcí
• Spravovaný kód a SQL Server 2005 (video)
• Referenční informace k jazyku Transact-SQL
• Návod: Vytvoření uložené procedury ve spravovaném kódu

O autorovi

Scott Mitchell, autor sedmi knih ASP/ASP.NET a zakladatel 4GuysFromRolla.com, pracuje s webovými technologiemi Microsoftu od roku 1998. Scott pracuje jako nezávislý konzultant, trenér a spisovatel. Jeho nejnovější kniha je Sams Teach Yourself ASP.NET 2.0 za 24 hodin. Je dostupný na mitchell@4GuysFromRolla.comadrese . nebo prostřednictvím svého blogu, který lze najít na http://ScottOnWriting.NET.

Zvláštní díky

Tato série kurzů byla zkontrolována mnoha užitečnými recenzenty. Vedoucí recenzent pro tento kurz byl S ren Jacob Lauritsen. Kromě kontroly tohoto článku vytvořil S ren také projekt Visual C# Express Edition, který je součástí tohoto článku ke stažení pro ruční kompilaci spravovaných databázových objektů. Chcete si projít nadcházející články MSDN? Pokud ano, zahoďte mi řádek na mitchell@4GuysFromRolla.com.

PředchozíDalší