Tárolt eljárások és User-Defined függvények létrehozása felügyelt kóddal (C#)

által Scott Mitchell

PDF letöltése

A Microsoft SQL Server 2005 integrálható a .NET Common Language Runtime szolgáltatással, így a fejlesztők felügyelt kóddal hozhatnak létre adatbázis-objektumokat. Ez az oktatóanyag bemutatja, hogyan hozhat létre felügyelt tárolt eljárásokat és felügyelt, felhasználó által definiált függvényeket Visual Basic- vagy C#-kóddal. Azt is láthatjuk, hogy a Visual Studio ezen kiadásai hogyan teszik lehetővé az ilyen felügyelt adatbázis-objektumok hibakeresését.

Bevezetés

A Microsoft SQL Server 2005-höz hasonló adatbázisok a Transact-Structured lekérdezési nyelvet (T-SQL) használják adatok beszúrásához, módosításához és beolvasásához. A legtöbb adatbázisrendszer olyan szerkezeteket tartalmaz, amelyek több SQL-utasítást csoportosítanak, amelyeket aztán egyetlen, újrafelhasználható egységként lehet végrehajtani. A tárolt eljárások egy példa. A másik a User-Defined Functions (UDF-ek), egy szerkezet, amelyet a 9. lépésben részletesebben fogunk megvizsgálni.

Az SQL alapvető célja az adathalmazok használata. A SELECT, UPDATEés DELETE az utasítások eredendően a megfelelő tábla összes rekordja esetében érvényesek, és csak a záradékaik WHERE korlátozzák őket. Mégis számos nyelvi funkció van kialakítva egy rekord egyidejű kezelésére és a skaláris adatok manipulálására. CURSOR lehetővé teszi, hogy a rekordok halmazán egyesével legyen végigiterálva. A sztringmanipulálási függvények például LEFTa , CHARINDEXés PATINDEX skaláris adatokkal működnek. Az SQL olyan vezérlőfolyamat-utasításokat is tartalmaz, mint és IFWHILE.

A Microsoft SQL Server 2005 előtt a tárolt eljárások és az UDF-ek csak T-SQL-utasítások gyűjteményeként definiálhatók. Az SQL Server 2005 azonban úgy lett kialakítva, hogy integrációt biztosítson a Common Language Runtime (CLR) alkalmazással, amely az összes .NET-szerelvények által használt futtatókörnyezet. Következésképpen az SQL Server 2005-adatbázisokban tárolt eljárások és UDF-ek felügyelt kóddal hozhatók létre. Ez azt is jelentheti, hogy egy C#-osztályban létrehozhat egy tárolt eljárást vagy UDF-et metódusként. Ez lehetővé teszi, hogy ezek a tárolt eljárások és UDF-ek a .NET-keretrendszerben és a saját egyéni osztályaikban használják a funkciókat.

Ebben az oktatóanyagban azt vizsgáljuk meg, hogyan hozhat létre felügyelt tárolt eljárásokat és User-Defined Függvényeket, és hogyan integrálhatja őket a Northwind-adatbázisba. Lássunk hozzá!

Megjegyzés:

A felügyelt adatbázis-objektumok bizonyos előnyöket kínálnak az SQL-megfelelőikkel szemben. A nyelvgazdagság és a jártasság, valamint a meglévő kód és logika újrafelhasználásának képessége a fő előnye. A felügyelt adatbázis-objektumok azonban valószínűleg kevésbé lesznek hatékonyak olyan adathalmazok használatakor, amelyek nem járnak sok eljárási logikával. A felügyelt kód és a T-SQL használatának előnyeiről részletesebben is tájékozódhat a felügyelt kód adatbázis-objektumok létrehozásához való használatának előnyeiről.

1. lépés: A Northwind-adatbázis áthelyezése az App_Data könyvtárból

Minden eddigi oktatóanyagunk egy Microsoft SQL Server 2005 Express Edition adatbázisfájlt használt a webalkalmazás mappájában App_Data . Az adatbázis App_Data helyre tétele leegyszerűsítette az oktatóanyagok terjesztését és futtatását, mivel az összes fájl egy könyvtárban helyezkedik el, és az oktatóanyag teszteléséhez nem volt szükség további konfigurációs lépésekre.

Ebben az oktatóanyagban azonban vegyük ki a Northwind-adatbázist a App_Data-ból, és kifejezetten regisztráljuk az SQL Server 2005 Express Edition adatbázispéldányban. Bár az oktatóanyag lépéseit a mappában lévő App_Data adatbázissal is végrehajthatjuk, a lépések egy része sokkal egyszerűbb lesz, ha explicit módon regisztráljuk az adatbázist az SQL Server 2005 Express Edition adatbázispéldányában.

Az oktatóanyag letöltéséhez a két adatbázisfájl - NORTHWND.MDF és NORTHWND_log.LDF - egy nevesített DataFilesmappába kerül. Ha Ön a saját oktatóanyagai alkalmazását követi, zárja be a Visual Studiót, és helyezze át a NORTHWND.MDF és NORTHWND_log.LDF fájlokat a webhely App_Data mappájából egy, a webhelyen kívüli mappába. Miután az adatbázisfájlokat áthelyezték egy másik mappába, regisztrálni kell a Northwind-adatbázist az SQL Server 2005 Express Edition adatbázispéldányával. Ez az SQL Server Management Studióban végezhető el. Ha az SQL Server 2005 nem Express kiadású verziója van telepítve a számítógépre, akkor valószínűleg már telepítve van a Management Studio. Ha csak az SQL Server 2005 Express Editiont használja a számítógépén, szánjon egy kis időt a Microsoft SQL Server Management Studio letöltésére és telepítésére.

Indítsa el az SQL Server Management Studiót. Ahogy az 1. ábrán látható, a Management Studio azzal kezdi, hogy megkérdezi, melyik kiszolgálóhoz szeretne csatlakozni. Írja be a localhost\SQLExpress nevet a kiszolgálónévhez, válassza a Windows-hitelesítés lehetőséget a Hitelesítés legördülő listában, majd kattintson a Csatlakozás gombra.

1. ábra: Csatlakozás a megfelelő adatbázispéldányhoz

Miután csatlakozott, az Object Explorer ablak felsorolja az SQL Server 2005 Express Edition-adatbázispéldány adatait, beleértve az adatbázisait, a biztonsági információkat, a felügyeleti beállításokat stb.

Csatolnunk kell a Northwind-adatbázist a DataFiles mappában (vagy bárhol, ahol áthelyezte) az SQL Server 2005 Express Edition adatbázispéldányhoz. Kattintson az Adatbázisok mappára jobb gombbal, majd a helyi menüben válassza a 'Csatolás' lehetőséget. Ekkor megjelenik az Adatbázisok csatolása párbeszédpanel. Kattintson a Hozzáadás gombra, részletezse le a megfelelő NORTHWND.MDF fájlt, és kattintson az OK gombra. Ekkor a képernyőnek a 2. ábrához hasonlóan kell kinéznie.

2. ábra: Csatlakozás a megfelelő adatbázispéldányhoz (kattintson ide a teljes méretű kép megtekintéséhez)

Megjegyzés:

Amikor a Management Studión keresztül csatlakozik az SQL Server 2005 Express Edition-példányhoz, az Adatbázisok csatolása párbeszédpanel nem teszi lehetővé a felhasználói profilkönyvtárak, például a Dokumentumok részletezését. Ezért ügyeljen arra, hogy a NORTHWND.MDF és NORTHWND_log.LDF fájlokat egy nem felhasználói profilkönyvtárba helyezze.

Kattintson az OK gombra az adatbázis csatolásához. Bezárul az Adatbázisok csatolása párbeszédpanel, és az Object Explorernek most már fel kell sorolnia az imént csatolt adatbázist. Valószínű, hogy a Northwind adatbázisnak olyan neve van, mint 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDFa . Nevezze át az adatbázist Northwind névre a jobb gombbal az adatbázisra kattintva, és válassza az Átnevezés parancsot.

3. ábra: Az adatbázis átnevezése Northwindre

2. lépés: Új megoldás és SQL Server-projekt létrehozása a Visual Studióban

Felügyelt tárolt eljárások vagy UDF-ek SQL Server 2005-ben való létrehozásához a tárolt eljárást és az UDF-logikát C#-kódként fogjuk írni egy osztályban. A kód megírása után ezt az osztályt egy szerelvénybe ( .dll fájlba) kell lefordítanunk, regisztrálni kell a szerelvényt az SQL Server-adatbázissal, majd létre kell hoznunk egy tárolt eljárást vagy UDF-objektumot az adatbázisban, amely a szerelvény megfelelő metódusára mutat. Ezek a lépések manuálisan is elvégezhetők. Bármilyen szövegszerkesztőben létrehozhatjuk a kódot, lefordíthatjuk a parancssorból a C#-fordítóval (csc.exe), regisztrálhatjuk az adatbázisba a parancs vagy a CREATE ASSEMBLY Management Studio használatával, és hasonló módon adhatjuk hozzá a tárolt eljárást vagy UDF-objektumot. Szerencsére a Visual Studio Professional és Team Systems verziói tartalmaznak egy SQL Server Project-típust, amely automatizálja ezeket a feladatokat. Ebben az oktatóanyagban végigvezetjük az SQL Server Project típusának használatával egy felügyelt tárolt eljárás és UDF létrehozását.

Megjegyzés:

Ha a Visual Web Developert vagy a Visual Studio Standard kiadását használja, akkor inkább a manuális megközelítést kell használnia. A 13. lépés részletes útmutatást nyújt ezeknek a lépéseknek a manuális végrehajtásához. Azt javasoljuk, hogy olvassa el a 2–12. lépést a 13. lépés elolvasása előtt, mivel ezek a lépések olyan fontos SQL Server-konfigurációs utasításokat tartalmaznak, amelyeket a Visual Studio melyik verziójától függetlenül kell alkalmazni.

Először nyissa meg a Visual Studiót. A Fájl menüBen válassza az Új projekt lehetőséget az Új projekt párbeszédpanel megjelenítéséhez (lásd a 4. ábrát). Fúrjon le az Adatbázis projekttípusra, majd a jobb oldali sablonok közül válassza ki az új SQL Server-projekt létrehozását. Úgy döntöttem, hogy elnevezem ezt a projektet ManagedDatabaseConstructs , és egy nevű megoldásban Tutorial75helyezem el.

4. ábra: Új SQL Server-projekt létrehozása (ide kattintva megtekintheti a teljes méretű képet)

Kattintson az OK gombra az Új projekt párbeszédpanelen a Megoldás és az SQL Server Project létrehozásához.

Az SQL Server-projekt egy adott adatbázishoz van kötve. Következésképpen az új SQL Server Project létrehozása után a rendszer azonnal kéri, hogy adja meg ezeket az információkat. Az 5. ábra az új adatbázis-referencia párbeszédpanelt mutatja, amely ki lett töltve, és az 1. lépésben az SQL Server 2005 Express Edition adatbázispéldányban regisztrált Northwind-adatbázisra mutat.

5. ábra: Az SQL Server-projekt társítása a Northwind Database-hez

A projektben létrehozott felügyelt tárolt eljárások és UDF-ek hibakereséséhez engedélyezni kell az SQL/CLR hibakeresési támogatását a kapcsolathoz. Amikor egy SQL Server-projektet új adatbázissal társít (ahogy az 5. ábrán is), a Visual Studio megkérdezi, hogy engedélyezni szeretnénk-e az SQL/CLR hibakeresést a kapcsolaton (lásd a 6. ábrát). Kattintson az Igen gombra.

6. ábra: AZ SQL/CLR hibakeresésének engedélyezése

Ezen a ponton az új SQL Server Project hozzá lett adva a megoldáshoz. Tartalmaz egy Test Scripts nevű mappát egy Test.sql nevű fájllal, amely a projektben létrehozott felügyelt adatbázis-objektumok hibakeresésére szolgál. A hibakeresést a 12. lépésben tekintjük meg.

Most már hozzáadhatunk új felügyelt tárolt eljárásokat és UDF-eket ehhez a projekthez, de mielőtt belefoglalnánk a meglévő webalkalmazást a Megoldásba. A Fájl menüben válassza a Hozzáadás lehetőséget, és válassza a Meglévő webhely lehetőséget. Keresse meg a megfelelő webhelymappát, és kattintson az OK gombra. A 7. ábrán látható, hogy a megoldás két projektet tartalmaz: a webhelyet és az ManagedDatabaseConstructs SQL Server Projectet.

7. ábra: A Megoldáskezelő mostantól két projektet tartalmaz

Az NORTHWNDConnectionString érték Web.config jelenleg a NORTHWND.MDF mappában lévő App_Data fájlra hivatkozik. Mivel eltávolítottuk ezt az adatbázist az SQL Server 2005 Express Edition adatbázispéldányából App_Data , ezért ennek megfelelően frissíteni kell az NORTHWNDConnectionString értéket. Nyissa meg a Web.config fájlt a webhelyen, és módosítsa az NORTHWNDConnectionString értéket úgy, hogy a kapcsolati sztring a következőt olvassa: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. A módosítás után a <connectionStrings> szakasznak Web.config a következőhöz hasonlóan kell kinéznie:

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

Megjegyzés:

Az előző oktatóanyagban leírtak szerint az SQL Server-objektum ügyfélalkalmazásból , például egy ASP.NET webhelyről történő hibakeresésekor le kell tiltanunk a kapcsolatkészletezést. A fentebb látható kapcsolati karakterlánc letiltja a kapcsolatkészletezést ( Pooling=false ). Ha nem tervezi a felügyelt tárolt eljárások és UDF-ek hibakeresését az ASP.NET webhelyről, engedélyezze a kapcsolatkészletezést.

3. lépés: Felügyelt tárolt eljárás létrehozása

Ha felügyelt tárolt eljárást szeretne hozzáadni a Northwind-adatbázishoz, először létre kell hoznia a tárolt eljárást metódusként az SQL Server Projectben. A Megoldáskezelőben kattintson a jobb gombbal a ManagedDatabaseConstructs projekt nevére, és válasszon új elemet. Ekkor megjelenik az Új elem hozzáadása párbeszédpanel, amely felsorolja a projekthez hozzáadható felügyelt adatbázis-objektumok típusait. Ahogy a 8. ábra mutatja, ez többek között a tárolt eljárásokat és a User-Defined Függvényeket is magában foglalja.

Kezdjük azzal, hogy hozzáadunk egy tárolt eljárást, amely egyszerűen visszaadja az összes megszűnt terméket. Nevezze el az új tárolt eljárásfájlt GetDiscontinuedProducts.cs.

8. ábra: Új tárolt eljárás hozzáadása elnevezve GetDiscontinuedProducts.cs (kattintson ide a teljes méretű kép megtekintéséhez)

Ezzel létrehoz egy új C#-osztályfájlt a következő tartalommal:

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

Vegye figyelembe, hogy a tárolt eljárás metódusként static van implementálva egy partial nevesített StoredProceduresosztályfájlban. Ezenkívül a GetDiscontinuedProducts metódus SqlProcedure attribute-vel van dekorálva, amely a metódust tárolt eljárásként jelöli meg.

Az alábbi kód létrehoz egy SqlCommand objektumot, és beállítja a CommandText-t egy SELECT lekérdezésre, amely visszaadja a Products táblázat összes oszlopát azoknál a termékeknél, amelyek Discontinued mezője 1. Ezután végrehajtja a parancsot, és visszaküldi az eredményeket az ügyfélalkalmazásnak. Adja hozzá ezt a kódot a GetDiscontinuedProducts metódushoz.

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

Minden felügyelt adatbázis-objektum rendelkezik hozzáféréssel egy SqlContext olyan objektumhoz , amely a hívó környezetét jelöli. A SqlContexttulajdonságon keresztülSqlPipe hozzáférést biztosít egy Pipe objektumhoz. Ez SqlPipe az objektum az SQL Server-adatbázis és a hívó alkalmazás közötti adatközlésére szolgál. Ahogy a neve is mutatja, a ExecuteAndSend metódus végrehajt egy átadott SqlCommand objektumot, és visszaküldi az eredményeket az ügyfélalkalmazásnak.

Megjegyzés:

A felügyelt adatbázis-objektumok olyan tárolt eljárásokhoz és UDF-ekhez ideálisak, amelyek a set-based logika helyett eljárási logikát használnak. A procedurális logika magában foglalja az adathalmazok egymás utáni, illetve skaláris adatokkal való együttes működését. Az GetDiscontinuedProducts imént létrehozott módszer azonban nem tartalmaz eljárási logikát. Ezért ideális esetben T-SQL-alapú tárolt eljárásként lenne implementálva. Felügyelt tárolt eljárásként van implementálva a felügyelt tárolt eljárások létrehozásához és üzembe helyezéséhez szükséges lépések bemutatásához.

4. lépés: A felügyelt tárolt eljárás üzembe helyezése

Ezzel a kóddal készen állunk a Northwind-adatbázisban való üzembe helyezésre. Az SQL Server Project telepítése lefordítja a kódot egy összeállításba, regisztrálja az összeállítást az adatbázissal, létrehozza az adatbázisban a megfelelő objektumokat, és összekapcsolja őket az összeállítás megfelelő függvényeivel. Az Üzembe helyezés beállítás által végrehajtott feladatok pontos készletét a 13. lépésben írják le pontosabban. Kattintson a jobb gombbal a ManagedDatabaseConstructs projekt nevére a Megoldáskezelőben, és válassza az Üzembe helyezés lehetőséget. Az üzembe helyezés azonban a következő hibával meghiúsul: Helytelen szintaxis a "KÜLSŐ" közelében. Előfordulhat, hogy a funkció engedélyezéséhez magasabb értékre kell állítania az aktuális adatbázis kompatibilitási szintjét. Tekintse meg a súgót a(z) sp_dbcmptlevel tárolt eljáráshoz.

Ez a hibaüzenet akkor jelenik meg, amikor megpróbálja regisztrálni a szerelvényt a Northwind-adatbázisban. Ha egy szerelvényt egy SQL Server 2005-adatbázissal szeretne regisztrálni, az adatbázis kompatibilitási szintjét 90-esre kell állítani. Alapértelmezés szerint az új SQL Server 2005-adatbázisok kompatibilitási szintje 90. A Microsoft SQL Server 2000 használatával létrehozott adatbázisok azonban alapértelmezett kompatibilitási szintje 80. Mivel a Northwind adatbázis eredetileg Microsoft SQL Server 2000-adatbázis volt, kompatibilitási szintje jelenleg 80, ezért a felügyelt adatbázis-objektumok regisztrálásához 90-re kell növelni.

Az adatbázis kompatibilitási szintjének frissítéséhez nyisson meg egy Új lekérdezés ablakot a Management Studióban, és írja be a következőt:

exec sp_dbcmptlevel 'Northwind', 90

A fenti lekérdezés futtatásához kattintson az eszköztár Végrehajtás ikonra.

9. ábra: A Northwind Database kompatibilitási szintjének frissítése (ide kattintva megtekintheti a teljes méretű képet)

A kompatibilitási szint frissítése után telepítse újra az SQL Server Projectet. Ezúttal az üzembe helyezésnek hiba nélkül kell befejeződnie.

Térjen vissza az SQL Server Management Studióba, kattintson a jobb gombbal a Northwind-adatbázisra az Object Explorerben, és válassza a Frissítés lehetőséget. Ezután bontsa ki a Programozhatóság mappát, majd bontsa ki a Szerelvények mappát. A 10. ábrán látható, hogy a Northwind adatbázis most már tartalmazza a ManagedDatabaseConstructs projekt által létrehozott szerelvényt.

10. ábra: A ManagedDatabaseConstructs szerelvény regisztrálva van a Northwind Database-ben

Bontsa ki a Tárolt eljárások mappát is. Itt megjelenik egy tárolt eljárás neve GetDiscontinuedProducts. Ezt a tárolt eljárást az üzembe helyezési folyamat hozta létre, és a GetDiscontinuedProducts metódusra mutat a ManagedDatabaseConstructs szerelvényben. Amikor a GetDiscontinuedProducts tárolt eljárás végrehajtásra kerül, az viszont végrehajtja a GetDiscontinuedProducts metódust. Mivel ez egy felügyelt tárolt eljárás, nem szerkeszthető a Management Studióban (ezért a tárolt eljárás neve melletti zárolás ikon).

11. ábra: A GetDiscontinuedProducts tárolt eljárás szerepel a Tárolt eljárások mappában

Még egy akadályt kell leküzdenünk, mielőtt meghívhatjuk a felügyelt tárolt eljárást: az adatbázis úgy van konfigurálva, hogy megakadályozza a felügyelt kód végrehajtását. Ezt egy új lekérdezési ablak megnyitásával és a GetDiscontinuedProducts tárolt eljárás végrehajtásával ellenőrizheti. A következő hibaüzenet jelenik meg: A felhasználói kód végrehajtása le van tiltva a .NET-keretrendszerben. Engedélyezze a "clr-kompatibilis konfigurációs beállítást."

A Northwind-adatbázis konfigurációs adatainak vizsgálatához írja be és hajtsa végre a parancsot exec sp_configure a lekérdezési ablakban. Ez azt mutatja, hogy a clr-kompatibilis beállítás jelenleg 0 értékre van állítva.

12. ábra: A clr-kompatibilis beállítás jelenleg 0-ra van állítva (kattintson ide a teljes méretű kép megtekintéséhez)

Vegye figyelembe, hogy a 12. ábrán minden konfigurációs beállítás négy értéket tartalmaz: a minimális és maximális értékeket, valamint a konfigurációs és futtatási értékeket. A clr-kompatibilis beállítás konfigurációs értékének frissítéséhez hajtsa végre a következő parancsot:

exec sp_configure 'clr enabled', 1

Ha újrafuttatja a exec sp_configure parancsot, látni fogja, hogy a fenti utasítás 1-re módosította a clr-kompatibilis beállítás konfigurációs értékét, de a futtatási érték továbbra is 0. Ahhoz, hogy ez a konfigurációs módosítás hatással legyen, végre kell hajtanunk a RECONFIGURE parancsot, amely a futtatási értéket az aktuális konfigurációs értékre állítja. Egyszerűen írja be RECONFIGURE a lekérdezés ablakát, és kattintson a Végrehajtás ikonra az eszköztáron. Ha most futtatja exec sp_configure, 1 értéket kell látnia a CLR engedélyezett beállítás konfigurációjában és a futtatási értékeknél.

A clr-kompatibilis konfiguráció befejeződése után készen állunk a felügyelt GetDiscontinuedProducts tárolt eljárás futtatására. A lekérdezési ablakban adja meg és hajtsa végre a parancsot execGetDiscontinuedProducts. A tárolt eljárás meghívása a metódus megfelelő felügyelt kódjának GetDiscontinuedProducts végrehajtását eredményezi. Ez a kód egy SELECT lekérdezést hajt végre, amely visszaadja az összes megszűnt terméket, és az adatokat továbbítja a hívó alkalmazásnak, amely ebben az esetben az SQL Server Management Studio. A Management Studio megkapja ezeket az eredményeket, és megjeleníti őket az Eredmények ablakban.

13. ábra: A GetDiscontinuedProducts tárolt eljárás az összes megszűnt terméket visszaadja (ide kattintva megtekintheti a teljes méretű képet)

5. lépés: A bemeneti paramétereket elfogadó felügyelt tárolt eljárások létrehozása

Az oktatóanyagok során létrehozott lekérdezések és tárolt eljárások közül számos paramétert használt. Például a Typed DataSet-adapterekhez új tárolt eljárások létrehozása című oktatóanyagban létrehoztunk egy tárolt eljárást, amely elfogadott egy bemeneti paramétert, amelynek a neve GetProductsByCategoryID volt. A tárolt eljárás ezután visszaadta az összes olyan terméket, amelynek CategoryID mezője megfelelt a megadott @CategoryID paraméter értékének.

A bemeneti paramétereket elfogadó felügyelt tárolt eljárás létrehozásához egyszerűen adja meg ezeket a paramétereket a metódus definíciójában. Ennek szemléltetéséhez adjunk hozzá egy másik felügyelt tárolt eljárást a projekthez.ManagedDatabaseConstructsGetProductsWithPriceLessThan Ez a felügyelt tárolt eljárás egy árat meghatározó bemeneti paramétert fogad el, és minden olyan terméket visszaad, amelynek UnitPrice a mezője kisebb, mint a paraméter értéke.

Ha új tárolt eljárást szeretne hozzáadni a projekthez, kattintson a jobb gombbal a ManagedDatabaseConstructs projekt nevére, és válasszon új tárolt eljárást. A fájl neve legyen GetProductsWithPriceLessThan.cs. Ahogy a 3. lépésben láttuk, ez létrehoz egy új C# osztályfájlt, amely tartalmaz egy GetProductsWithPriceLessThan nevű metódus az partial osztályban StoredProcedures.

Frissítse a GetProductsWithPriceLessThan metódus definícióját, hogy fogadja el a SqlMoney névvel ellátott price bemeneti paramétert, és írja meg a kódot a lekérdezési eredmények végrehajtásához és visszaadásához:

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

A GetProductsWithPriceLessThan metódus s definíciója és kódja szorosan hasonlít a 3. lépésben létrehozott metódus definíciójára és kódjára GetDiscontinuedProducts . Az egyetlen különbség az, hogy a GetProductsWithPriceLessThan metódus bemeneti paraméterként fogad el (price), az SqlCommand s lekérdezés tartalmaz egy paramétert (@MaxPrice), és a rendszer hozzáad egy paramétert az SqlCommand s Parameters gyűjteményhez, és hozzárendeli a price változó értékét.

A kód hozzáadása után telepítse újra az SQL Server Projectet. Ezután térjen vissza az SQL Server Management Studióba, és frissítse a Tárolt eljárások mappát. Látnia kell egy új bejegyzést, GetProductsWithPriceLessThan. Egy lekérdezési ablakban adja meg és hajtsa végre a parancsot exec GetProductsWithPriceLessThan 25, amely az összes 25 usd-nél kisebb terméket listázni fogja, ahogy a 14. ábra mutatja.

14. ábra: A 25 usd alatti termékek jelennek meg (kattintson ide a teljes méretű kép megtekintéséhez)

6. lépés: A felügyelt tárolt eljárás meghívása az adatelérési rétegből

Ezen a ponton hozzáadtuk a GetDiscontinuedProducts és GetProductsWithPriceLessThan kezelt tárolt eljárásokat a ManagedDatabaseConstructs projekthez, és regisztráltuk őket a Northwind SQL Server adatbázisban. Ezeket a felügyelt tárolt eljárásokat az SQL Server Management Studióból is meghívtuk (lásd a 13. és 14. ábrát). Ahhoz azonban, hogy az ASP.NET alkalmazás használja ezeket a felügyelt tárolt eljárásokat, hozzá kell adnunk őket az architektúra adathozzáférési és üzleti logikai rétegeihez. Ebben a lépésben két új módszert adunk hozzá a ProductsTableAdapterNorthwindWithSprocs típusos DataSet-hez, amelyet eredetileg a "Új tárolt eljárások létrehozása a típusos DataSet táblaadaptereihez" című oktatóanyagban hoztunk létre. A 7. lépésben megfelelő metódusokat adunk hozzá a BLL-hez.

Nyissa meg a NorthwindWithSprocs Beírt adathalmazt a Visual Studióban, és kezdje azzal, hogy hozzáad egy új metódust a ProductsTableAdapter nevesítetthez GetDiscontinuedProducts. Ha új metódust szeretne hozzáadni egy TableAdapterhez, kattintson a jobb gombbal a TableAdapter s nevére a Tervezőben, és válassza a helyi menü Lekérdezés hozzáadása parancsát.

Megjegyzés:

Mivel áthelyeztük a Northwind-adatbázist a App_Data mappából az SQL Server 2005 Express Edition-adatbázispéldányba, elengedhetetlen, hogy a megfelelő kapcsolati sztring Web.config frissüljön, hogy tükrözze ezt a változást. A 2. lépésben azt tárgyaltuk, hogy hogyan frissítsük a NORTHWNDConnectionString értéket a Web.config-ben. Ha elfelejtette végrehajtani ezt a frissítést, akkor a lekérdezés hozzáadása sikertelen volt hibaüzenet jelenik meg. Nem található kapcsolat NORTHWNDConnectionString az objektumhoz Web.config egy párbeszédpanelen, amikor új metódust próbál hozzáadni a TableAdapterhez. A hiba megoldásához kattintson az OK gombra, majd nyissa meg Web.config és frissítse az értéket a NORTHWNDConnectionString 2. lépésben ismertetett módon. Ezután próbálja meg újból hozzáadni a metódust a TableAdapterhez. Ezúttal hiba nélkül kell működnie.

Új metódus hozzáadása elindítja a TableAdapter Lekérdezéskonfiguráció varázslót, amelyet a korábbi oktatóanyagokban már sokszor használtunk. Az első lépésben meg kell határozzuk, hogy a TableAdapternek hogyan kell hozzáférnie az adatbázishoz: egy alkalmi SQL-utasításon keresztül, vagy egy új vagy meglévő tárolt eljáráson keresztül. Mivel már létrehoztuk és regisztráltuk a GetDiscontinuedProducts felügyelt tárolt eljárást az adatbázissal, válassza a Meglévő tárolt eljárás használata lehetőséget, és nyomja le a Tovább elemet.

15. ábra: Válassza a Meglévő tárolt eljárás használata lehetőséget (kattintson ide a teljes méretű kép megtekintéséhez)

A következő képernyő a metódus által meghívandó tárolt eljárás megadását kéri. Válassza ki a GetDiscontinuedProducts felügyelt tárolt eljárást a legördülő listából, és nyomja le a Tovább elemet.

16. ábra: Válassza ki a GetDiscontinuedProducts felügyelt tárolt eljárást (kattintson ide a teljes méretű kép megtekintéséhez)

Ezután meg kell határoznunk, hogy a tárolt eljárás sorokat, egyetlen értéket vagy semmit ad-e vissza. Mivel GetDiscontinuedProducts a megszűnt terméksorok készletét adja vissza, válassza az első lehetőséget ( Táblázatos adatok ) és kattintson a Tovább gombra.

17. ábra: Válassza ki a Táblázatos adatok lehetőséget (kattintson ide a teljes méretű kép megtekintéséhez)

A varázsló utolsó képernyője lehetővé teszi a használt adatelérési minták és az eredményként kapott metódusok nevének megadását. Hagyja bejelölve mindkét jelölőnégyzetet, és nevezze el a metódusokat FillByDiscontinued és GetDiscontinuedProductsa . A varázsló befejezéséhez kattintson a Befejezés gombra.

18. ábra: Nevezze el a metódusokat FillByDiscontinued , és GetDiscontinuedProducts (kattintson ide a teljes méretű kép megtekintéséhez)

Ismételje meg ezeket a lépéseket a FillByPriceLessThan felügyelt tárolt eljárás GetProductsWithPriceLessThan-ban a ProductsTableAdapter és GetProductsWithPriceLessThan nevű metódusok létrehozásához.

A 19. ábrán a DataSet Designer képernyőképe látható, miután hozzáadta a metódusokat a ProductsTableAdapterGetDiscontinuedProducts felügyelt és GetProductsWithPriceLessThan tárolt eljárásokhoz.

19. ábra: Az ProductsTableAdapter ebben a lépésben hozzáadott új metódusokat tartalmazza (kattintson ide a teljes méretű kép megtekintéséhez)

7. lépés: Megfelelő metódusok hozzáadása az üzleti logikai réteghez

Most, hogy frissítettük az adatelérési réteget, hogy a 4. és 5. lépésben hozzáadott felügyelt tárolt eljárások meghívására szolgáló metódusokat is tartalmazzon, hozzá kell adnunk a megfelelő metódusokat az üzleti logikai réteghez. Adja hozzá a következő két metódust az ProductsBLLWithSprocs osztályhoz:

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

Mindkét metódus egyszerűen meghívja a megfelelő DAL metódust, majd visszaadja a ProductsDataTable példányt. Az DataObjectMethodAttribute egyes metódusok feletti korrektúra miatt ezek a metódusok szerepelni fognak az ObjectDataSource Adatforrás konfigurálása varázsló SELECT lapján található legördülő listában.

8. lépés: Felügyelt tárolt eljárások meghívása a bemutató rétegből

Az üzleti logikát és az adatelérési rétegeket kibővítettük, hogy támogatást nyújtsunk a GetDiscontinuedProducts tárolt és GetProductsWithPriceLessThan a felügyelt eljárások meghívásához, mostantól egy ASP.NET lapon jeleníthetjük meg ezeket a tárolt eljárásokat.

Nyissa meg a ManagedFunctionsAndSprocs.aspx lapot a AdvancedDAL mappában, és az Eszközkészletből húzza a GridView-t a Tervezőre. Állítsa be a GridView ID tulajdonságát DiscontinuedProducts értékre, és intelligens címkéjével kösse hozzá egy új, DiscontinuedProductsDataSource nevű ObjectDataSource-hoz. Konfigurálja az ObjectDataSource-t, hogy lekérje az adatait az ProductsBLLWithSprocs osztály GetDiscontinuedProducts metódusából.

20. ábra: Az ObjectDataSource konfigurálása az ProductsBLLWithSprocs osztály használatára (kattintson ide a teljes méretű kép megtekintéséhez)

21. ábra: Válassza ki a metódust a GetDiscontinuedProducts SELECT lap Drop-Down listájából (kattintson ide a teljes méretű kép megtekintéséhez)

Mivel ez a rács csak a termékinformációk megjelenítésére szolgál, állítsa a legördülő listákat az UPDATE, INSERT és DELETE füleken a (Nincs) értékre, majd kattintson a Befejezés gombra.

A varázsló elvégzése után a Visual Studio automatikusan hozzáad egy BoundField vagy CheckBoxField mezőt a mező minden adatmezője számára.ProductsDataTable Szánjon egy kis időt az összes mező eltávolítására, kivéve a ProductName és Discontinued mezőket, amelyeknél a GridView és az ObjectDataSource deklaratív jelölése a következőhöz hasonlóan néz ki:

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

Szánjon egy kis időt a lap megtekintésére egy böngészőben. A lap megnyitásakor az ObjectDataSource meghívja az ProductsBLLWithSprocs osztály metódusát GetDiscontinuedProducts . Ahogy a 7. lépésben láttuk, ez a metódus lehívja a DAL s ProductsDataTable osztály s GetDiscontinuedProducts metódusát, amely meghívja a GetDiscontinuedProducts tárolt eljárást. Ez a tárolt eljárás egy felügyelt tárolt eljárás, és végrehajtja a 3. lépésben létrehozott kódot, visszaadva a megszűnt termékeket.

A felügyelt tárolt eljárás által visszaadott eredményeket a DAL csomagolja be, ProductsDataTable majd visszaadja a BLL-nek, majd visszaadja azokat a bemutató rétegnek, ahol a GridView-hoz vannak kötve és megjelennek. A vártnak megfelelően a táblázat felsorolja azokat a termékeket, amelyeket forgalomból kivontak.

22. ábra: A megszűnt termékek listája (kattintson ide a teljes méretű kép megtekintéséhez)

További gyakorlatként adjon hozzá egy TextBoxot és egy másik GridView-t a laphoz. A GridView jelenítse meg a szövegdobozba beírt mennyiségnél kisebb termékeket a ProductsBLLWithSprocs osztály GetProductsWithPriceLessThan metódusának meghívásával.

9. lépés: T-SQL UDF-ek létrehozása és meghívása

User-Defined Függvények vagy UDF-ek olyan adatbázis-objektumok, amelyek szorosan utánozzák a függvények szemantikáját a programozási nyelvekben. A C#-függvényekhez hasonlóan az UDF-ek is tartalmazhatnak változó számú bemeneti paramétert, és egy adott típusú értéket ad vissza. Az UDF skaláris adatokat – sztringet, egész számot és így tovább – vagy táblázatos adatokat adhat vissza. Vessünk egy gyors pillantást mindkét UDF-típusra, kezdve egy Skaláris adattípust visszaadó UDF-sel.

Az alábbi UDF kiszámítja egy adott termék készletének becsült értékét. Ezt úgy teszi, hogy egy adott termék három bemeneti paraméterét – a UnitPrice, UnitsInStockés Discontinued az értékeit – veszi figyelembe , és egy típusértéket moneyad vissza . Kiszámítja a leltár becsült értékét úgy, hogy a UnitPrice -t megszorozza a UnitsInStock -gyel. A megszűnt elemek esetében ez az érték felére csökken.

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

Miután ezt az UDF-et hozzáadták az adatbázishoz, a Management Studio-ban található a Programozhatóság mappára, majd a Functions-re, és végül a Scalar-érték Függvényekre kattintva. A következő módon használható egy SELECT lekérdezésben:

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

Hozzáadtam az udf_ComputeInventoryValue UDF-et a Northwind-adatbázishoz; A 23. ábra a fenti SELECT lekérdezés kimenetét mutatja a Management Studióban való megtekintéskor. Azt is vegye figyelembe, hogy az UDF az Object Explorer Scalar-value Functions mappájában található.

23. ábra: Az egyes termékek készletértékei listázva jelennek meg (ide kattintva megtekintheti a teljes méretű képet)

Az UDF-ek táblázatos adatokat is visszaadhatnak. Létrehozhatunk például egy UDF-t, amely egy adott kategóriához tartozó termékeket ad vissza:

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
)

Az udf_GetProductsByCategoryID UDF elfogad egy bemeneti paramétert @CategoryID , és visszaadja a megadott SELECT lekérdezés eredményeit. Miután létrehoztuk, erre az UDF-re a FROM (vagy JOIN) záradékban egy SELECT lekérdezés során lehet hivatkozni. Az alábbi példa az ProductIDProductNameegyes italok értékeit és CategoryID értékeit adja vissza.

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

Hozzáadtam az udf_GetProductsByCategoryID UDF-et a Northwind-adatbázishoz; A 24. ábrán a fenti SELECT lekérdezés kimenete látható a Management Studióban való megtekintéskor. A táblázatos adatokat visszaadó UDF-ek az Object Explorer Table-value Functions mappájában találhatók.

24. ábra: A ProductID, ProductNameés CategoryID listázva vannak az egyes italokhoz (kattintson ide a teljes méretű kép megtekintéséhez)

Megjegyzés:

Az UDF-ek létrehozásával és használatával kapcsolatos további információkért tekintse meg a Függvények User-Defined Intro című témakört. Tekintse meg a User-Defined Functions előnyeit és hátrányait is.

10. lépés: Felügyelt UDF létrehozása

A udf_ComputeInventoryValue és udf_GetProductsByCategoryID UDF-ek, amelyeket a fenti példákban hoztak létre, T-SQL adatbázisobjektumok. Az SQL Server 2005 támogatja a felügyelt UDF-eket is, amelyek ugyanúgy hozzáadhatók a ManagedDatabaseConstructs projekthez, mint a 3. és 5. lépésben ismertetett felügyelt tárolt eljárások. Ebben a lépésben implementáljuk az UDF-et a udf_ComputeInventoryValue felügyelt kódban.

Ha felügyelt UDF-et szeretne hozzáadni a ManagedDatabaseConstructs projekthez, kattintson a jobb gombbal a projekt nevére a Megoldáskezelőben, és válassza az Új elem hozzáadása lehetőséget. Válassza ki a User-Defined sablont az Új elem hozzáadása párbeszédpanelen, és nevezze el az új UDF-fájlt udf_ComputeInventoryValue_Managed.cs.

25. ábra: Új felügyelt UDF hozzáadása a projekthez (ManagedDatabaseConstructs teljes méretű képet)

A User-Defined függvénysablon létrehoz egy partial metódussal elnevezett UserDefinedFunctions osztályt, amelynek neve megegyezik az osztályfájl nevével (udf_ComputeInventoryValue_Managedebben a példányban). Ezt a metódust az SqlFunction attribútum díszíti, amely felügyelt UDF-ként jelöli a metódust.

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

A udf_ComputeInventoryValue metódus jelenleg egy objektumotSqlString ad vissza, és nem fogad el bemeneti paramétereket. Frissíteni kell a metódusdefiníciót, hogy három bemeneti paramétert fogadjon el – UnitPriceUnitsInStockés Discontinued egy objektumot SqlMoney adjon vissza. A leltárérték kiszámításának logikája megegyezik a T-SQL udf_ComputeInventoryValue UDF-ben lévővel.

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

Vegye figyelembe, hogy az UDF-metódus bemeneti paraméterei a megfelelő SQL-típusok: SqlMoney a UnitPrice mező, SqlInt16 a UnitsInStock, és SqlBoolean a Discontinued. Ezek az adattípusok a táblázatban definiált Products típusokat tükrözik: az UnitPrice oszlop típusa money, a UnitsInStock típus smallintoszlopa és a Discontinued típus bitoszlopa.

A kód egy SqlMoney példányt hoz létre, amelynek neve inventoryValue, és értéke 0. A Products táblázat lehetővé teszi az adatbázis NULL értékeit a UnitsInPrice és UnitsInStock oszlopokban. Ezért először ellenőrizni kell, hogy ezek az értékek tartalmaznak-e NULL elemeket, amit az SqlMoney objektum tulajdonságánIsNull keresztül végzünk el. Ha mindkettő UnitPrice és UnitsInStock nemNULL értékeket is tartalmaz, akkor kiszámítjuk, inventoryValue hogy a kettő szorzata legyen. Ezután, ha Discontinued igaz, akkor felére csökkentjük az értéket.

Megjegyzés:

Az SqlMoney objektum csak két SqlMoney példány szorzatát teszi lehetővé. Nem teszi lehetővé, hogy egy SqlMoney példányt lebegőpontos számmal szorozzanak meg. Ezért a felezéséhez inventoryValue megszorozzuk egy új SqlMoney példánnyal, amelynek értéke 0,5.

11. lépés: A felügyelt UDF üzembe helyezése

Most, hogy létrejött a felügyelt UDF, készen állunk arra, hogy üzembe helyezzük a Northwind-adatbázisban. Ahogy a 4. lépésben láttuk, az SQL Server-projekt felügyelt objektumai a jobb gombbal a megoldáskezelőben a projekt nevére kattintva és a helyi menü Üzembe helyezés lehetőségének kiválasztásával lesznek üzembe helyezve.

A projekt üzembe helyezése után térjen vissza az SQL Server Management Studióba, és frissítse a skaláris értékű Functions mappát. Most két bejegyzésnek kell megjelennie:

• dbo.udf_ComputeInventoryValue - a 9. lépésben létrehozott T-SQL UDF, és
• dbo.udf ComputeInventoryValue_Managed - az imént üzembe helyezett 10. lépésben létrehozott felügyelt UDF.

A felügyelt UDF teszteléséhez hajtsa végre a következő lekérdezést a Management Studióban:

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

Ez a parancs a felügyelt udf ComputeInventoryValue_Managed UDF-t használja a T-SQL udf_ComputeInventoryValue UDF helyett, de a kimenet ugyanaz. A UDF kimenetének képernyőképét a 23. ábrán tekinthet meg.

12. lépés: A felügyelt adatbázis-objektumok hibakeresése

A Tárolt eljárások hibakeresése oktatóanyagban az SQL Server Visual Studión keresztüli hibakeresésének három lehetőségét tárgyaltuk: közvetlen adatbázis-hibakeresést, alkalmazáskeresést és hibakeresést egy SQL Server-projektből. A felügyelt adatbázis-objektumok nem háríthatók el a közvetlen adatbázis-hibakereséssel, de lehet hibakeresést végezni egy ügyfélalkalmazásból és közvetlenül az SQL Server-projektből. A hibakeresés működéséhez azonban az SQL Server 2005-adatbázisnak engedélyeznie kell az SQL/CLR hibakeresést. Ne feledje, hogy amikor először létrehoztuk a projektet, a ManagedDatabaseConstructs Visual Studio megkérdezte, hogy engedélyezni szeretnénk-e az SQL/CLR hibakeresést (lásd a 2. lépés 6. ábráját). Ez a beállítás módosítható úgy, hogy a kiszolgálókezelő ablakából a jobb gombbal az adatbázisra kattint.

26. ábra: Győződjön meg arról, hogy az adatbázis engedélyezi az SQL/CLR hibakeresését

Képzeljük el, hogy a GetProductsWithPriceLessThan felügyelt tárolt eljárást szeretnénk hibakeresni. Először is állítsunk be egy töréspontot a GetProductsWithPriceLessThan metódus kódjába.

27. ábra: Töréspont beállítása a GetProductsWithPriceLessThan metódusban (kattintson ide a teljes méretű kép megtekintéséhez)

Először nézzük meg a felügyelt adatbázis-objektumok hibakeresését az SQL Server Projectből. Mivel a megoldás két projektet is tartalmaz – az ManagedDatabaseConstructs SQL Server Projectet és a webhelyet – az SQL Server Project hibakereséséhez meg kell utasítanunk a Visual Studiót, hogy indítsa el az SQL Server-projektet a ManagedDatabaseConstructs hibakeresés megkezdésekor. Kattintson a jobb gombbal a projektre a ManagedDatabaseConstructs Megoldáskezelőben, és válassza a Helyi menüBen a Set as StartUp Project (Indítási projekt beállítása) lehetőséget.

Amikor a ManagedDatabaseConstructs projekt elindul a hibakeresőből, végrehajtja a Test.sql fájlban lévő SQL-utasításokat, amelyek a Test Scripts mappában találhatók. Például a felügyelt tárolt eljárás teszteléséhez cserélje le a meglévő GetProductsWithPriceLessThan fájltartalmat a következő utasításra, amely meghívja a Test.sql felügyelt tárolt eljárást, és 14,95 értéket ad át a GetProductsWithPriceLessThan paraméterként.

exec GetProductsWithPriceLessThan 14.95

Miután a fent említett szkriptet beírta a Test.sql-be, kezdje el a hibakeresést a Hibakeresés menüben az Indítsa el a hibakeresést lehetőséget választva, vagy az F5 billentyű megnyomásával, illetve az eszköztár zöld lejátszás ikonja megnyomásával. Ez létrehozza a projekteket a Megoldásban, üzembe helyezi a felügyelt adatbázis-objektumokat a Northwind-adatbázisban, majd végrehajtja a Test.sql szkriptet. Ezen a ponton a töréspontot eléri a rendszer, és végiglépkedhetünk a GetProductsWithPriceLessThan metóduson, megvizsgálhatjuk a bemeneti paraméterek értékeit stb.

28. ábra: A metódus töréspontja GetProductsWithPriceLessThan találatot kapott (kattintson ide a teljes méretű kép megtekintéséhez)

Ahhoz, hogy egy SQL-adatbázis-objektumot egy ügyfélalkalmazáson keresztül lehessen hibakeresésre beállítani, elengedhetetlen, hogy az adatbázis az alkalmazás hibakeresésének támogatására legyen konfigurálva. Kattintson a jobb gombbal az adatbázisra a Kiszolgálókezelőben, és győződjön meg arról, hogy az alkalmazás hibakeresése jelölőnégyzet be van jelölve. Emellett konfigurálnunk kell a ASP.NET alkalmazást, hogy integrálható legyen az SQL Debuggerrel, és letiltsuk a kapcsolatkészletezést. Ezeket a lépéseket részletesen ismertette a Tárolt eljárások hibakeresése oktatóanyag 2. lépésében.

Miután konfigurálta a ASP.NET alkalmazást és adatbázist, állítsa be a ASP.NET webhelyet indítási projektként, és kezdje el a hibakeresést. Ha egy olyan lapot keres fel, amely a törésponttal rendelkező felügyelt objektumok egyikét hívja meg, az alkalmazás leáll, és a vezérlő át lesz kapcsolva a hibakeresőnek, ahol a 28. ábrán látható módon haladhat végig a kódon.

13. lépés: Felügyelt adatbázis-objektumok manuális összeállítása és üzembe helyezése

Az SQL Server-projektek megkönnyítik a felügyelt adatbázis-objektumok létrehozását, fordítását és üzembe helyezését. Sajnos az SQL Server-projektek csak a Visual Studio Professional és Team Systems kiadásaiban érhetők el. Ha a Visual Web Developert vagy a Visual Studio Standard Kiadását használja, és felügyelt adatbázis-objektumokat szeretne használni, manuálisan kell létrehoznia és üzembe helyeznie őket. Ez négy lépésből áll:

1. Hozzon létre egy fájlt, amely tartalmazza a felügyelt adatbázis-objektum forráskódját,
2. Állítsa össze az objektumot egy szerelvénybe,
3. Regisztrálja az assemblyt az SQL Server 2005-adatbázissal, és
4. Hozzon létre egy adatbázis-objektumot az SQL Serverben, amely a szerelvény megfelelő metódusára mutat.

A feladatok szemléltetéséhez hozzunk létre egy új felügyelt tárolt eljárást, amely visszaadja azokat a termékeket, amelyek UnitPrice nagyobbak egy megadott értéknél. Hozzon létre egy új fájlt a számítógépen, és GetProductsWithPriceGreaterThan.cs írja be a következő kódot a fájlba (ehhez használhatja a Visual Studiót, a Jegyzettömbet vagy bármely szövegszerkesztőt):

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

Ez a kód majdnem megegyezik az GetProductsWithPriceLessThan 5. lépésben létrehozott metódus kóddal. Az egyetlen különbség a metódusnevek, a WHERE záradék és a lekérdezésben használt paraméternév. A GetProductsWithPriceLessThan metódusban a WHERE záradék így szólt: WHERE UnitPrice < @MaxPrice. Itt a következőt GetProductsWithPriceGreaterThanhasználjuk: WHERE UnitPrice > @MinPrice .

Most le kell fordítanunk ezt az osztályt egy assembly-be. A parancssorból navigáljon arra a könyvtárra, ahová a GetProductsWithPriceGreaterThan.cs fájlt mentette, és az osztályfájlt a C# fordító használatával fordítsa le assemblybe:

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

Ha a mappa, amely csc.exe-t tartalmaz, nincs a rendszer PATH-ben, akkor teljes mértékben meg kell adnia az elérési útját %WINDOWS%\Microsoft.NET\Framework\version\, a következőképpen:

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

29. ábra: Fordítás GetProductsWithPriceGreaterThan.cs szerelvénybe (ide kattintva megtekintheti a teljes méretű képet)

A /t jelző azt határozza meg, hogy a C#-osztályfájlt dll-fájlba kell lefordítani (nem végrehajthatóvá). A /out jelölő az eredményként kapott szerelvény nevét adja meg.

Megjegyzés:

Az osztályfájl parancssorból való GetProductsWithPriceGreaterThan.cs összeállítása helyett használhatja a Visual C# Express Editiont , vagy létrehozhat egy külön Osztálytár projektet a Visual Studio Standard Edition kiadásban. S ren Jacob Lauritsen kedvesen biztosított egy ilyen Visual C# Express Edition projektet a GetProductsWithPriceGreaterThan tárolt eljárás kódjával, valamint a 3., 5. és 10. lépésben létrehozott két felügyelt tárolt eljárással és UDF-sel. Az S ren s projekt tartalmazza a megfelelő adatbázis-objektumok hozzáadásához szükséges T-SQL-parancsokat is.

Az elkészült assembly kóddal készen állunk az assembly regisztrálására az SQL Server 2005 adatbázisban. Ez a T-SQL-en keresztül, a parancs CREATE ASSEMBLYhasználatával vagy az SQL Server Management Studióval is elvégezhető. Koncentráljunk a Management Studio használatára.

A Management Studióban bontsa ki a Programozhatóság mappát a Northwind-adatbázisban. Az egyik almappája a Szerelvények. Ha manuálisan szeretne hozzáadni egy új szerelvényt az adatbázishoz, kattintson a jobb gombbal a Szerelvények mappára, és válassza az Új szerelvény lehetőséget a helyi menüből. Ekkor megjelenik az Új szerelvény párbeszédpanel (lásd a 30. ábrát). Kattintson a Tallózás gombra, válassza ki az ManuallyCreatedDBObjects.dll összeállítást, amit éppen lefordítottunk, és az OK gombra kattintva adja hozzá az összeállítást az adatbázishoz. Ne lássuk a ManuallyCreatedDBObjects.dll szerelvényt az Objektumkezelőben.

30. ábra: A szerelvény hozzáadása ManuallyCreatedDBObjects.dll az adatbázishoz (ide kattintva megtekintheti a teljes méretű képet)

31. ábra: A ManuallyCreatedDBObjects.dll lista az Objektumkezelőben található

Bár hozzáadtuk a szerelvényt a Northwind-adatbázishoz, még nem sikerült társítanunk a tárolt eljárást a GetProductsWithPriceGreaterThan metódushoz a szerelvényben. Ehhez nyisson meg egy új lekérdezési ablakot, és hajtsa végre a következő szkriptet:

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

Ez létrehoz egy új tárolt eljárást a Northwind-adatbázisban nevesített GetProductsWithPriceGreaterThan és a felügyelt metódushoz GetProductsWithPriceGreaterThan társítja (amely a StoredProceduresszerelvény ManuallyCreatedDBObjectsosztályában található).

A fenti szkript végrehajtása után frissítse a Tárolt eljárások mappát az Object Explorerben. Látnia kell egy új tárolt eljárás bejegyzést , GetProductsWithPriceGreaterThan amely mellett egy zárolási ikon található. A tárolt eljárás teszteléséhez írja be és hajtsa végre a következő szkriptet a lekérdezési ablakban:

exec GetProductsWithPriceGreaterThan 24.95

A 32. ábrán látható, hogy a fenti parancs a 24,95 USD-nél nagyobb termékekkel UnitPrice kapcsolatos információkat jeleníti meg.

32. ábra: A ManuallyCreatedDBObjects.dll lista az Objektumkezelőben található (ide kattintva megtekintheti a teljes méretű képet)

Összefoglalás

A Microsoft SQL Server 2005 integrációt biztosít a Common Language Runtime (CLR) szolgáltatással, amely lehetővé teszi az adatbázis-objektumok felügyelt kóddal történő létrehozását. Korábban ezek az adatbázis-objektumok csak T-SQL használatával hozhatók létre, de most már létrehozhatjuk ezeket az objektumokat .NET-programozási nyelvekkel, például C#-tal. Ebben az oktatóanyagban két felügyelt tárolt eljárást és egy felügyelt User-Defined függvényt hoztunk létre.

A Visual Studio SQL Server Project-típusa megkönnyíti a felügyelt adatbázis-objektumok létrehozását, összeállítását és üzembe helyezését. Emellett gazdag hibakeresési támogatást is kínál. Az SQL Server-projekttípusok azonban csak a Visual Studio Professional és Team Systems kiadásaiban érhetők el. A Visual Web Developert vagy a Visual Studio Standard Kiadását használó felhasználók esetében a létrehozási, fordítási és üzembe helyezési lépéseket manuálisan kell végrehajtani, ahogyan azt a 13. lépésben láttuk.

Boldog programozást!

További olvasás

Az oktatóanyagban tárgyalt témakörökről az alábbi forrásokban talál további információt:

• A User-Defined Functions előnyei és hátrányai
• SQL Server 2005-objektumok létrehozása felügyelt kódban
• Útmutató: CLR SQL Server tárolt eljárás létrehozása és futtatása
• Útmutató: CLR SQL Server User-Defined függvény létrehozása és futtatása
• Útmutató: A Test.sql szkript szerkesztése SQL-objektumok futtatásához
• Bevezetés a felhasználó által definiált függvényekbe
• Kezelt kód és SQL Server 2005 (videó)
• Transact-SQL referencia
• Útmutató: Tárolt eljárás létrehozása felügyelt kódban

Tudnivalók a szerzőről

Scott Mitchell, hét ASP/ASP.NET-könyv szerzője és a 4GuysFromRolla.com alapítója, 1998 óta dolgozik a Microsoft webtechnológiáival. Scott független tanácsadóként, edzőként és íróként dolgozik. Legújabb könyve Sams Tanuld meg ASP.NET 2.0 24 óra alatt. Ő itt elérhető mitchell@4GuysFromRolla.com.

Külön köszönet

Ezt az oktatóanyag-sorozatot sok hasznos véleményező áttekintette. Az oktatóanyag vezető véleményezője S ren Jacob Lauritsen volt. A cikk áttekintése mellett S ren a jelen cikk letöltésében szereplő Visual C# Express Edition projektet is létrehozta a felügyelt adatbázis-objektumok manuális összeállításához. Szeretné áttekinteni a közelgő MSDN-cikkeimet? Ha igen, írj egy sort a mitchell@4GuysFromRolla.com-ra.

ElőzőKövetkező