Skapa lagrade procedurer och User-Defined functions med hanterad kod (C#)

av Scott Mitchell

Ladda ned PDF

Microsoft SQL Server 2005 integreras med .NET Common Language Runtime så att utvecklare kan skapa databasobjekt via hanterad kod. Den här handledningen visar hur du skapar hanterade lagrade procedurer och hanterade användardefinierade funktioner med hjälp av din kod i Visual Basic eller C#. Vi ser också hur dessa utgåvor av Visual Studio gör att du kan felsöka sådana hanterade databasobjekt.

Inledning

Databaser som Microsofts SQL Server 2005 använder Transact-Structured Query Language (T-SQL) för att infoga, ändra och hämta data. De flesta databassystem innehåller konstruktioner för att gruppera en serie SQL-instruktioner som sedan kan köras som en enda återanvändbar enhet. Lagrade procedurer är ett exempel. En annan är User-Defined Functions (UDF: er), en konstruktion som vi kommer att undersöka i detalj i steg 9.

I grunden är SQL utformat för att arbeta med datauppsättningar. - SELECToch UPDATEDELETE -uttrycken gäller i sig för alla poster i motsvarande tabell och begränsas endast av deras WHERE satser. Ändå finns det många språkfunktioner som är utformade för att arbeta med en post i taget och för att manipulera skalära data. CURSOR s tillåter att en uppsättning register itereras genom en i taget. Strängmanipuleringsfunktioner som LEFT, CHARINDEXoch PATINDEX fungerar med skalära data. SQL innehåller även kontrollflödesinstruktioner som IF och WHILE.

Före Microsoft SQL Server 2005 kunde lagrade procedurer och UDF:er endast definieras som en samling T-SQL-instruktioner. SQL Server 2005 har dock utformats för att ge integrering med Common Language Runtime (CLR) som är den körning som används av alla .NET-sammansättningar. Därför kan lagrade procedurer och UDF:er i en SQL Server 2005-databas skapas med hjälp av hanterad kod. Du kan alltså skapa en lagrad procedur eller UDF som en metod i en C#-klass. Detta gör att dessa lagrade procedurer och UDF:er kan använda funktioner i .NET Framework och från dina egna anpassade klasser.

I den här självstudien kommer vi att undersöka hur du skapar hanterade lagrade procedurer och User-Defined Functions och hur du integrerar dem i vår Northwind-databas. Låt oss komma igång!

Anmärkning

Hanterade databasobjekt har vissa fördelar jämfört med sina SQL-motsvarigheter. Språkrikhet och kunskap och möjligheten att återanvända befintlig kod och logik är de största fördelarna. Men hanterade databasobjekt är sannolikt mindre effektiva när du arbetar med datauppsättningar som inte innehåller mycket procedurlogik. En mer ingående diskussion om fördelarna med att använda hanterad kod jämfört med T-SQL finns i Fördelarna med att använda hanterad kod för att skapa databasobjekt.

Steg 1: Flytta Northwind-databasen från App_Data

Alla våra självstudier hittills har använt en Microsoft SQL Server 2005 Express Edition-databasfil i webbapplikationens mapp App_Data. Att placera databasen i App_Data förenklad distribution och körning av dessa självstudier eftersom alla filer fanns i en katalog och inte krävde några ytterligare konfigurationssteg för att testa självstudien.

I den här självstudien ska vi dock flytta Northwind-databasen från App_Data och uttryckligen registrera den med SQL Server 2005 Express Edition-databasinstansen. Även om vi kan utföra stegen för den här självstudien med databasen i App_Data mappen, blir ett antal av stegen mycket enklare genom att uttryckligen registrera databasen med SQL Server 2005 Express Edition-databasinstansen.

Nedladdningen för den här självstudien innehåller de två databasfilerna – NORTHWND.MDF och NORTHWND_log.LDF – placerade i en mapp med namnet DataFiles. Om du följer med i din egen implementering av handledningarna, stänger du Visual Studio och flyttar NORTHWND.MDF och NORTHWND_log.LDF filerna från webbplatsens App_Data mapp till en mapp utanför webbplatsen. När databasfilerna har flyttats till en annan mapp måste vi registrera Northwind-databasen med SQL Server 2005 Express Edition-databasinstansen. Detta kan göras från SQL Server Management Studio. Om du har en icke-Express Edition av SQL Server 2005 installerad på datorn har du förmodligen redan Management Studio installerat. Om du bara har SQL Server 2005 Express Edition på datorn kan du ladda ned och installera Microsoft SQL Server Management Studio.

Starta SQL Server Management Studio. Som bild 1 visar börjar Management Studio med att fråga vilken server som ska anslutas till. Ange localhost\SQLExpress som servernamn, välj Windows-autentisering i listrutan Autentisering och klicka på Anslut.

Bild 1: Anslut till lämplig databasinstans

När du har anslutit visar fönstret Object Explorer information om SQL Server 2005 Express Edition-databasinstansen, inklusive dess databaser, säkerhetsinformation, hanteringsalternativ och så vidare.

Vi måste bifoga Northwind-databasen i DataFiles mappen (eller var du än har flyttat den) till SQL Server 2005 Express Edition-databasinstansen. Högerklicka på mappen Databaser och välj alternativet Bifoga på snabbmenyn. Då visas dialogrutan Bifoga databaser. Klicka på knappen Lägg till, öka detaljnivån till lämplig NORTHWND.MDF fil och klicka på OK. Nu bör skärmen se ut ungefär som i bild 2.

Bild 2: Anslut till lämplig databasinstans (Klicka om du vill visa en bild i full storlek)

Anmärkning

När du ansluter till SQL Server 2005 Express Edition-instansen via Management Studio kan du inte navigera i katalogerna för användarprofiler, till exempel Mina Dokument. Se därför till att placera NORTHWND.MDF filerna och NORTHWND_log.LDF i en icke-användarprofilkatalog.

Klicka på ok-knappen för att bifoga databasen. Dialogrutan Bifoga databaser stängs och Objektutforskaren bör nu visa en lista över den just anslutna databasen. Risken är stor att Northwind-databasen har ett namn som 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF. Byt namn på databasen till Northwind genom att högerklicka på databasen och välja Byt namn.

Bild 3: Byt namn på databasen till Northwind

Steg 2: Skapa en ny lösning och ETT SQL Server-projekt i Visual Studio

För att skapa hanterade lagrade procedurer eller UDF:er i SQL Server 2005 skriver vi den lagrade proceduren och UDF-logiken som C#-kod i en klass. När koden har skrivits måste vi kompilera den här klassen till en sammansättning (en .dll fil), registrera sammansättningen med SQL Server-databasen och sedan skapa en lagrad procedur eller ett UDF-objekt i databasen som pekar på motsvarande metod i sammansättningen. Alla dessa steg kan utföras manuellt. Vi kan skapa koden i valfri textredigerare, kompilera den från kommandoraden med C#-kompilatorn (csc.exe), registrera den med databasen med kommandot CREATE ASSEMBLY eller från Management Studio och lägga till den lagrade proceduren eller UDF-objektet på liknande sätt. Lyckligtvis innehåller professional- och teamsystemversionerna av Visual Studio en SQL Server-projekttyp som automatiserar dessa uppgifter. I den här självstudien går vi igenom hur du använder SQL Server Project-typen för att skapa en hanterad lagrad procedur och UDF.

Anmärkning

Om du använder Visual Web Developer eller Standard-versionen av Visual Studio måste du använda den manuella metoden i stället. Steg 13 innehåller detaljerade instruktioner för att utföra dessa steg manuellt. Jag rekommenderar att du läser steg 2 till och med 12 innan du läser steg 13 eftersom dessa steg innehåller viktiga konfigurationsinstruktioner för SQL Server som måste tillämpas oavsett vilken version av Visual Studio du använder.

Börja med att öppna Visual Studio. På menyn Arkiv väljer du Nytt projekt för att visa dialogrutan Nytt projekt (se bild 4). Öka detaljnivån till projekttypen Databas och välj sedan att skapa ett nytt SQL Server-projekt från mallarna som visas till höger. Jag har valt att namnge det här projektet ManagedDatabaseConstructs och placerat det i en lösning med namnet Tutorial75.

Bild 4: Skapa ett nytt SQL Server-projekt (Klicka om du vill visa en bild i full storlek)

Klicka på KNAPPEN OK i dialogrutan Nytt projekt för att skapa lösningen och SQL Server-projektet.

Ett SQL Server-projekt är kopplat till en viss databas. När vi har skapat det nya SQL Server-projektet uppmanas vi därför omedelbart att ange den här informationen. Bild 5 visar dialogrutan Ny databasreferens som har fyllts i för att peka på den Northwind-databas som vi registrerade i SQL Server 2005 Express Edition-databasinstansen i steg 1.

Bild 5: Associera SQL Server-projektet med Northwind Database

För att kunna felsöka hanterade lagrade procedurer och UDF:er som vi skapar i det här projektet måste vi aktivera SQL/CLR-felsökningsstöd för anslutningen. När du associerar ett SQL Server-projekt med en ny databas (som vi gjorde i bild 5) frågar Visual Studio oss om vi vill aktivera SQL/CLR-felsökning på anslutningen (se bild 6). Klicka på Ja.

Bild 6: Aktivera SQL/CLR-felsökning

Nu har det nya SQL Server-projektet lagts till i lösningen. Den innehåller en mapp med namnet Test Scripts med en fil med namnet Test.sql, som används för att felsöka de hanterade databasobjekt som skapats i projektet. Vi ska titta på felsökning i steg 12.

Vi kan nu lägga till nya hanterade lagrade procedurer och UDF:er i det här projektet, men innan vi gör det ska vi först inkludera vårt befintliga webbprogram i lösningen. På menyn Arkiv väljer du alternativet Lägg till och väljer Befintlig webbplats. Bläddra till lämplig webbplatsmapp och klicka på OK. Som bild 7 visar uppdaterar detta lösningen så att den innehåller två projekt: webbplatsen och ManagedDatabaseConstructs SQL Server-projektet.

Bild 7: Solution Explorer innehåller nu två projekt

Värdet NORTHWNDConnectionString i Web.config refererar för närvarande till NORTHWND.MDF filen i App_Data mappen. Eftersom vi tog bort den här databasen från App_Data och uttryckligen registrerade den i SQL Server 2005 Express Edition-databasinstansen måste vi uppdatera värdet på NORTHWNDConnectionString motsvarande sätt. Web.config Öppna filen på webbplatsen och ändra NORTHWNDConnectionString värdet så att anslutningssträngen lyder: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. Efter den här ändringen bör avsnittet <connectionStrings> i Web.config se ut ungefär så här:

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

Anmärkning

Som beskrivs i föregående självstudie måste vi inaktivera anslutningspooler när du felsöker ett SQL Server-objekt från ett klientprogram, till exempel en ASP.NET webbplats. Anslutningssträngen som visas ovan inaktiverar anslutningspooler ( Pooling=false ). Om du inte planerar att felsöka hanterade lagrade procedurer och UDF:er från ASP.NET-webbplatsen, aktiverar du anslutningspoolning.

Steg 3: Skapa en hanterad lagrad procedur

För att lägga till en hanterad lagrad procedur i Northwind-databasen måste vi först skapa den lagrade proceduren som en metod i SQL Server-projektet. Högerklicka på ManagedDatabaseConstructs projektnamnet i Solution Explorer och välj att lägga till ett nytt objekt. Då visas dialogrutan Lägg till nytt objekt, som visar en lista över de typer av hanterade databasobjekt som kan läggas till i projektet. Som bild 8 visar innehåller detta bland annat lagrade procedurer och User-Defined Functions.

Låt oss börja med att lägga till en lagrad procedur som enkelt returnerar alla produkter som har utgått. Namnge den nya lagrade procedurfilen GetDiscontinuedProducts.cs.

Bild 8: Lägg till en ny lagrad procedur med namnet GetDiscontinuedProducts.cs (Klicka om du vill visa en bild i full storlek)

Då skapas en ny C#-klassfil med följande innehåll:

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

Observera att den lagrade proceduren implementeras som en static metod i en partial klassfil med namnet StoredProcedures. Metoden GetDiscontinuedProducts är dessutom dekorerad med SqlProcedure attribute, vilket markerar metoden som en lagrad procedur.

Följande kod skapar ett SqlCommand objekt och anger dess CommandText till en SELECT fråga som returnerar alla kolumner från Products tabellen för produkter vars Discontinued fält är lika med 1. Sedan körs kommandot och resultatet skickas tillbaka till klientprogrammet. Lägg till den här koden i GetDiscontinuedProducts metoden.

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

Alla hanterade databasobjekt har åtkomst till ett SqlContext objekt som representerar anroparens kontext. SqlContext ger tillgång till ett SqlPipe-objekt via dess Pipe-egenskap. Det här SqlPipe objektet används för att skicka information mellan SQL Server-databasen och det anropande programmet. Som namnet antyder ExecuteAndSend kör metoden ett inskickade SqlCommand objekt och skickar resultatet tillbaka till klientprogrammet.

Anmärkning

Hanterade databasobjekt passar bäst för lagrade procedurer och UDF:er som använder procedurlogik i stället för set-baserad logik. Procedurlogik innebär att arbeta med datauppsättningar rad för rad eller arbeta med skalära data. Den GetDiscontinuedProducts metod som vi nyss skapade omfattar dock ingen procedurlogik. Därför skulle den helst implementeras som en T-SQL-lagrad procedur. Den implementeras som en hanterad lagrad procedur för att demonstrera de steg som krävs för att skapa och distribuera hanterade lagrade procedurer.

Steg 4: Implementera den hanterade lagrade proceduren

När den här koden är klar är vi redo att distribuera den till Northwind-databasen. När du distribuerar ett SQL Server-projekt kompileras koden till en sammansättning, sammansättningen registreras med databasen och motsvarande objekt skapas i databasen och de länkas till lämpliga metoder i sammansättningen. Den exakta uppsättningen uppgifter som utförs av alternativet Distribuera beskrivs mer exakt i steg 13. Högerklicka på ManagedDatabaseConstructs projektnamnet i Solution Explorer och välj alternativet Distribuera. Distributionen misslyckas dock med följande fel: Felaktig syntax nära "EXTERNAL". Du kan behöva ange kompatibilitetsnivån för den aktuella databasen till ett högre värde för att aktivera den här funktionen. Se hjälpen för den lagrade proceduren sp_dbcmptlevel.

Det här felmeddelandet uppstår när du försöker registrera sammansättningen med Northwind-databasen. För att kunna registrera en sammansättning med en SQL Server 2005-databas måste databasens kompatibilitetsnivå vara inställd på 90. Som standard har nya SQL Server 2005-databaser en kompatibilitetsnivå på 90. Databaser som skapats med Microsoft SQL Server 2000 har dock en standardkompatibilitetsnivå på 80. Eftersom Northwind-databasen ursprungligen var en Microsoft SQL Server 2000-databas är dess kompatibilitetsnivå för närvarande inställd på 80 och måste därför ökas till 90 för att kunna registrera hanterade databasobjekt.

Om du vill uppdatera databasens kompatibilitetsnivå öppnar du ett nytt frågefönster i Management Studio och anger:

exec sp_dbcmptlevel 'Northwind', 90

Klicka på ikonen Kör i verktygsfältet för att köra ovanstående fråga.

Bild 9: Uppdatera Northwind Database-kompatibilitetsnivån (klicka om du vill visa en bild i full storlek)

När du har uppdaterat kompatibilitetsnivån distribuerar du om SQL Server-projektet. Den här gången ska distributionen slutföras utan fel.

Gå tillbaka till SQL Server Management Studio, högerklicka på Northwind-databasen i Object Explorer och välj Uppdatera. Öka sedan detaljnivån i mappen Programmability och expandera sedan mappen Sammansättningar. Som bild 10 visar innehåller Northwind-databasen nu den sammansättning som genereras av ManagedDatabaseConstructs projektet.

Bild 10: ManagedDatabaseConstructs Sammansättningen är nu registrerad med Northwind-databasen

Expandera även mappen Lagrade procedurer. Där visas en lagrad procedur med namnet GetDiscontinuedProducts. Den här lagrade proceduren skapades av distributionsprocessen och pekar på GetDiscontinuedProducts metoden i ManagedDatabaseConstructs sammansättningen. När den GetDiscontinuedProducts lagrade proceduren körs, körs i sin tur metoden GetDiscontinuedProducts. Eftersom det här är en hanterad lagrad procedur kan den inte redigeras via Management Studio (därav låsikonen bredvid namnet på den lagrade proceduren).

Bild 11: Den GetDiscontinuedProducts lagrade proceduren visas i mappen Lagrade procedurer

Det finns fortfarande ytterligare ett hinder som vi måste övervinna innan vi kan anropa den hanterade lagrade proceduren: databasen är konfigurerad för att förhindra körning av hanterad kod. Kontrollera detta genom att öppna ett nytt frågefönster och köra den GetDiscontinuedProducts lagrade proceduren. Du får följande felmeddelande: Körning av användarkod i .NET Framework är inaktiverat. Aktivera konfigurationsalternativet "clr enabled".

Om du vill undersöka konfigurationsinformationen för Northwind-databasen anger du och kör kommandot exec sp_configure i frågefönstret. Detta visar att den clr-aktiverade inställningen för närvarande är inställd på 0.

Bild 12: Den clr-aktiverade inställningen är för närvarande inställd på 0 (Klicka om du vill visa en bild i full storlek)

Observera att varje konfigurationsinställning i bild 12 har fyra värden listade med den: lägsta och högsta värden samt konfigurations- och körningsvärdena. Kör följande kommando för att uppdatera konfigurationsvärdet för den clr-aktiverade inställningen:

exec sp_configure 'clr enabled', 1

Om du kör exec sp_configure igen ser du att ovanstående påstående uppdaterade konfigurationsvärdet för clr-aktiverad inställning till 1, men att körningsvärdet fortfarande är inställt på 0. För att den här konfigurationsändringen ska påverka måste vi köra RECONFIGURE kommandot, som anger körningsvärdet till det aktuella konfigurationsvärdet. Ange helt enkelt RECONFIGURE i frågefönstret och klicka på ikonen Kör i verktygsfältet. Om du kör exec sp_configure nu bör du se värdet 1 för den clr-aktiverade inställningens konfigurations- och körningsvärden.

När den clr-aktiverade konfigurationen är klar är vi redo att köra den hanterade GetDiscontinuedProducts lagrade proceduren. I frågefönstret anger du och kör kommandot execGetDiscontinuedProducts. Om du anropar den lagrade proceduren körs motsvarande hanterade kod i GetDiscontinuedProducts -metoden. Den här koden utfärdar en SELECT fråga för att returnera alla produkter som har upphört och returnerar dessa data till det anropande programmet, som är SQL Server Management Studio i den här instansen. Management Studio tar emot dessa resultat och visar dem i fönstret Resultat.

Bild 13: Den GetDiscontinuedProducts lagrade proceduren returnerar alla utgångna produkter (klicka om du vill visa en bild i full storlek)

Steg 5: Skapa hanterade lagrade procedurer som accepterar indataparametrar

Många av de frågor och lagrade procedurer som vi har skapat under de här självstudierna har använt parametrar. I självstudien Skapa nya lagrade procedurer för Typed DataSet s TableAdapters skapade vi till exempel en lagrad procedur med namnet GetProductsByCategoryID som accepterade en indataparameter med namnet @CategoryID. Den lagrade proceduren returnerade sedan alla produkter vars CategoryID fält matchade värdet för den angivna @CategoryID parametern.

Om du vill skapa en hanterad lagrad procedur som accepterar indataparametrar anger du bara dessa parametrar i metodens definition. För att illustrera detta ska vi lägga till en annan hanterad lagrad procedur i ManagedDatabaseConstructs projektet med namnet GetProductsWithPriceLessThan. Den här hanterade lagrade proceduren accepterar en indataparameter som anger ett pris och returnerar alla produkter vars UnitPrice fält är mindre än parametervärdet.

Om du vill lägga till en ny lagrad procedur i projektet högerklickar du på ManagedDatabaseConstructs projektnamnet och väljer att lägga till en ny lagrad procedur. Ge filen namnet GetProductsWithPriceLessThan.cs. Som vi såg i steg 3 skapar detta en ny C#-klassfil med en metod med namnet GetProductsWithPriceLessThan placerad i partial klassen StoredProcedures.

GetProductsWithPriceLessThan Uppdatera metodens definition så att den accepterar en SqlMoney indataparameter med namnet price och skriver koden för att köra och returnera frågeresultatet:

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

Metodens GetProductsWithPriceLessThan definition och kod liknar definitionen och koden för metoden GetDiscontinuedProducts som skapades i steg 3. De enda skillnaderna är att GetProductsWithPriceLessThan metoden accepterar som indataparameter (price), SqlCommand s-frågan innehåller en parameter (@MaxPrice), och en parameter läggs till i SqlCommand s-samlingen Parameters och tilldelas värdet för variabeln price .

När du har lagt till den här koden distribuerar du om SQL Server-projektet. Gå sedan tillbaka till SQL Server Management Studio och uppdatera mappen Lagrade procedurer. Du bör kunna se en ny post, GetProductsWithPriceLessThan. Från ett frågefönster anger och kör du kommandot exec GetProductsWithPriceLessThan 25, som visar alla produkter som är mindre än 25 USD, som bild 14 visar.

Bild 14: Produkter under 25 USD visas (Klicka om du vill visa en bild i full storlek)

Steg 6: Anropa den hanterade lagrade proceduren från dataåtkomstlagret

Nu har vi lagt till GetDiscontinuedProducts och GetProductsWithPriceLessThan lagrade procedurer som hanteras i ManagedDatabaseConstructs projektet och registrerat dem i Northwind SQL Server-databasen. Vi anropade även dessa hanterade lagrade procedurer från SQL Server Management Studio (se bild 13 och 14). För att vårt ASP.NET program ska kunna använda dessa hanterade lagrade procedurer måste vi dock lägga till dem i dataåtkomst- och affärslogiklagren i arkitekturen. I det här steget lägger vi till två nya metoder i den typade datauppsättningen ProductsTableAdapter, som ursprungligen skapades i tutorialen NorthwindWithSprocs. I steg 7 lägger vi till motsvarande metoder i BLL:n.

Öppna Den typerade datauppsättningen NorthwindWithSprocs i Visual Studio och börja med att lägga till en ny metod i namnet ProductsTableAdapterGetDiscontinuedProducts. Om du vill lägga till en ny metod i en TableAdapter högerklickar du på TableAdapter-namnet i designern och väljer alternativet Lägg till fråga på snabbmenyn.

Anmärkning

Eftersom vi flyttade Northwind-databasen från App_Data mappen till SQL Server 2005 Express Edition-databasinstansen är det absolut nödvändigt att motsvarande anslutningssträng i Web.config uppdateras för att återspegla den här ändringen. I steg 2 diskuterade vi att uppdatera NORTHWNDConnectionString värdet i Web.config. Om du har glömt att göra den här uppdateringen visas felmeddelandet Det gick inte att lägga till frågan. Det går inte att hitta anslutningen NORTHWNDConnectionString för objektet Web.config i en dialogruta när du försöker lägga till en ny metod i TableAdapter. Lös det här felet genom att klicka på OK och sedan gå till Web.config och uppdatera värdet enligt beskrivningen NORTHWNDConnectionString i steg 2. Försök sedan att lägga till metoden i TableAdapter igen. Den här gången bör det fungera utan fel.

När du lägger till en ny metod startas guiden TableAdapter Query Configuration, som vi har använt många gånger i tidigare självstudier. I det första steget uppmanas vi att ange hur TableAdapter ska komma åt databasen: via en ad hoc SQL-instruktion eller via en ny eller befintlig lagrad procedur. Eftersom vi redan har skapat och registrerat den GetDiscontinuedProducts hanterade lagrade proceduren med databasen väljer du alternativet Använd befintlig lagrad procedur och trycker på Nästa.

Bild 15: Välj alternativet Använd befintlig lagrad procedur (Klicka om du vill visa en bild i full storlek)

Nästa skärm uppmanar oss att ange den lagrade procedur som metoden ska anropa. Välj den GetDiscontinuedProducts hanterade lagrade proceduren i rullgardinsmenyn och klicka på Nästa.

Bild 16: Välj den GetDiscontinuedProducts hanterade lagrade proceduren (Klicka om du vill visa en bild i full storlek)

Vi uppmanas sedan att ange om den lagrade proceduren returnerar rader, ett enda värde eller ingenting. Eftersom GetDiscontinuedProducts returnerar uppsättningen med utgångna produktrader väljer du det första alternativet ( Tabelldata ) och klickar på Nästa.

Bild 17: Välj alternativet Tabelldata (Klicka om du vill visa en bild i full storlek)

Med den sista guideskärmen kan vi ange de dataåtkomstmönster som används och namnen på de resulterande metoderna. Låt båda kryssrutorna vara markerade och namnge metoderna FillByDiscontinued och GetDiscontinuedProducts. Klicka på Slutför för att slutföra guiden.

Bild 18: Namnge metoderna FillByDiscontinued och GetDiscontinuedProducts (Klicka om du vill visa en bild i full storlek)

Upprepa de här stegen för att skapa metoder med namnet FillByPriceLessThan och GetProductsWithPriceLessThan i ProductsTableAdapter för den GetProductsWithPriceLessThan hanterade lagrade proceduren.

Bild 19 visar en skärmbild av DataSet Designer när du har lagt till metoderna i ProductsTableAdapter för de hanterade lagrade procedurer.

Bild 19: ProductsTableAdapter Innehåller de nya metoder som lagts till i det här steget (Klicka om du vill visa en bild i full storlek)

Steg 7: Lägga till motsvarande metoder i affärslogiklagret

Nu när vi har uppdaterat dataåtkomstskiktet så att det innehåller metoder för att anropa hanterade lagrade procedurer som lagts till i steg 4 och 5 måste vi lägga till motsvarande metoder i affärslogiklagret. Lägg till följande två metoder i ProductsBLLWithSprocs klassen:

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

Båda metoderna anropar helt enkelt motsvarande DAL-metod och returnerar instansen ProductsDataTable . Markeringen DataObjectMethodAttribute ovanför varje metod gör att dessa metoder tas med i listrutan på fliken SELECT i guiden Konfigurera datakälla i ObjectDataSource.

Steg 8: Anropa hanterade lagrade procedurer från presentationsskiktet

Med affärslogik- och dataåtkomstskikten utökade med stöd för att anropa de hanterade lagrade procedurerna GetDiscontinuedProducts och GetProductsWithPriceLessThan, kan vi nu visa dessa lagrade procedurers resultat via en ASP.NET-sida.

Öppna sidan ManagedFunctionsAndSprocs.aspx i AdvancedDAL mappen och dra en GridView till designern från verktygslådan. Ställ in GridViews ID-egenskap till DiscontinuedProducts och använd dess snabbval för att binda den till en ny ObjectDataSource med namnet DiscontinuedProductsDataSource. Konfigurera ObjectDataSource för att hämta dess data från ProductsBLLWithSprocs klassens GetDiscontinuedProducts -metod.

Bild 20: Konfigurera ObjectDataSource att använda ProductsBLLWithSprocs klassen (Klicka om du vill visa en bild i full storlek)

Bild 21: Välj GetDiscontinuedProducts metoden i listan Drop-Down på fliken SELECT (Klicka om du vill visa en bild i full storlek)

Eftersom det här rutnätet används för att bara visa produktinformation anger du listrutorna på flikarna UPDATE, INSERT och DELETE till (Ingen) och klickar sedan på Slutför.

När du har slutfört guiden lägger Visual Studio automatiskt till ett BoundField- eller CheckBoxField-fält för varje datafält i ProductsDataTable. Ta en stund att ta bort alla dessa fält förutom ProductName och Discontinued, då bör din GridView- och ObjectDataSource-deklarativa markering se ut ungefär så här:

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

Ta en stund och titta på den här sidan via en webbläsare. När sidan besöks anropar ProductsBLLWithSprocs ObjectDataSource klassens GetDiscontinuedProducts metod. Som vi såg i steg 7, anropar denna metod DAL:s ProductsDataTable klass GetDiscontinuedProducts metod, som i sin tur anropar GetDiscontinuedProducts lagrad procedur. Den här lagrade proceduren är en hanterad lagrad procedur och kör koden som vi skapade i steg 3 och returnerar de produkter som upphört.

Resultaten som returneras av den hanterade lagrade proceduren paketeras upp till en ProductsDataTable av DAL och returneras sedan till BLL, som sedan returnerar dem till presentationsskiktet där de är bundna till GridView och visas. Som förväntat listar rutnätet de produkter som har upphört att fungera.

Bild 22: Produkter som upphört visas (Klicka om du vill visa en bild i full storlek)

För ytterligare övning lägger du till en textruta och en annan GridView på sidan. Låt den här GridView visa produkter som är mindre än den mängd som anges i textrutan genom att anropa ProductsBLLWithSprocs klassens GetProductsWithPriceLessThan metod.

Steg 9: Skapa och anropa T-SQL UDF:er

User-Defined Functions, eller UDF:er, är databasobjekt som nära efterliknar semantiken för funktioner i programmeringsspråk. Precis som en funktion i C# kan UDF:er innehålla ett variabelt antal indataparametrar och returnera ett värde av en viss typ. En UDF kan returnera antingen skalära data – en sträng, ett heltal och så vidare – eller tabelldata. Låt oss ta en snabb titt på båda typerna av UDF:er, som börjar med en UDF som returnerar en skalär datatyp.

Följande UDF beräknar det uppskattade värdet för lagret för en viss produkt. Det gör det genom att ta in tre indataparametrar – UnitPricevärdena , UnitsInStockoch Discontinued för en viss produkt – och returnerar ett värde av typen money. Den beräknar det uppskattade värdet för lagret genom att multiplicera UnitPrice med UnitsInStock. För utgångna objekt halveras det här värdet.

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

När den här UDF:n har lagts till i databasen kan du hitta den via Management Studio genom att expandera mappen Programmability, sedan Functions och sedan Scalar-value Functions. Den kan användas i en SELECT fråga så här:

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

Jag har lagt till udf_ComputeInventoryValue UDF i Northwind-databasen; Bild 23 visar utdata från ovanstående SELECT fråga när den visas via Management Studio. Observera också att UDF visas under mappen Scalar-value Functions i Object Explorer.

Bild 23: Varje produkts inventeringsvärden visas (Klicka om du vill visa en bild i full storlek)

UDF:er kan också returnera tabelldata. Vi kan till exempel skapa en UDF som returnerar produkter som tillhör en viss kategori:

CREATE FUNCTION dbo.udf_GetProductsByCategoryID
(    
    @CategoryID int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ProductID, ProductName, SupplierID, CategoryID, 
           QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
           ReorderLevel, Discontinued
    FROM Products
    WHERE CategoryID = @CategoryID
)

udf_GetProductsByCategoryID UDF accepterar en @CategoryID indataparameter och returnerar resultatet av den angivna SELECT frågan. När du har skapat den här UDF:n kan du referera till den i FROM- eller JOIN-satsen i en SELECT-fråga. I följande exempel returneras ProductID, ProductName och CategoryID värdena för varje dryck.

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

Jag har lagt till udf_GetProductsByCategoryID UDF i Northwind-databasen; Bild 24 visar utdata från ovanstående SELECT fråga när den visas via Management Studio. UDF:er som returnerar tabelldata finns i mappen Object Explorers Table-value Functions.

Bild 24: , ProductIDProductNameoch CategoryID visas för varje dryck (klicka om du vill visa en bild i full storlek)

Anmärkning

Mer information om hur du skapar och använder UDF:er finns i Introduktion till User-Defined Functions. Se även fördelar och nackdelar med User-Defined Functions.

Steg 10: Skapa en hanterad UDF

Och udf_ComputeInventoryValueudf_GetProductsByCategoryID UDF:er som skapas i exemplen ovan är T-SQL-databasobjekt. SQL Server 2005 stöder även hanterade UDF:er, som kan läggas till i ManagedDatabaseConstructs projektet precis som de hanterade lagrade procedurerna från steg 3 och 5. I det här steget ska vi implementera udf_ComputeInventoryValue UDF i hanterad kod.

Om du vill lägga till en hanterad UDF i ManagedDatabaseConstructs projektet högerklickar du på projektnamnet i Solution Explorer och väljer lägg till ett nytt objekt. Välj User-Defined-mallen i dialogrutan Lägg till nytt objekt och ge den nya UDF-filen udf_ComputeInventoryValue_Managed.csnamnet .

Bild 25: Lägg till en ny hanterad UDF i ManagedDatabaseConstructs projektet (Klicka om du vill visa en bild i full storlek)

Mallen User-Defined Function skapar en partial klass med namnet UserDefinedFunctions med en metod vars namn är samma som klassfilens namn (udf_ComputeInventoryValue_Managedi den här instansen). Den här metoden är dekorerad med attributetSqlFunction som flaggar metoden som en hanterad UDF.

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

Metoden udf_ComputeInventoryValue returnerar för närvarande ett SqlString objekt och accepterar inga indataparametrar. Vi måste uppdatera metoddefinitionen så att den accepterar tre indataparametrar - UnitPrice, UnitsInStockoch Discontinued - och returnerar ett SqlMoney objekt. Logiken för att beräkna lagervärdet är identisk med den i T-SQL udf_ComputeInventoryValue UDF.

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

Observera att UDF-metodens indataparametrar är av motsvarande SQL-typer: SqlMoney för UnitPrice fältet, SqlInt16 för UnitsInStockoch SqlBoolean för Discontinued. Dessa datatyper återspeglar de typer som definierats i Products tabellen: UnitPrice kolumnen är av typen money, UnitsInStock kolumnen av typen smallintoch Discontinued kolumnen av typen bit.

Koden börjar med att skapa en SqlMoney instans med namnet inventoryValue som har tilldelats värdet 0. Tabellen Products tillåter databasvärden NULL i kolumnerna UnitsInPrice och UnitsInStock . Därför måste vi först kontrollera om dessa värden innehåller NULL s, vilket vi gör via SqlMoney objektets IsNull egenskap. Om både UnitPrice och UnitsInStock innehåller värden som inte är NULL, beräknar vi inventoryValue till produkten av de två. Discontinued Om är sant halverar vi sedan värdet.

Anmärkning

Objektet SqlMoney tillåter endast att två SqlMoney instanser multipliceras. Det tillåter inte att en SqlMoney instans multipliceras med ett literalt flyttalsnummer. Därför multiplicerar vi den med en ny inventoryValue instans som har värdet 0,5 för att halvera SqlMoney den.

Steg 11: Distribuering av hanterad UDF

Nu när den hanterade UDF:n har skapats är vi redo att distribuera den till Northwind-databasen. Som vi såg i steg 4 distribueras de hanterade objekten i ett SQL Server-projekt genom att högerklicka på projektnamnet i Solution Explorer och välja alternativet Distribuera på snabbmenyn.

När du har distribuerat projektet går du tillbaka till SQL Server Management Studio och uppdaterar mappen Scalar-valued Functions. Du bör nu se två poster:

• dbo.udf_ComputeInventoryValue – T-SQL UDF som skapades i steg 9 och
• dbo.udf ComputeInventoryValue_Managed – den hanterade UDF som skapades i steg 10 och just har distribuerats.

Om du vill testa den här hanterade UDF:n kör du följande fråga inifrån Management Studio:

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

Det här kommandot använder den hanterade udf ComputeInventoryValue_Managed UDF:n i stället för T-SQL udf_ComputeInventoryValue UDF, men utdata är desamma. Gå tillbaka till bild 23 för att se en skärmbild av UDF:s utdata.

Steg 12: Felsöka hanterade databasobjekt

I självstudien Felsökning av lagrade procedurer diskuterade vi de tre alternativen för att felsöka SQL Server via Visual Studio: Direkt databasfelsökning, programfelsökning och felsökning från ett SQL Server-projekt. Hanterade databasobjekt kan inte felsökas via direkt databasfelsökning, men kan felsökas från ett klientprogram och direkt från SQL Server-projekt. För att felsökningen ska fungera måste dock SQL Server 2005-databasen tillåta SQL/CLR-felsökning. Kom ihåg att när vi först skapade ManagedDatabaseConstructs projektet frågade Visual Studio oss om vi ville aktivera SQL/CLR-felsökning (se bild 6 i steg 2). Den här inställningen kan ändras genom att högerklicka på databasen från fönstret ServerUtforskaren.

Bild 26: Kontrollera att databasen tillåter SQL/CLR-felsökning

Tänk om vi ville felsöka den hanterade lagrade proceduren GetProductsWithPriceLessThan. Vi börjar med att ange en brytpunkt i metodens GetProductsWithPriceLessThan kod.

Bild 27: Ange en brytpunkt i GetProductsWithPriceLessThan metoden (Klicka om du vill visa en bild i full storlek)

Låt oss först titta på felsökning av hanterade databasobjekt från SQL Server-projektet. Eftersom vår lösning innehåller två projekt – ManagedDatabaseConstructs SQL Server-projektet tillsammans med vår webbplats – för att kunna felsöka från SQL Server-projektet måste vi instruera Visual Studio att starta ManagedDatabaseConstructs SQL Server-projektet när vi börjar felsöka. Högerklicka på ManagedDatabaseConstructs projektet i Solution Explorer och välj alternativet Ange som startprojekt på snabbmenyn.

ManagedDatabaseConstructs När projektet startas från felsökningsprogrammet kör det SQL-uttrycken Test.sql i filen, som finns i Test Scripts mappen. Om du till exempel vill testa den GetProductsWithPriceLessThan hanterade lagrade proceduren ersätter du det befintliga Test.sql filinnehållet med följande -instruktion, som anropar den GetProductsWithPriceLessThan hanterade lagrade proceduren som skickar @CategoryID värdet 14,95:

exec GetProductsWithPriceLessThan 14.95

När du har angett skriptet ovan i Test.sqlbörjar du felsöka genom att gå till felsökningsmenyn och välja Starta felsökning eller genom att trycka på F5 eller den gröna uppspelningsikonen i verktygsfältet. Detta skapar projekten i lösningen, distribuerar de hanterade databasobjekten till Northwind-databasen och kör sedan skriptet Test.sql . I det här läget kommer brytpunkten att slås och vi kan gå igenom GetProductsWithPriceLessThan metoden, undersöka värdena för indataparametrarna och så vidare.

Bild 28: Brytpunkten i GetProductsWithPriceLessThan metoden träffades (klicka om du vill visa en bild i full storlek)

För att ett SQL-databasobjekt ska kunna kopplas från ett klientprogram är det absolut nödvändigt att databasen konfigureras för att stödja programfelsökning. Högerklicka på databasen i Server Explorer och kontrollera att alternativet Programfelsökning är markerat. Dessutom måste vi konfigurera ASP.NET-programmet så att det integreras med SQL-felsökningsprogrammet och inaktiverar anslutningspooler. De här stegen beskrivs i detalj i steg 2 i självstudien Felsökning av lagrade procedurer .

När du har konfigurerat ASP.NET program och databas anger du ASP.NET webbplats som startprojekt och börjar felsöka. Om du besöker en sida som anropar ett av de hanterade objekt som har en brytpunkt stoppas programmet och kontrollen överförs till felsökningsprogrammet, där du kan gå igenom koden enligt bild 28.

Steg 13: Kompilera och distribuera hanterade databasobjekt manuellt

SQL Server Projects gör det enkelt att skapa, kompilera och distribuera hanterade databasobjekt. SQL Server Projects är tyvärr bara tillgängliga i Professional- och Team Systems-utgåvorna av Visual Studio. Om du använder Visual Web Developer eller Standard Edition av Visual Studio och vill använda hanterade databasobjekt måste du skapa och distribuera dem manuellt. Detta omfattar fyra steg:

1. Skapa en fil som innehåller källkoden för det hanterade databasobjektet.
2. Kompilera objektet till en sammansättning,
3. Registrera sammansättningen med SQL Server 2005-databasen och
4. Skapa ett databasobjekt i SQL Server som pekar på lämplig metod i sammansättningen.

För att illustrera dessa uppgifter ska vi skapa en ny hanterad lagrad procedur som returnerar de produkter vars UnitPrice är större än ett angivet värde. Skapa en ny fil på datorn med namnet GetProductsWithPriceGreaterThan.cs och ange följande kod i filen (du kan använda Visual Studio, Anteckningar eller valfri textredigerare för att åstadkomma detta):

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

Den här koden är nästan identisk med den GetProductsWithPriceLessThan metod som skapades i steg 5. De enda skillnaderna är metodnamnen WHERE , -satsen och parameternamnet som används i frågan. I GetProductsWithPriceLessThan-metoden igen stod det i WHERE-satsen: WHERE UnitPrice < @MaxPrice. Här i GetProductsWithPriceGreaterThananvänder vi: WHERE UnitPrice > @MinPrice .

Nu måste vi kompilera den här klassen till en sammansättning. Från kommandoraden går du till katalogen där du sparade GetProductsWithPriceGreaterThan.cs filen och använder C#-kompilatorn (csc.exe) för att kompilera klassfilen till en sammansättning:

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

Om mappen som innehåller csc.exe inte finns i systemets PATH, måste du ange hela sökvägen, %WINDOWS%\Microsoft.NET\Framework\version\, exempelvis:

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

Bild 29: Kompilera GetProductsWithPriceGreaterThan.cs till en sammansättning (Klicka om du vill visa en bild i full storlek)

Flaggan /t anger att C#-klassfilen ska kompileras till en DLL (i stället för en körbar fil). Flaggan /out anger namnet på den resulterande sammansättningen.

Anmärkning

I stället för att kompilera GetProductsWithPriceGreaterThan.cs klassfilen från kommandoraden kan du också använda Visual C# Express Edition eller skapa ett separat klassbiblioteksprojekt i Visual Studio Standard Edition. S ren Jacob Lauritsen har generöst tillhandahållit ett Visual C# Express Edition-projekt med kod för den lagrade proceduren GetProductsWithPriceGreaterThan och de två hanterade lagrade procedurerna samt den UDF som skapades i steg 3, 5 och 10. S ren s-projektet innehåller även de T-SQL-kommandon som behövs för att lägga till motsvarande databasobjekt.

När koden kompileras till en sammansättning är vi redo att registrera sammansättningen i SQL Server 2005-databasen. Detta kan utföras via T-SQL, med hjälp av kommandot CREATE ASSEMBLYeller via SQL Server Management Studio. Låt oss fokusera på att använda Management Studio.

Från Management Studio expanderar du mappen Programmability i Northwind-databasen. En av dess undermappar är Sammansättningar. Om du vill lägga till en ny sammansättning i databasen manuellt högerklickar du på mappen Sammansättningar och väljer Ny sammansättning på snabbmenyn. Då visas dialogrutan Ny sammansättning (se bild 30). Klicka på knappen Bläddra, välj den ManuallyCreatedDBObjects.dll sammansättning som vi just kompilerade och klicka sedan på OK för att lägga till sammansättningen i databasen. Du bör inte se ManuallyCreatedDBObjects.dll sammansättningen i Objektutforskaren.

Bild 30: Lägg till ManuallyCreatedDBObjects.dll sammansättningen i databasen (Klicka om du vill visa en bild i full storlek)

Bild 31: ManuallyCreatedDBObjects.dll Listat i Objektutforskaren

Vi har lagt till sammansättningen i Northwind-databasen, men vi har ännu inte associerat en lagrad procedur med GetProductsWithPriceGreaterThan metoden i sammansättningen. Det gör du genom att öppna ett nytt frågefönster och köra följande skript:

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

Detta skapar en ny lagrad procedur i Northwind-databasen med namnet GetProductsWithPriceGreaterThan och associerar den med den hanterade metoden GetProductsWithPriceGreaterThan (som finns i klassen StoredProcedures, som finns i sammansättningen ManuallyCreatedDBObjects).

När du har kört skriptet ovan uppdaterar du mappen Lagrade procedurer i Object Explorer. Du bör se en ny lagrad procedurpost – GetProductsWithPriceGreaterThan – som har en låsikon bredvid sig. Om du vill testa den här lagrade proceduren anger du och kör följande skript i frågefönstret:

exec GetProductsWithPriceGreaterThan 24.95

Som figur 32 visar, visar kommandot ovan information för de produkter med ett UnitPrice större än 24,95 USD.

Bild 32: ManuallyCreatedDBObjects.dll Visas i Objektutforskaren (Klicka om du vill visa en bild i full storlek)

Sammanfattning

Microsoft SQL Server 2005 tillhandahåller integrering med Common Language Runtime (CLR), som gör att databasobjekt kan skapas med hanterad kod. Tidigare kunde dessa databasobjekt bara skapas med T-SQL, men nu kan vi skapa dessa objekt med hjälp av .NET-programmeringsspråk som C#. I den här handledningen skapade vi två hanterade lagrade procedurer och en hanterad User-Defined-funktion.

Visual Studio SQL Server-projekttypen underlättar skapandet, kompileringen och distributionen av hanterade databasobjekt. Dessutom erbjuder det omfattande felsökningsstöd. SQL Server-projekttyper är dock endast tillgängliga i Professional- och Team Systems-utgåvorna av Visual Studio. För dem som använder Visual Web Developer eller Standard Edition av Visual Studio måste stegen för att skapa, kompilering och distribution utföras manuellt, som vi såg i steg 13.

Lycka till med programmerandet!

Ytterligare läsning

Mer information om de ämnen som beskrivs i den här självstudien finns i följande resurser:

• Fördelar och nackdelar med User-Defined Functions
• Skapa SQL Server 2005-objekt i hanterad kod
• Anvisningar: Skapa och köra en CLR SQL Server-lagrad procedur
• Anvisningar: Skapa och köra en CLR SQL Server User-Defined-funktion
• Anvisningar: Redigera skriptet Test.sql för att köra SQL-objekt
• Introduktion till användardefinierade funktioner
• Hanterad kod och SQL Server 2005 (video)
• Transact-SQL Referens
• Genomgång: Skapa en lagrad procedur i hanterad kod

Om författaren

Scott Mitchell, författare till sju ASP/ASP.NET-böcker och grundare av 4GuysFromRolla.com, har arbetat med Microsofts webbtekniker sedan 1998. Scott arbetar som oberoende konsult, tränare och författare. Hans senaste bok är Sams Teach Yourself ASP.NET 2.0 på 24 timmar. Han kan nås på mitchell@4GuysFromRolla.com.

Särskilt tack till

Den här självstudieserien granskades av många användbara granskare. Ansvarig granskare för den här handledningen var Søren Jacob Lauritsen. Förutom att granska den här artikeln skapade S ren även Visual C# Express Edition-projektet som ingår i den här artikelns nedladdning för att manuellt kompilera de hanterade databasobjekten. Vill du granska mina kommande MSDN-artiklar? Om så är fallet, hör av dig på mitchell@4GuysFromRolla.com.

FöregåendeNästa