ADODB: Access-Wrapper für gespeicherte Prozeduren

Wer Daten zwischen einer Access-Anwendung und einem SQL Server austauscht, landet früher oder später bei gespeicherten Prozeduren. Sie kapseln die Datenbanklogik serverseitig, liefern bessere Performance als dynamisch zusammengebautes SQL und lassen sich sauber rechtebasiert absichern. Der Aufruf per ADODB erfordert allerdings jedes Mal eine ganze Reihe gleicher Schritte: Verbindung öffnen, Command-Objekt erzeugen, CommandType setzen, Parameter definieren, Werte zuweisen, Prozedur ausführen, Ergebnis auslesen. Schreibt man das für jede Prozedur neu, wächst der Code schnell zu unübersichtlichen Ausmaßen an. In diesem Artikel bauen wir ein kleines Wrapper-Modul, das diese Arbeit einmalig erledigt. Anschließend kannst Du jede gespeicherte Prozedur mit einer einzigen Zeile Code aufrufen – inklusive Parameterübergabe und Rückgabewert. Als praktisches Beispiel binden wir den Wrapper an ein vollständiges Mitarbeiter-Formular mit allen vier CRUD-Operationen an.

Beispieldatenbank

Die Beispiele dieses Artikels findest Du in der Beispieldatenbank ADODB_GespeicherteProzeduren.accdb. Die zugehörige SQL Server-Datenbank heißt Test_GespeicherteProzeduren und enthält die beiden Tabellen tblAbteilung und tblMitarbeiter. Das Skript zum Anlegen der Tabellen, der Testdaten und der gespeicherten Prozeduren findest Du im Modul mdlSQLServerDB als auskommentierten T-SQL-Code, den Du direkt in ein Abfragefenster des SQL Server Management Studio kopieren kannst.

Die vier gespeicherten Prozeduren

Für die Tabelle tblMitarbeiter stehen vier gespeicherte Prozeduren bereit, die sich an den vier klassischen CRUD-Operationen orientieren: SELECT, INSERT, UPDATE und DELETE. Schauen wir uns diese kurz der Reihe nach an, damit wir wissen, welche Parameter unser Wrapper später bedienen muss.

Die Prozedur spMitarbeiterSelectNachMitarbeiterID liefert entweder alle Mitarbeiter zurück oder nur den Mitarbeiter mit einer bestimmten ID, abhängig davon, ob der optionale Parameter @MitarbeiterID übergeben wird (siehe Listing 1).

CREATE PROCEDURE [dbo].[spMitarbeiterSelectNachMitarbeiterID]
    @MitarbeiterID INT = NULL
AS
SET NOCOUNT ON
SELECT  MitarbeiterID, AbteilungID, Vorname, Nachname, Email, Eintrittsdatum, Gehalt, ErstelltVon, ErstelltAm
FROM tblMitarbeiter
WHERE   (@MitarbeiterID IS NULL
         OR MitarbeiterID = @MitarbeiterID)
ORDER BY Nachname, Vorname

Listing 1: Gespeicherte Prozedur zum Auslesen der Mitarbeiter

Die Prozedur spMitarbeiterInsert legt einen neuen Mitarbeiter an und liefert die neu vergebene MitarbeiterID über den Output-Parameter @IDNEW zurück. Wir verwenden dafür die Funktion SCOPE_IDENTITY, die den zuletzt in der aktuellen Sitzung und im aktuellen Gültigkeitsbereich erzeugten Autowert liefert (siehe Listing 2).

CREATE PROCEDURE [dbo].[spMitarbeiterInsert]
    @AbteilungID    INT,
    @Vorname        NVARCHAR(100),
    @Nachname       NVARCHAR(100),
    @Email          NVARCHAR(255) = NULL,
    @Eintrittsdatum DATE          = NULL,
    @Gehalt         DECIMAL(10,2) = NULL,
    @IDNEW          INT          = -1 OUTPUT
AS
SET NOCOUNT ON
INSERT INTO tblMitarbeiter (AbteilungID,
    Vorname, Nachname, Email,
    Eintrittsdatum, Gehalt)
VALUES (@AbteilungID, @Vorname, @Nachname,
    @Email, @Eintrittsdatum, @Gehalt)
SET @IDNEW = SCOPE_IDENTITY()

Listing 2: Gespeicherte Prozedur zum Anlegen eines Mitarbeiters

Die Prozedur spMitarbeiterUpdate aktualisiert einen bestehenden Datensatz und liefert über @RowsAffected zurück, wie viele Datensätze betroffen waren (siehe Listing 3).

CREATE PROCEDURE [dbo].[spMitarbeiterUpdate]
    @MitarbeiterID  INT,
    @AbteilungID    INT,
    @Vorname        NVARCHAR(100),
    @Nachname       NVARCHAR(100),
    @Email          NVARCHAR(255)   = NULL,
    @Eintrittsdatum DATE            = NULL,
    @Gehalt         DECIMAL(10,2)   = NULL,
    @RowsAffected   INT             = 0 OUTPUT
AS
SET NOCOUNT ON
Update tblMitarbeiter
SET    AbteilungID    = @AbteilungID,
       Vorname        = @Vorname,
       Nachname       = @Nachname,
       Email          = @Email,
       Eintrittsdatum = @Eintrittsdatum,
       Gehalt         = @Gehalt
WHERE  MitarbeiterID  = @MitarbeiterID
SET @RowsAffected = @@ROWCOUNT

Listing 3: Gespeicherte Prozedur zum Aktualsiieren eines Mitarbeiters

Die Prozedur spMitarbeiterDelete schließlich löscht einen Datensatz und liefert ebenfalls @RowsAffected zurück. Den Wert für @@ROWCOUNT greifen wir in beiden Fällen direkt im Anschluss an die UPDATE– beziehungsweise DELETE-Anweisung ab, bevor eine andere Anweisung diesen Wert überschreibt (siehe Listing 4).

CREATE PROCEDURE [dbo].[spMitarbeiterDelete]
    @MitarbeiterID  INT,
    @RowsAffected   INT = 0 OUTPUT
AS
SET NOCOUNT ON
DELETE FROM tblMitarbeiter
WHERE MitarbeiterID = @MitarbeiterID
SET @RowsAffected = @@ROWCOUNT

Listing 4: Gespeicherte Prozedur zum Löschen eines Mitarbeiters

Warum ein Wrapper?

Der direkte Aufruf einer gespeicherten Prozedur per ADODB sieht üblicherweise so aus: Du erzeugst ein Connection-Objekt, öffnest es mit einer Verbindungszeichenfolge, erzeugst ein Command-Objekt, weist diesem die Verbindung zu, setzt den Namen der Prozedur als CommandText und den CommandType auf adCmdStoredProc. Danach legst Du per CreateParameter jeden einzelnen Parameter an, weist ihm einen Wert zu und hängst ihn an die Parameters-Auflistung des Command-Objekts an. Erst dann kannst Du Execute aufrufen.

Wie das im Einzelnen aussieht, haben wir bereits im Artikel ADODB: Gespeicherte Prozeduren ausführen(www.vbentwickler.de/ 503) beschrieben.

Macht man das für vier Prozeduren, vervierfacht sich dieser Code. Macht man es für 40 Prozeduren, hat man sehr viel Schreibarbeit und sehr viele Stellen, an denen Tippfehler passieren können. Ein Wrapper-Modul erledigt diese immer gleiche Arbeit genau einmal und stellt den Aufruf als kurze Funktion zur Verfügung. Der Aufruf einer Prozedur sieht damit so aus:

Set rst = ADODB_GetRecordset( _
    "spMitarbeiterSelectNachMitarbeiterID", 5)

Eine Zeile – und Du erhältst das Recordset für den Mitarbeiter mit der ID 5. Das ist das Ziel.

Die Verbindungsfunktion

Den ganzen Code unseres Wrappers packen wir in das Modul mdlADODB_Parameter. Als Erstes definieren wir oben im Modul einige Konstanten mit den Verbindungsdaten. Diese legen wir als Private Const an, damit sie nur innerhalb des Moduls sichtbar sind. In einer produktiven Anwendung würdest Du diese Werte vermutlich aus einer Konfigurationstabelle oder aus der Registry lesen, aber für unser Beispiel reicht die Lösung als Konstanten.

Option Compare Database
Option Explicit
Private Const strServer As String = "Dein Server"
Private Const strDatabase As String = "Deine Datenbank"
Private Const blnUseSSPI As Boolean = True
Private Const strUsr As String = ""
Private Const strPwd As String = ""

Die Funktion ADODB_GetConnection baut aus diesen Konstanten eine Verbindungszeichenfolge zusammen und öffnet eine neue ADODB.Connection (siehe Listing 5).

Public Function ADODB_GetConnection() As ADODB.Connection
    Dim strConnection As String
    Dim cnn As ADODB.Connection
    On Error GoTo ErrHandler
    Set cnn = New ADODB.Connection
    If blnUseSSPI Then
        strConnection = "Provider=MSOLEDBSQL;Data Source=" & strServer & ";Initial Catalog=" _
            & strDatabase & ";Integrated Security=SSPI;"
    Else
        strConnection = "Provider=MSOLEDBSQL;Data Source=" & strServer & ";Initial Catalog=" _
            & strDatabase & ";User ID=" & strUsr & ";Password=" & strPwd & ";"
    End If
    cnn.Open strConnection
    Set ADODB_GetConnection = cnn
    Exit Function
ErrHandler:
    MsgBox "Fehler in ADODB_Connection:" & vbCrLf & Err.Description, vbCritical, "DB Connection"
    Set ADODB_GetConnection = Nothing
End Function

Listing 5: Verbindung zum SQL Server herstellen

Über die Konstante blnUseSSPI steuern wir, ob die Verbindung per Windows-Authentifizierung (Integrated Security=SSPI) oder per SQL Server-Authentifizierung mit Benutzername und Kennwort aufgebaut wird:

Als Provider verwenden wir MSOLEDBSQL, den aktuellen OLE DB-Treiber für den SQL Server. Im Fehlerfall zeigen wir eine Meldung an und liefern Nothing zurück, damit der aufrufende Code prüfen kann, ob die Verbindung erfolgreich war.

Die Helferfunktion ADODB_BuildCommand

Der Kern des Wrappers steckt in der Funktion ADODB_BuildCommand (siehe Listing 6). Sie ist als Private deklariert, weil sie nur intern von den öffentlichen Funktionen aufgerufen wird. Ihre Aufgabe ist es, ein Command-Objekt zu erzeugen, ihm die Verbindung und den Prozedurnamen zuzuweisen und anschließend die Werte aus einem Parameter-Array automatisch in die Parameter der gespeicherten Prozedur zu kopieren.

Private Function ADODB_BuildCommand(ByVal strProcedure As String, ByRef varParameters As Variant) As ADODB.Command
    Dim cmd As ADODB.Command
    Dim intParameter As Integer
    Dim lngCurrentParameter As Long
    Dim lngMaxIndex As Long
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = ADODB_GetConnection
    cmd.CommandText = strProcedure
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Refresh
    On Error Resume Next
    lngMaxIndex = UBound(varParameters)
    If Err.Number <> 0 Then
        lngMaxIndex = -1
    End If
    On Error GoTo 0
    lngCurrentParameter = 0
    For intParameter = 1 To cmd.Parameters.Count - 1
        If cmd.Parameters(intParameter).Direction = adParamInput _
                Or cmd.Parameters(intParameter).Direction = adParamInputOutput Then
            If lngCurrentParameter <= lngMaxIndex Then
                If IsNull(varParameters(lngCurrentParameter)) Then
                    cmd.Parameters(intParameter).Value = Null
                Else
                    cmd.Parameters(intParameter).Value = varParameters(lngCurrentParameter)
                End If
                lngCurrentParameter = lngCurrentParameter + 1
            End If
        End If
    Next intParameter
    Set ADODB_BuildCommand = cmd
End Function

Listing 6: Command-Objekt bauen und Parameter füllen

Damit das funktioniert, müssen wir die Parameter-Definitionen der Prozedur kennen. Statt sie mühsam per Hand anzulegen, lassen wir ADODB diese Arbeit übernehmen. Die Methode Parameters.Refresh fragt die Parameter-Informationen direkt vom SQL Server ab. Danach sind alle Parameter mit dem richtigen Datentyp, der richtigen Länge und der richtigen Richtung (Input, Output oder beides) bereits angelegt – wir müssen nur noch die Werte zuweisen.

Schauen wir uns die Logik der Funktion im Detail an. Nach dem Anlegen des Command-Objekts weisen wir die Verbindung zu, setzen den Prozedurnamen und legen CommandType auf adCmdStoredProc fest. Der Aufruf von Parameters.Refresh fragt die Parameterdefinitionen beim SQL Server ab und füllt die Parameters-Auflistung des Command-Objekts.

Danach ermitteln wir mit UBound den höchsten Index des Parameter-Arrays. Das kann fehlschlagen, wenn das Array leer ist – deshalb kapseln wir die Zuweisung mit On Error Resume Next und setzen lngMaxIndex im Fehlerfall auf -1. So stellen wir sicher, dass die nachfolgende Schleife auch bei leeren Parameter-Arrays sauber durchläuft.

In der For-Schleife beginnen wir mit Index 1, nicht mit 0. Das liegt daran, dass ADODB beim Refresh an Position 0 immer einen impliziten Parameter mit dem Namen @RETURN_VALUE für den Rückgabewert der Prozedur anlegt. Den brauchen wir hier nicht und überspringen ihn daher.