So vereinfachen Sie den Aufruf von Stored Procedures in ADO

Veröffentlicht: 15. Dez 2001 | Aktualisiert: 19. Jun 2004

Von Ken Spencer

Über ein leicht modifiziertes Add-In als Assistent, das aus einer Microsoft-Beispielanwendung stammt, können Stored Procedures wesentlich schneller in Visual Basic-Code integriert werden.

Auf dieser Seite

Mit einem eigenen Add-in
Änderung des Programmbeispiels
In den Tiefen des Codes
Noch mehr Code
Die Arbeit mit dem neuen Zusatzmodul
Fazit

Diesen Artikel können Sie hier lesen dank freundlicher Unterstützung der Zeitschrift:

Mit einem eigenen Add-in

Diesen Monat möchte ich einen Aspekt der Programmentwicklung herausgreifen, der sich sehr schön zur Produktivitätssteigerung eignet. Häufig sieht man sich als Entwickler doch in der Situation, immer wieder dieselben Arbeiten ausführen zu müssen. Vor kurzem habe ich eine Reihe von Entwicklern gefragt, ob sie auch immer wieder dieselben ADO-Befehlssequenzen schreiben, mit denen irgendwelche Informationen aus der Datenbank ausgelesen werden sollen. Die meisten Befragten wussten sofort, was ich mit meiner Frage meinte.

Zur Vereinfachung der Entwicklung von Anwendungen, die mit ADO arbeiten und gespeicherte Prozeduren (stored procedures) benutzen, hat Microsoft das "ADO Stored Procedure Add-In for Visual Basic" entwickelt. Es generiert den ADO-Code, der für den Aufruf der von Ihnen ausgewählten gespeicherten Prozedur erforderlich ist. Das ist eine willkommene Hilfe, denn der Aufruf von gespeicherten Prozeduren via ADO ist doch etwas aufwendiger. Normalerweise muss man ein Command-Objekt einrichten und die entsprechende Parameters-Sammlung zusammenstellen.

Der genannte Assistent kann zwar sehr schön den ADO-Code generieren, aber wie sieht die Sache aus, wenn man für die Datenbankzugriffe eigene Objekte entwickelt hat?

Das Fitch & Mather-Beispielprogramm FMStocks eignet sich als gutes Beispiel (siehe http://www.fmstocks.com). Es hat eine ADOHelper-Klasse mit den verschiedenen Run-Funktionen, in denen die eigentliche Arbeit erledigt wird. Sie brauchen den fertigen Code nur noch aufzurufen. Aber selbst für den relativ einfachen Einsatz der Run-Funktionen müssen Sie immer noch die verschiedenen Parameter der gespeicherten Prozeduren kennen, samt Typ und Größe. Also dachte ich mir, es müsse doch eine einfachere Lösung für die Arbeit mit Datenbankobjekten geben.

Also entschloss ich mich, ein neues Modul (oder "Add-In") zu entwickeln, das den Aufruf der Run-Funktionen nach FMStocks-Art automatisiert. Ausgangspunkt sollte der ADO Stored Procedure-Assistent sein. Dieses Modul kann die Entwickler auf zweierlei Arten unterstützen. Erstens ermöglicht es die Auswahl der aufzurufenden gespeicherten Prozedur. Zweitens ermöglicht es die Auswahl einer Methode vom Datenbankobjekt und es bietet Ihnen die Möglichkeit, verschiedene Argumente für den Methodenaufruf festzulegen. Anschließend kann das Modul den entsprechenden Code in den Editor von Visual Basic einfügen, und zwar an der aktuellen Cursorposition. So können Sie praktisch in Sekunden die richtige Methode und gespeicherte Prozedur aufrufen.

Änderung des Programmbeispiels

Ich möchte nun die einzelnen Schritte beschreiben, in denen ich die Vorlage geändert habe. Nach dem Herunterladen des Codes von der Fitch & Mather-Site kopierte ich alle Dateien in einen neuen Ordner. Anschließend änderte ich den Namen der Projektdatei in SprocWizardMP.vbp ab. Dann öffnete ich das Projekt in Visual Basic und änderte den Projektnamen auf SprocWizardMP und die Projektbeschreibung auf "Stored Procedure MP Generator Wizard".

Nach dem Öffnen von frmAddin begann ich mit der Änderung der Anwenderschnittstelle. Bild B1 zeigt das fertige Formular. Das Textfeld in der oberen linken Ecke zeigt den Text GetDSN(). Damit ist die Funktion gemeint, die ich zur Ermittlung eines DSN-Strings benutze. Ihr Ergebniswert wird als letztes Argument an jede der Run-Methoden übergeben, so dass sich in jedem Aufruf der korrekte unverwechselbare DSN verwenden lässt. Die GetDSN-Funktion muss in dem Modul oder in der Klasse definiert werden, in welcher der Aufruf der Run-Methode erfolgt. Sie können selbst einen DSN-String vorgeben oder den aktuellen String mit der Funktion GetDSN ermitteln.

B1 Das Zusatzmodul Ado Stored Procedure (ein "Add-in").

Das Listenfeld auf der rechten Seite des Formulars zeigt die Namen der Methoden, die das Datenbankobjekt anzubieten hat. Sie werden als statische Liste eingetragen. Ändern Sie diese Liste bei Bedarf für Ihre eigenen Zwecke ab, damit sie alle Funktionen anzeigt, die von Ihren Objekten angeboten werden.

Das Kombinationsfeld cboSproc erhält die Namen der gespeicherten Prozeduren dagegen dynamisch, und zwar von der Prozedur LoadSprocList (dazu gleich mehr). Im Kombinationsfeld cboDSNForSproc sind die DSNs zu finden, die zur Verbindungsaufnahme mit der Datenbank benutzt werden, in welcher die in cboSproc angegebenen gespeicherten Prozeduren zu finden sind. Dieses Steuerelement erhält seine Werte von der Prozedur LoadDSNList. Die DSNs liegen in der Datei DataAccess.txt unter c:\database. Der Dateiinhalt lautet:

Accounts DSN=Accounts 
Pubs DSN=Pubs 
SessionData  DSN=SessionData

Der erste Eintrag in jeder Zeile ist der DSN-Name, der im Kombinationsfeld angezeigt wird. Die zweite Angabe beginnt mit DSN= und nennt den eigentlichen DSN. Sie können einen DSN-Namen benutzen, wie ihn dieses Beispiel zeigt, oder einen vollständigen Verbindungsstring angeben.

Die erste Reihe mit den Schaltern wirkt sich auf den Code aus, der von dem Modul generiert wird. Je nach Bedarf haken Sie bei der Arbeit einfach die Punkte ab, die Sie interessieren. Manche gespeicherten Prozeduren liefern zum Beispiel Recordsets (Tabellen). In diesem Fall sollten Sie das Feld Returns Rows abhaken. Für gespeicherte Prozeduren, die nur einen Einzelwert liefern und keine Datensätze, sollte das Feld Include Return Parameter markiert werden und nicht Returns Rows.

Die Markierung des Felds Include Variable Declarations veranlasst das Modul, Dim-Anweisungen für den Recordset und die Ergebnisvariable zu generieren. Normalerweise erhält die Recordset-Variable den Namen rs. Er erscheint im ersten Textfeld auf der letzten Zeile. Das zweite Textfeld zeigt den Variablennamen für das Datenbankobjekt. Sie können diese Werte bei Bedarf ändern.

L1 So werden die Code-Details zusammengestellt

strRecordset = txtRecordset.Text 
strCode = "" 
If chkIncludeDeclare.Value Then 
strCode = strCode & "Dim " & strRecordset & _ 
      " as ADODB.Recordset" & vbCrLf 
      strCode = strCode & "Dim vReturn as variant" & vbCrLf 
End If 
If chkReturnsRows.Value Then 
strCode = strCode & "Set " & strRecordset & " = " & _ 
        txtDBObject.Text & "." & lstConnType.Text & "(" 
Else 
      strCode = strCode & "vReturn = " & txtDBObject.Text & "." _ 
        & lstConnType.Text & "(" 
End If

Die Zeile mit den Schaltflächen am unteren Rand des Dialogs ist für die Aktionen des Moduls zuständig. Die Apply-Schaltfläche generiert den Code an der aktuellen Cursorposition. Die zweite Schaltfläche namens Preview generiert den Code und bringt ihn in einem Vorschau-Formular unter. Mit Load Sprocs werden die Werte für das DSN-Kombinationsfeld geladen, während die Schaltfläche Load DSNs das DSN-Feld bestückt. Die Namen der gespeicherten Prozeduren werden bei der Auswahl eines DSNs automatisch geladen. Die DSNs werden automatisch geladen, wenn das erste Formular des Moduls geladen wird. Diese beiden Schaltflächen kommen dann zur Geltung, wenn entweder die DSNs oder die gespeicherten Prozeduren aktualisiert werden sollen, ohne das Modul neu laden zu müssen. Falls es zum Beispiel in der Datenbank neue gespeicherte Prozeduren gibt, können Sie deren Namen mit einem Klick auf die Schaltfläche Load Sprocs laden.

L2 Bearbeitung der Parameters-Sammlung

For Each param In params 
    If Not chkIncludeReturnParameter.Value = 1 Then 
        If param.Name = "@RETURN_VALUE" Then 
            GoTo forEnd 
        End If 
    End If 
    If Not bParams Then 
        strCode = strCode & "Array(" 
        strCode = strCode & "mp(" 
        bParams = True 
    Else 
        strCode = strCode & ", _ " & vbCrLf & "  mp(" 
    End If 
    strCode = strCode & Chr(34) & param.Name & Chr(34) & "," 
    Select Case param.Type 
        Case 3 
            strCode = strCode & "adInteger" 
        Case 129 
            strCode = strCode & "adChar" 
        Case 200 
            strCode = strCode & "adVarChar" 
        Case 128 
            strCode = strCode & "adBinary" 
        Case 11 
            strCode = strCode & "adBoolean" 
        Case Else 
            strCode = strCode & param.Type 
    End Select 
    strCode = strCode & ", " & param.Size 
    If Left$(param.Name, 1) = "@" Then 
        sParamName = Right(param.Name, Len(param.Name) - 1) 
    Else 
        sParamName = param.Name 
    End If 
    strCode = strCode & ", " & sParamName & ")" 
forEnd: 
    Next

In den Tiefen des Codes

Der Code in der Funktion GenADOCode erledigt fast die gesamte Arbeit, zu der dieses kleine Zusatzmodul fähig ist. Sie stellt den Text zusammen, der in den Editor von Visual Basic eingefügt werden soll, und gibt ihn als Ergebniswert an den Aufrufer zurück. Der Text wird in drei Variablen abgelegt. StrHeader enthält die Kopfzeilen für den Codeblock, strCode nimmt den eigentlichen Code auf und strFooter ist für die Fußzeilen zuständig.

Im ersten Teil des Codes werden die Variablen für die Prozedur dimensioniert. Anschließend definiert der Code die Strings für Kopf und Fuß:

strHeader = "' Sproc Wizard Code Generation START -" & _ 
    vbCrLf & vbCrLf 
strFooter = vbCrLf & "' Sproc Wizard Code Generation END -" _ 
    & vbCrLf

Beachten Sie bitte den Einsatz von vbCrLf. Dadurch werden beim Einfügen des Textes auch die Zeichen für Wagenrücklauf und Zeilenvorschub (CR/LF) in den Text eingebaut. Sie dienen zur Formatierungund beginnen jeweils eine neue Zeile.

Die nächsten Zeilen definieren die Properties für die ADO-Verbindung und stellen die Verbindung zu der Datenbank her, in der die gespeicherten Prozeduren liegen:

' Properties für Verbindung festlegen und Verbindung herstellen 
cn.CursorLocation = adUseClient 
cn.ConnectionString = gStrSprocDSN 
cn.Open

Sobald die Verbindung steht, richtet der Code ein Command-Objekt ein, um die gespeicherten Prozeduren aus dem Kombinationsfeld cboSproc benutzen zu können. Dann wird die Refresh-Methode des Parameters-Objekts aufgerufen. Sie ermittelt die Parameter der gespeicherten Prozeduren:

Set cmd.ActiveConnection = cn 
cmd.CommandText = cboSproc.Text 
cmd.CommandType = adCmdStoredProc 
cmd.Parameters.Refresh

Die nächste Codezeile setzt die Variable params als eine Referenz auf die Parameters-Sammlung. Diese Variable dient später zur Ermittlung der Parameter-Details:

Set params = cmd.Parameters

Der Code in Listing L1 beginnt mit der Zusammenstellung des Codes und legt das Ergebnis in der Variablen strCode ab. Nun sollte Ihnen der in strCode gespeicherte Code auch schon bekannt vorkommen. Es ist dieselbe Art von Code, die man für jedes Datenbankobjekt einsetzen würde. Die folgende Zeile hängt den Namen der gespeicherten Prozedur an strCode an:

strCode = strCode & Chr(34) & cboSproc.Text & Chr(34) & ","

Die Variable bParams wird auf False gesetzt und dient in einer For-Schleife dazu, die Ausgabe der Array-Funktion nach dem ersten Parameter anzuhalten.

bParams = False

Der Code in Listing L2 zeigt die For-Schleife, in der die Parameters-Sammlung bearbeitet wird. Jeder Parameter wird in dieser Schleife untersucht, indem bestimmte Properties überprüft werden, zum Beispiel Type und Name. Dann wird für jeden Parameter eine mp-Anweisung an strCode angehängt. In der Anweisung Select Case param.Type dürften noch einige Änderungen erforderlich werden, sofern Sie zusätzliche Datentypen implementieren möchten.

Nach dem Ende der For-Schleife schließt der Code die Zusammenstellung von strCode ab, indem er die abschließende runde Klammer einbaut, sofern irgendwelche Parameter ausgegeben wurden. Bei Bedarf fügt er die DSN-Anweisung hinzu und schließt dann die Verbindung. Damit wäre strCode abgeschlossen und der Code setzt den Rückgabewert auf strCode.

If bParams Then 
    strCode = strCode & ")" 
End If 
If chkIncludeDSN.Value Then 
    strCode = strCode & ", _" & vbCrLf & "  " & _ 
      txtConnectionString.Text & ")" & vbCrLf 
Else 
    strCode = strCode & ")" & vbCrLf 
End If 
cn.Close 
GenADOCode = strHeader & strCode & strFooter 

Die Funktion mp (Make Parameter), die bei jedem Parameter benutzt wird, stammt aus FMStocks und dient zur Konstruktion eines Parameter-Arrays für jeden Parameter. Die Funktion sieht so aus:

Public Function mp(ByVal PName As String, _     
    ByVal PType As ADODB.DataTypeEnum, _ 
    ByVal PSize As Long, ByVal PValue As Variant) 
    mp = Array(PName, PType, PSize, PValue) 
End Function

Diese Funktion muss zusammen mit der DSN-Funktion in jedes Projekt eingebaut werden, das die Ergebnisse dieses Zusatzmoduls verwendet. Am einfachsten dürfte es wohl sein, die Datei Helpers.bas aus FMStocks 2000 zu verwenden (siehe https://msdn.microsoft.com/library/techart/fm2kintro.htm).

B2 Einbau von Verweisen.

Noch mehr Code

Das Zusatzmodul fügt sich ins Objektmodell von Visual Basic 6.0 ein, ebenso in die Add-in-Verbindung. Dazu legt es die entsprechenden Referenzen (Verweise) an (Bild B2) und deklariert die beiden folgenden öffentlichen Variablen:

Public VBInstance As VBIDE.VBE 
Public Connect As Connect

Diese beiden Deklarationen liegen im Allgemein-Bereich von frmAddIn. Dadurch werden diese Referenzen im gesamten Zusatzmodul verfügbar. Bild B1 zeigt die Verweise, die in diesem Projekt benutzt werden.

Das Zusatzmodul arbeitet nur an einer Stelle mit der IDE von Visual Basic zusammen, nämlich im Codefenster. Nachdem Sie eine Methode und eine gespeicherte Prozedur ausgewählt haben, führt ein Klick auf die Apply-Schaltfläche dazu, dass der generierte Code an der aktuellen Cursorposition ins Codefenster eingefügt wird. Wenn Sie Apply anklicken, wird die Ereignisprozedur cmdApply_Click aufgerufen. Die ersten vier Zeilen dieser Prozedur definieren die benutzen Variablen:

Dim lngStartLine As Long 
Dim lngStartCol As Long 
Dim lngEndLine As Long 
Dim lngEndCol As Long

Die nächste Zeile ruft die Methode GetSelection auf und besorgt auf diese Weise Informationen darüber, ob und welcher Teil des Codes im Codefenster markiert ist. Das Zusatzmodul braucht die aktuelle Zeile, damit es den Code an der richtigen Stelle einfügen kann. Die aktuelle Zeile geht aus der Variablen IngStartLine hervor:

VBInstance.ActiveCodePane.CodeModule.CodePane.GetSelection _ 
    lngStartLine, lngStartCol, lngEndLine, lngEndCol

Die folgende Zeile generiert den Code und fügt ihn ins Codefenster ein:

VBInstance.ActiveCodePane.CodeModul.InsertLines _ 
    lngStartLine, GenADOCode()

GenADOCode taucht hier als letztes Argument der Methode InsertLines auf.

Falls der Entwickler die Preview-Schaltfläche statt Apply anklickt, wird der generierte Code von der Ereignisprozedur cmdPreview_Click ans Preview-Formular geschickt.

Die Arbeit mit dem neuen Zusatzmodul

Der Umgang mit dem Zusatzmodul ist ziemlich einfach. Wählen Sie es im Add-In-Menü und suchen Sie den DSN aus, der die richtige Datenbank bezeichnet. Wählen Sie die gewünschte gespeicherte Prozedur, legen Sie die erforderlichen Optionen fest und klicken Sie dann auf Apply. Der Code wird direkt an der aktuellen Cursorposition in den Editor eingefügt.

Die folgenden Zeilen stammen aus einer Testklasse, an der ich das Modul ausprobiert habe:

Dim oDB As BestISVDBUtil.DBHelper 
Set oDB = CreateObject("BestISVDBUtil.DBHelper") 
' Sproc Wizard Code Generation START --------- 
Dim rs As ADODB.Recordset 
Dim vReturn As Variant 
Set rs = oDB.RunSPReturnRS("GetUserByLastName", _ 
    Array(mp("@lastnamemask", adVarChar, 30, lastnamemask)), _  
      GetDSN()) 
' Sproc Wizard Code Generation END --------- 
Set tester2 = rs

Die ersten beiden Zeilen und die letzte Zeile stammen von mir. Die anderen stammen vom Codegenerator, einschließlich der beiden Kommentarzeilen (Außerdem muss sich natürlich die Formatierung den Regeln anpassen, die hier für die gedruckte Seite gelten.).

Interessant sind die Aufrufe von mp und GetDSN in der Zeile, die mit Set rs beginnt. Wie schon erwähnt, müssen Sie auch diese beiden Funktionen in Ihren Code einbauen.

Fazit

Die Erstellung von kleinen Zusatzmodulen oder "Add-Ins" ist eine Möglichkeit, die eigene Produktivität als Entwickler zu verbessern. Die entsprechende Automatisierung häufig anfallender Arbeitsschritte verkürzt nicht nur die reine Entwicklungszeit, weil der Codegenerator das Programm wieder ein paar Zeilen voran bringt, sondern verringert auch die Zahl der Fehlerquellen. Vermutlich werden Sie auch etwas weniger Zeit im Debugger verbringen müssen.

Es bieten sich noch viele andere Arten von Zusatzmodulen an, die auf ihre Entwicklung harren. So könnten Sie zum Beispiel ein Modul entwickeln, dass es Ihnen erlaubt, im Web herumzustöbern, während Sie an der Visual Basic-Anwendung arbeiten. Sie könnte auch für die anderen Geschäftsobjekte in Ihrer Sammlung entsprechende Codegeneratoren entwickeln. Dieses Thema wird uns wohl auch in Zukunft hin und wieder beschäftigen.