Opgeslagen procedures en User-Defined-functies maken met beheerde code (C#)

door Scott Mitchell

PDF downloaden

Microsoft SQL Server 2005 kan worden geïntegreerd met .NET Common Language Runtime, zodat ontwikkelaars databaseobjecten kunnen maken via beheerde code. Deze zelfstudie laat zien hoe u beheerde opgeslagen procedures en door de gebruiker gedefinieerde functies maakt met uw Visual Basic- of C#-code. We zien ook hoe u met deze edities van Visual Studio fouten kunt opsporen in dergelijke beheerde databaseobjecten.

Introductie

Databases zoals Microsoft SQL Server 2005 gebruiken de Transact-Structured Query Language (T-SQL) voor het invoegen, wijzigen en ophalen van gegevens. De meeste databasesystemen bevatten constructies voor het groeperen van een reeks SQL-instructies die vervolgens kunnen worden uitgevoerd als één herbruikbare eenheid. Opgeslagen procedures zijn één voorbeeld. Een andere is User-Defined Functions (UDF's), een constructie die we in stap 9 nader zullen onderzoeken.

Sql is in de kern ontworpen voor het werken met gegevenssets. De SELECT, UPDATE, en DELETE instructies zijn inherent van toepassing op alle records in de bijbehorende tabel en zijn alleen beperkt door hun WHERE clausules. Er zijn echter veel taalfuncties die zijn ontworpen voor het werken met één record tegelijk en voor het bewerken van scalaire gegevens. CURSOR staat toe dat een set records één voor één wordt doorlopen. Tekenreeksmanipulatiefuncties zoals LEFT, CHARINDEXen PATINDEX werken met scalaire gegevens. SQL bevat ook controlestroominstructies zoals IF en WHILE.

Vóór Microsoft SQL Server 2005 konden opgeslagen procedures en UDF's alleen worden gedefinieerd als een verzameling T-SQL-instructies. SQL Server 2005 is echter ontworpen om integratie te bieden met de Common Language Runtime (CLR), de runtime die wordt gebruikt door alle .NET-assembly's. Daarom kunnen de opgeslagen procedures en UDF's in een SQL Server 2005-database worden gemaakt met beheerde code. Dat wil gezegd, u kunt een opgeslagen procedure of UDF maken als een methode in een C#-klasse. Hierdoor kunnen deze opgeslagen procedures en UDF's gebruikmaken van functionaliteit in .NET Framework en vanuit uw eigen aangepaste klassen.

In deze zelfstudie bekijken we hoe u beheerde opgeslagen procedures en User-Defined Functions maakt en hoe u deze integreert in onze Northwind-database. Laten we beginnen!

Opmerking

Beheerde databaseobjecten bieden enkele voordelen ten opzichte van hun SQL-tegenhangers. Taalrijkheid en bekendheid en de mogelijkheid om bestaande code en logica opnieuw te gebruiken, zijn de belangrijkste voordelen. Maar beheerde databaseobjecten zijn waarschijnlijk minder efficiënt bij het werken met gegevenssets waarvoor niet veel procedurele logica is betrokken. Voor een uitgebreidere bespreking van de voordelen van het gebruik van beheerde code versus T-SQL, bekijkt u de voordelen van het gebruik van beheerde code om databaseobjecten te maken.

Stap 1: de Northwind-database uit App_Data verplaatsen

Al onze zelfstudies hebben tot nu toe een Microsoft SQL Server 2005 Express Edition-databasebestand gebruikt in de map van App_Data de webtoepassing. Het plaatsen van de database in App_Data vereenvoudigde distributie en het uitvoeren van deze zelfstudies omdat alle bestanden zich in één map bevinden en geen aanvullende configuratiestappen vereist om de zelfstudie te testen.

Voor deze zelfstudie gaan we echter de Northwind-database verplaatsen vanuit App_Data en deze expliciet registreren bij de SQL Server 2005 Express Edition-database-instantie. Hoewel we de stappen voor deze zelfstudie met de database in de App_Data map kunnen uitvoeren, worden een aantal stappen veel eenvoudiger gemaakt door de database expliciet te registreren bij het EXEMPLAAR van de SQL Server 2005 Express Edition-database.

De download voor deze zelfstudie bevat de twee databasebestanden - NORTHWND.MDF en NORTHWND_log.LDF - in een map met de naam DataFiles. Als u uw eigen implementatie van de tutorials volgt, sluit dan Visual Studio en verplaats de NORTHWND.MDF en NORTHWND_log.LDF bestanden van de map App_Data van de website naar een map buiten de website. Zodra de databasebestanden zijn verplaatst naar een andere map, moeten we de Northwind-database registreren bij het exemplaar van de SQL Server 2005 Express Edition-database. Dit kan worden gedaan vanuit SQL Server Management Studio. Als u een niet-Express-editie van SQL Server 2005 op uw computer hebt geïnstalleerd, is Management Studio waarschijnlijk al geïnstalleerd. Als u alleen SQL Server 2005 Express Edition op uw computer hebt, kunt u Microsoft SQL Server Management Studio downloaden en installeren.

Start SQL Server Management Studio. Zoals in afbeelding 1 wordt weergegeven, wordt in Management Studio gevraagd met welke server verbinding moet worden gemaakt. Voer localhost\SQLExpress in voor de servernaam, kies Windows-verificatie in de vervolgkeuzelijst Verificatie en klik op Verbinding maken.

Afbeelding 1: Verbinding maken met het juiste database-exemplaar

Zodra u verbinding hebt gemaakt, bevat het venster Objectverkenner informatie over het exemplaar van de SQL Server 2005 Express Edition-database, inclusief de databases, beveiligingsinformatie, beheeropties enzovoort.

De Northwind-database moet worden bijgevoegd aan de DataFiles map (of waar u deze ook hebt verplaatst) bij de instantie van de SQL Server 2005 Express Edition-database. Klik met de rechtermuisknop op de map Databases en kies de optie Bijvoegen in het contextmenu. Hiermee verschijnt het dialoogvenster Databases bijvoegen. Klik op de knop Toevoegen, zoom in op het juiste NORTHWND.MDF bestand en klik op OK. Op dit moment moet uw scherm er ongeveer uitzien als afbeelding 2.

Afbeelding 2: Verbinding maken met het juiste database-exemplaar (klik om de volledige afbeelding weer te geven)

Opmerking

Wanneer u verbinding maakt met de instantie van SQL Server 2005 Express Edition via Management Studio, kunt u in het dialoogvenster Databases toevoegen niet navigeren naar gebruikersprofielmappen, zoals Mijn documenten. Zorg er daarom voor dat u de NORTHWND.MDF en NORTHWND_log.LDF bestanden in een niet-gebruikersprofielmap plaatst.

Klik op de knop OK om de database toe te voegen. Het dialoogvenster Databases bijvoegen wordt gesloten en de Objectverkenner moet nu de zojuist gekoppelde database weergeven. De kans is groot dat de Northwind-database een naam heeft zoals 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF. Wijzig de naam van de database in Northwind door met de rechtermuisknop op de database te klikken en Naam wijzigen te kiezen.

Afbeelding 3: De naam van de database wijzigen in Northwind

Stap 2: Een nieuwe oplossing en SQL Server-project maken in Visual Studio

Voor het maken van beheerde opgeslagen procedures of UDF's in SQL Server 2005 schrijven we de opgeslagen procedure en UDF-logica als C#-code in een klasse. Zodra de code is geschreven, moeten we deze klasse compileren in een assembly (een .dll bestand), de assembly registreren bij de SQL Server-database en vervolgens een opgeslagen procedure of UDF-object maken in de database die verwijst naar de bijbehorende methode in de assembly. Deze stappen kunnen allemaal handmatig worden uitgevoerd. We kunnen de code maken in elke teksteditor, deze compileren vanaf de opdrachtregel met behulp van de C#-compiler (csc.exe), deze registreren bij de database met behulp van de CREATE ASSEMBLY opdracht of vanuit Management Studio, en de opgeslagen procedure of UDF-object op vergelijkbare manieren toevoegen. Gelukkig bevatten de versies Professional en Team Systems van Visual Studio een SQL Server-projecttype waarmee deze taken worden geautomatiseerd. In deze zelfstudie wordt stapsgewijs uitgelegd hoe u het sql Server-projecttype gebruikt om een beheerde opgeslagen procedure en UDF te maken.

Opmerking

Als u Visual Web Developer of de Standard-editie van Visual Studio gebruikt, moet u in plaats daarvan de handmatige benadering gebruiken. Stap 13 bevat gedetailleerde instructies voor het handmatig uitvoeren van deze stappen. Ik moedig u aan om stap 2 tot en met 12 te lezen voordat u stap 13 leest, omdat deze stappen belangrijke SQL Server-configuratie-instructies bevatten die moeten worden toegepast, ongeacht welke versie van Visual Studio u gebruikt.

Begin met het openen van Visual Studio. Kies nieuw project in het menu Bestand om het dialoogvenster Nieuw project weer te geven (zie afbeelding 4). Navigeer naar het databasetype project en selecteer vervolgens in de sjablonen aan de rechterkant om een nieuw SQL Server-project te maken. Ik heb ervoor gekozen dit project ManagedDatabaseConstructs een naam te geven en het in een oplossing met de naam Tutorial75te plaatsen.

Afbeelding 4: Een nieuw SQL Server-project maken (klik om de volledige afbeelding weer te geven)

Klik op de knop OK in het dialoogvenster Nieuw project om de oplossing en het SQL Server-project te maken.

Een SQL Server-project is gekoppeld aan een bepaalde database. Daarom wordt na het maken van het nieuwe SQL Server-project onmiddellijk gevraagd om deze informatie op te geven. In afbeelding 5 ziet u het dialoogvenster Nieuwe databasereferentie dat is ingevuld om te verwijzen naar de Northwind-database die we hebben geregistreerd in het SQL Server 2005 Express Edition-database-exemplaar in stap 1.

Afbeelding 5: Het SQL Server-project koppelen aan de Northwind-database

Om fouten op te sporen in de beheerde opgeslagen procedures en UDF's die we in dit project gaan maken, moeten we ondersteuning voor SQL/CLR-foutopsporing inschakelen voor de verbinding. Wanneer u een SQL Server-project koppelt aan een nieuwe database (zoals in afbeelding 5), vraagt Visual Studio ons of we SQL/CLR-foutopsporing willen inschakelen voor de verbinding (zie afbeelding 6). Klik op Ja.

Afbeelding 6: SQL/CLR-foutopsporing inschakelen

Op dit moment is het nieuwe SQL Server-project toegevoegd aan de oplossing. Het bevat een map met de naam Test Scripts een bestand met de naam Test.sql, dat wordt gebruikt voor het opsporen van fouten in de beheerde databaseobjecten die in het project zijn gemaakt. In stap 12 wordt gekeken naar foutopsporing.

We kunnen nu nieuwe beheerde opgeslagen procedures en UDF's toevoegen aan dit project, maar voordat we eerst onze bestaande webtoepassing in de oplossing opnemen. Selecteer in het menu Bestand de optie Toevoegen en kies Bestaande website. Blader naar de juiste websitemap en klik op OK. Zoals in afbeelding 7 wordt weergegeven, wordt de oplossing bijgewerkt met twee projecten: de website en het ManagedDatabaseConstructs SQL Server-project.

Afbeelding 7: Solution Explorer bevat nu twee projecten

De NORTHWNDConnectionString waarde in Web.config verwijst momenteel naar het NORTHWND.MDF bestand in de App_Data map. Omdat we deze database uit App_Data verwijderden en expliciet registreerden in de instantie van de SQL Server 2005 Express Edition-database, moeten we de waarde van NORTHWNDConnectionString overeenkomstig bijwerken. Open het Web.config bestand op de website en wijzig de NORTHWNDConnectionString waarde zodat de verbindingsreeks het volgende leest: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True Na deze wijziging moet uw <connectionStrings> sectie Web.config er ongeveer als volgt uitzien:

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

Opmerking

Zoals besproken in de vorige zelfstudie, moeten we verbindingspooling uitschakelen bij het opsporen van fouten in een SQL Server-object vanuit een clienttoepassing, zoals een ASP.NET website. De bovenstaande verbindingsreeks schakelt groepsgewijze verbindingen ( Pooling=false ) uit. Als u geen fouten wilt opsporen in de beheerde opgeslagen procedures en UDF's van de ASP.NET-website, schakelt u groepsgewijze verbindingen in.

Stap 3: Een beheerde opgeslagen procedure maken

Als u een beheerde opgeslagen procedure wilt toevoegen aan de Northwind-database, moet u eerst de opgeslagen procedure maken als een methode in het SQL Server-project. Klik in Solution Explorer met de rechtermuisknop op de ManagedDatabaseConstructs projectnaam en kies ervoor een nieuw item toe te voegen. Hiermee wordt het dialoogvenster Nieuw item toevoegen weergegeven, waarin de typen beheerde databaseobjecten worden vermeld die aan het project kunnen worden toegevoegd. Zoals in afbeelding 8 wordt weergegeven, omvat dit onder andere opgeslagen procedures en User-Defined Functions.

Laten we beginnen met het toevoegen van een procedure voor opslag die eenvoudigweg alle stopgezette producten retourneert. Geef het nieuwe opgeslagen procedurebestand GetDiscontinuedProducts.cseen naam.

Afbeelding 8: Een nieuwe opgeslagen procedure toevoegen met de naam GetDiscontinuedProducts.cs (klik hier om de volledige afbeelding weer te geven)

Hiermee maakt u een nieuw C#-klassebestand met de volgende inhoud:

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

Houd er rekening mee dat de opgeslagen procedure wordt geïmplementeerd als een static methode binnen een partial klassebestand met de naam StoredProcedures. Bovendien is de GetDiscontinuedProducts methode versierd met de SqlProcedure attribute, die de methode markeert als een opgeslagen procedure.

Met de volgende code wordt een SqlCommand object gemaakt en stelt CommandText in op een SELECT query die alle kolommen uit de Products tabel retourneert voor producten waarbij het Discontinued veld gelijk is aan 1. Vervolgens wordt de opdracht uitgevoerd en worden de resultaten teruggestuurd naar de clienttoepassing. Voeg deze code toe aan de GetDiscontinuedProducts methode.

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

Alle beheerde databaseobjecten hebben toegang tot een SqlContext object dat de context van de aanroeper vertegenwoordigt. Het SqlContext biedt toegang tot een SqlPipe object via de Pipe eigenschap. Dit SqlPipe object wordt gebruikt om informatie over te schakelen tussen de SQL Server-database en de aanroepende toepassing. Zoals de naam al aangeeft, voert de ExecuteAndSend methode een doorgegeven SqlCommand object uit en stuurt de resultaten terug naar de clienttoepassing.

Opmerking

Beheerde databaseobjecten zijn het meest geschikt voor opgeslagen procedures en UDF's die gebruikmaken van procedurele logica in plaats van op set gebaseerde logica. Procedurele logica omvat het werken met gegevenssets op rijbasis of het werken met scalaire gegevens. De GetDiscontinuedProducts methode die we zojuist hebben gemaakt, omvat echter geen procedurele logica. Daarom zou deze idealiter worden geïmplementeerd als een opgeslagen T-SQL-procedure. Het wordt geïmplementeerd als een beheerde opgeslagen procedure om de stappen te demonstreren die nodig zijn voor het maken en implementeren van beheerde opgeslagen procedures.

Stap 4: De beheerde opgeslagen procedure implementeren

Nu deze code is voltooid, zijn we klaar om deze te implementeren in de Northwind-database. Als u een SQL Server-project implementeert, wordt de code gecompileerd in een assembly, wordt de assembly bij de database geregistreerd en worden de bijbehorende objecten in de database gemaakt, waarbij deze worden gekoppeld aan de juiste methoden in de assembly. De exacte set taken die door de optie Implementeren worden uitgevoerd, is nauwkeuriger gespeld in stap 13. Klik met de rechtermuisknop op de ManagedDatabaseConstructs projectnaam in Solution Explorer en kies de optie Implementeren. De implementatie mislukt echter met de volgende fout: Onjuiste syntaxis in de buurt van 'EXTERNAL'. Mogelijk moet u het compatibiliteitsniveau van de huidige database instellen op een hogere waarde om deze functie in te schakelen. Raadpleeg de Help voor de opgeslagen procedure sp_dbcmptlevel.

Dit foutbericht treedt op wanneer u de assembly probeert te registreren bij de Northwind-database. Als u een assembly wilt registreren bij een SQL Server 2005-database, moet het compatibiliteitsniveau van de database zijn ingesteld op 90. Nieuwe SQL Server 2005-databases hebben standaard een compatibiliteitsniveau van 90. Databases die zijn gemaakt met Microsoft SQL Server 2000, hebben echter een standaardcompatibiliteitsniveau van 80. Omdat de Northwind-database aanvankelijk een Microsoft SQL Server 2000-database was, is het compatibiliteitsniveau momenteel ingesteld op 80 en moet daarom worden verhoogd tot 90 om beheerde databaseobjecten te registreren.

Als u het compatibiliteitsniveau van de database wilt bijwerken, opent u een nieuw queryvenster in Management Studio en voert u het volgende in:

exec sp_dbcmptlevel 'Northwind', 90

Klik op het pictogram Uitvoeren op de werkbalk om de bovenstaande query uit te voeren.

Afbeelding 9: Het compatibiliteitsniveau van de Northwind-database bijwerken (klik om de volledige afbeelding weer te geven)

Nadat u het compatibiliteitsniveau hebt bijgewerkt, implementeert u het SQL Server-project opnieuw. Deze keer moet de implementatie zonder fouten worden voltooid.

Ga terug naar SQL Server Management Studio, klik met de rechtermuisknop op de Northwind-database in Objectverkenner en kies Vernieuwen. Zoom vervolgens in op de map Programmeerbaarheid en vouw vervolgens de map Assembly's uit. Zoals in afbeelding 10 wordt weergegeven, bevat de Northwind-database nu de assembly die door het ManagedDatabaseConstructs project is gegenereerd.

Afbeelding 10: De ManagedDatabaseConstructs assembly is nu geregistreerd bij de Northwind-database

Vouw ook de map Opgeslagen procedures uit. Daar ziet u een opgeslagen procedure met de naam GetDiscontinuedProducts. Deze opgeslagen procedure is gemaakt door het implementatieproces en verwijst naar de GetDiscontinuedProducts methode in de ManagedDatabaseConstructs assembly. Wanneer de GetDiscontinuedProducts opgeslagen procedure wordt uitgevoerd, wordt de GetDiscontinuedProducts methode op zijn beurt uitgevoerd. Omdat dit een beheerde opgeslagen procedure is, kan deze niet worden bewerkt via Management Studio (vandaar het vergrendelingspictogram naast de naam van de opgeslagen procedure).

Afbeelding 11: De GetDiscontinuedProducts opgeslagen procedure wordt vermeld in de map Opgeslagen procedures

Er is nog een horde die we moeten overwinnen voordat we de beheerde opgeslagen procedure kunnen aanroepen: de database is geconfigureerd om de uitvoering van beheerde code te voorkomen. Controleer dit door een nieuw queryvenster te openen en de GetDiscontinuedProducts opgeslagen procedure uit te voeren. U ontvangt het volgende foutbericht: Uitvoering van gebruikerscode in .NET Framework is uitgeschakeld. Schakel de configuratieoptie 'CLR enabled' in.

Als u de configuratiegegevens van de Northwind-database wilt onderzoeken, voert u de opdracht exec sp_configure in het queryvenster in en voert u deze uit. Dit laat zien dat de instelling voor clr is momenteel ingesteld op 0.

Afbeelding 12: De instelling voor clr is momenteel ingesteld op 0 (klik om de afbeelding op volledige grootte weer te geven)

Houd er rekening mee dat elke configuratie-instelling in afbeelding 12 vier waarden bevat: de minimum- en maximumwaarden en de configuratie- en uitvoeringswaarden. Als u de configuratiewaarde voor de instelling CLR wilt bijwerken, voert u de volgende opdracht uit:

exec sp_configure 'clr enabled', 1

Als u de exec sp_configure bovenstaande instructie opnieuw uitvoert, ziet u dat de configuratiewaarde van de instelling clr is bijgewerkt naar 1, maar dat de uitvoeringswaarde nog steeds is ingesteld op 0. Als u deze configuratiewijziging wilt wijzigen, moet u de RECONFIGURE opdracht uitvoeren, waardoor de uitvoeringswaarde wordt ingesteld op de huidige configuratiewaarde. Voer gewoon het RECONFIGURE queryvenster in en klik op het pictogram Uitvoeren op de werkbalk. Als u exec sp_configure nu uitvoert, zou u de waarde 1 moeten zien voor zowel de configuratie- als de uitvoeringswaarden van de clr-ingeschakelde instelling.

Nu de configuratie voor clr ingeschakeld is voltooid, kunnen we nu de beheerde GetDiscontinuedProducts opgeslagen procedure uitvoeren. Voer in het queryvenster de opdracht execGetDiscontinuedProductsin en voer deze uit. Als u de opgeslagen procedure aanroept, wordt de bijbehorende beheerde code in de GetDiscontinuedProducts methode uitgevoerd. Deze code geeft een SELECT query uit om alle producten te retourneren die niet meer beschikbaar zijn en retourneert deze gegevens naar de aanroepende toepassing. Dit is SQL Server Management Studio in dit exemplaar. Management Studio ontvangt deze resultaten en geeft ze weer in het venster Resultaten.

Afbeelding 13: De GetDiscontinuedProducts opgeslagen procedure retourneert alle stopgezette producten (klik om de volledige afbeelding weer te geven)

Stap 5: Beheerde opgeslagen procedures maken die invoerparameters accepteren

Veel van de query's en opgeslagen procedures die we in deze zelfstudies hebben gemaakt, hebben parameters gebruikt. In de tutorial Nieuwe opgeslagen procedures maken voor de Getypte DataSet TableAdapters hebben we bijvoorbeeld een opgeslagen procedure gemaakt met de naam GetProductsByCategoryID, die een invoerparameter genaamd @CategoryID accepteerde. De opgeslagen procedure heeft vervolgens alle producten geretourneerd waarvan CategoryID het veld overeenkomt met de waarde van de opgegeven @CategoryID parameter.

Als u een beheerde opgeslagen procedure wilt maken die invoerparameters accepteert, geeft u deze parameters op in de definitie van de methode. Laten we dit illustreren door een andere beheerde opgeslagen procedure toe te voegen aan het project met de ManagedDatabaseConstructs naam GetProductsWithPriceLessThan. Deze beheerde opgeslagen procedure accepteert een invoerparameter die een prijs opgeeft en retourneert alle producten waarvan UnitPrice het veld kleiner is dan de waarde van de parameter.

Als u een nieuwe opgeslagen procedure aan het project wilt toevoegen, klikt u met de rechtermuisknop op de ManagedDatabaseConstructs projectnaam en kiest u ervoor om een nieuwe opgeslagen procedure toe te voegen. Geef het bestand de naam GetProductsWithPriceLessThan.cs. Zoals we in stap 3 hebben gezien, zal hiermee een nieuw C#-klassebestand worden gemaakt met een methode genaamd GetProductsWithPriceLessThan die is geplaatst binnen de partial klasse StoredProcedures.

Werk de definitie van de GetProductsWithPriceLessThan methode bij zodat deze een SqlMoney invoerparameter met de naam price accepteert en de code schrijft die moet worden uitgevoerd en de queryresultaten worden geretourneerd:

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

De GetProductsWithPriceLessThan definitie en code van de methode lijken sterk op de definitie en code van de GetDiscontinuedProducts methode die in stap 3 is gemaakt. De enige verschillen zijn dat de GetProductsWithPriceLessThan methode accepteert als invoerparameter (price), dat de SqlCommand s-query een parameter (@MaxPrice) bevat en dat er een parameter wordt toegevoegd aan de SqlCommand verzameling s Parameters en de waarde van de price variabele wordt toegewezen.

Nadat u deze code hebt toegevoegd, implementeert u het SQL Server-project opnieuw. Ga vervolgens terug naar SQL Server Management Studio en vernieuw de map Opgeslagen procedures. U zou een nieuwe vermelding moeten zien, GetProductsWithPriceLessThan. Voer in een queryvenster de opdracht exec GetProductsWithPriceLessThan 25in en voer deze uit, waarin alle producten van minder dan $ 25 worden weergegeven, zoals in afbeelding 14 wordt weergegeven.

Afbeelding 14: Producten onder $25 worden weergegeven (klik om de afbeelding op volledige grootte weer te geven)

Stap 6: De beheerde opgeslagen procedure aanroepen vanuit de gegevenstoegangslaag

Op dit moment hebben we de GetDiscontinuedProducts en GetProductsWithPriceLessThan beheerde opgeslagen procedures aan het ManagedDatabaseConstructs project toegevoegd en geregistreerd bij de Northwind SQL Server-database. We hebben deze beheerde opgeslagen procedures ook aangeroepen vanuit SQL Server Management Studio (zie afbeelding 13 en 14). Om ervoor te zorgen dat onze ASP.NET toepassing deze beheerde opgeslagen procedures kan gebruiken, moeten we ze echter toevoegen aan de gegevenstoegangs- en bedrijfslogicalagen in de architectuur. In deze stap voegen we twee nieuwe methoden toe aan de ProductsTableAdapter in de NorthwindWithSprocs getypte gegevensset, die in eerste instantie is gemaakt in de tutorial Nieuwe opgeslagen procedures maken voor de getypte gegevensset's TableAdapters. In stap 7 voegen we bijbehorende methoden toe aan de BLL.

Open de NorthwindWithSprocs Getypte Gegevensset in Visual Studio en voeg eerst een nieuwe methode toe aan de ProductsTableAdapter, genaamd GetDiscontinuedProducts. Als u een nieuwe methode wilt toevoegen aan een TableAdapter, klikt u met de rechtermuisknop op de naam van TableAdapter in de ontwerpfunctie en kiest u de optie Query toevoegen in het contextmenu.

Opmerking

Omdat we de Northwind-database van de map naar de SQL Server 2005 Express Edition-database instantie hebben verplaatst, is het noodzakelijk dat de bijbehorende verbindingsreeks in Web.config wordt bijgewerkt om deze wijziging weer te geven. In stap 2 hebben we het bijwerken van de NORTHWNDConnectionString waarde in Web.configbesproken. Als u bent vergeten deze update te maken, wordt het foutbericht 'Query toevoegen mislukt' weergegeven. Kan de verbinding NORTHWNDConnectionString voor het object Web.config niet vinden in een dialoogvenster wanneer u probeert een nieuwe methode toe te voegen aan de TableAdapter. Als u deze fout wilt oplossen, klikt u op OK en gaat u naar Web.config en werkt u de NORTHWNDConnectionString waarde bij zoals beschreven in stap 2. Voeg vervolgens de methode opnieuw toe aan de TableAdapter. Deze keer zou het zonder fouten moeten werken.

Door een nieuwe methode te starten, wordt de Wizard voor Queryconfiguratie van de TableAdapter geopend, die we in eerdere tutorials vele keren hebben gebruikt. De eerste stap vraagt ons om op te geven hoe TableAdapter toegang moet krijgen tot de database: via een ad-hoc SQL-instructie of via een nieuwe of bestaande opgeslagen procedure. Omdat we de GetDiscontinuedProducts beheerde opgeslagen procedure al bij de database hebben gemaakt en geregistreerd, kiest u de optie Bestaande opgeslagen procedure gebruiken en drukt u op Volgende.

Afbeelding 15: Kies de optie Bestaande opgeslagen procedure gebruiken (klik hier om de volledige afbeelding weer te geven)

Het volgende scherm vraagt ons om de opgeslagen procedure die door de methode wordt aangeroepen. Kies de GetDiscontinuedProducts beheerde opgeslagen procedure in de vervolgkeuzelijst en klik op Volgende.

Afbeelding 16: Selecteer de GetDiscontinuedProducts beheerde opgeslagen procedure (klik om de volledige afbeelding weer te geven)

Vervolgens wordt u gevraagd of de opgeslagen procedure rijen, één waarde of niets retourneert. Aangezien GetDiscontinuedProducts de set van stopgezette productrijen teruggeeft, kiest u voor de eerste optie (tabellaire gegevens) en klikt u op Volgende.

Afbeelding 17: Selecteer de optie Gegevens in tabelvorm (klik om de afbeelding op volledige grootte weer te geven)

Met het laatste wizardscherm kunnen we de gebruikte patronen voor gegevenstoegang en de namen van de resulterende methoden opgeven. Laat beide selectievakjes ingeschakeld en geef de methoden FillByDiscontinued een naam en GetDiscontinuedProducts. Klik op Voltooien om de wizard te voltooien.

Afbeelding 18: Geef de methoden FillByDiscontinued een naam en GetDiscontinuedProducts (klik hier om de volledige afbeelding weer te geven)

Herhaal deze stappen om methoden te maken met de naam FillByPriceLessThan en GetProductsWithPriceLessThan in de ProductsTableAdapter voor de GetProductsWithPriceLessThan beheerde opgeslagen procedure.

In afbeelding 19 ziet u een schermopname van de DataSet Designer nadat u de methoden hebt toegevoegd aan de ProductsTableAdapter voor de GetDiscontinuedProducts en GetProductsWithPriceLessThan beheerde opgeslagen procedures.

Afbeelding 19: ProductsTableAdapter Bevat de nieuwe methoden die in deze stap zijn toegevoegd (klik om de afbeelding op volledige grootte weer te geven)

Stap 7: Bijbehorende methoden toevoegen aan de laag bedrijfslogica

Nu we de Data Access-laag hebben bijgewerkt met methoden voor het aanroepen van de beheerde opgeslagen procedures die zijn toegevoegd in stap 4 en 5, moeten we bijbehorende methoden toevoegen aan de bedrijfslogicalaag. Voeg de volgende twee methoden toe aan de ProductsBLLWithSprocs klasse:

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

Beide methoden roepen gewoon de bijbehorende DAL-methode aan en retourneren het ProductsDataTable exemplaar. De DataObjectMethodAttribute opmaak boven elke methode zorgt ervoor dat deze methoden worden opgenomen in de vervolgkeuzelijst op het tabblad SELECT van de wizard Gegevensbron configureren van ObjectDataSource.

Stap 8: De beheerde opgeslagen procedures aanroepen vanuit de presentatielaag

Nu de lagen bedrijfslogica en gegevenstoegang zijn uitgebreid met ondersteuning voor het aanroepen van de GetDiscontinuedProducts en GetProductsWithPriceLessThan beheerde opgeslagen procedures, kunnen we deze opgeslagen procedures nu weergeven via een ASP.NET pagina.

Open de ManagedFunctionsAndSprocs.aspx pagina in de AdvancedDAL map en sleep vanuit de Werkset een GridView naar de ontwerpfunctie. Stel de eigenschap van de GridView in op IDDiscontinuedProducts en koppel deze via de smart tag aan een nieuwe ObjectDataSource genaamd DiscontinuedProductsDataSource. Configureer de ObjectDataSource om de gegevens op te halen uit de ProductsBLLWithSprocs klasse en de GetDiscontinuedProducts methode.

Afbeelding 20: De ObjectDataSource configureren om de ProductsBLLWithSprocs klasse te gebruiken (klik om de afbeelding op volledige grootte weer te geven)

Afbeelding 21: Kies de GetDiscontinuedProducts methode in de Drop-Down-lijst op het tabblad SELECT (klik hier om de volledige afbeelding weer te geven)

Omdat dit raster wordt gebruikt om alleen productgegevens weer te geven, stelt u de vervolgkeuzelijsten in op de tabbladen UPDATE, INSERT en DELETE op (Geen) en klikt u vervolgens op Voltooien.

Wanneer de wizard is voltooid, wordt in Visual Studio automatisch een BoundField- of CheckBoxField-veld toegevoegd voor elk gegevensveld in het ProductsDataTableveld. Neem even de tijd om al deze velden te verwijderen, met uitzondering ProductName van en Discontinued, op welk punt de declaratieve markeringen van GridView en ObjectDataSource er ongeveer als volgt uitzien:

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

Neem even de tijd om deze pagina via een browser weer te geven. Wanneer de pagina wordt bezocht, roept de ObjectDataSource de methode ProductsBLLWithSprocs van de klasse GetDiscontinuedProducts aan. Zoals we in stap 7 hebben gezien, roept deze methode de DAL klasse ProductsDataTable methode GetDiscontinuedProducts aan, die de opgeslagen procedure GetDiscontinuedProducts aanroept. Deze opgeslagen procedure is een beheerde opgeslagen procedure en voert de code uit die we in stap 3 hebben gemaakt en retourneert de stopgezette producten.

De resultaten die door de beheerde opgeslagen procedure worden geretourneerd, worden verpakt in een ProductsDataTable door de DAL en vervolgens geretourneerd naar de BLL, die ze vervolgens retourneert naar de presentatielaag waar ze zijn gebonden aan de GridView en worden weergegeven. Zoals verwacht vermeldt het raster de producten die zijn stopgezet.

Afbeelding 22: De stopgezette producten worden vermeld (klik hier om de volledige afbeelding weer te geven)

Voor verdere oefening voegt u een tekstvak en een andere GridView toe aan de pagina. Laat deze GridView de producten weergeven die kleiner zijn dan het bedrag dat in het tekstvak is ingevoerd door de methode ProductsBLLWithSprocs van de klasse GetProductsWithPriceLessThan aan te roepen.

Stap 9: T-SQL UDF's maken en aanroepen

User-Defined Functions of UDF's zijn databaseobjecten die de semantiek van functies in programmeertalen nauw nabootsen. Net als een functie in C# kunnen UDF's een variabel aantal invoerparameters bevatten en een waarde van een bepaald type retourneren. Een UDF kan scalaire gegevens retourneren: een tekenreeks, een geheel getal, enzovoort, of tabellaire gegevens. Laten we eens kijken naar beide typen UDF's, te beginnen met een UDF die een scalair gegevenstype retourneert.

Met de volgende UDF wordt de geschatte waarde van de voorraad voor een bepaald product berekend. Dit doet u door drie invoerparameters ( de UnitPrice, UnitsInStocken Discontinued waarden voor een bepaald product ) in te nemen en een waarde van het type moneyte retourneren. Het berekent de geschatte waarde van de voorraad door UnitPrice te vermenigvuldigen met UnitsInStock. Voor stopgezette items wordt deze waarde gehalveerd.

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

Zodra deze UDF is toegevoegd aan de database, kunt u deze vinden via Management Studio door de map Programmeerbaarheid uit te vouwen, vervolgens Functions en vervolgens Scalar-waardefuncties. Deze kan als volgt worden gebruikt in een SELECT query:

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

Ik heb de udf_ComputeInventoryValue UDF toegevoegd aan de Northwind-database; In afbeelding 23 ziet u de uitvoer van de bovenstaande SELECT query wanneer deze wordt weergegeven via Management Studio. Houd er ook rekening mee dat de UDF wordt vermeld onder de map Scalar-waarde Functies in de Objectverkenner.

Afbeelding 23: De inventariswaarden van elk product worden vermeld (klik hier om de volledige afbeelding weer te geven)

UDF's kunnen ook tabelgegevens retourneren. We kunnen bijvoorbeeld een UDF maken die producten retourneert die deel uitmaken van een bepaalde categorie:

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
)

De udf_GetProductsByCategoryID UDF accepteert een @CategoryID invoerparameter en retourneert de resultaten van de opgegeven SELECT query. Na het maken kan naar deze UDF worden verwezen in de FROM (of JOIN) component van een SELECT query. In het volgende voorbeeld worden de ProductID, ProductNameen CategoryID waarden voor elk van de dranken geretourneerd.

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

Ik heb de udf_GetProductsByCategoryID UDF toegevoegd aan de Northwind-database; Afbeelding 24 toont de uitvoer van de bovenstaande SELECT query wanneer deze wordt weergegeven via Management Studio. UDF's die tabelgegevens retourneren, zijn te vinden in de map Tabelwaardefuncties van Objectverkenner.

Afbeelding 24: De ProductID, ProductNameen CategoryID worden vermeld voor elke drank (klik om de afbeelding op volledige grootte weer te geven)

Opmerking

Zie Inleiding tot User-Defined Functions voor meer informatie over het maken en gebruiken van UDF's. Bekijk ook voordelen en nadelen van User-Defined Functions.

Stap 10: een beheerde UDF maken

De udf_ComputeInventoryValue en udf_GetProductsByCategoryID UDF's die in de bovenstaande voorbeelden zijn gemaakt, zijn T-SQL-databaseobjecten. SQL Server 2005 ondersteunt ook beheerde UDF's, die kunnen worden toegevoegd aan het ManagedDatabaseConstructs project, net zoals de beheerde opgeslagen procedures uit stap 3 en 5. Voor deze stap gaan we de udf_ComputeInventoryValue UDF implementeren in beheerde code.

Als u een beheerde UDF wilt toevoegen aan het project, klikt u met de ManagedDatabaseConstructs rechtermuisknop op de projectnaam in Solution Explorer en kiest u ervoor een nieuw item toe te voegen. Selecteer de User-Defined-sjabloon in het dialoogvenster Nieuw item toevoegen en geef het nieuwe UDF-bestand udf_ComputeInventoryValue_Managed.cseen naam.

Afbeelding 25: Een nieuwe beheerde UDF toevoegen aan het ManagedDatabaseConstructs project (klik om de volledige afbeelding weer te geven)

Met de sjabloon User-Defined functie wordt een partial klasse gemaakt met de naam UserDefinedFunctions van een methode waarvan de naam hetzelfde is als de naam van het klassebestand (udf_ComputeInventoryValue_Managedin dit geval). Deze methode is ingericht met behulp van het SqlFunction kenmerk, dat de methode markeert als een beheerde 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");
    }
};

De udf_ComputeInventoryValue methode retourneert momenteel een SqlString object en accepteert geen invoerparameters. We moeten de methodedefinitie bijwerken zodat deze drie invoerparameters accepteert - UnitPriceen UnitsInStockDiscontinued - en een SqlMoney object retourneert. De logica voor het berekenen van de voorraadwaarde is identiek aan die in de 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;
}

De invoerparameters van de UDF-methode zijn van de bijbehorende SQL-typen: SqlMoney voor het UnitPrice veld, SqlInt16 voor UnitsInStocken SqlBoolean voor Discontinued. Deze gegevenstypen weerspiegelen de typen die in de Products tabel zijn gedefinieerd: de UnitPrice kolom is van het type money, de kolom van het UnitsInStock type smallinten de kolom van het Discontinued type bit.

De code begint met het maken van een SqlMoney exemplaar met de naam inventoryValue waaraan een waarde van 0 is toegewezen. De Products tabel maakt databasewaarden NULL in de UnitsInPrice en UnitsInStock kolommen mogelijk. Daarom moeten we eerst controleren of deze waarden NULL's bevatten, wat we doen via de eigenschap SqlMoney van het IsNull object. Als zowel UnitPrice als UnitsInStock niet-NULL-waarden bevatten, berekenen we het inventoryValue als het product van de twee. Discontinued Als dit waar is, halveren we de waarde.

Opmerking

Met SqlMoney het object kunnen slechts twee SqlMoney exemplaren worden vermenigvuldigd. Een SqlMoney instantie kan niet worden vermenigvuldigd met een drijvendekommagetal. Daarom, om deze te halveren inventoryValue, vermenigvuldigen we het met een nieuw SqlMoney exemplaar met de waarde 0,5.

Stap 11: De beheerde UDF implementeren

Nu de beheerde UDF is gemaakt, zijn we klaar om deze te implementeren in de Northwind-database. Zoals we in stap 4 hebben gezien, worden de beheerde objecten in een SQL Server-project geïmplementeerd door met de rechtermuisknop te klikken op de projectnaam in Solution Explorer en de optie Implementeren te kiezen in het contextmenu.

Zodra u het project hebt geïmplementeerd, gaat u terug naar SQL Server Management Studio en vernieuwt u de map Scalar-waardefuncties. U ziet nu twee vermeldingen:

• dbo.udf_ComputeInventoryValue - de T-SQL UDF die is gemaakt in stap 9, en
• dbo.udf ComputeInventoryValue_Managed - de beheerde UDF die in stap 10 is gemaakt en zojuist is geïmplementeerd.

Als u deze beheerde UDF wilt testen, voert u de volgende query uit vanuit Management Studio:

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

Deze opdracht maakt gebruik van de beheerde udf ComputeInventoryValue_Managed UDF in plaats van de T-SQL udf_ComputeInventoryValue UDF, maar de uitvoer is hetzelfde. Raadpleeg afbeelding 23 om een schermopname te zien van de UDF-uitvoer.

Stap 12: Fouten opsporen in de beheerde databaseobjecten

In de zelfstudie Opgeslagen procedures voor foutopsporing hebben we de drie opties besproken voor het opsporen van fouten in SQL Server via Visual Studio: Direct Database-foutopsporing, Toepassingsopsporing en Foutopsporing vanuit een SQL Server-project. Beheerde databaseobjecten kunnen niet worden opgespoord via Direct Database-foutopsporing, maar kunnen worden opgespoord vanuit een clienttoepassing en rechtstreeks vanuit het SQL Server-project. Om foutopsporing te laten werken, moet de SQL Server 2005-database sql/CLR-foutopsporing toestaan. Zoals u weet, vroeg Visual Studio ons of we SQL/CLR-foutopsporing wilden inschakelen toen we het ManagedDatabaseConstructs-project in Visual Studio voor het eerst maakten (zie afbeelding 6 in stap 2). Deze instelling kan worden gewijzigd door met de rechtermuisknop op de database te klikken vanuit het Server Explorer-venster.

Afbeelding 26: Controleren of sql/CLR-foutopsporing is toegestaan in de database

Stel dat we fouten willen opsporen in de GetProductsWithPriceLessThan beheerde opgeslagen procedure. We beginnen met het instellen van een onderbrekingspunt in de code van de GetProductsWithPriceLessThan methode.

Afbeelding 27: Een onderbrekingspunt instellen in de GetProductsWithPriceLessThan methode (klik om de afbeelding op volledige grootte weer te geven)

Laten we eerst kijken naar het opsporen van fouten in de beheerde databaseobjecten uit het SQL Server-project. Omdat onze oplossing twee projecten bevat: het ManagedDatabaseConstructs SQL Server-project samen met onze website, om fouten op te sporen vanuit het SQL Server-project, moeten we Visual Studio instrueren om het ManagedDatabaseConstructs SQL Server-project te starten wanneer we beginnen met het opsporen van fouten. Klik met de rechtermuisknop op het ManagedDatabaseConstructs project in Solution Explorer en kies de optie Instellen als Opstartproject in het contextmenu.

Wanneer het ManagedDatabaseConstructs project wordt gestart vanuit het foutopsporingsprogramma, worden de SQL-instructies in het Test.sql bestand uitgevoerd, die zich in de Test Scripts map bevinden. Als u bijvoorbeeld de GetProductsWithPriceLessThan beheerde opgeslagen procedure wilt testen, vervangt u de bestaande Test.sql bestandsinhoud door de volgende instructie, die de GetProductsWithPriceLessThan beheerde opgeslagen procedure aanroept die de @CategoryID waarde van 14,95 doorgeeft:

exec GetProductsWithPriceLessThan 14.95

Nadat u het bovenstaande script Test.sqlhebt ingevoerd, start u de foutopsporing door naar het menu Foutopsporing te gaan en Start debugging te kiezen of door op F5 of het groene afspeelpictogram in de werkbalk te drukken. Hiermee worden de projecten in de oplossing gebouwd, worden de beheerde databaseobjecten geïmplementeerd in de Northwind-database en wordt het Test.sql script uitgevoerd. Op dit moment wordt het onderbrekingspunt bereikt en kunnen we de GetProductsWithPriceLessThan methode doorlopen, de waarden van de invoerparameters onderzoeken, enzovoort.

Afbeelding 28: Het onderbrekingspunt in de GetProductsWithPriceLessThan methode was bereikt (klik om de afbeelding op volledige grootte weer te geven)

Om fouten in een SQL-databaseobject te kunnen opsporen via een clienttoepassing, is het noodzakelijk dat de database wordt geconfigureerd voor het ondersteunen van foutopsporing van toepassingen. Klik met de rechtermuisknop op de database in Server Explorer en zorg ervoor dat de optie Toepassingsopsporing is ingeschakeld. Bovendien moeten we de ASP.NET-toepassing configureren om te integreren met het SQL Debugger en om groepsgewijze verbindingen uit te schakelen. Deze stappen zijn uitgebreid besproken in stap 2 van de zelfstudie Opgeslagen procedures voor foutopsporing .

Nadat u de ASP.NET toepassing en database hebt geconfigureerd, stelt u de ASP.NET-website in als het opstartproject en start u de foutopsporing. Als u een pagina bezoekt die een van de beheerde objecten aanroept die een onderbrekingspunt heeft, wordt de toepassing gestopt en wordt de controle overgezet naar het foutopsporingsprogramma, waar u de code kunt doorlopen, zoals wordt weergegeven in afbeelding 28.

Stap 13: Beheerde databaseobjecten handmatig compileren en implementeren

Met SQL Server Projects kunt u eenvoudig beheerde databaseobjecten maken, compileren en implementeren. Helaas zijn SQL Server Projects alleen beschikbaar in de edities Professional en Team Systems van Visual Studio. Als u Visual Web Developer of de Standard Edition van Visual Studio gebruikt en beheerde databaseobjecten wilt gebruiken, moet u deze handmatig maken en implementeren. Dit omvat vier stappen:

1. Maak een bestand met de broncode voor het beheerde databaseobject.
2. Compileer het object in een assembly,
3. Registreer de assembly bij de SQL Server 2005-database en
4. Maak een databaseobject in SQL Server dat verwijst naar de juiste methode in de assembly.

Ter illustratie van deze taken maken we een nieuwe beheerde opgeslagen procedure die die producten retourneert waarvan UnitPrice de waarde groter is dan een opgegeven waarde. Maak een nieuw bestand op uw computer met de naam GetProductsWithPriceGreaterThan.cs en voer de volgende code in het bestand in (u kunt Visual Studio, Kladblok of een teksteditor gebruiken om dit te doen):

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

Deze code is bijna identiek aan die van de GetProductsWithPriceLessThan methode die in stap 5 is gemaakt. De enige verschillen zijn de methodenamen, de WHERE component en de parameternaam die in de query wordt gebruikt. Terug in de GetProductsWithPriceLessThan methode staat de WHERE clausule: WHERE UnitPrice < @MaxPrice. Hier, in GetProductsWithPriceGreaterThan, gebruiken we: WHERE UnitPrice > @MinPrice .

Deze klasse moet nu worden gecompileerd in een assembly. Navigeer vanaf de opdrachtregel naar de map waarin u het GetProductsWithPriceGreaterThan.cs bestand hebt opgeslagen en gebruik de C#-compiler (csc.exe) om het klassebestand te compileren in een assembly:

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

Als de map die csc.exe bevat niet in het systeem is PATH, moet u het volledige pad, %WINDOWS%\Microsoft.NET\Framework\version\, als volgt verwijzen:

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

Afbeelding 29: Compileren GetProductsWithPriceGreaterThan.cs in een assembly (klik om de volledige afbeelding weer te geven)

De /t vlag geeft aan dat het C#-klassebestand moet worden gecompileerd in een DLL (in plaats van een uitvoerbaar bestand). De /out vlag geeft de naam van de resulterende assembly op.

Opmerking

In plaats van het GetProductsWithPriceGreaterThan.cs klassebestand te compileren vanaf de opdrachtregel, kunt u visual C# Express Edition ook gebruiken of een afzonderlijk klassebibliotheekproject maken in Visual Studio Standard Edition. S ren Jacob Lauritsen heeft zo'n Visual C# Express Edition-project geleverd met code voor de GetProductsWithPriceGreaterThan opgeslagen procedure en de twee beheerde opgeslagen procedures en UDF die zijn gemaakt in stap 3, 5 en 10. S ren s-project bevat ook de T-SQL-opdrachten die nodig zijn om de bijbehorende databaseobjecten toe te voegen.

Nu de code is gecompileerd in een assembly, zijn we klaar om de assembly te registreren in de SQL Server 2005-database. Dit kan worden uitgevoerd via T-SQL, met behulp van de opdracht CREATE ASSEMBLYof via SQL Server Management Studio. Laten we ons richten op het gebruik van Management Studio.

Vouw vanuit Management Studio de map Programmeerbaarheid uit in de Northwind-database. Een van de submappen is Assemblies. Als u handmatig een nieuwe assembly aan de database wilt toevoegen, klikt u met de rechtermuisknop op de map Assembly's en kiest u New Assembly in het contextmenu. Hiermee wordt het dialoogvenster Nieuwe samenstelling weergegeven (zie afbeelding 30). Klik op de knop Bladeren, selecteer de ManuallyCreatedDBObjects.dll assembly die we zojuist hebben gecompileerd en klik vervolgens op OK om de assembly toe te voegen aan de database. U zou de ManuallyCreatedDBObjects.dll assembly niet in de Objectverkenner moeten zien.

Afbeelding 30: Voeg de ManuallyCreatedDBObjects.dll assembly toe aan de database (klik om de volledige afbeelding weer te geven)

Afbeelding 31: De ManuallyCreatedDBObjects.dll waarde wordt vermeld in de Objectverkenner

Hoewel we de assembly hebben toegevoegd aan de Northwind-database, moeten we nog een opgeslagen procedure koppelen aan de GetProductsWithPriceGreaterThan methode in de assembly. U doet dit door een nieuw queryvenster te openen en het volgende script uit te voeren:

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

Hiermee maakt u een nieuwe opgeslagen procedure in de Northwind-database met de naam GetProductsWithPriceGreaterThan en koppelt u deze aan de beheerde methode GetProductsWithPriceGreaterThan (die zich in de klasse StoredProceduresbevindt, die zich in de assembly ManuallyCreatedDBObjectsbevindt).

Nadat u het bovenstaande script hebt uitgevoerd, vernieuwt u de map Opgeslagen procedures in Objectverkenner. U ziet nu een nieuwe opgeslagen procedurevermelding, GetProductsWithPriceGreaterThan die ernaast een vergrendelingspictogram bevat. Als u deze opgeslagen procedure wilt testen, voert u het volgende script in het queryvenster in en voert u dit uit:

exec GetProductsWithPriceGreaterThan 24.95

Zoals in afbeelding 32 wordt weergegeven, geeft de bovenstaande opdracht informatie weer voor deze producten met een UnitPrice waarde van meer dan $ 24,95.

Afbeelding 32: De ManuallyCreatedDBObjects.dll afbeelding wordt weergegeven in objectverkenner (klik om de afbeelding op volledige grootte weer te geven)

Samenvatting

Microsoft SQL Server 2005 biedt integratie met de Common Language Runtime (CLR), waarmee databaseobjecten kunnen worden gemaakt met beheerde code. Voorheen konden deze databaseobjecten alleen worden gemaakt met behulp van T-SQL, maar nu kunnen we deze objecten maken met behulp van .NET-programmeertalen zoals C#. In deze handleiding hebben we twee beheerde opgeslagen procedures en een beheerde User-Defined-functie gemaakt.

Het SQL Server-projecttype van Visual Studio vereenvoudigt het maken, compileren en implementeren van beheerde databaseobjecten. Bovendien biedt het uitgebreide ondersteuning voor foutopsporing. Sql Server-projecttypen zijn echter alleen beschikbaar in de edities Professional en Team Systems van Visual Studio. Voor degenen die Visual Web Developer of de Standard Edition van Visual Studio gebruiken, moeten de stappen voor het maken, compileren en implementeren handmatig worden uitgevoerd, zoals we in stap 13 hebben gezien.

Veel plezier met programmeren!

Meer lezen

Raadpleeg de volgende bronnen voor meer informatie over de onderwerpen die in deze zelfstudie worden besproken:

• Voordelen en nadelen van User-Defined-functies
• SQL Server 2005-objecten maken in beheerde code
• Handleiding voor: Het maken en uitvoeren van een opgeslagen CLR SQL Server-procedure
• Procedure: Een CLR SQL Server-User-Defined-functie maken en uitvoeren
• Procedure: Het Test.sql script bewerken om SQL-objecten uit te voeren
• Inleiding tot door de gebruiker gedefinieerde functies
• Beheerde code en SQL Server 2005 (Video)
• Transact-SQL-verwijzing
• Overzicht: Een opgeslagen procedure maken in beheerde code

Over de auteur

Scott Mitchell, auteur van zeven ASP/ASP.NET-boeken en oprichter van 4GuysFromRolla.com, werkt sinds 1998 met Microsoft-webtechnologieën. Scott werkt als onafhankelijk consultant, trainer en schrijver. Zijn laatste boek is Sams Teach Yourself ASP.NET 2.0 in 24 uur. Hij kan worden bereikt op mitchell@4GuysFromRolla.com.

Speciale dank aan

Deze tutorialreeks is beoordeeld door veel behulpzame beoordelers. Hoofdrevisor voor deze tutorial was Søren Jacob Lauritsen. Naast het bekijken van dit artikel, heeft S ren ook het Visual C# Express Edition-project gemaakt dat in dit artikel is opgenomen in de download voor het handmatig compileren van de beheerde databaseobjecten. Bent u geïnteresseerd in het bekijken van mijn aanstaande MSDN-artikelen? Zo ja, laat iets van je horen via mitchell@4GuysFromRolla.com.

VorigeVolgende