Verwenden von vorhandenen gespeicherten Prozeduren für die TableAdapter-Steuerelemente des typisierten DataSet (VB)
von Scott Mitchell
Im vorherigen Tutorial haben wir gelernt, wie Sie den TableAdapter-Assistenten verwenden, um neue gespeicherte Prozeduren zu generieren. In diesem Tutorial erfahren Sie, wie derselbe TableAdapter-Assistent mit vorhandenen gespeicherten Prozeduren arbeiten kann. Außerdem erfahren Sie, wie Sie unserer Datenbank manuell neue gespeicherte Prozeduren hinzufügen.
Einführung
Im vorherigen Tutorial haben wir erfahren, wie die TableAdapters für typisierte DataSets konfiguriert werden können, um gespeicherte Prozeduren anstelle von Ad-hoc-SQL-Anweisungen für den Zugriff auf Daten zu verwenden. Insbesondere haben wir untersucht, wie der TableAdapter-Assistent diese gespeicherten Prozeduren automatisch erstellt. Beim Portieren einer Legacyanwendung zu ASP.NET 2.0 oder beim Erstellen einer ASP.NET 2.0-Website um ein vorhandenes Datenmodell besteht die Wahrscheinlichkeit, dass die Datenbank bereits die benötigten gespeicherten Prozeduren enthält. Alternativ können Sie es vorziehen, Ihre gespeicherten Prozeduren manuell oder mit einem anderen Tool als dem TableAdapter-Assistenten zu erstellen, der Ihre gespeicherten Prozeduren automatisch generiert.
In diesem Tutorial erfahren Sie, wie Sie den TableAdapter für die Verwendung vorhandener gespeicherter Prozeduren konfigurieren. Da die Northwind-Datenbank nur einen kleinen Satz von integrierten gespeicherten Prozeduren enthält, sehen wir uns auch die Schritte an, die erforderlich sind, um der Datenbank über die Visual Studio-Umgebung manuell neue gespeicherte Prozeduren hinzuzufügen. Lassen Sie uns loslegen!
Hinweis
Im Tutorial Umschließen von Datenbankänderungen in einer Transaktion haben wir dem TableAdapter Methoden hinzugefügt, um Transaktionen (BeginTransaction
, CommitTransaction
usw.) zu unterstützen. Alternativ können Transaktionen vollständig in einer gespeicherten Prozedur verwaltet werden, die keine Änderungen am Datenzugriffsebenencode erfordert. In diesem Tutorial untersuchen wir die T-SQL-Befehle, die zum Ausführen von Anweisungen einer gespeicherten Prozedur im Bereich einer Transaktion verwendet werden.
Schritt 1: Hinzufügen gespeicherter Prozeduren zur Northwind-Datenbank
Visual Studio erleichtert das Hinzufügen neuer gespeicherter Prozeduren zu einer Datenbank. Fügen Sie der Northwind-Datenbank eine neue gespeicherte Prozedur hinzu, die alle Spalten aus der Products
Tabelle für diejenigen zurückgibt, die einen bestimmten CategoryID
Wert haben. Erweitern Sie im Fenster Server Explorer die Datenbank Northwind, damit ihre Ordner – Datenbankdiagramme, Tabellen, Sichten usw. – angezeigt werden. Wie im vorherigen Tutorial gezeigt, enthält der Ordner Gespeicherte Prozeduren die vorhandenen gespeicherten Prozeduren der Datenbank. Um eine neue gespeicherte Prozedur hinzuzufügen, klicken Sie einfach mit der rechten Maustaste auf den Ordner Gespeicherte Prozeduren, und wählen Sie im Kontextmenü die Option Neue gespeicherte Prozedur hinzufügen aus.
Abbildung 1: Right-Click des Ordners "Gespeicherte Prozeduren" und Hinzufügen einer neuen gespeicherten Prozedur (Klicken Sie hier, um ein Bild in voller Größe anzuzeigen)
Wie abbildung 1 zeigt, wird durch Auswählen der Option Neue gespeicherte Prozedur hinzufügen ein Skriptfenster in Visual Studio mit der Gliederung des SQL-Skripts geöffnet, das zum Erstellen der gespeicherten Prozedur erforderlich ist. Es ist unsere Aufgabe, dieses Skript auszuprobieren und auszuführen. An diesem Punkt wird die gespeicherte Prozedur der Datenbank hinzugefügt.
Geben Sie das folgende Skript ein:
CREATE PROCEDURE dbo.Products_SelectByCategoryID
(
@CategoryID int
)
AS
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = @CategoryID
Dieses Skript fügt der Northwind-Datenbank bei Ausführung eine neue gespeicherte Prozedur mit dem Namen Products_SelectByCategoryID
hinzu. Diese gespeicherte Prozedur akzeptiert einen einzelnen Eingabeparameter (@CategoryID
vom Typ int
) und gibt alle Felder für diese Produkte mit einem übereinstimmenden CategoryID
Wert zurück.
Um dieses CREATE PROCEDURE
Skript auszuführen und die gespeicherte Prozedur der Datenbank hinzuzufügen, klicken Sie auf der Symbolleiste auf das Symbol Speichern, oder drücken Sie STRG+S. Danach wird der Ordner Gespeicherte Prozeduren aktualisiert und zeigt die neu erstellte gespeicherte Prozedur an. Außerdem ändert CREATE PROCEDURE dbo.Products_SelectProductByCategoryID
sich das Skript im Fenster von in ALTER PROCEDURE
dbo.Products_SelectProductByCategoryID
. CREATE PROCEDURE
fügt der Datenbank eine neue gespeicherte Prozedur hinzu, während ALTER PROCEDURE
eine vorhandene aktualisiert wird. Da sich der Anfang des Skripts in ALTER PROCEDURE
geändert hat, wird die gespeicherte Prozedur durch Ändern der Eingabeparameter oder SQL-Anweisungen und klicken auf das Symbol Speichern die gespeicherte Prozedur mit diesen Änderungen aktualisiert.
Abbildung 2 zeigt Visual Studio, nachdem die Products_SelectByCategoryID
gespeicherte Prozedur gespeichert wurde.
Abbildung 2: Die gespeicherte Prozedur Products_SelectByCategoryID
wurde der Datenbank hinzugefügt (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)
Schritt 2: Konfigurieren des TableAdapters für die Verwendung einer vorhandenen gespeicherten Prozedur
Nachdem die Products_SelectByCategoryID
gespeicherte Prozedur der Datenbank hinzugefügt wurde, können wir unsere Datenzugriffsebene so konfigurieren, dass diese gespeicherte Prozedur verwendet wird, wenn eine ihrer Methoden aufgerufen wird. Insbesondere fügen wir dem ProductsTableAdapter
im typisierten DataSet eine GetProductsByCategoryID(<_i22_>categoryID)<!--_i22_-->
-Methode hinzu, die NorthwindWithSprocs
die Products_SelectByCategoryID
soeben erstellte gespeicherte Prozedur aufruft.
Öffnen Sie zunächst das NorthwindWithSprocs
DataSet. Klicken Sie mit der rechten Maustaste auf die ProductsTableAdapter
, und wählen Sie Abfrage hinzufügen aus, um den TableAdapter-Abfragekonfigurations-Assistenten zu starten. Im vorherigen Tutorial haben wir uns dafür entschieden, dass der TableAdapter eine neue gespeicherte Prozedur für uns erstellt. In diesem Tutorial möchten wir jedoch die neue TableAdapter-Methode mit der vorhandenen Products_SelectByCategoryID
gespeicherten Prozedur verknüpfen. Wählen Sie daher im ersten Schritt des Assistenten die Option Vorhandene gespeicherte Prozedur verwenden aus, und klicken Sie dann auf Weiter.
Abbildung 3: Auswählen der Option Vorhandene gespeicherte Prozedur verwenden (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)
Der folgende Bildschirm enthält eine Dropdownliste mit den gespeicherten Prozeduren der Datenbank. Wenn Sie eine gespeicherte Prozedur auswählen, werden die Eingabeparameter auf der linken Seite und die (falls vorhanden) zurückgegebenen Datenfelder auf der rechten Seite aufgelistet. Wählen Sie die Products_SelectByCategoryID
gespeicherte Prozedur aus der Liste aus, und klicken Sie auf Weiter.
Abbildung 4: Auswählen der gespeicherten Products_SelectByCategoryID
Prozedur (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)
Der nächste Bildschirm fragt uns, welche Art von Daten von der gespeicherten Prozedur zurückgegeben wird, und unsere Antwort bestimmt den Typ, der von der TableAdapter-Methode zurückgegeben wird. Wenn beispielsweise angegeben wird, dass tabellarische Daten zurückgegeben werden, gibt die Methode eine ProductsDataTable
instance zurück, die mit den von der gespeicherten Prozedur zurückgegebenen Datensätzen aufgefüllt wird. Wenn wir dagegen angeben, dass diese gespeicherte Prozedur einen einzelnen Wert zurückgibt, gibt der TableAdapter einen Object
zurück, dem der Wert in der ersten Spalte des ersten Datensatzes zugewiesen ist, der von der gespeicherten Prozedur zurückgegeben wird.
Da die Products_SelectByCategoryID
gespeicherte Prozedur alle Produkte zurückgibt, die zu einer bestimmten Kategorie gehören, wählen Sie die erste Antwort – Tabellarische Daten – aus, und klicken Sie auf Weiter.
Abbildung 5: Angeben, dass die gespeicherte Prozedur tabellarische Daten zurückgibt (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)
Es bleibt nur anzugeben, welche Methodenmuster verwendet werden sollen, gefolgt von den Namen für diese Methoden. Lassen Sie die Optionen DataTable ausfüllen und DataTable zurückgeben aktiviert, benennen Sie die Methoden jedoch in und GetProductsByCategoryID
umFillByCategoryID
. Klicken Sie dann auf Weiter, um eine Zusammenfassung der Aufgaben zu überprüfen, die der Assistent ausführt. Wenn alles richtig aussieht, klicken Sie auf Fertig stellen.
Abbildung 6: Benennen Sie die Methoden FillByCategoryID
und GetProductsByCategoryID
(Klicken Sie hier, um das Bild in voller Größe anzuzeigen)
Hinweis
Die soeben erstellten TableAdapter-Methoden FillByCategoryID
und GetProductsByCategoryID
erwarten einen Eingabeparameter vom Typ Integer
. Dieser Eingabeparameterwert wird über den @CategoryID
-Parameter an die gespeicherte Prozedur übergeben. Wenn Sie die Parameter der Products_SelectByCategory
gespeicherten Prozedur ändern, müssen Sie auch die Parameter für diese TableAdapter-Methoden aktualisieren. Wie im vorherigen Tutorial erläutert, kann dies auf zwei Arten erfolgen: durch manuelles Hinzufügen oder Entfernen von Parametern aus der Parameterauflistung oder durch erneutes Ausführen des TableAdapter-Assistenten.
Schritt 3: Hinzufügen einerGetProductsByCategoryID(categoryID)
Methode zur BLL
Nachdem die GetProductsByCategoryID
DAL-Methode abgeschlossen ist, besteht der nächste Schritt darin, zugriff auf diese Methode in der Geschäftslogikebene bereitzustellen. Öffnen Sie die ProductsBLLWithSprocs
Klassendatei, und fügen Sie die folgende Methode hinzu:
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsByCategoryID(ByVal categoryID As Integer) _
As NorthwindWithSprocs.ProductsDataTable
Return Adapter.GetProductsByCategoryID(categoryID)
End Function
Diese BLL-Methode gibt einfach die ProductsDataTable
von der ProductsTableAdapter
s-Methode GetProductsByCategoryID
zurückgegebene zurück. Das DataObjectMethodAttribute
Attribut stellt Metadaten bereit, die vom Assistenten zum Konfigurieren von Datenquellen von ObjectDataSource verwendet werden. Insbesondere wird diese Methode in der Dropdownliste der Registerkarten SELECT angezeigt.
Schritt 4: Anzeigen von Produkten nach Kategorie
Um die neu hinzugefügte Products_SelectByCategoryID
gespeicherte Prozedur und die entsprechenden DAL- und BLL-Methoden zu testen, erstellen wir eine ASP.NET Seite, die eine DropDownList und eine GridView enthält. Die DropDownList listet alle Kategorien in der Datenbank auf, während gridView die Produkte anzeigt, die zur ausgewählten Kategorie gehören.
Hinweis
In den vorherigen Tutorials haben wir master-/Detailschnittstellen mithilfe von DropDownLists erstellt. Einen ausführlicheren Einblick in die Implementierung eines solchen master-/Detailberichts finden Sie im Tutorial Master-/Detailfilterung mit dropDownList.
Öffnen Sie die ExistingSprocs.aspx
Seite im AdvancedDAL
Ordner, und ziehen Sie eine DropDownList aus der Toolbox auf die Designer. Legen Sie die DropDownList-Eigenschaft ID
auf Categories
und die zugehörige AutoPostBack
Eigenschaft auf fest True
. Binden Sie als Nächstes über das Smarttag die DropDownList an eine neue ObjectDataSource mit dem Namen CategoriesDataSource
. Konfigurieren Sie die ObjectDataSource so, dass sie ihre Daten aus der s-Methode GetCategories
der CategoriesBLL
Klasse abruft. Legen Sie die Dropdownlisten auf den Registerkarten UPDATE, INSERT und DELETE auf (Keine) fest.
Abbildung 7: Abrufen von Daten aus der CategoriesBLL
Class s-Methode (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)GetCategories
Abbildung 8: Festlegen der Drop-Down Listen in den Registerkarten UPDATE, INSERT und DELETE auf (Keine) (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)
Nachdem Sie den ObjectDataSource-Assistenten abgeschlossen haben, konfigurieren Sie dropDownList so, dass das CategoryName
Datenfeld angezeigt und das CategoryID
Feld als Value
für jedes ListItem
verwendet wird.
An diesem Punkt sollte das deklarative Markup DropDownList und ObjectDataSource in etwa wie folgt aussehen:
<asp:DropDownList ID="Categories" runat="server" AutoPostBack="True"
DataSourceID="CategoriesDataSource" DataTextField="CategoryName"
DataValueField="CategoryID">
</asp:DropDownList>
<asp:ObjectDataSource ID="CategoriesDataSource" runat="server"
OldValuesParameterFormatString="original_{0}"
SelectMethod="GetCategories" TypeName="CategoriesBLL">
</asp:ObjectDataSource>
Ziehen Sie als Nächstes ein GridView-Objekt auf die Designer, und platzieren Sie sie unter der DropDownList. Legen Sie die GridViews ID
auf ProductsByCategory
fest, und binden Sie sie über das Smarttag an eine neue ObjectDataSource mit dem Namen ProductsByCategoryDataSource
. Konfigurieren Sie objectDataSource ProductsByCategoryDataSource
für die Verwendung der ProductsBLLWithSprocs
-Klasse, damit sie ihre Daten mithilfe der GetProductsByCategoryID(categoryID)
-Methode abrufen kann. Da diese GridView nur zum Anzeigen von Daten verwendet wird, legen Sie die Dropdownlisten in den Registerkarten UPDATE, INSERT und DELETE auf (Keine) fest, und klicken Sie auf Weiter.
Abbildung 9: Konfigurieren der ObjectDataSource für die Verwendung der ProductsBLLWithSprocs
-Klasse (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)
Abbildung 10: Abrufen von Daten aus der GetProductsByCategoryID(categoryID)
-Methode (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)
Die auf der Registerkarte SELECT ausgewählte Methode erwartet einen Parameter, sodass wir im letzten Schritt des Assistenten zur Eingabe der Quelle des Parameters aufgefordert werden. Legen Sie die Dropdownliste Parameterquelle auf Steuerelement fest, und wählen Sie das Categories
Steuerelement aus der Dropdownliste ControlID aus. Klicken Sie auf Fertig stellen, um den Assistenten abzuschließen.
Abbildung 11: Verwenden von Categories
DropDownList als Quelle des categoryID
Parameters (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)
Nach Abschluss des ObjectDataSource-Assistenten fügt Visual Studio BoundFields und ein CheckBoxField-Element für jedes Produktdatenfeld hinzu. Sie können diese Felder nach Bedarf anpassen.
Besuchen Sie die Seite über einen Browser. Beim Besuch der Seite wird die Kategorie Getränke ausgewählt und die entsprechenden Produkte im Raster aufgeführt. Das Ändern der Dropdownliste in eine alternative Kategorie, wie in Abbildung 12 dargestellt, führt zu einem Postback und lädt das Raster mit den Produkten der neu ausgewählten Kategorie neu.
Abbildung 12: Die Produkte in der Kategorie "Produktion" werden angezeigt (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)
Schritt 5: Umschließen von Anweisungen einer gespeicherten Prozedur innerhalb des Bereichs einer Transaktion
Im Tutorial Umschließen von Datenbankänderungen in einer Transaktion haben wir Techniken zum Ausführen einer Reihe von Datenbankänderungsanweisungen innerhalb des Bereichs einer Transaktion erläutert. Denken Sie daran, dass die änderungen, die unter dem Dach einer Transaktion durchgeführt werden, entweder alle erfolgreich sind oder alle fehlschlagen, was die Atomarität garantiert. Zu den Techniken für die Verwendung von Transaktionen gehören:
- Verwenden der Klassen im
System.Transactions
-Namespace, - Verwenden der Datenzugriffsebene ADO.NET Klassen wie
SqlTransaction
und - Hinzufügen der T-SQL-Transaktionsbefehle direkt innerhalb der gespeicherten Prozedur
Im Tutorial Umschließen von Datenbankänderungen in einer Transaktion wurden die ADO.NET Klassen im DAL verwendet. Im weiteren Verlauf dieses Tutorials wird untersucht, wie Sie eine Transaktion mithilfe von T-SQL-Befehlen in einer gespeicherten Prozedur verwalten.
Die drei wichtigen SQL-Befehle zum manuellen Starten, Committen und Rollback einer Transaktion sind BEGIN TRANSACTION
, COMMIT TRANSACTION
und ROLLBACK TRANSACTION
. Wie beim ADO.NET-Ansatz müssen wir bei der Verwendung von Transaktionen aus einer gespeicherten Prozedur das folgende Muster anwenden:
- Geben Sie den Start einer Transaktion an.
- Führen Sie die SQL-Anweisungen aus, aus denen die Transaktion besteht.
- Wenn in einer der Anweisungen aus Schritt 2 ein Fehler auftritt, führen Sie ein Rollback für die Transaktion durch.
- Wenn alle Anweisungen aus Schritt 2 ohne Fehler abgeschlossen werden, committen Sie die Transaktion.
Dieses Muster kann in T-SQL-Syntax mit der folgenden Vorlage implementiert werden:
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
... Perform the SQL statements that makeup the transaction ...
-- If we reach here, success!
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Whoops, there was an error
ROLLBACK TRANSACTION
-- Raise an error with the
-- details of the exception
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
Die Vorlage beginnt mit der Definition eines TRY...CATCH
Blocks, eines Konstrukts, das SQL Server 2005 neu ist. Wie bei Try...Catch
Blöcken in Visual Basic führt der SQL-Block TRY...CATCH
die Anweisungen im TRY
Block aus. Wenn eine Anweisung einen Fehler auslöst, wird die Steuerung sofort an den CATCH
Block übertragen.
Wenn beim Ausführen der SQL-Anweisungen, die die Transaktion bilden, keine Fehler auftreten, committet die COMMIT TRANSACTION
Anweisung die Änderungen und schließt die Transaktion ab. Wenn jedoch eine der -Anweisungen zu einem Fehler führt, gibt die ROLLBACK TRANSACTION
CATCH
im -Block die Datenbank in ihren Zustand vor dem Start der Transaktion zurück. Die gespeicherte Prozedur löst auch mithilfe des Befehls RAISERROR einen Fehler aus, der bewirkt, dass in der Anwendung ein SqlException
ausgelöst wird.
Hinweis
Da der TRY...CATCH
Block in SQL Server 2005 neu ist, funktioniert die obige Vorlage nicht, wenn Sie ältere Versionen von Microsoft SQL Server verwenden.
Sehen wir uns ein konkretes Beispiel an. Zwischen den Categories
Tabellen und Products
ist eine Fremdschlüsseleinschränkung vorhanden, was bedeutet, dass jedes CategoryID
Feld in der Products
Tabelle einem CategoryID
Wert in der Categories
Tabelle zugeordnet werden muss. Jede Aktion, die gegen diese Einschränkung verstoßen würde, z. B. der Versuch, eine Kategorie mit zugeordneten Produkten zu löschen, führt zu einer Verletzung der Fremdschlüsseleinschränkung. Um dies zu überprüfen, lesen Sie das Beispiel Zum Aktualisieren und Löschen vorhandener Binärdaten im Abschnitt Arbeiten mit Binärdaten (~/BinaryData/UpdatingAndDeleting.aspx
) erneut. Auf dieser Seite werden jede Kategorie im System zusammen mit den Schaltflächen Bearbeiten und Löschen aufgelistet (siehe Abbildung 13). Wenn Sie jedoch versuchen, eine Kategorie zu löschen, die über zugeordnete Produkte wie Getränke verfügt, schlägt das Löschen aufgrund eines Verstoßes gegen die Fremdschlüsseleinschränkung fehl (siehe Abbildung 14).
Abbildung 13: Jede Kategorie wird in einer GridView mit den Schaltflächen Bearbeiten und Löschen angezeigt (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)
Abbildung 14: Eine Kategorie mit vorhandenen Produkten kann nicht gelöscht werden (Klicken Sie hier, um das Bild in voller Größe anzuzeigen)
Stellen Sie sich jedoch vor, wir möchten zulassen, dass Kategorien gelöscht werden können, unabhängig davon, ob sie über zugehörige Produkte verfügen. Wenn eine Kategorie mit Produkten gelöscht wird, stellen Sie sich vor, dass wir auch ihre vorhandenen Produkte löschen möchten (obwohl eine andere Möglichkeit wäre, einfach die Werte der Produkte CategoryID
auf festzulegen NULL
). Diese Funktionalität kann durch die Kaskadierungsregeln der Fremdschlüsseleinschränkung implementiert werden. Alternativ können wir eine gespeicherte Prozedur erstellen, die einen @CategoryID
Eingabeparameter akzeptiert und bei Aufruf explizit alle zugeordneten Produkte und dann die angegebene Kategorie löscht.
Unser erster Versuch einer solchen gespeicherten Prozedur könnte wie folgt aussehen:
CREATE PROCEDURE dbo.Categories_Delete
(
@CategoryID int
)
AS
-- First, delete the associated products...
DELETE FROM Products
WHERE CategoryID = @CategoryID
-- Now delete the category
DELETE FROM Categories
WHERE CategoryID = @CategoryID
Dies wird zwar definitiv die zugehörigen Produkte und Kategorien löschen, aber nicht unter dem Dach einer Transaktion. Stellen Sie sich vor, dass es eine andere Fremdschlüsseleinschränkung Categories
gibt, die das Löschen eines bestimmten @CategoryID
Werts verbieten würde. Das Problem ist, dass in einem solchen Fall alle Produkte gelöscht werden, bevor wir versuchen, die Kategorie zu löschen. Das Nettoergebnis ist, dass diese gespeicherte Prozedur für eine solche Kategorie alle ihre Produkte entfernt, während die Kategorie beibehalten wird, da sie noch verwandte Datensätze in einer anderen Tabelle enthält.
Wenn die gespeicherte Prozedur jedoch innerhalb des Bereichs einer Transaktion umschlossen wäre, würde für die Löschvorgänge in der Products
Tabelle ein Rollback aufgrund eines fehlgeschlagenen Löschvorgangs für ausgeführt Categories
. Das folgende Skript für gespeicherte Prozeduren verwendet eine Transaktion, um die Atomarität zwischen den beiden DELETE
Anweisungen sicherzustellen:
CREATE PROCEDURE dbo.Categories_Delete
(
@CategoryID int
)
AS
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
-- First, delete the associated products...
DELETE FROM Products
WHERE CategoryID = @CategoryID
-- Now delete the category
DELETE FROM Categories
WHERE CategoryID = @CategoryID
-- If we reach here, success!
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Whoops, there was an error
ROLLBACK TRANSACTION
-- Raise an error with the
-- details of the exception
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
Nehmen Sie sich einen Moment Zeit, um die Categories_Delete
gespeicherte Prozedur der Northwind-Datenbank hinzuzufügen. Anweisungen zum Hinzufügen gespeicherter Prozeduren zu einer Datenbank finden Sie zurück zu Schritt 1.
Schritt 6: Aktualisieren vonCategoriesTableAdapter
Während wir die Categories_Delete
gespeicherte Prozedur zur Datenbank hinzugefügt haben, ist die DAL derzeit so konfiguriert, dass ad-hoc-SQL-Anweisungen verwendet werden, um den Löschvorgang auszuführen. Wir müssen die CategoriesTableAdapter
aktualisieren und ihn anweisen, stattdessen die Categories_Delete
gespeicherte Prozedur zu verwenden.
Hinweis
Weiter oben in diesem Tutorial haben wir mit dem NorthwindWithSprocs
DataSet gearbeitet. Dieses DataSet verfügt jedoch nur über eine einzelne Entität , ProductsDataTable
und wir müssen mit Kategorien arbeiten. Daher beziehe ich mich für den Rest dieses Tutorials, wenn ich über die Datenzugriffsebene spreche, auf das Northwind
DataSet, das wir zuerst im Tutorial Erstellen einer Datenzugriffsebene erstellt haben.
Öffnen Sie das Northwind DataSet, wählen Sie ausCategoriesTableAdapter
, und wechseln Sie zum Eigenschaftenfenster. Die Eigenschaftenfenster listet die InsertCommand
vom TableAdapter verwendeten , UpdateCommand
DeleteCommand
, und SelectCommand
die zugehörigen Namen und Verbindungsinformationen auf. Erweitern Sie die DeleteCommand
-Eigenschaft, um ihre Details anzuzeigen. Wie abbildung 15 zeigt, ist die DeleteCommand
s-Eigenschaft CommandType
auf Text festgelegt, wodurch sie angewiesen wird, den Text in der CommandText
-Eigenschaft als Ad-hoc-SQL-Abfrage zu senden.
Abbildung 15: Wählen Sie im Designer aus, um seine CategoriesTableAdapter
Eigenschaften im Eigenschaftenfenster anzuzeigen.
Um diese Einstellungen zu ändern, wählen Sie den Text (DeleteCommand) im Eigenschaftenfenster und dann (Neu) aus der Dropdownliste aus. Dadurch werden die Einstellungen für die CommandText
Eigenschaften , CommandType
und Parameters
gelöscht. Legen Sie als Nächstes die CommandType
-Eigenschaft auf festStoredProcedure
, und geben Sie dann den Namen der gespeicherten Prozedur für (CommandText
dbo.Categories_Delete
) ein. Wenn Sie sicherstellen, dass Sie die Eigenschaften in dieser Reihenfolge eingeben – zuerst die CommandType
und dann die CommandText
- füllt Visual Studio die Parameters-Auflistung automatisch auf. Wenn Sie diese Eigenschaften nicht in dieser Reihenfolge eingeben, müssen Sie die Parameter manuell über die parametersammlung Editor hinzufügen. In beiden Fällen empfiehlt es sich, auf die Auslassungspunkte in der Parameters-Eigenschaft zu klicken, um die Editor Parameters Collection anzuzeigen, um zu überprüfen, ob die richtigen Parametereinstellungen geändert wurden (siehe Abbildung 16). Wenn im Dialogfeld keine Parameter angezeigt werden, fügen Sie den @CategoryID
Parameter manuell hinzu (Sie müssen den @RETURN_VALUE
Parameter nicht hinzufügen).
Abbildung 16: Sicherstellen, dass die Parametereinstellungen korrekt sind
Sobald die DAL aktualisiert wurde, werden beim Löschen einer Kategorie automatisch alle zugehörigen Produkte gelöscht und dies unter dem Dach einer Transaktion. Um dies zu überprüfen, kehren Sie zur Seite Aktualisieren und Löschen vorhandener Binärdaten zurück, und klicken Sie für eine der Kategorien auf die Schaltfläche Löschen. Mit nur einem Mausklick werden die Kategorie und alle zugehörigen Produkte gelöscht.
Hinweis
Vor dem Testen der Categories_Delete
gespeicherten Prozedur, die eine Reihe von Produkten zusammen mit der ausgewählten Kategorie löscht, kann es ratsam sein, eine Sicherungskopie Ihrer Datenbank zu erstellen. Wenn Sie die NORTHWND.MDF
Datenbank in App_Data
verwenden, schließen Sie einfach Visual Studio, und kopieren Sie die MDF- und LDF-Dateien in App_Data
einen anderen Ordner. Nach dem Testen der Funktionalität können Sie die Datenbank wiederherstellen, indem Sie Visual Studio schließen und die aktuellen MDF- und LDF-Dateien in durch App_Data
die Sicherungskopien ersetzen.
Zusammenfassung
Während der TableAdapter-Assistent automatisch gespeicherte Prozeduren für uns generiert, gibt es Situationen, in denen wir solche gespeicherten Prozeduren möglicherweise bereits erstellt haben oder sie stattdessen manuell oder mit anderen Tools erstellen möchten. Um solche Szenarien zu unterstützen, kann der TableAdapter auch so konfiguriert werden, dass er auf eine vorhandene gespeicherte Prozedur verweist. In diesem Tutorial haben wir uns mit dem manuellen Hinzufügen gespeicherter Prozeduren zu einer Datenbank über die Visual Studio-Umgebung und das Verknüpfen der TableAdapter-Methoden mit diesen gespeicherten Prozeduren befasst. Außerdem haben wir die T-SQL-Befehle und das Skriptmuster untersucht, die zum Starten, Committen und Rollback von Transaktionen innerhalb einer gespeicherten Prozedur verwendet werden.
Viel Spaß beim Programmieren!
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. Leitende Gutachter für dieses Tutorial waren Hilton Geisenow, S ren Jacob Lauritsen und Teresa Murphy. Möchten Sie meine bevorstehenden MSDN-Artikel lesen? Wenn dies der Fall ist, legen Sie eine Zeile unter abmitchell@4GuysFromRolla.com.
Feedback
https://aka.ms/ContentUserFeedback.
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unterFeedback senden und anzeigen für