Verwenden von gespeicherten Prozeduren und benutzerdefinierten Funktionen mit verwaltetem Code (C#)

von Scott Mitchell

PDF herunterladen

Microsoft SQL Server 2005 ist in die .NET Common Language Runtime integriert, damit Entwickler Datenbankobjekte über verwalteten Code erstellen können. In diesem Tutorial erfahren Sie, wie Sie mit Ihrem Visual Basic- oder C#-Code verwaltete gespeicherte Prozeduren und verwaltete benutzerdefinierte Funktionen erstellen. Wir sehen auch, wie Sie mit diesen Editionen von Visual Studio solche verwalteten Datenbankobjekte debuggen können.

Einführung

Datenbanken wie microsoft SQL Server 2005 verwenden transact-strukturierte Abfragesprache (T-SQL) zum Einfügen, Ändern und Abrufen von Daten. Die meisten Datenbanksysteme enthalten Konstrukte zum Gruppieren einer Reihe von SQL-Anweisungen, die dann als einzelne wiederverwendbare Einheit ausgeführt werden können. Gespeicherte Prozeduren sind ein Beispiel. Eine andere ist benutzerdefinierte Funktionen(UDFs), ein Konstrukt, das wir in Schritt 9 genauer untersuchen werden.

Im Kern ist SQL für die Arbeit mit Datensätzen konzipiert. Die SELECTAnweisungen , UPDATE, und DELETE gelten inhärent für alle Datensätze in der entsprechenden Tabelle und werden nur durch ihre WHERE Klauseln eingeschränkt. Es gibt jedoch viele Sprachfeatures, die für das Arbeiten mit einem Datensatz gleichzeitig und für die Bearbeitung skalarer Daten konzipiert sind. CURSOR s ermöglicht, dass eine Reihe von Datensätzen einzeln durchlaufen werden. Zeichenfolgenbearbeitungsfunktionen wie LEFT, CHARINDEXund PATINDEX arbeiten mit skalaren Daten. SQL enthält auch Ablaufsteuerungsanweisungen wie IF und WHILE.

Vor Microsoft SQL Server 2005 konnten gespeicherte Prozeduren und UDFs nur als Sammlung von T-SQL-Anweisungen definiert werden. SQL Server 2005 wurde jedoch entwickelt, um die Integration in die Common Language Runtime (CLR) bereitzustellen, die von allen .NET-Assemblys verwendete Runtime ist. Daher können die gespeicherten Prozeduren und UDFs in einer SQL Server 2005-Datenbank mit verwaltetem Code erstellt werden. Das heißt, Sie können eine gespeicherte Prozedur oder UDF als Methode in einer C#-Klasse erstellen. Dadurch können diese gespeicherten Prozeduren und UDFs Funktionen im .NET Framework und aus Ihren eigenen benutzerdefinierten Klassen nutzen.

In diesem Tutorial untersuchen wir, wie Sie verwaltete gespeicherte Prozeduren und User-Defined Functions erstellen und in unsere Northwind-Datenbank integrieren. Los geht's!

Hinweis

Verwaltete Datenbankobjekte bieten einige Vorteile gegenüber ihren SQL-Entsprechungen. Sprachreichtum und Vertrautheit sowie die Möglichkeit, vorhandenen Code und Logik wiederzuverwenden, sind die Standard Vorteile. Verwaltete Datenbankobjekte sind jedoch wahrscheinlich weniger effizient, wenn sie mit Datensätzen arbeiten, die nicht viel prozedurale Logik erfordern. Eine ausführlichere Diskussion über die Vorteile der Verwendung von verwaltetem Code im Vergleich zu T-SQL finden Sie unter Vorteile der Verwendung von verwaltetem Code zum Erstellen von Datenbankobjekten.

Schritt 1: Verschieben der Northwind-Datenbank aus App_Data

Alle unsere Tutorials haben bisher eine Microsoft SQL Server 2005 Express Edition-Datenbankdatei im Ordner der App_Data Webanwendung verwendet. Das Platzieren der Datenbank in App_Data vereinfachter Verteilung und Ausführung dieser Tutorials, da sich alle Dateien in einem Verzeichnis befinden und keine zusätzlichen Konfigurationsschritte zum Testen des Tutorials erforderlich waren.

In diesem Tutorial verschieben wir jedoch die Northwind-Datenbank aus App_Data und registrieren sie explizit bei der SQL Server 2005 Express Edition Datenbank instance. Während wir die Schritte für dieses Tutorial mit der Datenbank im App_Data Ordner ausführen können, wird eine Reihe der Schritte wesentlich vereinfacht, indem die Datenbank explizit bei der SQL Server 2005 Express Edition Datenbank instance registriert wird.

Der Download für dieses Tutorial enthält die beiden Datenbankdateien - NORTHWND.MDF und NORTHWND_log.LDF - in einem Ordner mit dem Namen DataFiles. Wenn Sie ihre eigene Implementierung der Tutorials befolgen, schließen Sie Visual Studio, und verschieben Sie die NORTHWND.MDF Dateien und NORTHWND_log.LDF aus dem Ordner der Website App_Data in einen Ordner außerhalb der Website. Nachdem die Datenbankdateien in einen anderen Ordner verschoben wurden, muss die Northwind-Datenbank beim SQL Server 2005 Express Edition Datenbank-instance registriert werden. Dies kann von SQL Server Management Studio aus erfolgen. Wenn Sie eine Nicht-Express Edition von SQL Server 2005 auf Ihrem Computer installiert haben, ist Management Studio wahrscheinlich bereits installiert. Wenn Sie nur SQL Server 2005 Express Edition auf Ihrem Computer haben, nehmen Sie sich einen Moment Zeit, um Microsoft SQL Server Management Studio herunterzuladen und zu installieren.

Starten Sie SQL Server Management Studio. Wie Abbildung 1 zeigt, fragt Management Studio, mit welchem Server eine Verbindung hergestellt werden soll. Geben Sie localhost\SQLExpress als Servernamen ein, wählen Sie windows-Authentifizierung in der Dropdownliste Authentifizierung aus, und klicken Sie auf Verbinden.

Abbildung 1: Herstellen einer Verbindung mit der entsprechenden Datenbankinstanz

Sobald Sie eine Verbindung hergestellt haben, werden im fenster Objekt-Explorer Informationen zum SQL Server 2005 Express Edition Datenbank instance aufgelistet, einschließlich der Datenbanken, Sicherheitsinformationen, Verwaltungsoptionen usw.

Wir müssen die Northwind-Datenbank im DataFiles Ordner (oder wo auch immer Sie sie verschoben haben) an den SQL Server 2005 Express Edition Datenbank-instance anfügen. Klicken Sie mit der rechten Maustaste auf den Ordner Datenbanken, und wählen Sie im Kontextmenü die Option Anfügen aus. Dadurch wird das Dialogfeld Datenbanken anfügen geöffnet. Klicken Sie auf die Schaltfläche Hinzufügen, führen Sie einen Drilldown zur entsprechenden NORTHWND.MDF Datei durch, und klicken Sie auf OK. An diesem Punkt sollte Ihr Bildschirm ähnlich wie Abbildung 2 aussehen.

Abbildung 2: Herstellen einer Verbindung mit der entsprechenden Datenbankinstanz (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Hinweis

Wenn Sie über Management Studio eine Verbindung mit dem SQL Server 2005 Express Edition instance herstellen, können Sie im Dialogfeld Datenbanken anfügen keinen Drilldown in Benutzerprofilverzeichnisse wie "Eigene Dokumente" durchführen. Stellen Sie daher sicher, dass Sie die NORTHWND.MDF Dateien und NORTHWND_log.LDF in einem Nichtbenutzerprofilverzeichnis ablegen.

Klicken Sie auf die Schaltfläche OK, um die Datenbank anzufügen. Das Dialogfeld Datenbanken anfügen wird geschlossen, und die Objekt-Explorer sollte nun die gerade angefügte Datenbank auflisten. Wahrscheinlich hat die Northwind-Datenbank einen Namen wie 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF. Benennen Sie die Datenbank in Northwind um, indem Sie mit der rechten Maustaste auf die Datenbank klicken und Umbenennen auswählen.

Abbildung 3: Umbenennen der Datenbank in Northwind

Schritt 2: Erstellen einer neuen Projektmappe und SQL Server-Projekts in Visual Studio

Um verwaltete gespeicherte Prozeduren oder UDFs in SQL Server 2005 zu erstellen, schreiben wir die gespeicherte Prozedur und die UDF-Logik als C#-Code in einer Klasse. Nachdem der Code geschrieben wurde, müssen wir diese Klasse in eine Assembly (eine .dll Datei) kompilieren, die Assembly bei der SQL Server Datenbank registrieren und dann eine gespeicherte Prozedur oder ein UDF-Objekt in der Datenbank erstellen, das auf die entsprechende Methode in der Assembly verweist. Diese Schritte können alle manuell ausgeführt werden. Wir können den Code in einem beliebigen Text-Editor erstellen, ihn über die Befehlszeile mithilfe des C#-Compilers (csc.exe) kompilieren, ihn mit dem Befehl oder in Management Studio bei der CREATE ASSEMBLY Datenbank registrieren und die gespeicherte Prozedur oder das UDF-Objekt auf ähnliche Weise hinzufügen. Glücklicherweise enthalten die Professional- und Team Systems-Versionen von Visual Studio einen SQL Server Project-Typ, der diese Aufgaben automatisiert. In diesem Tutorial führen wir die Verwendung des SQL Server Project-Typs durch, um eine verwaltete gespeicherte Prozedur und eine UDF zu erstellen.

Hinweis

Wenn Sie Visual Web Developer oder die Standard Edition von Visual Studio verwenden, müssen Sie stattdessen den manuellen Ansatz verwenden. Schritt 13 enthält ausführliche Anweisungen zum manuellen Ausführen dieser Schritte. Ich empfehle Ihnen, die Schritte 2 bis 12 vor dem Lesen von Schritt 13 zu lesen, da diese Schritte wichtige SQL Server Konfigurationsanweisungen enthalten, die unabhängig davon angewendet werden müssen, welche Version von Visual Studio Sie verwenden.

Öffnen Sie zunächst Visual Studio. Wählen Sie im Menü Datei die Option Neues Projekt aus, um das Dialogfeld Neues Projekt anzuzeigen (siehe Abbildung 4). Führen Sie einen Drilldown zum Projekttyp Datenbank aus, und wählen Sie dann in den auf der rechten Seite aufgeführten Vorlagen aus, um eine neue SQL Server Project zu erstellen. Ich habe mich für den Namen dieses Projekts ManagedDatabaseConstructs entschieden und es in eine Projektmappe mit dem Namen Tutorial75platziert.

Abbildung 4: Erstellen eines neuen SQL Server-Projekts (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)

Klicken Sie im Dialogfeld Neues Projekt auf die Schaltfläche OK, um die Projektmappe und SQL Server Projekt zu erstellen.

Ein SQL Server Project ist an eine bestimmte Datenbank gebunden. Daher werden wir nach dem Erstellen des neuen SQL Server Project sofort aufgefordert, diese Informationen anzugeben. Abbildung 5 zeigt das Dialogfeld Neuer Datenbankverweis, das ausgefüllt wurde, um auf die Northwind-Datenbank zu verweisen, die wir in der SQL Server 2005 Express Edition Datenbank registriert haben instance zurück in Schritt 1.

Abbildung 5: Zuordnen des SQL Server-Projekts zur Northwind-Datenbank

Um die verwalteten gespeicherten Prozeduren und UDFs zu debuggen, die wir in diesem Projekt erstellen, müssen wir sql/CLR-Debugunterstützung für die Verbindung aktivieren. Wann immer ein SQL Server Project einer neuen Datenbank zugeordnet wird (wie in Abbildung 5), fragt Visual Studio uns, ob wir das SQL/CLR-Debuggen für die Verbindung aktivieren möchten (siehe Abbildung 6). Klicken Sie auf „Ja“.

Abbildung 6: Aktivieren des SQL/CLR-Debuggens

An diesem Punkt wurde die neue SQL Server Project der Projektmappe hinzugefügt. Sie enthält einen Ordner mit dem Namen Test Scripts einer Datei namens Test.sql, die zum Debuggen der im Projekt erstellten verwalteten Datenbankobjekte verwendet wird. In Schritt 12 wird das Debuggen untersucht.

Wir können diesem Projekt jetzt neue verwaltete gespeicherte Prozeduren und UDFs hinzufügen, aber bevor wir dies tun, sollten wir zuerst unsere vorhandene Webanwendung in die Projektmappe einschließen. Wählen Sie im Menü Datei die Option Hinzufügen aus, und wählen Sie Vorhandene Website aus. Navigieren Sie zum entsprechenden Websiteordner, und klicken Sie auf OK. Wie Abbildung 7 zeigt, wird die Projektmappe so aktualisiert, dass sie zwei Projekte enthält: die Website und das ManagedDatabaseConstructs SQL Server Project.

Abbildung 7: Die Projektmappen-Explorer enthält jetzt zwei Projekte

Der NORTHWNDConnectionString Wert in Web.config verweist derzeit auf die NORTHWND.MDF Datei im App_Data Ordner. Da wir diese Datenbank aus App_Data entfernt und explizit im SQL Server 2005 Express Edition Datenbank-instance registriert haben, müssen wir den NORTHWNDConnectionString Wert entsprechend aktualisieren. Öffnen Sie die Web.config Datei auf der Website, und ändern Sie den NORTHWNDConnectionString Wert so, dass der Verbindungszeichenfolge wie folgt lautet: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. Nach dieser Änderung sollte Ihr <connectionStrings> Abschnitt in Web.config wie folgt aussehen:

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

Hinweis

Wie im vorherigen Tutorial erläutert, müssen wir beim Debuggen eines SQL Server-Objekts aus einer Clientanwendung, z. B. einer ASP.NET Website, das Verbindungspooling deaktivieren. Die oben gezeigte Verbindungszeichenfolge deaktiviert das Verbindungspooling ( Pooling=false ). Wenn Sie nicht planen, die verwalteten gespeicherten Prozeduren und UDFs über die ASP.NET-Website zu debuggen, aktivieren Sie das Verbindungspooling.

Schritt 3: Erstellen einer verwalteten gespeicherten Prozedur

Um der Northwind-Datenbank eine verwaltete gespeicherte Prozedur hinzuzufügen, müssen Sie zunächst die gespeicherte Prozedur als Methode im SQL Server Project erstellen. Klicken Sie im Projektmappen-Explorer mit der rechten Maustaste auf den ManagedDatabaseConstructs Projektnamen, und wählen Sie ein neues Element aus. Dadurch wird das Dialogfeld Neues Element hinzufügen angezeigt, in dem die Typen der verwalteten Datenbankobjekte aufgelistet sind, die dem Projekt hinzugefügt werden können. Wie Abbildung 8 zeigt, umfasst dies unter anderem gespeicherte Prozeduren und User-Defined Functions.

Beginnen wir mit dem Hinzufügen einer gespeicherten Prozedur, die einfach alle Produkte zurückgibt, die eingestellt wurden. Nennen Sie die neue gespeicherte Prozedurdatei GetDiscontinuedProducts.cs.

Abbildung 8: Hinzufügen einer neuen gespeicherten Prozedur namens GetDiscontinuedProducts.cs (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Dadurch wird eine neue C#-Klassendatei mit dem folgenden Inhalt erstellt:

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

Beachten Sie, dass die gespeicherte Prozedur als static Methode in einer partial Klassendatei mit dem Namen StoredProceduresimplementiert wird. Darüber hinaus ist die GetDiscontinuedProducts -Methode mit SqlProcedure attributedem versehen, wodurch die -Methode als gespeicherte Prozedur markiert wird.

Der folgende Code erstellt ein SqlCommand -Objekt und legt es CommandText auf eine SELECT Abfrage fest, die alle Spalten aus der Products Tabelle für Produkte zurückgibt, deren Discontinued Feld gleich 1 ist. Anschließend wird der Befehl ausgeführt und die Ergebnisse zurück an die Clientanwendung gesendet. Fügen Sie diesen Code der Methode GetDiscontinuedProducts hinzu.

// 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 verwalteten Datenbankobjekte haben Zugriff auf ein SqlContext Objekt , das den Kontext des Aufrufers darstellt. Der SqlContext ermöglicht den Zugriff auf ein SqlPipe Objekt über seine Pipe -Eigenschaft. Dieses SqlPipe Objekt wird verwendet, um Informationen zwischen der SQL Server Datenbank und der aufrufenden Anwendung zu überfähren. Wie der Name schon sagt, führt die ExecuteAndSend Methode ein übergebenes SqlCommand Objekt aus und sendet die Ergebnisse zurück an die Clientanwendung.

Hinweis

Verwaltete Datenbankobjekte eignen sich am besten für gespeicherte Prozeduren und UDFs, die prozedurale Logik anstelle einer setbasierten Logik verwenden. Die prozedurale Logik umfasst das Arbeiten mit Datensätzen auf Zeilenbasis oder das Arbeiten mit skalaren Daten. Die GetDiscontinuedProducts soeben erstellte Methode beinhaltet jedoch keine prozedurale Logik. Daher wäre es idealerweise als gespeicherte T-SQL-Prozedur implementiert. Sie wird als verwaltete gespeicherte Prozedur implementiert, um die erforderlichen Schritte zum Erstellen und Bereitstellen verwalteter gespeicherter Prozeduren zu veranschaulichen.

Schritt 4: Bereitstellen der verwalteten gespeicherten Prozedur

Nachdem dieser Code abgeschlossen ist, können wir ihn in der Northwind-Datenbank bereitstellen. Beim Bereitstellen eines SQL Server Project wird der Code in einer Assembly kompiliert, die Assembly bei der Datenbank registriert und die entsprechenden Objekte in der Datenbank erstellt, wobei sie mit den entsprechenden Methoden in der Assembly verknüpft werden. Der genaue Satz von Aufgaben, die von der Option Bereitstellen ausgeführt werden, wird in Schritt 13 genauer beschrieben. Klicken Sie im Projektmappen-Explorer mit der rechten Maustaste auf den ManagedDatabaseConstructs Projektnamen, und wählen Sie die Option Bereitstellen aus. Die Bereitstellung schlägt jedoch mit dem folgenden Fehler fehl: Falsche Syntax in der Nähe von "EXTERNAL". Möglicherweise müssen Sie für den Kompatibilitätsgrad der aktuellen Datenbank einen höheren Wert festlegen, um diese Funktion zu aktivieren. Weitere Informationen finden Sie in der Hilfe zur gespeicherten Prozedur sp_dbcmptlevel.

Diese Fehlermeldung tritt auf, wenn versucht wird, die Assembly bei der Northwind-Datenbank zu registrieren. Um eine Assembly bei einer SQL Server 2005-Datenbank zu registrieren, muss der Kompatibilitätsgrad der Datenbank auf 90 festgelegt werden. Standardmäßig weisen neue SQL Server 2005-Datenbanken den Kompatibilitätsgrad 90 auf. Datenbanken, die mit Microsoft SQL Server 2000 erstellt wurden, weisen jedoch den Standardkompatibilitätsgrad 80 auf. Da die Northwind-Datenbank ursprünglich eine Microsoft SQL Server 2000-Datenbank war, ist ihr Kompatibilitätsgrad derzeit auf 80 festgelegt und muss daher auf 90 erhöht werden, um verwaltete Datenbankobjekte zu registrieren.

Um den Kompatibilitätsgrad der Datenbank zu aktualisieren, öffnen Sie in Management Studio ein Fenster Neue Abfrage, und geben Sie Folgendes ein:

exec sp_dbcmptlevel 'Northwind', 90

Klicken Sie in der Symbolleiste auf das Symbol Ausführen, um die obige Abfrage auszuführen.

Abbildung 9: Aktualisieren des Kompatibilitätsgrads der Northwind-Datenbank (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Stellen Sie nach dem Aktualisieren des Kompatibilitätsgrads das SQL Server Project erneut bereit. Diesmal sollte die Bereitstellung ohne Fehler abgeschlossen werden.

Kehren Sie zu SQL Server Management Studio zurück, klicken Sie mit der rechten Maustaste auf die Northwind-Datenbank im Objekt-Explorer, und wählen Sie Aktualisieren aus. Führen Sie als Nächstes einen Drilldown in den Ordner Programmability aus, und erweitern Sie dann den Ordner Assemblys. Wie Abbildung 10 zeigt, enthält die Northwind-Datenbank jetzt die vom Projekt generierte ManagedDatabaseConstructs Assembly.

Abbildung 10: Die ManagedDatabaseConstructs Assembly ist jetzt bei der Northwind-Datenbank registriert.

Erweitern Sie auch den Ordner Gespeicherte Prozeduren. Dort wird eine gespeicherte Prozedur mit dem Namen angezeigt GetDiscontinuedProducts. Diese gespeicherte Prozedur wurde vom Bereitstellungsprozess erstellt und verweist auf die GetDiscontinuedProducts -Methode in der ManagedDatabaseConstructs Assembly. Wenn die GetDiscontinuedProducts gespeicherte Prozedur ausgeführt wird, führt sie wiederum die GetDiscontinuedProducts -Methode aus. Da es sich um eine verwaltete gespeicherte Prozedur handelt, kann sie nicht über Management Studio bearbeitet werden (daher das Sperrsymbol neben dem Namen der gespeicherten Prozedur).

Abbildung 11: Die GetDiscontinuedProducts gespeicherte Prozedur ist im Ordner Gespeicherte Prozeduren aufgeführt.

Es gibt noch eine weitere Hürde, die wir überwinden müssen, bevor wir die verwaltete gespeicherte Prozedur aufrufen können: Die Datenbank ist so konfiguriert, dass die Ausführung von verwaltetem Code verhindert wird. Überprüfen Sie dies, indem Sie ein neues Abfragefenster öffnen und die GetDiscontinuedProducts gespeicherte Prozedur ausführen. Sie erhalten die folgende Fehlermeldung: Die Ausführung von Benutzercode in der .NET Framework ist deaktiviert. Aktivieren Sie die Konfigurationsoption "clr-aktiviert".

Um die Konfigurationsinformationen der Northwind-Datenbank zu untersuchen, geben Sie den Befehl exec sp_configure im Abfragefenster ein, und führen Sie diesen aus. Dies zeigt, dass die Einstellung clr enabled derzeit auf 0 festgelegt ist.

Abbildung 12: Die Clr-aktivierte Einstellung ist derzeit auf 0 festgelegt (Klicken Sie, um das bild in voller Größe anzuzeigen)

Beachten Sie, dass jede Konfigurationseinstellung in Abbildung 12 vier Werte enthält: die Mindest- und Höchstwerte sowie die Konfigurations- und Ausführungswerte. Um den Konfigurationswert für die Clr-Aktiviert-Einstellung zu aktualisieren, führen Sie den folgenden Befehl aus:

exec sp_configure 'clr enabled', 1

Wenn Sie das exec sp_configure erneut ausführen, sehen Sie, dass die obige Anweisung die clr-aktivierte Einstellung des Konfigurationswerts auf 1 aktualisiert hat, dass der Ausführungswert jedoch weiterhin auf 0 festgelegt ist. Damit diese Konfigurationsänderung auswirkungen kann, müssen wir den RECONFIGURE Befehl ausführen, wodurch der Ausführungswert auf den aktuellen Konfigurationswert festgelegt wird. Geben Sie einfach in das Abfragefenster ein RECONFIGURE , und klicken Sie in der Symbolleiste auf das Symbol Ausführen. Wenn Sie jetzt ausführen exec sp_configure , sollte der Wert 1 für die clr-aktivierte Einstellung der Konfigurations- und Ausführungswerte angezeigt werden.

Nachdem die clr-Konfiguration abgeschlossen ist, können Sie die verwaltete gespeicherte GetDiscontinuedProducts Prozedur ausführen. Geben Sie im Abfragefenster den Befehl ein, und führen Sie diesen aus execGetDiscontinuedProducts. Durch das Aufrufen der gespeicherten Prozedur wird der entsprechende verwaltete Code in der GetDiscontinuedProducts -Methode ausgeführt. Dieser Code stellt eine SELECT Abfrage aus, um alle Produkte zurückzugeben, die nicht mehr ausgeführt werden, und gibt diese Daten an die aufrufende Anwendung zurück, die in diesem instance SQL Server Management Studio wird. Management Studio empfängt diese Ergebnisse und zeigt sie im Fenster Ergebnisse an.

Abbildung 13: Die GetDiscontinuedProducts gespeicherte Prozedur gibt alle nicht eingestellten Produkte zurück (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Schritt 5: Erstellen verwalteter gespeicherter Prozeduren, die Eingabeparameter akzeptieren

Viele der Abfragen und gespeicherten Prozeduren, die wir in diesen Tutorials erstellt haben, haben Parameter verwendet. Beispielsweise haben wir im Tutorial Erstellen neuer gespeicherter Prozeduren für das TableAdapters typed DataSet s eine gespeicherte Prozedur mit dem Namen GetProductsByCategoryID erstellt, die einen Eingabeparameter mit dem Namen @CategoryIDakzeptiert. Die gespeicherte Prozedur hat dann alle Produkte zurückgegeben, deren CategoryID Feld mit dem Wert des angegebenen @CategoryID Parameters übereinstimmte.

Um eine verwaltete gespeicherte Prozedur zu erstellen, die Eingabeparameter akzeptiert, geben Sie einfach diese Parameter in der Definition der Methode an. Um dies zu veranschaulichen, fügen Sie dem Projekt eine weitere verwaltete gespeicherte Prozedur mit dem ManagedDatabaseConstructs Namen GetProductsWithPriceLessThanhinzu. Diese verwaltete gespeicherte Prozedur akzeptiert einen Eingabeparameter, der einen Preis angibt, und gibt alle Produkte zurück, deren UnitPrice Feld kleiner als der Wert des Parameters ist.

Um dem Projekt eine neue gespeicherte Prozedur hinzuzufügen, klicken Sie mit der rechten Maustaste auf den ManagedDatabaseConstructs Projektnamen, und wählen Sie eine neue gespeicherte Prozedur aus. Nennen Sie die Datei GetProductsWithPriceLessThan.cs. Wie wir in Schritt 3 gesehen haben, wird dadurch eine neue C#-Klassendatei mit einer Methode mit dem Namen GetProductsWithPriceLessThan erstellt, die in der partial Klasse StoredProceduresplatziert wird.

Aktualisieren Sie die Definition der GetProductsWithPriceLessThan Methode, sodass sie einen SqlMoney Eingabeparameter namens price akzeptiert, und schreiben Sie den Code, der ausgeführt werden soll, und geben Sie die Abfrageergebnisse zurück:

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

Die GetProductsWithPriceLessThan Definition und der Code der Methode ähneln der Definition und dem Code der GetDiscontinuedProducts in Schritt 3 erstellten Methode. Der einzige Unterschied besteht darin, dass die GetProductsWithPriceLessThan Methode als Eingabeparameter akzeptiert (price), die SqlCommand s-Abfrage einen Parameter (@MaxPrice) enthält und ein Parameter der SqlCommand s-Auflistung Parameters hinzugefügt wird und dem Wert der price Variablen zugewiesen wird.

Nachdem Sie diesen Code hinzugefügt haben, stellen Sie das SQL Server Project erneut bereit. Kehren Sie als Nächstes zu SQL Server Management Studio zurück, und aktualisieren Sie den Ordner Gespeicherte Prozeduren. Der neue Eintrag sollte angezeigt werden GetProductsWithPriceLessThan. Geben Sie in einem Abfragefenster den Befehl ein, und führen Sie den Befehl exec GetProductsWithPriceLessThan 25aus, der alle Produkte unter 25 USD auflistet, wie Abbildung 14 zeigt.

Abbildung 14: Produkte unter 25 USD werden angezeigt (Klicken Sie, um das bild in voller Größe anzuzeigen)

Schritt 6: Aufrufen der verwalteten gespeicherten Prozedur über die Datenzugriffsebene

An diesem Punkt haben wir die gespeicherten Prozeduren und GetProductsWithPriceLessThan verwalteten GetDiscontinuedProducts gespeicherten Prozeduren zum ManagedDatabaseConstructs Projekt hinzugefügt und bei der Northwind SQL Server-Datenbank registriert. Außerdem haben wir diese verwalteten gespeicherten Prozeduren aus SQL Server Management Studio aufgerufen (siehe Abbildungen 13 und 14). Damit unsere ASP.NET-Anwendung diese verwalteten gespeicherten Prozeduren verwenden kann, müssen wir sie jedoch den Datenzugriffs- und Geschäftslogikebenen in der Architektur hinzufügen. In diesem Schritt fügen wir zwei neue Methoden im Typisierten DataSet hinzuProductsTableAdapter, das ursprünglich im Tutorial Erstellen neuer gespeicherter Prozeduren für das TableAdapters-Tutorial typisierter DataSets erstellt wurde.NorthwindWithSprocs In Schritt 7 fügen wir der BLL entsprechende Methoden hinzu.

Öffnen Sie das NorthwindWithSprocs Typisierte DataSet in Visual Studio, und fügen Sie zunächst eine neue Methode mit dem ProductsTableAdapter Namen hinzu GetDiscontinuedProducts. Um einem TableAdapter eine neue Methode hinzuzufügen, klicken Sie mit der rechten Maustaste auf den Namen des TableAdapter im Designer, und wählen Sie im Kontextmenü die Option Abfrage hinzufügen aus.

Hinweis

Da wir die Northwind-Datenbank aus dem Ordner in die App_Data SQL Server 2005 Express Edition Datenbank instance verschoben haben, ist es zwingend erforderlich, dass die entsprechenden Verbindungszeichenfolge in Web.config aktualisiert werden, um diese Änderung widerzuspiegeln. In Schritt 2 wurde das Aktualisieren des Werts NORTHWNDConnectionString in Web.configerläutert. Wenn Sie vergessen haben, dieses Update durchzuführen, wird die Fehlermeldung Fehler beim Hinzufügen der Abfrage angezeigt. Beim Versuch, dem TableAdapter eine neue Methode hinzuzufügen, kann keine Verbindung NORTHWNDConnectionString für ein Objekt Web.config in einem Dialogfeld gefunden werden. Um diesen Fehler zu beheben, klicken Sie auf OK, und aktualisieren Web.config Sie den NORTHWNDConnectionString Wert wie in Schritt 2 beschrieben. Versuchen Sie dann, die Methode erneut zum TableAdapter hinzuzufügen. Dieses Mal sollte es ohne Fehler funktionieren.

Wenn Sie eine neue Methode hinzufügen, wird der TableAdapter-Abfragekonfigurations-Assistent gestartet, den wir in früheren Tutorials mehrfach verwendet haben. Im ersten Schritt müssen wir angeben, wie der TableAdapter auf die Datenbank zugreifen soll: über eine Ad-hoc-SQL-Anweisung oder über eine neue oder vorhandene gespeicherte Prozedur. Da wir die verwaltete gespeicherte Prozedur bereits bei der GetDiscontinuedProducts Datenbank erstellt und registriert haben, wählen Sie die Option Vorhandene gespeicherte Prozedur verwenden aus, und klicken Sie auf Weiter.

Abbildung 15: Wählen Sie die Option Vorhandene gespeicherte Prozedur verwenden aus (Klicken Sie, um das Bild in voller Größe anzuzeigen)

Auf dem nächsten Bildschirm werden wir zur gespeicherten Prozedur aufgefordert, die von der Methode aufgerufen wird. Wählen Sie die GetDiscontinuedProducts verwaltete gespeicherte Prozedur aus der Dropdownliste aus, und klicken Sie auf Weiter.

Abbildung 16: Auswählen der verwalteten GetDiscontinuedProducts gespeicherten Prozedur (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Anschließend werden wir aufgefordert, anzugeben, ob die gespeicherte Prozedur Zeilen, einen einzelnen Wert oder nichts zurückgibt. Da GetDiscontinuedProducts die Gruppe der eingestellten Produktzeilen zurückgegeben wird, wählen Sie die erste Option ( Tabellarische Daten ) aus, und klicken Sie auf Weiter.

Abbildung 17: Auswählen der Option "Tabellarische Daten" (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)

Auf dem letzten Assistentenbildschirm können wir die verwendeten Datenzugriffsmuster und die Namen der resultierenden Methoden angeben. Lassen Sie beide Kontrollkästchen aktiviert, und benennen Sie die Methoden FillByDiscontinued und GetDiscontinuedProducts. Klicken Sie auf Fertig stellen, um den Assistenten abzuschließen.

Abbildung 18: Benennen Sie die Methoden FillByDiscontinued und GetDiscontinuedProducts (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Wiederholen Sie diese Schritte, um Methoden mit dem Namen FillByPriceLessThan und GetProductsWithPriceLessThan in für ProductsTableAdapter die GetProductsWithPriceLessThan verwaltete gespeicherte Prozedur zu erstellen.

Abbildung 19 zeigt einen Screenshot des DataSet-Designer nach dem Hinzufügen der Methoden zu den ProductsTableAdapter und GetProductsWithPriceLessThan den GetDiscontinuedProducts verwalteten gespeicherten Prozeduren.

Abbildung 19: Enthält ProductsTableAdapter die neuen Methoden, die in diesem Schritt hinzugefügt wurden (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Schritt 7: Hinzufügen entsprechender Methoden zur Geschäftslogikebene

Nachdem wir nun die Datenzugriffsebene so aktualisiert haben, dass sie Methoden zum Aufrufen der verwalteten gespeicherten Prozeduren enthält, die in den Schritten 4 und 5 hinzugefügt wurden, müssen wir der Geschäftslogikebene entsprechende Methoden hinzufügen. Fügen Sie der -Klasse die folgenden beiden Methoden hinzu ProductsBLLWithSprocs :

[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetDiscontinuedProducts()
{
    return Adapter.GetDiscontinuedProducts();
}
[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable 
    GetProductsWithPriceLessThan(decimal priceLessThan)
{
    return Adapter.GetProductsWithPriceLessThan(priceLessThan);
}

Beide Methoden rufen einfach die entsprechende DAL-Methode auf und geben die ProductsDataTable instance zurück. Das DataObjectMethodAttribute Markup über den einzelnen Methoden bewirkt, dass diese Methoden in die Dropdownliste auf der Registerkarte SELECT des Assistenten zum Konfigurieren von Datenquellen von ObjectDataSource aufgenommen werden.

Schritt 8: Aufrufen der verwalteten gespeicherten Prozeduren über die Präsentationsebene

Da die Geschäftslogik- und Datenzugriffsebenen um unterstützung für das Aufrufen der und GetProductsWithPriceLessThan verwalteten GetDiscontinuedProducts gespeicherten Prozeduren erweitert wurden, können wir diese gespeicherten Prozeduren jetzt über eine ASP.NET Seite anzeigen.

Öffnen Sie die ManagedFunctionsAndSprocs.aspx Seite im AdvancedDAL Ordner, und ziehen Sie aus der Toolbox eine GridView auf die Designer. Legen Sie die GridView-Eigenschaft ID auf fest DiscontinuedProducts , und binden Sie sie über ihr Smarttag an eine neue ObjectDataSource mit dem Namen DiscontinuedProductsDataSource. Konfigurieren Sie objectDataSource so, dass ihre Daten aus der Methode der ProductsBLLWithSprocs Klasse s GetDiscontinuedProducts abgerufen werden.

Abbildung 20: Konfigurieren der ObjectDataSource für die Verwendung der ProductsBLLWithSprocs -Klasse (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Abbildung 21: Wählen Sie die GetDiscontinuedProducts Methode aus der Drop-Down-Liste auf der Registerkarte SELECT (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Da dieses Raster nur zum Anzeigen von Produktinformationen verwendet wird, legen Sie die Dropdownlisten in den Registerkarten UPDATE, INSERT und DELETE auf (Keine) fest, und klicken Sie dann auf Fertig stellen.

Nach Abschluss des Assistenten fügt Visual Studio automatisch ein BoundField- oder CheckBoxField-Element für jedes Datenfeld in hinzu ProductsDataTable. Nehmen Sie sich einen Moment Zeit, um alle diese Felder mit Ausnahme ProductName von und Discontinuedzu entfernen, wobei Ihr deklaratives GridView- und ObjectDataSource-Markup wie folgt aussehen sollte:

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

Nehmen Sie sich einen Moment Zeit, um diese Seite über einen Browser anzuzeigen. Wenn die Seite besucht wird, ruft ObjectDataSource die Methode s GetDiscontinuedProducts der ProductsBLLWithSprocs Klasse auf. Wie in Schritt 7 gezeigt, ruft diese Methode die Methode s der DAL-Klasse ProductsDataTableGetDiscontinuedProducts auf, die die GetDiscontinuedProducts gespeicherte Prozedur aufruft. Diese gespeicherte Prozedur ist eine verwaltete gespeicherte Prozedur und führt den Code aus, den wir in Schritt 3 erstellt haben, und gibt die eingestellten Produkte zurück.

Die von der verwalteten gespeicherten Prozedur zurückgegebenen Ergebnisse werden von der DAL in ein ProductsDataTable gepackt und dann an die BLL zurückgegeben, die sie dann an die Präsentationsebene zurückgibt, wo sie an die GridView gebunden und angezeigt werden. Wie erwartet listet das Raster die Produkte auf, die eingestellt wurden.

Abbildung 22: Die Nicht mehr verfügbaren Produkte werden aufgelistet (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Fügen Sie zur weiteren Übung der Seite ein TextBox-Element und ein weiteres GridView-Element hinzu. Lassen Sie diese GridView die Produkte anzeigen, die kleiner sind als die Menge, die in das TextBox eingegeben wurde, indem Sie die Methode der ProductsBLLWithSprocs Klasse s GetProductsWithPriceLessThan aufrufen.

Schritt 9: Erstellen und Aufrufen von T-SQL-UDFs

User-Defined Functions oder UDFs sind Datenbankobjekte, die die Semantik von Funktionen in Programmiersprachen genau nachahmen. Wie eine Funktion in C# können UDFs eine variable Anzahl von Eingabeparametern enthalten und einen Wert eines bestimmten Typs zurückgeben. Eine UDF kann entweder skalare Daten (eine Zeichenfolge, eine ganze Zahl usw.) oder tabellarische Daten zurückgeben. Lassen Sie uns einen kurzen Blick auf beide Arten von UDFs werfen, beginnend mit einer UDF, die einen skalaren Datentyp zurückgibt.

Die folgende UDF berechnet den geschätzten Wert des Inventars für ein bestimmtes Produkt. Dazu werden drei Eingabeparameter – die UnitPriceWerte , UnitsInStockund für Discontinued ein bestimmtes Produkt – übernommen und ein Wert vom Typ moneyzurückgegeben. Er berechnet den geschätzten Wert des Inventars, indem mit UnitPrice multipliziert wird UnitsInStock. Bei nicht mehr eingestellten Elementen wird dieser Wert halbiert.

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

Nachdem diese UDF der Datenbank hinzugefügt wurde, kann sie über Management Studio gefunden werden, indem Sie den Ordner Programmability, functions und dann Scalar-Value Functions erweitern. Sie kann in einer SELECT Abfrage wie folgt verwendet werden:

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

Ich habe die udf_ComputeInventoryValue UDF der Northwind-Datenbank hinzugefügt. Abbildung 23 zeigt die Ausgabe der obigen SELECT Abfrage, wenn sie über Management Studio angezeigt wird. Beachten Sie außerdem, dass die UDF unter dem Ordner Scalar-Value Functions im Objekt-Explorer aufgeführt ist.

Abbildung 23: Inventarwerte für jedes Produkt sind aufgelistet (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

UDFs können auch tabellarische Daten zurückgeben. Beispielsweise können wir eine UDF erstellen, die Produkte zurückgibt, die zu einer bestimmten Kategorie gehören:

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
)

Die udf_GetProductsByCategoryID UDF akzeptiert einen @CategoryID Eingabeparameter und gibt die Ergebnisse der angegebenen SELECT Abfrage zurück. Nach der Erstellung kann in der FROM -Klausel (oder JOIN) einer SELECT Abfrage auf diese UDF verwiesen werden. Im folgenden Beispiel werden die ProductIDWerte , ProductNameund CategoryID für die einzelnen Getränke zurückgegeben.

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

Ich habe die udf_GetProductsByCategoryID UDF der Northwind-Datenbank hinzugefügt. Abbildung 24 zeigt die Ausgabe der obigen SELECT Abfrage, wenn sie über Management Studio angezeigt wird. UDFs, die tabellarische Daten zurückgeben, finden Sie im Ordner Tabellenwertfunktionen Objekt-Explorer s.

Abbildung 24: Die ProductID, ProductName, und CategoryID werden für jedes Getränk aufgelistet (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Hinweis

Weitere Informationen zum Erstellen und Verwenden von UDFs finden Sie unter Einführung in User-Defined Functions. Sehen Sie sich auch die Vor- und Nachteile von User-Defined-Funktionen an.

Schritt 10: Erstellen einer verwalteten UDF

Die udf_ComputeInventoryValue in den obigen Beispielen erstellten UDFs und udf_GetProductsByCategoryID sind T-SQL-Datenbankobjekte. SQL Server 2005 unterstützt auch verwaltete UDFs, die dem ManagedDatabaseConstructs Projekt genau wie die verwalteten gespeicherten Prozeduren aus den Schritten 3 und 5 hinzugefügt werden können. Für diesen Schritt implementieren wir die udf_ComputeInventoryValue UDF in verwaltetem Code.

Um dem ManagedDatabaseConstructs Projekt eine verwaltete UDF hinzuzufügen, klicken Sie mit der rechten Maustaste auf den Projektnamen in Projektmappen-Explorer, und wählen Sie Neues Element hinzufügen aus. Wählen Sie im Dialogfeld Neues Element hinzufügen die User-Defined-Vorlage aus, und nennen Sie die neue UDF-Datei udf_ComputeInventoryValue_Managed.cs.

Abbildung 25: Hinzufügen einer neuen verwalteten ManagedDatabaseConstructs UDF zum Projekt (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Die User-Defined-Funktionsvorlage erstellt eine partial Klasse namens UserDefinedFunctions mit einer Methode, deren Name mit dem Namen der Klassendatei (udf_ComputeInventoryValue_Managedin diesem instance) identisch ist. Diese Methode wird mithilfe des SqlFunction -Attributs gestaltet, das die Methode als verwaltete UDF kennzeichnet.

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

Die udf_ComputeInventoryValue -Methode gibt derzeit ein SqlString -Objekt zurück und akzeptiert keine Eingabeparameter. Wir müssen die Methodendefinition so aktualisieren, dass sie drei Eingabeparameter akzeptiert – UnitPrice, UnitsInStockund - und Discontinued ein SqlMoney -Objekt zurückgibt. Die Logik zum Berechnen des Bestandswerts ist identisch mit der logik in der T-SQL-UDF udf_ComputeInventoryValue .

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlMoney udf_ComputeInventoryValue_Managed
    (SqlMoney UnitPrice, SqlInt16 UnitsInStock, SqlBoolean Discontinued)
{
    SqlMoney inventoryValue = 0;
    if (!UnitPrice.IsNull && !UnitsInStock.IsNull)
    {
        inventoryValue = UnitPrice * UnitsInStock;
        if (Discontinued == true)
            inventoryValue = inventoryValue * new SqlMoney(0.5);
    }
    return inventoryValue;
}

Beachten Sie, dass die Eingabeparameter der UDF-Methode die entsprechenden SQL-Typen aufweisen: SqlMoney für das UnitPrice Feld, SqlInt16 für UnitsInStockund SqlBoolean für Discontinued. Diese Datentypen spiegeln die in der Products Tabelle definierten Typen wider: Die UnitPrice Spalte ist vom Typ money, die UnitsInStock Spalte vom Typ smallintund die Discontinued Spalte vom Typ bit.

Der Code beginnt mit dem Erstellen eines SqlMoney instance namensinventoryValue, dem der Wert 0 zugewiesen ist. Die Products Tabelle ermöglicht Datenbankwerte NULL in den UnitsInPrice Spalten und UnitsInStock . Daher müssen wir zuerst überprüfen, ob diese Werte s enthaltenNULL, was wir über die Eigenschaft s IsNulldes SqlMoney Objekts tun. Wenn sowohl als auch UnitPriceUnitsInStock NichtwerteNULL enthalten, berechnen wir die inventoryValue als Produkt der beiden. Discontinued Wenn dann true ist, halbieren wir den Wert.

Hinweis

Das SqlMoney -Objekt erlaubt nur die Multiplikation von zwei SqlMoney Instanzen. Es lässt nicht zu, dass ein SqlMoney instance mit einer literalen Gleitkommazahl multipliziert wird. Um sie zu halbiereninventoryValue, multiplizieren wir sie daher mit einem neuen SqlMoney instance, der den Wert 0,5 aufweist.

Schritt 11: Bereitstellen der verwalteten UDF

Nachdem die verwaltete UDF erstellt wurde, können wir sie in der Northwind-Datenbank bereitstellen. Wie in Schritt 4 gezeigt, werden die verwalteten Objekte in einem SQL Server Project bereitgestellt, indem Sie mit der rechten Maustaste auf den Projektnamen im Projektmappen-Explorer klicken und im Kontextmenü die Option Bereitstellen auswählen.

Nachdem Sie das Projekt bereitgestellt haben, kehren Sie zu SQL Server Management Studio zurück, und aktualisieren Sie den Ordner "Scalar-Valued Functions". Nun sollten zwei Einträge angezeigt werden:

• dbo.udf_ComputeInventoryValue – die in Schritt 9 erstellte T-SQL-UDF und
• dbo.udf ComputeInventoryValue_Managed – die in Schritt 10 erstellte verwaltete UDF, die soeben bereitgestellt wurde.

Führen Sie die folgende Abfrage in Management Studio aus, um diese verwaltete UDF zu testen:

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

Dieser Befehl verwendet die verwaltete udf ComputeInventoryValue_Managed UDF anstelle der T-SQL-UDF udf_ComputeInventoryValue , aber die Ausgabe ist identisch. In Abbildung 23 finden Sie einen Screenshot der UDF-Ausgabe.

Schritt 12: Debuggen der verwalteten Datenbankobjekte

Im Tutorial Zum Debuggen gespeicherter Prozeduren wurden die drei Optionen zum Debuggen von SQL Server über Visual Studio erläutert: Direktes Datenbankdebuggen, Anwendungsdebuggen und Debuggen aus einem SQL Server-Projekt. Verwaltete Datenbankobjekte können nicht über das direkte Datenbankdebuggen debuggen, sondern über eine Clientanwendung und direkt aus dem SQL Server Project debuggen. Damit das Debuggen funktioniert, muss die SQL Server 2005-Datenbank das SQL/CLR-Debuggen zulassen. Denken Sie daran, dass Visual Studio uns beim Erstellen des ManagedDatabaseConstructs Projekts gefragt hat, ob wir das SQL/CLR-Debuggen aktivieren möchten (siehe Abbildung 6 in Schritt 2). Diese Einstellung kann geändert werden, indem Sie im Fenster Server Explorer mit der rechten Maustaste auf die Datenbank klicken.

Abbildung 26: Sicherstellen, dass die Datenbank SQL/CLR-Debuggen zulässt

Stellen Sie sich vor, wir wollten die GetProductsWithPriceLessThan verwaltete gespeicherte Prozedur debuggen. Wir würden zunächst einen Haltepunkt im Code der GetProductsWithPriceLessThan -Methode festlegen.

Abbildung 27: Festlegen eines Haltepunkts in der GetProductsWithPriceLessThan Methode (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)

Betrachten wir zunächst das Debuggen der verwalteten Datenbankobjekte aus dem SQL Server Project. Da unsere Projektmappe zwei Projekte enthält - das ManagedDatabaseConstructs SQL Server Project zusammen mit unserer Website -, um über das SQL Server Project zu debuggen, müssen wir Visual Studio anweisen, das SQL Server Project zu starten, wenn wir mit dem ManagedDatabaseConstructs Debuggen beginnen. Klicken Sie mit der rechten Maustaste auf das ManagedDatabaseConstructs Projekt in Projektmappen-Explorer, und wählen Sie im Kontextmenü die Option Als Startprojekt festlegen aus.

Wenn das ManagedDatabaseConstructs Projekt über den Debugger gestartet wird, werden die SQL-Anweisungen in der Test.sql Datei ausgeführt, die Test Scripts sich im Ordner befindet. Um beispielsweise die GetProductsWithPriceLessThan verwaltete gespeicherte Prozedur zu testen, ersetzen Sie den vorhandenen Test.sql Dateiinhalt durch die folgende Anweisung, die die GetProductsWithPriceLessThan verwaltete gespeicherte Prozedur aufruft, die den @CategoryID Wert 14.95 übergibt:

exec GetProductsWithPriceLessThan 14.95

Nachdem Sie das obige Skript in Test.sqleingegeben haben, starten Sie das Debuggen, indem Sie zum Menü Debuggen wechseln und Debuggen starten auswählen oder auf F5 oder das grüne Wiedergabesymbol in der Symbolleiste klicken. Dadurch werden die Projekte innerhalb der Projektmappe erstellt, die verwalteten Datenbankobjekte in der Northwind-Datenbank bereitgestellt und anschließend das Test.sql Skript ausgeführt. An diesem Punkt wird der Haltepunkt erreicht, und wir können die GetProductsWithPriceLessThan Methode schrittweise durchlaufen, die Werte der Eingabeparameter untersuchen usw.

Abbildung 28: Der Haltepunkt in der GetProductsWithPriceLessThan Methode wurde erreicht (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Damit ein SQL-Datenbankobjekt über eine Clientanwendung debuggen kann, muss die Datenbank unbedingt für die Unterstützung des Anwendungsdebuggings konfiguriert werden. Klicken Sie mit der rechten Maustaste auf die Datenbank in Server Explorer, und stellen Sie sicher, dass die Option Anwendungsdebuggen aktiviert ist. Darüber hinaus müssen wir die ASP.NET Anwendung für die Integration in den SQL-Debugger konfigurieren und das Verbindungspooling deaktivieren. Diese Schritte wurden in Schritt 2 des Tutorials zum Debuggen gespeicherter Prozeduren ausführlich erläutert.

Nachdem Sie die ASP.NET Anwendung und Datenbank konfiguriert haben, legen Sie die ASP.NET Website als Startprojekt fest, und starten Sie das Debuggen. Wenn Sie eine Seite aufrufen, die eines der verwalteten Objekte mit einem Haltepunkt aufruft, wird die Anwendung angehalten, und die Steuerung wird an den Debugger übergeben, wo Sie den Code schrittweise durchlaufen können, wie in Abbildung 28 dargestellt.

Schritt 13: Manuelles Kompilieren und Bereitstellen von verwalteten Datenbankobjekten

SQL Server Projekte vereinfachen das Erstellen, Kompilieren und Bereitstellen verwalteter Datenbankobjekte. Leider sind SQL Server Projekte nur in den Editionen Professional und Team Systems von Visual Studio verfügbar. Wenn Sie Visual Web Developer oder die Standard Edition von Visual Studio verwenden und verwaltete Datenbankobjekte verwenden möchten, müssen Sie diese manuell erstellen und bereitstellen. Dies umfasst vier Schritte:

1. Erstellen Sie eine Datei, die den Quellcode für das objekt der verwalteten Datenbank enthält.
2. Kompilieren Sie das Objekt in eine Assembly.
3. Registrieren Sie die Assembly bei der SQL Server 2005-Datenbank, und
4. Erstellen Sie ein Datenbankobjekt in SQL Server, das auf die entsprechende Methode in der Assembly verweist.

Um diese Aufgaben zu veranschaulichen, erstellen Sie eine neue verwaltete gespeicherte Prozedur, die die Produkte zurückgibt, deren UnitPrice Wert größer als ein angegebener Wert ist. Erstellen Sie auf Ihrem Computer eine neue Datei namens, GetProductsWithPriceGreaterThan.cs und geben Sie den folgenden Code in die Datei ein (Sie können dazu Visual Studio, Editor oder einen beliebigen Text-Editor verwenden):

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

Dieser Code ist nahezu identisch mit dem GetProductsWithPriceLessThan der in Schritt 5 erstellten Methode. Die einzigen Unterschiede sind die Methodennamen, die WHERE -Klausel und der Parametername, die in der Abfrage verwendet werden. Zurück in der GetProductsWithPriceLessThan -Methode lautet die WHERE -Klausel: WHERE UnitPrice < @MaxPrice. Hier wird in GetProductsWithPriceGreaterThanverwendet: WHERE UnitPrice > @MinPrice .

Diese Klasse muss nun in eine Assembly kompiliert werden. Navigieren Sie in der Befehlszeile zu dem Verzeichnis, in dem Sie die GetProductsWithPriceGreaterThan.cs Datei gespeichert haben, und verwenden Sie den C#-Compiler (csc.exe), um die Klassendatei in eine Assembly zu kompilieren:

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

Wenn der Ordner csc.exe , der in enthält, nicht im System s PATHenthält, müssen Sie vollständig auf seinen Pfad verweisen, %WINDOWS%\Microsoft.NET\Framework\version\wie so:

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

Abbildung 29: Kompilieren GetProductsWithPriceGreaterThan.cs in eine Assembly (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Das /t Flag gibt an, dass die C#-Klassendatei in eine DLL (und nicht in eine ausführbare Datei) kompiliert werden soll. Das /out Flag gibt den Namen der resultierenden Assembly an.

Hinweis

Anstatt die GetProductsWithPriceGreaterThan.cs Klassendatei über die Befehlszeile zu kompilieren, können Sie alternativ Visual C# Express Edition verwenden oder ein separates Klassenbibliotheksprojekt in Visual Studio Standard Edition erstellen. S ren Jacob Lauritsen hat freundlicherweise ein solches Visual C# Express Edition-Projekt mit Code für die GetProductsWithPriceGreaterThan gespeicherte Prozedur und die beiden verwalteten gespeicherten Prozeduren und UDF bereitgestellt, die in den Schritten 3, 5 und 10 erstellt wurden. S ren s project enthält auch die T-SQL-Befehle, die zum Hinzufügen der entsprechenden Datenbankobjekte erforderlich sind.

Nachdem der Code in eine Assembly kompiliert wurde, können Wir die Assembly in der SQL Server 2005-Datenbank registrieren. Dies kann über T-SQL, mit dem Befehl CREATE ASSEMBLYoder über SQL Server Management Studio erfolgen. Konzentrieren Wir uns auf die Verwendung von Management Studio.

Erweitern Sie in Management Studio den Ordner Programmability in der Northwind-Datenbank. Einer der Unterordner ist Assemblys. Um der Datenbank manuell eine neue Assembly hinzuzufügen, klicken Sie mit der rechten Maustaste auf den Ordner Assemblys, und wählen Sie im Kontextmenü Neue Assembly aus. Dadurch wird das Dialogfeld Neue Assembly angezeigt (siehe Abbildung 30). Klicken Sie auf die Schaltfläche Durchsuchen, wählen Sie die ManuallyCreatedDBObjects.dll gerade kompilierte Assembly aus, und klicken Sie dann auf OK, um die Assembly der Datenbank hinzuzufügen. Die Assembly sollte nicht im Objekt-Explorer angezeigt werdenManuallyCreatedDBObjects.dll.

Abbildung 30: Hinzufügen der ManuallyCreatedDBObjects.dll Assembly zur Datenbank (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Abbildung 31: Ist ManuallyCreatedDBObjects.dll im Objekt-Explorer aufgeführt

Obwohl wir die Assembly der Northwind-Datenbank hinzugefügt haben, müssen wir der -Methode in der GetProductsWithPriceGreaterThan Assembly noch eine gespeicherte Prozedur zuordnen. Öffnen Sie dazu ein neues Abfragefenster, und führen Sie das folgende Skript aus:

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

Dadurch wird eine neue gespeicherte Prozedur in der Northwind-Datenbank namens GetProductsWithPriceGreaterThan erstellt und der verwalteten Methode GetProductsWithPriceGreaterThan zugeordnet (die sich in der -Klasse StoredProceduresbefindet, die sich in der Assembly ManuallyCreatedDBObjectsbefindet).

Nachdem Sie das obige Skript ausgeführt haben, aktualisieren Sie den Ordner Gespeicherte Prozeduren im Objekt-Explorer. Es sollte ein neuer Eintrag GetProductsWithPriceGreaterThan für eine gespeicherte Prozedur angezeigt werden, neben dem ein Sperrsymbol angezeigt wird. Um diese gespeicherte Prozedur zu testen, geben Sie das folgende Skript im Abfragefenster ein, und führen Sie es aus:

exec GetProductsWithPriceGreaterThan 24.95

Wie abbildung 32 zeigt, zeigt der obige Befehl Informationen für die Produkte mit einem UnitPrice Wert von mehr als 24,95 USD an.

Abbildung 32: In ManuallyCreatedDBObjects.dll der Objekt-Explorer aufgeführt (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)

Zusammenfassung

Microsoft SQL Server 2005 bietet die Integration in die Common Language Runtime (CLR), die das Erstellen von Datenbankobjekten mit verwaltetem Code ermöglicht. Bisher konnten diese Datenbankobjekte nur mit T-SQL erstellt werden, aber jetzt können wir diese Objekte mithilfe von .NET-Programmiersprachen wie C# erstellen. In diesem Tutorial haben wir zwei verwaltete gespeicherte Prozeduren und eine verwaltete User-Defined-Funktion erstellt.

Visual Studio SQL Server Project-Typ erleichtert das Erstellen, Kompilieren und Bereitstellen von verwalteten Datenbankobjekten. Darüber hinaus bietet es umfassende Debugunterstützung. SQL Server Project-Typen sind jedoch nur in den Professional- und Team Systems-Editionen von Visual Studio verfügbar. Für Benutzer, die Visual Web Developer oder die Standard Edition von Visual Studio verwenden, müssen die Schritte zum Erstellen, Kompilieren und Bereitstellen manuell ausgeführt werden, wie in Schritt 13 gezeigt.

Viel Spaß beim Programmieren!

Weitere Informationen

Weitere Informationen zu den in diesem Tutorial behandelten Themen finden Sie in den folgenden Ressourcen:

• Vor- und Nachteile von User-Defined-Funktionen
• Erstellen SQL Server 2005-Objekte in verwaltetem Code
• Vorgehensweise: Erstellen und Ausführen einer gespeicherten CLR-SQL Server-Prozedur
• Vorgehensweise: Erstellen und Ausführen einer CLR-SQL Server User-Defined-Funktion
• Vorgehensweise: Bearbeiten des Test.sql Skripts zum Ausführen von SQL-Objekten
• Einführung in benutzerdefinierte Funktionen
• Verwalteter Code und SQL Server 2005 (Video)
• Transact-SQL-Referenz
• Exemplarische Vorgehensweise: Erstellen einer gespeicherten Prozedur in verwaltetem Code

Zum Autor

Scott Mitchell, Autor von sieben ASP/ASP.NET-Büchern und Gründer von 4GuysFromRolla.com, arbeitet seit 1998 mit Microsoft-Webtechnologien. Scott arbeitet als unabhängiger Berater, Trainer und Autor. Sein neuestes Buch ist Sams Teach Yourself ASP.NET 2.0 in 24 Hours. Er kann unter mitchell@4GuysFromRolla.comoder über seinen Blog erreicht werden, der unter http://ScottOnWriting.NETzu finden ist.

Besonderer Dank an

Diese Tutorialreihe wurde von vielen hilfreichen Prüfern überprüft. Leitender Bearbeiter für dieses Tutorial war S ren Jacob Lauritsen. Zusätzlich zur Überprüfung dieses Artikels hat S ren auch das Visual C#-Express Edition-Projekt erstellt, das im Download dieses Artikels zum manuellen Kompilieren der verwalteten Datenbankobjekte enthalten ist. Möchten Sie meine bevorstehenden MSDN-Artikel lesen? Wenn dies der Fall ist, legen Sie eine Zeile unter abmitchell@4GuysFromRolla.com.

ZurückWeiter