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 a umožňuje vývojářům 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 je Microsoft s 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ů obsahuje konstruktory pro seskupování řady příkazů SQL, které lze pak spustit jako jednu opakovaně použitelnou jednotku. Jedním z příkladů jsou uložené procedury. Další jsou uživatelem definované funkce (UDF), konstruktor, který podrobněji prozkoumáme v kroku 9.

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

Před verzí Microsoft SQL Server 2005 bylo možné uložené procedury a uživatelem definované funkce 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. V důsledku toho lze uložené procedury a funkce definované uživatelem v databázi SQL Server 2005 vytvořit pomocí spravovaného kódu. To znamená, že můžete vytvořit uloženou proceduru nebo definovanou uživatelem jako metodu ve třídě Jazyka Visual Basic. To umožňuje těmto uloženým procedurám a funkcím UDF využívat funkce v rozhraní .NET Framework a z vašich vlastních tříd.

V tomto kurzu prozkoumáme, jak vytvořit spravované uložené procedury a User-Defined Functions a jak je integrovat do 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é bohatství 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í velkou procedurální logiku. Podrobnější diskuzi o výhodách použití spravovaného kódu oproti T-SQL najdete v tématu Výhody použití spravovaného kódu k vytváření databázových objektů.

Krok 1: Přesunutí databáze Northwind z App_Data

Všechny naše kurzy dosud používaly soubor databáze Microsoft SQL Server 2005 Express Edition ve složce s webové aplikaceApp_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.

Pro účely tohoto kurzu však přesuňme databázi Northwind mimo App_Data databázi a explicitně ji zaregistrujte v instanci SQL Server 2005 Express Edition databáze. I když můžeme kroky pro tento kurz provést 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.

Soubor ke 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 vlastní implementaci kurzů, zavřete Visual Studio a přesuňte NORTHWND.MDF soubory a NORTHWND_log.LDF ze složky webu App_Data do složky mimo web. Jakmile se soubory databáze přesunou do jiné složky, musíme databázi Northwind zaregistrovat v instanci databáze SQL Server 2005 Express Edition. Můžete to udělat z SQL Server Management Studio. Pokud máte v počítači nainstalovanou edici 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 si 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í se v okně Průzkumník objektů zobrazí seznam informací o instanci SQL Server 2005 Express Edition databáze, včetně jejích 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 přesunuli) k instanci SQL Server 2005 Express Edition databáze. 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 příslušnému NORTHWND.MDF souboru a klikněte na 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 v plné velikosti)

Poznámka

Při připojování k instanci SQL Server 2005 Express Edition přes Management Studio vám dialogové okno Připojit databáze neumožňuje přejít k podrobnostem do adresářů profilů uživatelů, jako jsou dokumenty. Proto nezapomeňte soubory a NORTHWND_log.LDF umístit NORTHWND.MDF do 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 obsahovat seznam právě připojené databáze. 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 na ni kliknete pravým tlačítkem a zvolíte Přejmenovat.

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

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

Pokud chcete vytvořit spravované uložené procedury nebo funkce definované uživatelem v SQL Server 2005, zapíšeme uloženou proceduru a logiku UDF jako kód Jazyka Visual Basic ve třídě. Po napsání kódu budeme muset tuto třídu zkompilovat do sestavení (.dllsouboru), zaregistrovat sestavení v databázi SQL Server 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 lze 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 Visual Basic (vbc.exe), zaregistrovat ho v databázi pomocí CREATE ASSEMBLY příkazu nebo z nástroje Management Studio a podobným způsobem přidat uloženou proceduru nebo objekt UDF. Verze Professional a Team Systems sady Visual Studio naštěstí obsahují typ projektu SQL Server, který tyto úlohy automatizuje. V tomto kurzu si projdeme použití typu projektu SQL Server 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 pro ruční provedení těchto kroků. Doporučujeme, abyste si před čtením kroku 13 přečetli kroky 2 až 12, protože tyto kroky obsahují důležité SQL Server pokyny ke konfiguraci, 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 k typu projektu Databáze a pak v části Šablony uvedené vpravo zvolte vytvořit nový SQL Server projektu. Rozhodl(a) jsem se tento projekt ManagedDatabaseConstructs pojmenovat a umístil(a) do řešení s názvem Tutorial75.

Obrázek 4: Vytvoření nového projektu SQL Server (kliknutím zobrazíte obrázek v plné velikosti)

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

Projekt SQL Server je svázán s konkrétní databází. V důsledku toho jsme po vytvoření nového SQL Server projectu okamžitě požádáni o zadání těchto informací. Obrázek 5 znázorňuje 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 v kroku 1.

Obrázek 5: Přidružení projektu SQL Server 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 pro připojení povolit podporu ladění SQL/CLR. Kdykoli přidružujeme projekt SQL Server k nové databázi (jak jsme to udělali na obrázku 5), visual Studio se nás zeptá, jestli chceme povolit ladění SQL/CLR pro připojení (viz obrázek 6). Klikněte na Ano.

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

V tomto okamžiku byl do řešení přidán nový projekt SQL Server. Obsahuje složku s názvem Test Scripts se souborem s názvem Test.sql, který slouží k ladění spravovaných databázových objektů vytvořených v projektu. Na ladění se podíváme v kroku 12.

Do tohoto projektu teď můžeme přidat nové spravované uložené procedury a funkce definované uživatelem, ale než to uděláme, pojďme do řešení nejprve 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 OK. Jak ukazuje obrázek 7, aktualizuje se řešení tak, aby zahrnovalo dva projekty: web a ManagedDatabaseConstructs SQL Server Project.

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

Hodnota NORTHWNDConnectionString v Web.config aktuálně odkazuje na NORTHWND.MDF soubor ve App_Data složce. Vzhledem k tomu, že jsme tuto databázi odebrali z App_Data a explicitně ji zaregistrovali v instanci SQL Server 2005 Express Edition databáze, musíme odpovídajícím způsobem aktualizovat NORTHWNDConnectionString hodnotu. Otevřete soubor na Web.config webu a změňte NORTHWNDConnectionString hodnotu tak, aby připojovací řetězec četla: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. Po této změně by váš <connectionStrings> oddíl v Web.config souboru 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 Server 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í UDF z webu ASP.NET, povolte sdružování připojení.

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

Pokud chcete přidat spravovanou uloženou proceduru do databáze Northwind, musíme nejprve vytvořit uloženou proceduru jako metodu v SQL Server Projectu. V Průzkumník řešení klikněte pravým tlačítkem na ManagedDatabaseConstructs název projektu a zvolte přidat novou položku. Zobrazí se dialogové okno Přidat novou položku se seznamem typů spravovaných databázových objektů, které lze přidat do projektu. Jak ukazuje obrázek 8, to zahrnuje mimo jiné uložené procedury a funkce User-Defined.

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

Obrázek 8: Přidání nové uložené procedury s názvem GetDiscontinuedProducts.vb (kliknutím zobrazíte obrázek v plné velikosti)

Tím se vytvoří nový soubor třídy jazyka Visual Basic s následujícím obsahem:

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

Všimněte si, že uložená procedura je implementována Shared jako metoda v souboru Partial třídy s názvem StoredProcedures. Kromě toho GetDiscontinuedProducts je metoda zdobena atributemSqlProcedure, který označuje metodu jako uloženou proceduru.

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

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

Všechny spravované databázové objekty mají přístup k objektuSqlContext, který představuje kontext volajícího. Objekt SqlContext poskytuje přístup k objektuSqlPipe prostřednictvím jeho Pipe vlastnosti. Tento SqlPipe objekt slouží k převozu informací mezi databází SQL Server 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ách používají procedurální logiku. Procedurální logika zahrnuje práci se sadami dat v jednotlivých řádcích nebo práci se skalárními daty. Metoda GetDiscontinuedProducts , kterou jsme právě vytvořili, ale nezahrnuje žádnou procedurální logiku. Proto by se v ideálním případě 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í SQL Server Project zkompiluje kód do sestavení, zaregistruje sestavení s databází a vytvoří odpovídající objekty v databázi a propojí je s příslušnými metodami v sestavení. Přesná sada úloh prováděných možností Nasazení je přesněji uvedena v kroku 13. Klikněte pravým tlačítkem na ManagedDatabaseConstructs název projektu v Průzkumník řešení a zvolte možnost Nasadit. Nasazení ale selže s následující chybou: Nesprávná syntaxe v blízkosti EXTERNAL. Pokud chcete tuto funkci povolit, možná budete muset nastavit úroveň kompatibility aktuální databáze na vyšší hodnotu. Projděte si nápovědu k uložené proceduře sp_dbcmptlevel.

Tato chybová zpráva se zobrazí při pokusu o registraci sestavení v databázi Northwind. Chcete-li zaregistrovat sestavení v databázi SQL Server 2005, musí být úroveň kompatibility databáze nastavena na hodnotu 90. Ve výchozím nastavení mají nové databáze SQL Server 2005 úroveň kompatibility 90. Databáze vytvořené pomocí Microsoft SQL Server 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, je její úroveň kompatibility v současné době nastavená na 80, a proto je potřeba ji zvýšit na 90, aby bylo možné zaregistrovat spravované databázové objekty.

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

exec sp_dbcmptlevel 'Northwind', 90

Kliknutím na ikonu Execute (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 v plné velikosti)

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

Vraťte se do SQL Server Management Studio, klikněte pravým tlačítkem na databázi Northwind v Průzkumník objektů a zvolte Aktualizovat. Dále přejděte k podrobnostem do složky Programmability a 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í zaregistrované 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 GetDiscontinuedProductsManagedDatabaseConstructs v sestavení. Když se uložená GetDiscontinuedProducts procedura spustí, spustí metodu GetDiscontinuedProducts . Vzhledem k tomu, že se jedná o spravovanou uloženou proceduru, nelze ji upravovat pomocí nástroje Management Studio (proto je ikona zámku vedle názvu uložené procedury).

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

Před voláním spravované uložené procedury musíme ještě jednu překážku překonat: 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 povoleným 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 povoleným clr je aktuálně nastavené na hodnotu 0.

Obrázek 12: Nastavení s povoleným clr je aktuálně nastaveno na hodnotu 0 (kliknutím zobrazíte obrázek v plné velikosti)

Všimněte si, že každé nastavení konfigurace na obrázku 12 obsahuje čtyři hodnoty: minimální a maximální hodnotu a hodnoty konfigurace a spuštění. Pokud chcete aktualizovat hodnotu konfigurace 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 příkaz, uvidíte, že výše uvedený příkaz aktualizoval hodnotu konfigurace s nastavením s povoleným clr na 1, ale že hodnota spuštění je stále nastavená na 0. Aby se tato změna konfigurace projevila, 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 na panelu nástrojů klikněte na ikonu Execute (Spustit). Pokud spustíte exec sp_configure nyní, měla by se zobrazit hodnota 1 pro nastavení s povolenou funkcí clr s konfigurací a hodnotami spuštění.

Po dokončení konfigurace s povoleným clr jsme připraveni spustit spravovanou GetDiscontinuedProducts uloženou proceduru. V okně dotazu zadejte a spusťte příkaz execGetDiscontinuedProducts. 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á SELECT dotaz, který vrátí všechny produkty, které jsou ukončeny, a vrátí tato data do volající aplikace, která je v tomto případě SQL Server Management Studio. 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 v těchto kurzech vytvořili, používalo parametry. Například v kurzu Vytváření nových uložených procedur pro objekty TableAdapter typed DataSet s 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.

Pokud chcete vytvořit spravovanou uloženou proceduru, která přijímá vstupní parametry, jednoduše zadejte tyto parametry v definici metody. Chcete-li to ilustrovat, přidejte do projektu další spravovanou uloženou proceduru ManagedDatabaseConstructs s názvem GetProductsWithPriceLessThan. 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 na ManagedDatabaseConstructs název projektu a zvolte přidat novou uloženou proceduru. Pojmenujte soubor GetProductsWithPriceLessThan.vb. Jak jsme viděli v kroku 3, vytvoří se nový soubor třídy jazyka Visual Basic s metodou s názvem GetProductsWithPriceLessThan umístěnou Partial v rámci třídy StoredProcedures.

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

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

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

Po přidání tohoto kódu znovu nasaďte SQL Server Project. Pak se vraťte do 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ů pod 25 USD, jak je znázorněno na obrázku 14.

Obrázek 14: Zobrazí se produkty do 25 USD (kliknutím zobrazíte obrázek v plné velikosti).

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

V tomto okamžiku GetDiscontinuedProducts jsme do projektu přidali uložené procedury ManagedDatabaseConstructs a GetProductsWithPriceLessThan spravovali je a zaregistrovali je v databázi northwind SQL Server. Tyto spravované uložené procedury jsme také vyvolali z SQL Server Management Studio (viz obrázky 13 a 14). Aby ale naše aplikace ASP.NET mohla tyto spravované uložené procedury používat, musíme je přidat do vrstev přístupu k datům a do vrstvy obchodní logiky v architektuře. V tomto kroku přidáme dvě nové metody do ProductsTableAdapterNorthwindWithSprocs v Typed DataSet, která byla původně vytvořena v kurzu Vytváření nových uložených procedur pro Typed DataSet s TableAdapter . V kroku 7 přidáme odpovídající metody do BLL.

NorthwindWithSprocs Otevřete Typed DataSet v sadě Visual Studio a začněte přidáním nové metody do ProductsTableAdapter s názvem GetDiscontinuedProducts. Chcete-li přidat novou metodu do objektu TableAdapter, klikněte pravým tlačítkem na název objektu TableAdapter v Designer 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 Web.config aktualizovat 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 Nepodařilo se přidat dotaz. Nelze najít připojení NORTHWNDConnectionString pro objekt Web.config v dialogovém okně při pokusu o přidání nové metody do objektu TableAdapter. Pokud chcete tuto chybu vyřešit, klikněte na OK, přejděte na Web.config a aktualizujte NORTHWNDConnectionString hodnotu, jak je popsáno v kroku 2. Pak zkuste metodu znovu přidat do třídy TableAdapter. Tentokrát by to mělo fungovat bez chyby.

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

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

Na další obrazovce se zobrazí výzva k zadání uložené procedury, která metoda vyvolá. V rozevíracím seznamu zvolte spravovanou GetDiscontinuedProducts uloženou proceduru a klikněte na Další.

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

Pak jsme požádáni, abychom určili, jestli uložená procedura vrací řádky, jednu hodnotu nebo nic. Vzhledem k tomu GetDiscontinuedProducts , že vrátí sadu řádků s ukončeným produktem, zvolte první možnost ( Tabulková data ) a klikněte na 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 umožňuje zadat použité vzory přístupu k datům a názvy výsledných metod. Ponechte zaškrtnutá obě políčka a pojmenujte metody FillByDiscontinued a GetDiscontinuedProducts. Dokončete průvodce kliknutím na Dokončit.

Obrázek 18: Pojmenujte metody FillByDiscontinued a GetDiscontinuedProducts (Kliknutím zobrazíte obrázek v plné velikosti)

Opakováním těchto kroků vytvořte metody s názvem FillByPriceLessThan a v pro spravovanou uloženou proceduru ProductsTableAdapterGetProductsWithPriceLessThan.GetProductsWithPriceLessThan

Obrázek 19 ukazuje snímek obrazovky s Designer DataSet po přidání metod do ProductsTableAdapterGetDiscontinuedProducts pro a GetProductsWithPriceLessThan spravované uložené procedury.

Obrázek 19: Zahrnuje ProductsTableAdapter nové metody přidané v tomto kroku (kliknutím zobrazíte obrázek v plné velikosti)

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 volání spravovaných uložených procedur přidané v krocích 4 a 5, potřebujeme 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 Function GetDiscontinuedProducts() As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetDiscontinuedProducts()
End Function
<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsWithPriceLessThan(ByVal priceLessThan As Decimal) _
    As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetProductsWithPriceLessThan(priceLessThan)
End Function

Obě metody jednoduše volají odpovídající metodu DAL a vrátí ProductsDataTable instanci. Značky DataObjectMethodAttribute nad každou metodou způsobí, že tyto metody budou zahrnuty do rozevíracího seznamu na kartě SELECT v průvodci Konfigurovat zdroj dat ObjectDataSource.

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

S rozšířením vrstvy obchodní logiky a přístupu k datům o podporu volání GetDiscontinuedProducts a GetProductsWithPriceLessThan spravovaných uložených procedur teď můžeme tyto výsledky uložených procedur 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 na Designer. Nastavte vlastnost GridView na IDDiscontinuedProducts a z její inteligentní značky vytvořte vazbu na nový objekt ObjectDataSource s názvem DiscontinuedProductsDataSource. Nakonfigurujte ObjectDataSource tak, aby načítá data z ProductsBLLWithSprocs metody třídy s GetDiscontinuedProducts .

Obrázek 20: Konfigurace objektu ObjectDataSource pro použití ProductsBLLWithSprocs třídy (kliknutím zobrazíte obrázek v plné velikosti)

Obrázek 21: Výběr GetDiscontinuedProducts metody ze seznamu Drop-Down na kartě SELECT (kliknutím zobrazíte obrázek v plné velikosti)

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

Po dokončení průvodce sada Visual Studio automaticky přidá BoundField nebo CheckBoxField pro každé datové pole v .ProductsDataTable Chvíli odeberte všechna tato pole s výjimkou ProductName a Discontinued. V tomto okamžiku by deklarativní kód GridView a ObjectDataSource měl vypadat nějak takto:

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

Chvíli se na tuto stránku podívejte 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 dal s ProductsDataTable třídy s GetDiscontinuedProducts , která vyvolá uloženou proceduru GetDiscontinuedProducts . Tato uložená procedura je spravovaná uložená procedura a spouští kód, který jsme vytvořili v kroku 3 a vrací ukončené produkty.

Výsledky vrácené spravovanou uloženou procedurou ProductsDataTable jsou zabaleny do dal a poté vráceny do BLL, který je pak vrátí do prezentační vrstvy, kde jsou vázány na GridView a zobrazeny. Podle očekávání se v mřížce zobrazí seznam produktů, které byly ukončeny.

Obrázek 22: Vyřazené produkty jsou uvedené (kliknutím zobrazíte obrázek v plné velikosti)

Pro další cvičení přidejte textbox a další objekt GridView na stránku. Nechte tento GridView zobrazit produkty menší než množství zadané do TextBox voláním ProductsBLLWithSprocs třídy s GetProductsWithPriceLessThan metoda.

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

User-Defined funkce neboli funkce definované uživatelem jsou databázové objekty, které přesně napodobují sémantiku funkcí v programovacích jazycích. Podobně jako funkce v jazyce Visual Basic můžou funkce definované uživatelem obsahovat proměnný počet vstupních parametrů a vracet hodnotu určitého typu. Uživatelsky definovaná funkce může vracet buď 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 funkcí definovanou uživatelem, která vrací skalární datový typ.

Následující funkce definovaná uživatelem vypočítá odhadovanou hodnotu inventáře pro konkrétní produkt. Provede to tak, že vezme tři vstupní parametry – UnitPricehodnoty , UnitsInStocka Discontinued pro konkrétní produkt – a vrátí hodnotu typu money. Vypočítá odhadovanou hodnotu inventáře vynásobením UnitPrice hodnoty hodnotou UnitsInStock. U ukončených položek je tato hodnota poloviční.

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 je tato funkce definovaná uživatelem přidána do databáze, můžete ji najít v sadě Management Studio rozbalením složky Programovatelnost, funkce a funkce skalární hodnoty. Dá se použít v dotazu, SELECT například takto:

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

Přidal udf_ComputeInventoryValue jsem UDF do databáze Northwind; Obrázek 23 znázorňuje výstup výše uvedeného SELECT dotazu při zobrazení v nástroji Management Studio. Všimněte si také, že funkce definovaná uživatelem je uvedená ve složce Skalární hodnoty Functions v Průzkumník objektů.

Obrázek 23: Jsou uvedeny hodnoty skladových zásob jednotlivých produktů (kliknutím zobrazíte obrázek v plné velikosti)

Funkce definované uživatelem můžou také vracet tabulková data. Můžeme například vytvořit UDF, která vrací produkty, které patří do určité 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 definovaná udf_GetProductsByCategoryID uživatelem @CategoryID přijímá vstupní parametr a vrací výsledky zadaného SELECT dotazu. Po vytvoření můžete na tuto funkci definovanou uživatelem odkazovat v klauzuli FROMSELECT (nebo JOIN) dotazu. Následující příklad by vrátil ProductIDhodnoty , ProductNamea CategoryID pro každý z nápojů.

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

Přidal udf_GetProductsByCategoryID jsem UDF do databáze Northwind; Obrázek 24 znázorňuje výstup výše SELECT uvedeného dotazu při zobrazení prostřednictvím nástroje Management Studio. Funkce definované uživatelem, které vracejí tabulková data, najdete ve složce Funkce table-value Průzkumník objektů.

Obrázek 24: , ProductIDProductNamea CategoryID jsou uvedeny pro každý nápoj (kliknutím zobrazíte obrázek v plné velikosti)

Poznámka

Další informace o vytváření a používání funkcí definovaných uživatelem najdete v článku Úvod do User-Defined Functions. Podívejte se také na výhody a nevýhody funkcí User-Defined.

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

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

Pokud chcete do ManagedDatabaseConstructs projektu přidat spravovanou definovanou uživatelem, klikněte pravým tlačítkem 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 User-Defined a pojmenujte nový soubor udf_ComputeInventoryValue_Managed.vbUDF .

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

Šablona funkce User-Defined vytvoří Partial třídu s názvem UserDefinedFunctions metody, jejíž název je stejný jako název souboru třídy (udf_ComputeInventoryValue_Managedv tomto případě). Tato metoda je zdobena pomocí atributuSqlFunction, který označuje metodu jako spravovanou UDF.

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

Metoda udf_ComputeInventoryValue 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 – UnitPrice, UnitsInStocka Discontinued – a vrátila SqlMoney objekt. Logika výpočtu hodnoty inventáře je shodná s logikou v sadě T-SQL udf_ComputeInventoryValue UDF.

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

Všimněte si, že vstupní parametry metody UDF jsou odpovídajících typů SQL: SqlMoney pro UnitPrice pole, SqlInt16 pro UnitsInStock, a 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 ve sloupcích UnitsInPrice a UnitsInStock hodnoty databázeNULL. Proto musíme nejprve zkontrolovat, jestli tyto hodnoty obsahují NULL s, což provádíme prostřednictvím vlastnosti objektu SqlMoney sIsNull. Pokud obě UnitPrice hodnoty a UnitsInStock obsahují jiné hodnotyNULL , vypočítáme inventoryValue jako součin těchto dvou hodnot. Pak, pokud Discontinued je pravda, pak hodnotu snížíme na polovinu.

Poznámka

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

Krok 11: Nasazení spravovaného UDF

Teď, když se vytvořil spravovaný UDF, jsme připraveni ji nasadit do databáze Northwind. Jak jsme viděli v kroku 4, spravované objekty v projektu SQL Server 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 SQL Server Management Studio a aktualizujte složku Skalární funkce. Teď by se měly zobrazit dvě položky:

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

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

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

Tento příkaz používá místo funkce T-SQL udf_ComputeInventoryValue UDF spravovanou udf ComputeInventoryValue_Managed 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 Server prostřednictvím sady Visual Studio: Ladění přímé databáze, Ladění aplikací a Ladění z SQL Server projektu. Spravované databázové objekty nelze ladit prostřednictvím přímého ladění databáze, ale lze je ladit z klientské aplikace a přímo z projektu SQL Server. Aby však ladění fungovalo, musí databáze SQL Server 2005 povolit ladění SQL/CLR. Vzpomeňte si, že když jsme poprvé vytvořili ManagedDatabaseConstructs projekt, sada Visual Studio se nás ptala, jestli chceme povolit ladění SQL/CLR (viz obrázek 6 v kroku 2). Toto nastavení lze změnit kliknutím pravým tlačítkem na databázi v okně Průzkumník serveru.

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

Představte si, že jsme chtěli ladit spravovanou uloženou proceduru GetProductsWithPriceLessThan . Začali bychom nastavením zarážky v kódu GetProductsWithPriceLessThan metody.

Obrázek 27: Nastavení zarážky v GetProductsWithPriceLessThan metodě (kliknutím zobrazíte obrázek v plné velikosti)

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

ManagedDatabaseConstructs Při spuštění projektu z ladicího programu spustí příkazy SQL v Test.sql souboru, který je umístěn ve Test Scripts složce. Pokud například chcete otestovat spravovanou uloženou proceduru GetProductsWithPriceLessThan , nahraďte existující Test.sql obsah souboru následujícím příkazem, který vyvolá GetProductsWithPriceLessThan spravovanou uloženou proceduru @CategoryID s hodnotou 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 vytvoříte projekty v rámci řešení, nasadíte spravované databázové objekty do databáze Northwind a pak spustíte Test.sql skript. V tomto okamžiku dojde k dosažení zarážky a můžeme procházet metodu GetProductsWithPriceLessThan , prozkoumat hodnoty vstupních parametrů atd.

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

Aby bylo možné objekt databáze SQL ladit prostřednictvím klientské aplikace, je nutné, aby byla databáze nakonfigurována tak, aby podporovala ladění aplikací. Klikněte pravým tlačítkem na databázi v Průzkumníku serveru a ujistěte se, že je zaškrtnutá možnost Ladění aplikací. Kromě toho musíme nakonfigurovat aplikaci ASP.NET 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ěcí projekt a spusťte ladění. Pokud navštívíte stránku, která volá jeden ze spravovaných objektů, který má zarážku, aplikace se zastaví a řízení se předá ladicímu 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ů

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

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

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

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

Tento kód je téměř shodný s kódem GetProductsWithPriceLessThan metody vytvořené v kroku 5. Jedinými rozdíly jsou názvy metod, klauzule WHERE a název parametru použitého v dotazu. Zpět v GetProductsWithPriceLessThan metodě je klauzule WHERE : WHERE UnitPrice < @MaxPrice. Tady v GetProductsWithPriceGreaterThannástroji použijeme: WHERE UnitPrice > @MinPrice .

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

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

Pokud složka obsahující v bc.exe není v systému , PATHbudete muset plně odkazovat na její cestu, %WINDOWS%\Microsoft.NET\Framework\version\například takto:

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

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

Příznak /t určuje, že soubor třídy jazyka Visual Basic by měl být kompilován 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.vb souboru třídy z příkazového řádku můžete alternativně použít Visual Basic Express Edition nebo vytvořit samostatný projekt knihovny tříd v sadě Visual Studio Standard Edition. S ren Jacob Lauritsen laskavě poskytl takový projekt Visual Basic Express Edition s kódem pro uloženou GetProductsWithPriceGreaterThan proceduru a dvě spravované uložené procedury a UDF vytvořené v krocích 3, 5 a 10. Projekt S ren s také zahrnuje 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 Server 2005. To lze provést prostřednictvím T-SQL, pomocí příkazu CREATE ASSEMBLY, nebo prostřednictvím SQL Server Management Studio. Pojďme se zaměřit na používání nástroje Management Studio.

V nástroji Management Studio rozbalte složku Programmability v databázi Northwind. Jednou z jeho podsložek je Sestavení. Pokud chcete 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 sestavení, které ManuallyCreatedDBObjects.dll jsme právě zkompilovali, a kliknutím na OK přidejte sestavení do databáze. V Průzkumník objektů byste neměli ManuallyCreatedDBObjects.dll vidět sestavení.

Obrázek 30: Přidání ManuallyCreatedDBObjects.dll sestavení do databáze (kliknutím zobrazíte obrázek v plné velikosti)

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

Zatímco jsme přidali sestavení do databáze Northwind, ještě jsme přidružili uloženou proceduru GetProductsWithPriceGreaterThan k metodě v sestavení. To provedete tak, že otevřete nové okno dotazu a spustí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 v databázi Northwind vytvoří nová uložená procedura s názvem GetProductsWithPriceGreaterThan a přidruží se 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 vedle které je ikona zámku. Pokud chcete tuto uloženou proceduru otestovat, 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 o produktech s UnitPrice hodnotou vyšší než 24,95 USD.

Obrázek 32: Hodnota ManuallyCreatedDBObjects.dll je uvedená v Průzkumník objektů (kliknutím zobrazíte obrázek v plné velikosti)

Souhrn

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 bylo možné tyto databázové objekty vytvořit pouze pomocí jazyka T-SQL, ale nyní je můžeme vytvořit pomocí programovacích jazyků .NET, jako je Visual Basic. V tomto kurzu jsme vytvořili dvě spravované uložené procedury a spravovanou funkci User-Defined.

Typ projektu SQL Server 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 Server jsou ale dostupné jenom v edicích Professional a Team Systems sady Visual Studio. Pro ty, kteří používají Visual Web Developer nebo Standardní edici sady Visual Studio, musí být kroky vytvoření, kompilace a nasazení provedeny ručně, jak jsme viděli v kroku 13.

Všechno nejlepší na 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 User-Defined Functions
• Vytváření objektů SQL Server 2005 ve spravovaném kódu
• Postupy: Vytvoření a spuštění modulu CLR SQL Server uložené procedury
• Postupy: Vytvoření a spuštění funkce SQL Server User-Defined CLR
• 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í dokumentace k jazyku Transact-SQL
• Návod: Vytvoření uložené procedury ve spravovaném kódu

O autorovi

Scott Mitchell, autor sedmi knih o ASP/ASP.NET a zakladatel 4GuysFromRolla.com, pracuje s webovými technologiemi Microsoftu od roku 1998. Scott pracuje jako nezávislý konzultant, školitel a spisovatel. Jeho nejnovější kniha je Sams Teach Yourself ASP.NET 2.0 in 24 Hours. Můžete ho zastihnout na mitchell@4GuysFromRolla.comadrese . nebo prostřednictvím jeho blogu, který najdete na adrese http://ScottOnWriting.NET.

Zvláštní poděkování

Tato série kurzů byla zkontrolována mnoha užitečnými recenzenty. Hlavním recenzentem pro tento kurz byl S ren Jacob Lauritsen. Kromě toho, že si prostudoval tento článek, S ren také vytvořil projekt Visual C# Express Edition zahrnutý v tomto článku ke stažení pro ruční kompilaci spravovaných databázových objektů. Chtěli byste si projít své nadcházející články na webu MSDN? Pokud ano, dejte mi řádek na mitchell@4GuysFromRolla.com.

Předchozí