eins. Vorwort:
Gespeicherte Prozeduren sind eine Reihe von SQL-Anweisungen zur Ausführung bestimmter Funktionen, die kompiliert und in der Datenbank gespeichert werden. Der Benutzer führt eine gespeicherte Prozedur aus, indem er ihren Namen angibt und Parameter angibt (sofern die gespeicherte Prozedur über Parameter verfügt). Gespeicherte Prozeduren sind ein wichtiges Objekt in der Datenbank, und jede gut gestaltete Datenbankanwendung sollte gespeicherte Prozeduren verwenden. Im Allgemeinen haben gespeicherte Prozeduren die folgenden Vorteile:
◆ Gespeicherte Prozeduren ermöglichen die Programmierung von Standardkomponenten
◆ Gespeicherte Prozeduren können eine schnellere Ausführungsgeschwindigkeit erreichen
◆ Gespeicherte Prozeduren können den Netzwerkverkehr reduzieren
◆ Gespeicherte Prozeduren können vollständig als Sicherheitsmechanismus genutzt werden
Der Autor dieses Artikels wird eine Einführung geben Sie lernen die Anwendung gespeicherter Prozeduren in .NET-Datenbankanwendungen kennen und erfahren, wie Sie sie in Verbindung mit dem SqlDataAdapter-Objekt, DataSet-Objekt usw. in ADO.NET verwenden, um die Gesamtleistung von .NET-Datenbankanwendungen zu verbessern.
zwei. Systemanforderungen:
Entwicklungstools: Visual Studio.NET
Datenbankverwaltungssystem: SQL Server 2000 (einschließlich der im Beispielprogramm verwendeten Pubs-Datenbank)
3. Erstellen Sie eine einfache gespeicherte Prozedur:
Hier stelle ich Ihnen vor, wie Sie mit der Visual Studio.NET-IDE eine gespeicherte Prozedur erstellen. Es ist sehr einfach und intuitiv, gespeicherte Prozeduren mit der Visual Studio.NET-IDE zu erstellen. Wenn Sie im Server-Explorer zur Pubs-Datenbank navigieren und den Knoten erweitern, finden Sie verschiedene Datenbankobjekte, einschließlich gespeicherter Prozeduren, wie in Abbildung 1 dargestellt Zeigen.
Klicken Sie mit der rechten Maustaste auf den Knoten der gespeicherten Prozedur, um ein Menü mit dem Befehl „Neue gespeicherte Prozedur“ aufzurufen. Nach dem Erstellen einer neuen gespeicherten Prozedur wird die unten gezeigte Codevorlage im Codebearbeitungsfenster in der IDE angezeigt:
PROZEDUR ERSTELLEN dbo.StoredProcedure1
/*
(
@parameter1 Datentyp = Standardwert,
@parameter2 Datentyp OUTPUT )
*/
ALS
/* SET NOCOUNT ON */
ZURÜCKKEHREN
Die obige Codevorlage entspricht den vereinfachten Syntaxregeln zum Erstellen gespeicherter Prozeduren. Die vollständigen Syntaxregeln lauten wie folgt:
CREATE PROC [ EDURE ] procedure_name [ ;
[ { @parameter data_type }
[VARYING] [=Standard] [OUTPUT]
] [ ,...N ]
[ MIT
{ NEU KOMPILE |. VERSCHLÜSSELUNG |. NEU KOMPILE , VERSCHLÜSSELUNG } ]
[ZUR REPLIKATION]
AS sql_statement [ ...n ]
Aus Platzgründen wird die Bedeutung der einzelnen Parameter hier nicht vorgestellt. Interessierte Leser können sich auf die Informationen zum Datenbankverwaltungssystem SQL Server 2000 beziehen.
Im Folgenden werde ich jede grammatikalische Komponente in dieser Codevorlage kurz vorstellen. Die CREATE PROCEDURE-Anweisung erstellt eine gespeicherte Prozedur, gefolgt vom Namen der gespeicherten Prozedur. Die Komponenten in „/*...*/“ sind die Parameter der gespeicherten Prozedur, die Eingabeparameter und Ausgabeparameter umfassen kann. Der Inhalt, der auf das Schlüsselwort AS folgt, ist der Hauptteil der gespeicherten Prozedur, bei dem es sich um eine beliebige Anzahl und Art von SQL-Anweisungen handelt, die in der gespeicherten Prozedur enthalten sind. Das Schlüsselwort RETURN gibt das Ende der gespeicherten Prozedur an und kann einen ganzzahligen Statuswert an den Aufrufer zurückgeben. Lassen Sie uns eine einfache gespeicherte Prozedur ohne Parameter erstellen und verwenden:
CREATE PROCEDURE dbo.up_GetPublisherInfo
ALS
SELECT pub_id, pub_name, Stadt, Bundesland, Land
VON Verlagen
ZURÜCKKEHREN
Speichern Sie die oben genannte gespeicherte Prozedur, nachdem Sie sie erstellt haben. Nach dem Speichern wird der der gespeicherten Prozedur entsprechende Knoten im Server-Explorer angezeigt. Bitte beachten Sie auch, dass das Schlüsselwort CREATE im Codebearbeitungsfenster in das Schlüsselwort ALTER geändert wurde, das zum Ändern vorhandener gespeicherter Prozeduren verwendet wird. Um die oben genannte gespeicherte Prozedur auszuführen, klicken Sie einfach auf ihren Knoten und wählen Sie im Kontextmenü „Gespeicherte Prozedur ausführen“ aus. Das Ergebnis des Vorgangs ist wie folgt:
Vier. Erstellen Sie eine gespeicherte Prozedur mit Parametern:
Oben haben wir eine einfache gespeicherte Prozedur ohne Parameter erstellt, aber in tatsächlichen Anwendungen werden häufig viele gespeicherte Prozeduren mit Parametern verwendet. Gespeicherte Prozeduren mit Parametern werden im Allgemeinen zum Aktualisieren von Daten oder zum Einfügen von Daten verwendet. Im Folgenden können wir dieselbe Operationsmethode verwenden, um eine gespeicherte Prozedur mit Parametern zu erstellen:
PROZEDUR ERSTELLEN dbo.up_UpdatePublisherInfo
(
@pub_id char (4),
@pub_name varchar (40),
@cityvarchar(20),
@state char (2),
@country varchar (30)
)
ALS
UPDATE-Verleger
SET pub_name = @pub_name, city = @city, state = @state,
Land = @Land
WO ( pub_id = @pub_id )
ZURÜCKKEHREN
Im obigen Code zum Erstellen einer gespeicherten Prozedur deklarieren wir die lokalen Variablen-Parameter der gespeicherten Prozedur, indem wir vor dem Namen ein „@“-Zeichen hinzufügen. Außerdem deklarieren wir den Typ jedes Parameters und bestimmen den Richtungswert jedes Parameters ist, Gibt an, ob der Parameter ein Eingabetyp oder ein Ausgabetyp oder ein Eingabe-Ausgabe-Typ oder ein Rückgabewerttyp ist. Benutzer können die gespeicherte Prozedur über den entsprechenden Namen der gespeicherten Prozedur und korrekte und gültige Parameter aufrufen. Sie können den Parametern auch Ausgabeparameter hinzufügen, indem Sie das Schlüsselwort OUTPUT verwenden. Informationen zu bestimmten Methoden finden Sie in den Syntaxregeln oben. Ausgabeparameter können dem Aufrufer relevante Informationen zurückgeben.
Die oben gespeicherte Prozedur kann die entsprechenden Herausgeberinformationen in der Herausgebertabelle aktualisieren. Sie können es ausführen, indem Sie auf den Knoten der gespeicherten Prozedur klicken und im Kontextmenü „Gespeicherte Prozedur ausführen“ auswählen. Nach der Ausführung wird in der IDE ein Dialogfeld zur Eingabe von Herausgeberinformationen angezeigt (wie in Abbildung 3 dargestellt). Geben Sie in diesem Dialogfeld die korrekten und gültigen Aktualisierungsinformationen ein. Beachten Sie, dass der Wert von pub_id in der Originaltabelle vorhanden sein muss, und klicken Sie dann auf die Schaltfläche „OK“, um die Daten zu aktualisieren.
fünf. Erstellen Sie eine Datenbankanwendung mit einer einfachen gespeicherten Prozedur:
Als Nächstes verwenden wir die oben genannte gespeicherte Prozedur ohne Parameter, um eine Datenbankanwendung zu erstellen, die auch das SqlDataAdapter-Objekt und das DataSet-Objekt in ADO.NET verwendet. Das SqlDataAdapter-Objekt dient als Brücke zwischen der SQL Server-Datenbank und dem DataSet-Objekt, um beide zu verbinden. Das SqlDataAdapter-Objekt enthält zwei häufig verwendete Methoden: die Fill()-Methode und die Update()-Methode. Die Fill()-Methode kann die entsprechenden Daten aus der Datenbank abrufen und in das DataSet-Objekt füllen, und die Update()-Methode aktualisiert, wie der Name schon sagt, den Datensatz. Bevor wir die Fill()-Methode aufrufen, müssen wir die SelectCommand-Eigenschaft des SqlDataAdapter-Objekts festlegen, bei dem es sich eigentlich um ein SqlCommand-Objekt handelt. Die SelectCommand-Eigenschaft enthält gültige SQL-Anweisungen und kann entsprechende Daten aus der Datenbank abrufen und in das DataSet-Objekt füllen.
Zuerst erstellen wir eine Windows Forms-Anwendung, die Programmiersprache ist C#. Nachdem Sie ein neues Projekt in Visual Studio.NET erstellt haben, fügen Sie dem Projekt eine neue Klasse hinzu – die Publishers-Klasse, die die Geschäftslogik für die Verbindung mit der Backend-Datenbank und das Abrufen des Datensatzobjekts kapselt. Die Schritte sind wie folgt:
1. Fügen Sie die erforderlichen Namespace-Referenzen hinzu: using System.Data.SqlClient
; Fügen Sie dieser Klasse die folgenden erforderlichen Variablen hinzu:
private SqlConnection cnPubs;
private SqlCommand cmdPubs;
privater SqlDataAdapter daPubs;
privates DataSet dsPubs;
3． Vervollständigen Sie im Konstruktor dieser Klasse die Verbindung zur Backend-Datenbank und rufen Sie das SqlDataAdapter-Objekt und andere Geschäftslogik ab:
public Publishers()
{
versuchen
{
//Erstelle ein Datenbankverbindungsobjekt
cnPubs = new SqlConnection( "server=localhost;integrated security=true;database=pubs" );
//Erstellen Sie ein SqlCommand-Objekt und geben Sie seinen Befehlstyp als gespeicherte Prozedur an
cmdPubs = new SqlCommand();
cmdPubs.Connection = cnPubs;
cmdPubs.CommandType = CommandType.StoredProcedure;
cmdPubs.CommandText = "up_GetPublisherInfo";
//Erstellen Sie ein SqlDataAdapter-Objekt und legen Sie seine SelectCommand-Eigenschaft auf das obige SqlCommand-Objekt fest
daPubs = new SqlDataAdapter();
daPubs.SelectCommand = cmdPubs;
//Erstelle ein DataSet-Objekt
dsPubs = new DataSet();
}
Catch(Ausnahme) {}
}
4. Schließlich wird für diese Klasse eine GetPublisherInfo()-Methode bereitgestellt, die das DataSet-Objekt mit dem SqlDataAdapter-Objekt füllt und das gefüllte DataSet-Objekt zurückgibt. Die Methode lautet wie folgt (es ist zu beachten, dass das SqlDataAdapter-Objekt implizit die Datenbankverbindung öffnet). Öffnen Sie implizit die Datenbankverbindung, nachdem Sie die Daten erhalten haben. Schließen Sie die Verbindung formell, was bedeutet, dass das DataSet-Objekt im Nicht-Verbindungsmodus arbeitet und wenn Sie die Datenbankverbindung explizit öffnen und die Daten abrufen, wird das SqlDataAdapter-Objekt die Verbindung nicht schließen.
öffentliches DataSet GetPublisherInfo().
{
// Rufen Sie die Fill()-Methode des SqlDataAdapter-Objekts auf und geben Sie das Datensatzobjekt zurück
daPubs.Fill(dsPubs);
dsPubs zurückgeben;
}
Nachdem wir den Entwurf der Publishers-Klasse abgeschlossen haben, fügen wir dem Hauptformular ein DataGrid-Steuerelement hinzu und verwenden es, um die Daten im DataSet-Objekt anzuzeigen. Fügen Sie zunächst die folgenden Mitgliedsvariablen zur Hauptformularklasse hinzu:
private Publishers pubs;
privates DataSet ds;
Anschließend ändern Sie den Konstruktor der Hauptformularklasse wie folgt:
öffentliches Form1()
{
//
// Erforderlich für die Unterstützung von Windows Forms Designer
//
InitializeComponent();
//
// TODO: Konstruktorcode nach dem InitializeComponent-Aufruf hinzufügen
// pubs = new Publishers();
ds = pubs.GetPublisherInfo();
dataGrid1.DataSource = ds.Tables[0];
}
Auf diese Weise werden, sobald die Anwendung gestartet wird, die entsprechenden Daten, die mit der oben genannten gespeicherten Prozedur ohne Parameter aus der Pubs-Datenbank abgerufen wurden, im DataGrid-Steuerelement des Hauptformulars angezeigt. Das Programmlaufdiagramm sieht wie folgt aus:
6. Erstellen Sie eine Datenbankanwendung mit einer gespeicherten Prozedur mit Parametern:
Oben haben wir eine Anwendung mit einer gespeicherten Prozedur ohne Parameter erstellt, und jetzt erstellen wir eine komplexere Datenbankanwendung. In tatsächlichen Datenbankanwendungen müssen wir häufig Daten abrufen und Daten aktualisieren, einfügen oder löschen. Gleichzeitig müssen wir bei Verwendung des SqlDataAdapter-Objekts dessen Update() aufrufen. Verfahren. Die Update()-Methode schließt den entsprechenden Vorgang automatisch basierend auf den Änderungen in jedem Datensatz im DataTable-Objekt im DataSet-Objekt ab. Das SqlDataAdapter-Objekt enthält auch Eigenschaften wie UpdateCommand, InsertCommand, DeleteCommand usw. Diese Eigenschaften sind eigentlich SqlCommand-Objekte. Die Update()-Methode wählt die entsprechenden Attribute basierend auf der Art der Operation aus.
Wenn wir gespeicherte Prozeduren mit Parametern zum Erstellen von Datenbankanwendungen verwenden, verwenden wir im Allgemeinen die Klasse SqlParameter, die verschiedene Eigenschaften und Methoden im Zusammenhang mit SQL-Parametern kapselt. Zu den Eigenschaften gehören ParameterName, SqlDBType, Direction, Size, Value, SourceColumn und SourceVersion usw. Darunter werden ParameterName, SqlDBType, Direction, Size und andere Attribute verwendet, um die in der gespeicherten Prozedur definierten Parameter abzugleichen. Beispielsweise wird das unten definierte SqlParameter-Objekt verwendet, um den Parameter „@pub_id “ in der zuvor definierten gespeicherten Prozedur up_UpdatePublisherInfo abzugleichen.
SqlParameter updParam = new SqlParameter( "@pub_id", SqlDbType.Char, 4 );
Obwohl das Direction-Attribut in der obigen Definition nicht explizit angegeben ist, ist sein Standardwert Input, sodass es unseren Anforderungen entspricht. Und wenn die Direction-Eigenschaft eines SqlParameter-Objekts InputOutput oder Output oder ReturnValue ist, muss seine Direction-Eigenschaft klar angegeben werden. Der folgende Code deklariert beispielsweise eindeutig, dass die Direction-Eigenschaft eines SqlParameter-Objekts Output ist.
oParam.Direction = ParameterDirection.Output;
Die SourceColumn-Eigenschaft wird verwendet, um das DataColumn-Objekt in einem DataTable-Objekt abzugleichen. Dieser Abgleich kann implizit das erforderliche SqlParameter-Objekt importieren, wenn die Update()-Methode aufgerufen wird, um das DataTable-Objekt zu aktualisieren. Wenn diese Eigenschaft bei der Definition nicht deklariert wird, müssen Sie die SourceColumn-Eigenschaft des SqlParameter-Objekts in Ihrem Code explizit angeben.
Der Standardwert der SourceVersion-Eigenschaft ist der aktuelle Wert im entsprechenden Feld des DataRow-Objekts, also der Wert, der in der Datenbank aktualisiert werden soll. Natürlich kann die SourceVersion-Eigenschaft auch auf den Originalwert im entsprechenden Feld des DataRow-Objekts verweisen, also auf den aus der Datenbank erhaltenen Anfangswert. In einem Datenbanktransaktionsverarbeitungssystem ist das Problem der Datensynchronisierung sehr wichtig. Erstellen wir eine gespeicherte Prozedur, die die Datensynchronisierung erkennen kann.
PROZEDUR ERSTELLEN dbo.up_UpdatePublisherName
(
@pub_id char(4),
@pub_name varchar(40),
@Original_pub_name varchar(40)
)
ALS
falls vorhanden (wählen Sie pub_id aus
von Verlagen
wobei (pub_id = @pub_id) UND (pub_name = @Original_pub_name))
Beginnen
UPDATE-Herausgeber SET pub_name = @pub_name
WO (pub_id = @pub_id)
Ende
ZURÜCKKEHREN
Als Nächstes rufen wir die gespeicherte Prozedur in der obigen Anwendung auf, um den Namen des Herausgebers zu aktualisieren. Verbessern Sie zunächst die Geschäftslogikklasse-Publishers-Klasse basierend auf der Originalanwendung:
1. Fügen Sie ein neues SqlCommand-Objekt hinzu, das als UpdateCommand-Eigenschaft des SqlDataAdapter-Objekts verwendet werden kann:
privater SqlCommand cmdUpdPubs;
2. Aktualisieren Sie die Konstruktorfunktion Publishers() dieser Klasse, um Folgendes hinzuzufügen:
// Ein weiteres SqlCommand-Objekt erstellen, das auf die gespeicherte Prozedur verweist, die den Herausgebernamen aktualisiert
cmdUpdPubs = new SqlCommand();
cmdUpdPubs.Connection = cnPubs;
cmdUpdPubs.CommandType = CommandType.StoredProcedure;
cmdUpdPubs.CommandText = "up_UpdatePublisherName";
//Erforderliche Parameter zum obigen SqlCommand-Objekt hinzufügen
cmdUpdPubs.Parameters.Add( "@pub_id", SqlDbType.Char, 4, "pub_id" );
cmdUpdPubs.Parameters.Add( "@pub_name", SqlDbType.VarChar, 40, "pub_name" );
SqlParameter updParam = neuer SqlParameter
("@Original_pub_name", SqlDbType.VarChar, 40, "pub_name" );
updParam.SourceVersion = DataRowVersion.Original;
cmdUpdPubs.Parameters.Add( updParam );
3． Geben Sie die UpdateCommand-Eigenschaft des SqlDataAdapter-Objekts als das oben definierte SqlCommand-Objekt an:
daPubs.UpdateCommand = cmdUpdPubs;
4. Methode UpdatePublisherName() hinzufügen:
public void UpdatePublisherName(DataSet dsChanges)
{
// Alle Änderungen aktualisieren
daPubs.Update(dsChanges);
}
Nachdem die Geschäftslogikklasse der Anwendung abgeschlossen ist, fügen Sie im Hauptformular eine Schaltfläche mit dem Namen „Datensatz aktualisieren“ hinzu und fügen Sie die Ereignisantwortfunktion der Schaltfläche wie folgt hinzu:
private void button1_Click(object sender, System.EventArgs e) { if ( ds.HasChanges() ) { pubs.UpdatePublisherName( ds.GetChanges() ); ds.Clear();
Bisher wurden die Geschäftslogikklasse und die Hauptformularklasse der Anwendung aktualisiert. Jetzt kann die Anwendung den relevanten Inhalt in der Datenbank entsprechend den Änderungen des Benutzers aktualisieren.
Sieben. Zusammenfassung:
Dieser Artikel führt Sie in die Grundkenntnisse gespeicherter Prozeduren ein und zeigt Ihnen, wie Sie SqlDataAdapter-Objekte, DataSet-Objekte usw. kombinieren, um datengesteuerte Anwendungen in .NET-Datenbankanwendungen zu erstellen. In diesem Artikel haben wir zwei Arten von gespeicherten Prozeduren verwendet: Eine ist eine einfache gespeicherte Prozedur ohne Parameter, die relativ einfach zu verwenden ist, und die andere ist eine gespeicherte Prozedur mit Parametern, und Sie müssen diese Art von gespeicherter Prozedur aufrufen zum SqlParameter-Objekt. Gleichzeitig ist es nicht schwer zu erkennen, dass die Kapselung der Geschäftslogik zur Datenaktualisierung in einer gespeicherten Prozedur eine gute Entwurfsmethode ist, die die Verwaltbarkeit, Skalierbarkeit und Datenbanksicherheit der Anwendung verbessern kann. Ebenso kann die Geschäftslogik zum Einfügen und Löschen von Daten in gespeicherten Prozeduren gekapselt und auf ähnliche Weise in Anwendungen verwendet werden. Abschließend hoffe ich, dass dieser Artikel für alle eine große Hilfe ist.