VBA
Lavorare con ADO

Osservazioni

Effettuare una connessione a un'origine dati

Il primo passo per accedere a un'origine dati tramite ADO è la creazione di un oggetto Connection ADO. Questo viene in genere eseguito utilizzando una stringa di connessione per specificare i parametri dell'origine dati, sebbene sia anche possibile aprire una connessione DSN passando il DSN, l'ID utente e la password al metodo .Open .

Si noti che non è richiesto un DSN per connettersi a un'origine dati tramite ADO - qualsiasi origine dati a cui è collegato un provider ODBC con la stringa di connessione appropriata. Mentre stringhe di connessione specifiche per diversi provider non rientrano nell'ambito di questo argomento, ConnectionStrings.com è un eccellente riferimento per trovare la stringa appropriata per il provider.

Const SomeDSN As String = "DSN=SomeDSN;Uid=UserName;Pwd=MyPassword;"

Public Sub Example()
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    If Not database Is Nothing Then
        '... Do work.
        database.Close          'Make sure to close all database connections.
    End If
End Sub

Public Function OpenDatabaseConnection(ConnString As String) As ADODB.Connection
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = New ADODB.Connection
    
    With database
        .ConnectionString = ConnString
        .ConnectionTimeout = 10             'Value is given in seconds.
        .Open
    End With
     
    OpenDatabaseConnection = database
     
    Exit Function
Handler:
    Debug.Print "Database connection failed. Check your connection string."
End Function

Si noti che la password del database è inclusa nella stringa di connessione nell'esempio precedente solo per motivi di chiarezza. Le migliori pratiche imporranno di non memorizzare le password del database nel codice. Questo può essere ottenuto prendendo la password tramite l'input dell'utente o usando l'autenticazione di Windows.

Recupero di record con una query

Le query possono essere eseguite in due modi, entrambi restituiscono un oggetto Recordset ADO che è una raccolta di righe restituite. Si noti che entrambi gli esempi di seguito utilizzano la funzione OpenDatabaseConnection dall'esempio Effettuare una connessione a un'origine dati a scopo di brevità. Ricordare che la sintassi del codice SQL passato all'origine dati è specifica del provider.

Il primo metodo consiste nel passare l'istruzione SQL direttamente all'oggetto Connection ed è il metodo più semplice per l'esecuzione di query semplici:

Public Sub DisplayDistinctItems()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim records As ADODB.Recordset
        Set records = database.Execute("SELECT DISTINCT Item FROM Table")
        'Loop through the returned Recordset.
        Do While Not records.EOF      'EOF is false when there are more records.
            'Individual fields are indexed either by name or 0 based ordinal.
            'Note that this is using the default .Fields member of the Recordset.
            Debug.Print records("Item")
            'Move to the next record.
            records.MoveNext
        Loop
    End If
CleanExit:
    If Not records Is Nothing Then records.Close
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

Il secondo metodo consiste nel creare un oggetto Command ADO per la query che si desidera eseguire. Ciò richiede un po 'più di codice, ma è necessario per utilizzare le query parametrizzate:

Public Sub DisplayDistinctItems()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim query As ADODB.Command
        Set query = New ADODB.Command
        'Build the command to pass to the data source.
        With query
            .ActiveConnection = database
            .CommandText = "SELECT DISTINCT Item FROM Table"
            .CommandType = adCmdText
        End With
        Dim records As ADODB.Recordset
        'Execute the command to retrieve the recordset.
        Set records = query.Execute()

        Do While Not records.EOF
            Debug.Print records("Item")
            records.MoveNext
        Loop
    End If
CleanExit:
    If Not records Is Nothing Then records.Close
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

Si noti che i comandi inviati all'origine dati sono vulnerabili all'iniezione SQL , intenzionale o non intenzionale. In generale, le query non dovrebbero essere create concatenando input utente di alcun tipo. Invece, dovrebbero essere parametrizzati (vedere Creazione di comandi parametrizzati ).

Esecuzione di funzioni non scalari

Le connessioni ADO possono essere utilizzate per eseguire praticamente qualsiasi funzione di database supportata dal provider tramite SQL. In questo caso non è sempre necessario utilizzare il Recordset restituito dalla funzione Execute , sebbene possa essere utile per ottenere assegnazioni di chiavi dopo le istruzioni INSERT con @@ Identity o comandi SQL simili. Si noti che nell'esempio seguente viene utilizzata la funzione OpenDatabaseConnection dall'esempio Effettuare una connessione a un'origine dati a scopo di brevità.

Public Sub UpdateTheFoos()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim update As ADODB.Command
        Set update = New ADODB.Command
        'Build the command to pass to the data source.
        With update
            .ActiveConnection = database
            .CommandText = "UPDATE Table SET Foo = 42 WHERE Bar IS NULL"
            .CommandType = adCmdText
            .Execute        'We don't need the return from the DB, so ignore it.
        End With
    End If
CleanExit:
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

Si noti che i comandi inviati all'origine dati sono vulnerabili all'iniezione SQL , intenzionale o non intenzionale. In generale, le istruzioni SQL non dovrebbero essere create concatenando input utente di alcun tipo. Invece, dovrebbero essere parametrizzati (vedere Creazione di comandi parametrizzati ).

Creazione di comandi parametrizzati

Ogni volta che SQL eseguito tramite una connessione ADO deve contenere input dell'utente, è consigliabile parametrizzarlo al fine di ridurre al minimo la possibilità di SQL injection. Questo metodo è anche più leggibile rispetto alle concatenazioni lunghe e facilita il codice più robusto e gestibile (ovvero utilizzando una funzione che restituisce un array di Parameter ).

Nella sintassi ODBC standard, vengono forniti i parametri ? "segnaposto" nel testo della query, quindi i parametri vengono aggiunti al Command nello stesso ordine in cui appaiono nella query.

Si noti che nell'esempio seguente viene utilizzata la funzione OpenDatabaseConnection dalla creazione di una connessione a un'origine dati per brevità.

Public Sub UpdateTheFoos()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim update As ADODB.Command
        Set update = New ADODB.Command
        'Build the command to pass to the data source.
        With update
            .ActiveConnection = database
            .CommandText = "UPDATE Table SET Foo = ? WHERE Bar = ?"
            .CommandType = adCmdText
            
            'Create the parameters.
            Dim fooValue As ADODB.Parameter
            Set fooValue = .CreateParameter("FooValue", adNumeric, adParamInput)
            fooValue.Value = 42
            
            Dim condition As ADODB.Parameter
            Set condition = .CreateParameter("Condition", adBSTR, adParamInput)
            condition.Value = "Bar"
            
            'Add the parameters to the Command
            .Parameters.Append fooValue
            .Parameters.Append condition
            .Execute
        End With
    End If
CleanExit:
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

Nota: l'esempio sopra mostra un'istruzione UPDATE parametrizzata, ma a qualsiasi istruzione SQL possono essere dati parametri.