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

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 Lernprogramm wird gezeigt, wie Sie verwaltete gespeicherte Prozeduren und verwaltete benutzerdefinierte Funktionen mit Ihrem Visual Basic- oder C#-Code erstellen. Außerdem erfahren Sie, wie Sie mit diesen Editionen von Visual Studio solche verwalteten Datenbankobjekte debuggen können.

Einführung

Datenbanken wie Microsoft SQL Server 2005 verwenden die 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. Ein weiteres ist user-Defined Functions(UDFs), ein Konstrukt, das wir in Schritt 9 genauer untersuchen.

Im Kern ist SQL für die Arbeit mit Datengruppen konzipiert. Die SELECT, UPDATEund DELETE Die Anweisungen gelten inhärent für alle Datensätze in der entsprechenden Tabelle und sind nur durch ihre WHERE Klauseln begrenzt. Es gibt jedoch viele Sprachfeatures, die für die Gleichzeitige Arbeit mit einem Datensatz und zum Bearbeiten von Skalardaten entwickelt wurden. CURSOR s ermöglicht, dass jeweils eine Reihe von Datensätzen durchlaufen werden kann. Zeichenfolgenmanipulationsfunktionen wie LEFT, CHARINDEXund PATINDEX arbeiten mit skalaren Daten. SQL enthält auch Steuerungsflussanweisungen wie IF und WHILE.

Vor Microsoft SQL Server 2005 konnten gespeicherte Prozeduren und UDFs nur als Eine Sammlung von T-SQL-Anweisungen definiert werden. SQL Server 2005 wurde jedoch für die Integration mit der Common Language Runtime (CLR) entwickelt, die von allen .NET-Assemblys verwendet wird. Folglich 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 Visual Basic-Klasse erstellen. Dadurch können diese gespeicherten Prozeduren und UDFs Funktionen in .NET Framework und aus Ihren eigenen benutzerdefinierten Klassen nutzen.

In diesem Lernprogramm untersuchen wir, wie verwaltete gespeicherte Prozeduren und benutzerdefinierte Funktionen erstellt und wie sie in unsere Northwind-Datenbank integriert werden. Los geht's!

Hinweis

Verwaltete Datenbankobjekte bieten gegenüber ihren SQL-Gegenstücken einige Vorteile. Sprachreiche und Vertrautheit sowie die Möglichkeit, vorhandenen Code und Logik wiederzuverwenden, sind die hauptvorteile. Verwaltete Datenbankobjekte sind jedoch wahrscheinlich weniger effizient, wenn Sie mit Datengruppen arbeiten, die keine große prozedurale Logik erfordern. Eine ausführlichere Erläuterung zu den Vorteilen der Verwendung von verwaltetem Code im Vergleich zu T-SQL finden Sie unter den Vorteilen der Verwendung von verwaltetem Code zum Erstellen von Datenbankobjekten.

Schritt 1: Verschieben der Northwind-Datenbank aus App_Data

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

In diesem Lernprogramm wird die Northwind-Datenbank jedoch aus App_Data der Datenbank heraus verschoben und explizit mit der SQL Server 2005 Express Edition-Datenbankinstanz registriert. Während wir die Schritte für dieses Lernprogramm mit der Datenbank im App_Data Ordner ausführen können, wird eine Reihe der Schritte erheblich vereinfacht, indem die Datenbank explizit bei der SQL Server 2005 Express Edition-Datenbankinstanz registriert wird.

Der Download für dieses Lernprogramm enthält die beiden Datenbankdateien - NORTHWND.MDF und NORTHWND_log.LDF - in einem Ordner mit dem Namen DataFiles. Wenn Sie zusammen mit Ihrer eigenen Implementierung der Lernprogramme folgen, schließen Sie Visual Studio, und verschieben Sie die NORTHWND.MDF Dateien 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, müssen wir die Northwind-Datenbank bei der SQL Server 2005 Express Edition-Datenbankinstanz registrieren. Dies kann aus SQL Server Management Studio erfolgen. Wenn auf Ihrem Computer eine Nicht-Express Edition von SQL Server 2005 installiert ist, 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 in Abbildung 1 dargestellt, fragt Management Studio, mit welchem Server eine Verbindung hergestellt werden soll. Geben Sie "localhost\SQLExpress" für den Servernamen ein, wählen Sie in der Dropdownliste "Authentifizierung" die Option "Windows-Authentifizierung" aus, und klicken Sie auf "Verbinden".

Abbildung 1: Herstellen einer Verbindung mit der entsprechenden Datenbankinstanz

Nachdem Sie eine Verbindung hergestellt haben, listet das fenster Objekt-Explorer Informationen zur SQL Server 2005 Express Edition-Datenbankinstanz auf, einschließlich seiner Datenbanken, Sicherheitsinformationen, Verwaltungsoptionen usw.

Die Northwind-Datenbank muss an die DataFiles SQL Server 2005 Express Edition-Datenbankinstanz angefügt werden (oder wo auch immer Sie sie verschoben haben). 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" angezeigt. Klicken Sie auf die Schaltfläche "Hinzufügen", führen Sie einen Drilldown zur entsprechenden NORTHWND.MDF Datei aus, und klicken Sie auf "OK". An diesem Punkt sollte ihr Bildschirm ähnlich aussehen wie in Abbildung 2.

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

Hinweis

Wenn Sie eine Verbindung mit der SQL Server 2005 Express Edition-Instanz über Management Studio herstellen, können Sie keinen Drilldown in Benutzerprofilverzeichnisse wie "Eigene Dokumente" ausführen. Stellen Sie daher sicher, dass die Dateien und NORTHWND_log.LDF Die NORTHWND.MDF Dateien in einem Nicht-Benutzerprofilverzeichnis gespeichert werden.

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 soeben 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 Lösung und eines SQL Server-Projekts in Visual Studio

Zum Erstellen verwalteter gespeicherter Prozeduren oder UDFs in SQL Server 2005 schreiben wir die gespeicherte Prozedur und UDF-Logik als Visual Basic-Code in einer Klasse. Nachdem der Code geschrieben wurde, müssen wir diese Klasse in eine Assembly (eine .dll Datei) kompilieren, die Assembly mit der SQL Server-Datenbank registrieren und dann eine gespeicherte Prozedur oder ein UDF-Objekt in der Datenbank erstellen, die 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, über die Befehlszeile mithilfe des Visual Basic-Compilers kompilieren (vbc.exe), ihn mit dem CREATE ASSEMBLY Befehl oder aus Management Studio mit der Datenbank registrieren und die gespeicherte Prozedur oder das UDF-Objekt auf ähnliche Weise hinzufügen. Glücklicherweise enthalten die Versionen Professional und Team Systems von Visual Studio einen SQL Server-Projekttyp, der diese Aufgaben automatisiert. In diesem Lernprogramm werden wir schrittweise durch die Verwendung des SQL Server-Projekttyps zum Erstellen einer verwalteten gespeicherten Prozedur und UDF geführt.

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 detaillierte Anweisungen zum manuellen Ausführen dieser Schritte. Ich ermutige Sie, die Schritte 2 bis 12 vor dem Lesen von Schritt 13 zu lesen, da diese Schritte wichtige SQL Server-Konfigurationsanweisungen enthalten, die unabhängig von der verwendeten Version von Visual Studio angewendet werden müssen.

Ö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 Datenbankprojekttyp durch, und wählen Sie dann in den auf der rechten Seite aufgeführten Vorlagen ein neues SQL Server-Projekt aus. Ich habe mich entschieden, dieses Projekt ManagedDatabaseConstructs zu benennen und in eine Projektmappe mit dem Namen Tutorial75zu setzen.

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 das SQL Server-Projekt zu erstellen.

Ein SQL Server-Projekt ist an eine bestimmte Datenbank gebunden. Folglich werden wir nach dem Erstellen des neuen SQL Server-Projekts sofort aufgefordert, diese Informationen anzugeben. Abbildung 5 zeigt das Dialogfeld "Neue Datenbankreferenz", das ausgefüllt wurde, um auf die Northwind-Datenbank zu verweisen, die wir in der SQL Server 2005 Express Edition-Datenbankinstanz wieder in Schritt 1 registriert haben.

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 die SQL/CLR-Debuggingunterstützung für die Verbindung aktivieren. Wann immer ein SQL Server-Projekt mit einer neuen Datenbank verknüpft wird (wie in Abbildung 5 dargestellt), fragt Uns Visual Studio, ob das SQL/CLR-Debugging für die Verbindung aktiviert werden soll (siehe Abbildung 6). Klicken Sie auf Ja.

Abbildung 6: Aktivieren des SQL/CLR-Debuggings

An diesem Punkt wurde das neue SQL Server-Projekt der Lösung hinzugefügt. Sie enthält einen Ordner mit dem Namen Test Scripts "Datei" Test.sql, der zum Debuggen der im Projekt erstellten verwalteten Datenbankobjekte verwendet wird. Das Debuggen wird in Schritt 12 erläutert.

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

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

Der NORTHWNDConnectionString Wert in Web.config der aktuellen Datei verweist auf die NORTHWND.MDF Datei im App_Data Ordner. Da wir diese Datenbank aus App_Data der SQL Server 2005 Express Edition-Datenbankinstanz entfernt und explizit registriert haben, müssen wir den NORTHWNDConnectionString Wert entsprechend aktualisieren. Öffnen Sie die Datei auf der Web.config Website, und ändern Sie den NORTHWNDConnectionString Wert so, dass die Verbindungszeichenfolge liest: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. Nach dieser Änderung sollte Ihr <connectionStrings> Abschnitt 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 Lernprogramm erläutert, müssen wir beim Debuggen eines SQL Server-Objekts aus einer Clientanwendung, z. B. einer ASP.NET Website, verbindungspooling deaktivieren. Die oben gezeigte Verbindungszeichenfolge deaktiviert verbindungspooling ( Pooling=false ). Wenn Sie das Debuggen der verwalteten gespeicherten Prozeduren und UDFs von der ASP.NET-Website nicht planen, aktivieren Sie das Verbindungspooling.

Schritt 3: Erstellen einer verwalteten gespeicherten Prozedur

Um der Northwind-Datenbank eine verwaltete gespeicherte Prozedur hinzuzufügen, müssen wir zuerst die gespeicherte Prozedur als Methode im SQL Server-Projekt 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 von verwalteten Datenbankobjekten aufgelistet werden, die dem Projekt hinzugefügt werden können. Wie In Abbildung 8 dargestellt, umfasst dies unter anderem gespeicherte Prozeduren und benutzerdefinierte Funktionen.

Beginnen wir mit dem Hinzufügen einer gespeicherten Prozedur, die einfach alle nicht mehr verfügbaren Produkte zurückgibt. Benennen Sie die neue gespeicherte Prozedurdatei GetDiscontinuedProducts.vb.

Abbildung 8: Hinzufügen einer neuen gespeicherten Prozedur mit dem Namen GetDiscontinuedProducts.vb (Klicken, um das Bild in voller Größe anzuzeigen)

Dadurch wird eine neue Visual Basic-Klassendatei mit dem folgenden Inhalt erstellt:

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

Beachten Sie, dass die gespeicherte Prozedur als Shared Methode in einer Partial Klassendatei namens StoredProceduresimplementiert wird. Darüber hinaus wird die GetDiscontinuedProducts Methode mit dem SqlProcedure Attribut versehen, das die Methode als gespeicherte Prozedur kennzeichnet.

Mit dem folgenden Code wird ein SqlCommand Objekt erstellt und auf eine SELECT Abfrage festgelegtCommandText, die alle Spalten aus der Products Tabelle für Produkte zurückgibt, deren Discontinued Feld 1 entspricht. 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
Dim myCommand As New SqlCommand()
myCommand.CommandText = _
    "SELECT ProductID, ProductName, SupplierID, CategoryID, " & _
    "       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, " & _
    "       ReorderLevel, Discontinued " & _
    "FROM Products " & _
    "WHERE Discontinued = 1"
' Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand)

Alle verwalteten Datenbankobjekte haben Zugriff auf ein SqlContext Objekt , das den Kontext des Aufrufers darstellt. Das 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 übertragen. Wie der Name schon sagt, führt die ExecuteAndSend Methode ein übergebenes SqlCommand Objekt aus und sendet die Ergebnisse an die Clientanwendung zurück.

Hinweis

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

Schritt 4: Bereitstellen der verwalteten gespeicherten Prozedur

Mit diesem Code sind wir bereit, ihn in der Northwind-Datenbank bereitzustellen. Durch die Bereitstellung eines SQL Server-Projekts wird der Code in einer Assembly kompiliert, die Assembly mit der Datenbank registriert und die entsprechenden Objekte in der Datenbank erstellt und mit den entsprechenden Methoden in der Assembly verknüpft. Die genaue Gruppe von Aufgaben, die von der Option "Bereitstellen" ausgeführt werden, wird in Schritt 13 genauer beschrieben. Klicken Sie mit der rechten Maustaste auf den ManagedDatabaseConstructs Projektnamen in der Projektmappen-Explorer, 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 Sie versuchen, die Assembly bei der Northwind-Datenbank zu registrieren. Um eine Assembly mit einer SQL Server 2005-Datenbank zu registrieren, muss die Kompatibilitätsstufe der Datenbank auf 90 festgelegt werden. Standardmäßig verfügen neue SQL Server 2005-Datenbanken über eine Kompatibilitätsstufe von 90. Datenbanken, die mit Microsoft SQL Server 2000 erstellt wurden, weisen jedoch eine Standardkompatibilitätsstufe von 80 auf. Da die Northwind-Datenbank anfänglich eine Microsoft SQL Server 2000-Datenbank war, ist die Kompatibilitätsstufe derzeit auf 80 festgelegt und muss daher auf 90 erhöht werden, um verwaltete Datenbankobjekte zu registrieren.

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

exec sp_dbcmptlevel 'Northwind', 90

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

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

Nach dem Aktualisieren der Kompatibilitätsstufe müssen Sie das SQL Server-Projekt erneut bereitstellen. Dieses Mal 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 in der Objekt-Explorer, und wählen Sie "Aktualisieren" aus. Führen Sie als Nächstes einen Drilldown in den Ordner "Programmierbarkeit" aus, und erweitern Sie dann den Ordner "Assemblys". Wie in Abbildung 10 dargestellt, enthält die Northwind-Datenbank jetzt die vom ManagedDatabaseConstructs Projekt generierte Assembly.

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

Erweitern Sie auch den Ordner "Gespeicherte Prozeduren". Dort sehen Sie eine gespeicherte Prozedur mit dem Namen 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 wird 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 .NET Framework ist deaktiviert. Aktivieren Sie die Option "clr-aktivierte Konfiguration".

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

Abbildung 12: Die clr-aktivierte Einstellung ist zurzeit auf 0 festgelegt (Klicken, 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. Führen Sie den folgenden Befehl aus, um den Konfigurationswert für die aktivierte Clr-Einstellung zu aktualisieren:

exec sp_configure 'clr enabled', 1

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

Nachdem die clr-Konfiguration abgeschlossen ist, können wir die verwaltete GetDiscontinuedProducts gespeicherte Prozedur ausführen. Geben Sie im Abfragefenster den Befehl exec GetDiscontinuedProductsein, und führen Sie ihn aus. Durch Aufrufen der gespeicherten Prozedur wird der entsprechende verwaltete Code in der GetDiscontinuedProducts Methode ausgeführt. Dieser Code gibt eine SELECT Abfrage aus, um alle Produkte zurückzugeben, die nicht mehr unterstützt werden, und gibt diese Daten an die aufrufende Anwendung zurück, die sql Server Management Studio in dieser Instanz ist. Management Studio empfängt diese Ergebnisse und zeigt sie im Fenster "Ergebnisse" an.

Abbildung 13: Die GetDiscontinuedProducts gespeicherte Prozedur gibt alle nicht mehr verfügbaren 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 Lernprogrammen erstellt haben, haben Parameter verwendet. Im Lernprogramm "Typed DataSet s TableAdapters" haben wir beispielsweise in der Erstellung neuer gespeicherter Prozeduren eine gespeicherte Prozedur erstellt, die einen Eingabeparameter mit dem Namen GetProductsByCategoryID @CategoryIDakzeptiert hat. Die gespeicherte Prozedur hat dann alle Produkte zurückgegeben, deren CategoryID Feld mit dem Wert des angegebenen @CategoryID Parameters übereinstimmte.

Wenn Sie eine verwaltete gespeicherte Prozedur erstellen möchten, die Eingabeparameter akzeptiert, geben Sie einfach diese Parameter in der Definition der Methode an. Um dies zu veranschaulichen, fügen wir dem Projekt eine weitere verwaltete gespeicherte Prozedur mit dem ManagedDatabaseConstructs Namen hinzu GetProductsWithPriceLessThan. 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.

Wenn Sie dem Projekt eine neue gespeicherte Prozedur hinzufügen möchten, klicken Sie mit der rechten Maustaste auf den ManagedDatabaseConstructs Projektnamen, und wählen Sie aus, eine neue gespeicherte Prozedur hinzuzufügen. Nennen Sie die Datei GetProductsWithPriceLessThan.vb. Wie wir in Schritt 3 gesehen haben, erstellt dies eine neue Visual Basic-Klassendatei mit einer Methode, die in der Partial Klasse StoredProceduresplatziert istGetProductsWithPriceLessThan.

Aktualisieren Sie die Definition der GetProductsWithPriceLessThan Methode so, dass sie einen SqlMoney Eingabeparameter namens akzeptiert price , und schreiben Sie den Code, um die Abfrageergebnisse auszuführen und zurückzugeben:

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

Die GetProductsWithPriceLessThan Definition und der Code der Methode ähneln der Definition und dem Code der methode, die GetDiscontinuedProducts in Schritt 3 erstellt wurde. Die einzigen Unterschiede sind, dass die GetProductsWithPriceLessThan Methode als Eingabeparameterprice (), die SqlCommand Abfrage einen Parameter (@MaxPrice) akzeptiert, und ein Parameter wird der SqlCommand Auflistung Parameters hinzugefügt und dem Wert der price Variablen zugewiesen.

Nachdem Sie diesen Code hinzugefügt haben, müssen Sie das SQL Server-Projekt erneut bereitstellen. Kehren Sie als Nächstes zu SQL Server Management Studio zurück, und aktualisieren Sie den Ordner "Gespeicherte Prozeduren". Ein neuer Eintrag sollte angezeigt werden. GetProductsWithPriceLessThan Geben Sie in einem Abfragefenster den Befehl exec GetProductsWithPriceLessThan 25ein, und führen Sie ihn aus, der alle Produkte unter $25 auflistet, wie in Abbildung 14 dargestellt.

Abbildung 14: Produkte unter $25 werden angezeigt (Zum Anzeigen des Bilds mit voller Größe klicken)

Schritt 6: Aufrufen der verwalteten gespeicherten Prozedur aus der Datenzugriffsebene

An diesem Punkt haben wir die GetDiscontinuedProducts gespeicherten Prozeduren dem ManagedDatabaseConstructs Projekt hinzugefügt und GetProductsWithPriceLessThan verwaltet und mit 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 dem typierten NorthwindWithSprocs DataSet zwei neue Methoden hinzuProductsTableAdapter, die ursprünglich im Lernprogramm zum Erstellen neuer gespeicherter Prozeduren für das TableAdapters-Lernprogramm "Typed DataSets" erstellt wurde. In Schritt 7 fügen wir der BLL entsprechende Methoden hinzu.

Öffnen Sie das NorthwindWithSprocs Typed DataSet in Visual Studio, und fügen Sie zunächst eine neue Methode zum benannten Hinzufüger GetDiscontinuedProductshinzuProductsTableAdapter. Wenn Sie einem TableAdapter eine neue Methode hinzufügen möchten, klicken Sie im Designer mit der rechten Maustaste auf den Namen des TableAdapters, und wählen Sie im Kontextmenü die Option "Abfrage hinzufügen" aus.

Hinweis

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

Das Hinzufügen einer neuen Methode startet den TableAdapter-Abfragekonfigurations-Assistenten, den wir in früheren Lernprogrammen oft verwendet haben. Der erste Schritt fordert uns auf, anzugeben, wie das 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 mit der Datenbank erstellt und registriert GetDiscontinuedProducts haben, wählen Sie die Option "Vorhandene gespeicherte Prozedur verwenden" aus, und drücken Sie "Weiter".

Abbildung 15: Auswählen der Option "Vorhandene gespeicherte Prozedur verwenden" (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)

Der nächste Bildschirm fordert uns zur gespeicherten Prozedur auf, die die Methode aufruft. Wählen Sie die GetDiscontinuedProducts verwaltete gespeicherte Prozedur aus der Dropdownliste aus, und drücken Sie "Weiter".

Abbildung 16: Auswählen der GetDiscontinuedProducts verwalteten 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 der Satz von nicht mehr 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, 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, um das Bild in voller Größe anzuzeigen)

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

Abbildung 19 zeigt einen Screenshot des DataSet-Designers nach dem ProductsTableAdapter Hinzufügen der Methoden zu den GetDiscontinuedProducts gespeicherten und GetProductsWithPriceLessThan verwalteten 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 Datenzugriffsschicht aktualisiert haben, um Methoden zum Aufrufen der in Schritt 4 und 5 hinzugefügten verwalteten gespeicherten Prozeduren einzuschließen, müssen wir der Geschäftslogikebene entsprechende Methoden hinzufügen. Fügen Sie der ProductsBLLWithSprocs Klasse die folgenden beiden Methoden hinzu:

<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetDiscontinuedProducts() As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetDiscontinuedProducts()
End Function
<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsWithPriceLessThan(ByVal priceLessThan As Decimal) _
    As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetProductsWithPriceLessThan(priceLessThan)
End Function

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

Schritt 8: Aufrufen der verwalteten gespeicherten Prozeduren aus der Präsentationsebene

Da die Geschäftslogik- und Datenzugriffsebenen erweitert wurden, um Unterstützung für das Aufrufen der GetDiscontinuedProducts gespeicherten Prozeduren und GetProductsWithPriceLessThan verwalteten gespeicherten Prozeduren zu unterstützen, 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 in der Toolbox eine GridView auf den Designer. Legen Sie die GridView-Eigenschaft ID auf und binden Sie sie von ihrem Smarttag an DiscontinuedProducts eine neue ObjectDataSource mit dem Namen DiscontinuedProductsDataSource. Konfigurieren Sie objectDataSource, um die Daten aus der ProductsBLLWithSprocs Klassenmethode GetDiscontinuedProducts abzurufen.

Abbildung 20: Konfigurieren der ObjectDataSource für die Verwendung der Klasse (Zum Anzeigen des ProductsBLLWithSprocs Bilds mit voller Größe klicken)

Abbildung 21: Auswählen der GetDiscontinuedProducts Methode aus der Dropdownliste auf der REGISTERKARTE SELECT (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)

Da dieses Raster verwendet wird, um nur Produktinformationen anzuzeigen, 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 für jedes Datenfeld in der ProductsDataTableDatei automatisch ein BoundField- oder CheckBoxField-Objekt hinzu. Nehmen Sie sich einen Moment Zeit, um alle diese Felder zu entfernen, mit Ausnahme ProductName von und Discontinued, an welcher Stelle ihr deklaratives GridView- und ObjectDataSource-Markup ähnlich 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 der ProductsBLLWithSprocs GetDiscontinuedProducts Klasse auf. Wie wir in Schritt 7 gesehen haben, ruft diese Methode die DAL s-Klasse-Methode ProductsDataTable GetDiscontinuedProducts 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 nicht mehr verfügbaren Produkte zurück.

Die von der verwalteten gespeicherten Prozedur zurückgegebenen Ergebnisse werden von DAL in eine ProductsDataTable von der DAL verpackt und dann an die BLL zurückgegeben, die sie dann an die Präsentationsebene zurückgibt, an die sie an die GridView gebunden und angezeigt werden. Wie erwartet, listet das Raster die Produkte auf, die nicht mehr unterstützt wurden.

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

Fügen Sie der Seite ein TextBox- und ein anderes GridView-Element hinzu, um weitere Vorgehensweisen zu erhalten. Lassen Sie dieses GridView-Objekt die Produkte anzeigen, die kleiner als der in das TextBox-Objekt eingegebene Betrag sind, indem Sie die Methode der ProductsBLLWithSprocs Klasse GetProductsWithPriceLessThan aufrufen.

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

Benutzerdefinierte Funktionen oder UDFs sind Datenbankobjekte, die die Semantik von Funktionen in Programmiersprachen genau nachahmen. Wie eine Funktion in Visual Basic 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. Sehen wir uns beide Arten von UDFs an, beginnend mit einer UDF, die einen skalaren Datentyp zurückgibt.

Die folgende UDF berechnet den geschätzten Wert des Lagerbestands für ein bestimmtes Produkt. Dazu werden drei Eingabeparameter ( die UnitPriceWerte UnitsInStockfür Discontinued ein bestimmtes Produkt ) verwendet und ein Wert vom Typ zurückgegeben money. Er berechnet den geschätzten Wert des Lagerbestands durch Multiplizieren mit dem UnitPrice 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

Sobald diese UDF der Datenbank hinzugefügt wurde, kann sie über Management Studio gefunden werden, indem Sie den Ordner "Programmierbarkeit" und dann "Funktionen" und dann "Skalarwertfunktionen" 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 zur 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: Jedes Produktinventarwert ist 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 auf diese UDF in der FROM (oder JOIN) Klausel einer SELECT Abfrage verwiesen werden. Im folgenden Beispiel werden die ProductIDWerte ProductNameund Werte CategoryID für jedes der Getränke zurückgegeben.

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

Ich habe die udf_GetProductsByCategoryID UDF zur 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 Objekt-Explorer Tabellenwertfunktionen.

Abbildung 24: Das ProductID, ProductNameund CategoryID sind für jedes Getränk aufgelistet (Klicken Sie hier, um das Bild mit voller Größe anzuzeigen)

Hinweis

Weitere Informationen zum Erstellen und Verwenden von UDFs finden Sie in der Einführung in benutzerdefinierte Funktionen. Sehen Sie sich auch Die Vorteile und Nachteile von benutzerdefinierten Funktionen an.

Schritt 10: Erstellen einer verwalteten UDF

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

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

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

Die Vorlage "User-Defined Function" erstellt eine Partial Klasse UserDefinedFunctions mit einer Methode, deren Name mit dem Namen der Klassendatei übereinstimmt (udf_ComputeInventoryValue_Managedin dieser Instanz). Diese Methode wird mit dem SqlFunction Attribut versehen, das die Methode als verwaltete UDF kennzeichnet.

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

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 - UnitPrice, UnitsInStockund - akzeptiert und Discontinued ein SqlMoney Objekt zurückgibt. Die Logik für die Berechnung des Bestandswerts ist identisch mit dem in der T-SQL udf_ComputeInventoryValue UDF.

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

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, der UnitsInStock Spalte vom Typ moneysmallintund der Discontinued Spalte des Typsbit.

Der Code beginnt mit dem Erstellen einer SqlMoney Instanz mit dem Namen inventoryValue 0. Die Products Tabelle ermöglicht Datenbankwerte NULL in den UnitsInPrice Und UnitsInStock Spalten. Daher müssen wir zuerst überprüfen, ob diese Werte s enthaltenNULL, die wir über die Eigenschaft des SqlMoney IsNull Objekts ausführen. Wenn beide Werte enthalten UnitPrice und UnitsInStock keineNULL Werte enthalten, wird das Produkt der beiden berechnet inventoryValue . Discontinued Wenn dies der Fall ist, halbieren wir den Wert.

Hinweis

Das SqlMoney Objekt lässt nur zu, dass zwei SqlMoney Instanzen miteinander multipliziert werden. Es lässt nicht zu, dass eine SqlMoney Instanz mit einer literalen Gleitkommazahl multipliziert wird. Um sie zu halbieren inventoryValue , multiplizieren wir sie mit einer neuen SqlMoney Instanz, die 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 wir in Schritt 4 gesehen haben, werden die verwalteten Objekte in einem SQL Server-Projekt bereitgestellt, indem Sie im Projektmappen-Explorer mit der rechten Maustaste auf den Projektnamen 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 verwaltete UDF, die in Schritt 10 erstellt wurde, die gerade bereitgestellt wurde.

Führen Sie zum Testen dieser verwalteten UDF die folgende Abfrage in Management Studio aus:

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_ComputeInventoryValue UDF, aber die Ausgabe ist identisch. Verweisen Sie auf Abbildung 23, um einen Screenshot der UDF-Ausgabe anzuzeigen.

Schritt 12: Debuggen der verwalteten Datenbankobjekte

Im Lernprogramm zum Debuggen gespeicherter Prozeduren haben wir die drei Optionen zum Debuggen von SQL Server über Visual Studio erläutert: Direktes Debuggen von Datenbanken, Anwendungsdebugging und Debuggen aus einem SQL Server-Projekt. Verwaltete Datenbankobjekte können nicht über das Direkte Datenbankdebugging gedebuggt werden, können aber aus einer Clientanwendung und direkt aus dem SQL Server-Projekt gedebuggt werden. Damit das Debuggen funktioniert, muss die SQL Server 2005-Datenbank jedoch das SQL/CLR-Debuggen zulassen. Erinnern Sie sich daran, dass visual Studio beim ersten Erstellen des ManagedDatabaseConstructs Projekts gefragt wurde, ob das SQL/CLR-Debuggen aktiviert werden soll (siehe Abbildung 6 in Schritt 2). Diese Einstellung kann geändert werden, indem Sie im Server-Explorer-Fenster mit der rechten Maustaste auf die Datenbank klicken.

Abbildung 26: Sicherstellen, dass die Datenbank das DEBUGGEN von SQL/CLR zulässt

Stellen Sie sich vor, dass wir die GetProductsWithPriceLessThan verwaltete gespeicherte Prozedur debuggen wollten. Zunächst legen wir einen Haltepunkt im Code der GetProductsWithPriceLessThan Methode fest.

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

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

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

exec GetProductsWithPriceLessThan 14.95

Nachdem Sie das obige Skript Test.sqleingegeben haben, starten Sie das Debuggen, indem Sie zum Menü "Debuggen" wechseln und "Debuggen starten" auswählen oder F5 oder das grüne Wiedergabesymbol in der Symbolleiste drücken. Dadurch werden die Projekte in 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 durchlaufen, die Werte der Eingabeparameter untersuchen usw.

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

Damit ein SQL-Datenbankobjekt über eine Clientanwendung gedebuggt werden kann, muss die Datenbank für die Unterstützung des Anwendungsdebuggings konfiguriert werden. Klicken Sie im Server-Explorer mit der rechten Maustaste auf die Datenbank, und stellen Sie sicher, dass die Option "Anwendungsdebugging" aktiviert ist. Darüber hinaus müssen wir die ASP.NET Anwendung so konfigurieren, dass sie in den SQL-Debugger integriert und die Verbindungspooling deaktiviert wird. Diese Schritte wurden in Schritt 2 des Lernprogramms 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 aufruft, die einen Haltepunkt aufweisen, 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 erleichtern das Erstellen, Kompilieren und Bereitstellen von verwalteten Datenbankobjekten. 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 sie manuell erstellen und bereitstellen. Dies umfasst vier Schritte:

1. Erstellen Einer Datei, die den Quellcode für das verwaltete Datenbankobjekt enthält,
2. Kompilieren des Objekts in einer Assembly,
3. Registrieren der Assembly mit 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 wir eine neue verwaltete gespeicherte Prozedur, die diese Produkte zurückgibt, deren UnitPrice Wert größer als ein angegebener Wert ist. Erstellen Sie eine neue Datei auf Ihrem Computer namens GetProductsWithPriceGreaterThan.vb , und geben Sie den folgenden Code in die Datei ein (Sie können Visual Studio, Editor oder einen beliebigen Text-Editor verwenden, um dies zu erreichen):

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

Dieser Code ist nahezu identisch mit der methode, die GetProductsWithPriceLessThan in Schritt 5 erstellt wurde. Die einzigen Unterschiede sind die Methodennamen, die WHERE Klausel und der Parametername, der in der Abfrage verwendet wird. Zurück in der Methode liest die GetProductsWithPriceLessThan WHERE Klausel: WHERE UnitPrice < @MaxPrice. Hier, in GetProductsWithPriceGreaterThan, verwenden wir: WHERE UnitPrice > @MinPrice .

Wir müssen diese Klasse jetzt in eine Assembly kompilieren. Navigieren Sie in der Befehlszeile zu dem Verzeichnis, in dem Sie die GetProductsWithPriceGreaterThan.vb Datei gespeichert haben, und verwenden Sie den C#-Compiler (csc.exe), um die Klassendatei in eine Assembly zu kompilieren:

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

Wenn der Ordner, der "v bc.exe " enthält, nicht in den Systemdaten enthalten PATH, müssen Sie vollständig auf den Pfad verweisen, %WINDOWS%\Microsoft.NET\Framework\version\z. B.:

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

Abbildung 29: Kompilieren GetProductsWithPriceGreaterThan.vb in einer Assembly (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)

Das /t Flag gibt an, dass die Visual Basic-Klassendatei in eine DLL (anstelle einer ausführbaren Datei) kompiliert werden soll. Das /out Flag gibt den Namen der resultierenden Assembly an.

Hinweis

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

Nachdem der Code in einer Assembly kompiliert wurde, können wir die Assembly in der SQL Server 2005-Datenbank registrieren. Dies kann über T-SQL, mithilfe des Befehls CREATE ASSEMBLYoder über SQL Server Management Studio ausgeführt werden. Konzentrieren wir uns auf die Verwendung von Management Studio.

Erweitern Sie in Management Studio den Ordner "Programmierbarkeit" in der Northwind-Datenbank. Einer seiner Unterordner ist Assemblys. Wenn Sie der Datenbank manuell eine neue Assembly hinzufügen möchten, 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 der Datenbank die Assembly hinzuzufügen. Die ManuallyCreatedDBObjects.dll Assembly sollte nicht im Objekt-Explorer angezeigt werden.

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

Abbildung 31: Die ManuallyCreatedDBObjects.dll listet in der Objekt-Explorer

Während wir die Assembly der Northwind-Datenbank hinzugefügt haben, müssen wir noch eine gespeicherte Prozedur der GetProductsWithPriceGreaterThan Methode in der Assembly 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).

Aktualisieren Sie nach dem Ausführen des obigen Skripts den Ordner "Gespeicherte Prozeduren" im Objekt-Explorer. Es sollte ein neuer Eintrag für gespeicherte Prozeduren angezeigt werden – GetProductsWithPriceGreaterThan mit einem Sperrsymbol daneben. 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 in Abbildung 32 dargestellt, zeigt der obige Befehl Informationen für diese Produkte mit einem UnitPrice Wert von mehr als 24,95 $ an.

Abbildung 32: Die ManuallyCreatedDBObjects.dll listet in der Objekt-Explorer auf (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)

Zusammenfassung

Microsoft SQL Server 2005 bietet die Integration mit der Common Language Runtime (CLR), die das Erstellen von Datenbankobjekten mithilfe von verwaltetem Code ermöglicht. Bisher konnten diese Datenbankobjekte nur mit T-SQL erstellt werden. Jetzt können wir diese Objekte jedoch mit .NET-Programmiersprachen wie Visual Basic erstellen. In diesem Lernprogramm haben wir zwei verwaltete gespeicherte Prozeduren und eine verwaltete benutzerdefinierte Funktion erstellt.

Der SQL Server-Projekttyp von Visual Studio erleichtert das Erstellen, Kompilieren und Bereitstellen von verwalteten Datenbankobjekten. Darüber hinaus bietet es umfassende Debugging-Unterstützung. SQL Server-Projekttypen sind jedoch nur in den Editionen Professional und Team Systems von Visual Studio verfügbar. Für Personen, 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 dargestellt.

Glückliche Programmierung!

Weitere nützliche Informationen

Weitere Informationen zu den in diesem Lernprogramm erläuterten Themen finden Sie in den folgenden Ressourcen:

• Vorteile und Nachteile von benutzerdefinierten Funktionen
• Erstellen von SQL Server 2005-Objekten in verwaltetem Code
• Gewusst wie: Erstellen und Ausführen einer gespeicherten CLR SQL Server-Prozedur
• Gewusst wie: Erstellen und Ausführen einer benutzerdefinierten CLR SQL Server-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 Web Technologies zusammen. Scott arbeitet als unabhängiger Berater, Trainer und Schriftsteller. Sein neuestes Buch ist Sams Teach Yourself ASP.NET 2.0 in 24 Stunden. Er kann über mitchell@4GuysFromRolla.com seinen Blog erreicht werden, der unter .http://ScottOnWriting.NET

Besonderer Dank an

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

Vorherige