[VB.NET] Operazioni Base Access

Descrizione :
Raccolta di suggerimenti e tecniche base con System.Data.OleDb e DB Access.

+ Articolo :

Nonostante Microsoft stessa abbia dichiarato che non ci sarà ( mai ) un supporto Entity Framework per i database Access, e le non poche scomodità ( MDAC, Jet, limite file a 2 GB, ecc... ), molti utenti VB.NET, nel momento in cui si rende necessario disporre di un DB file based locale, puntano ancora istintivamente su Access.
Spesso si tratta di utenti VBA o VB6 che arrivano addirittura a caricare il Rif. COM ADODB in .NET per poter disporre del tanto ( ingiustamente ) "amato" Recordset...

Ho deciso di scrivere questo Articolo Blog, sperando di dare anche il mio contributo, con il solito spirito "propositivo", che in questo caso vale un po' come un : "Se lo vuoi/devi fare, almeno fallo nel modo giusto...".

"Modo giusto" in questo caso significa principalmente :

1. NON usare ADODB in .NET : non ha senso usare COM in .NET, a meno che non si sia costretti a farlo, e questo non è il caso, perchè per Access c'è un signor Provider Dati : System.Data.OleDb.

2. NON costruire le Query concatenando dati e direttive Sql : OleDb supporta tutti i tipi Access, e anche di più attraverso l'uso dei Named Parameters. Nessun bisogno di perdere tempo a combattere con le date Access e i suoi "famosi" formati ( MM/dd/yyyy, yyyy/MM/dd, "cancelletti" #, ecc... ), o dover gestire i numerosi casi ambigui con le stringhe racchiuse tra apici, separatori decimali, di migliaia, ecc.

3. NON costruire le Stringhe di Connessione al file DB concatenando percorsi e proprietà : forse pochi sanno che, allo stesso modo di quanto accade per System.Data.SqlClient, esiste una comoda Classe OleDbConnectionStringBuilder.

4. NON usare Controlli tipo-Lista per rappresentare tabelle Dati : mi riferisco soprattutto all'abuso delle ListView. 9 utenti su 10 che ho visto perdersi nella gestione "avanzata" di una ListView-Dati... Non hanno fatto più ritorno...

In questo Articolo i controlli sono ReadOnly. Uso una TextBox Multiline e un DataGridView.
Ho scelto di non gestire per nulla l'interazione dell'utente con i Controlli destinati a visualizzare i risultati delle varie Query e azioni, perchè ho intenzione di parlare dell'uso del DataGridView in un altro Articolo a sè stante.

--> DataBase :

Il DB di test è un semplice file Access 2003 : "testDB.mdb".
Ho scelto questa versione perchè Office 2003 è sempre molto diffuso e l'esempio seguente può facilmente essere replicato sulle versioni successive ( .accdb ) senza problemi, aggiornando la Stringa di Connessione.

In Standard Security ciò significa in pratica questo :

'
'Standard Security

'Access 2003
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myAccess2003file.mdb;User Id=admin;Password=;
'Oppure anche :
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myAccess2003file.mdb;Persist Security Info=False;

'Access 2007
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myAccess2007file.accdb;Persist Security Info=False;

'Access 2010
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myAccess2010file.accdb;Persist Security Info=False;
'

Non è ovviamente scopo di questo Articolo coprire i vari casi con le differenti installazioni di Windows ( vedi ad esempio i possibili problemi con il Provider ACE.OLEDB.12 in S.O. 64 bit, ecc... ), i driver di Access da installare, ecc...
Come già accennato, questa Stringa di Connessione verrà invece qui costruita "pezzo per pezzo" grazie a :

        'OleDb.OleDbConnectionStringBuilder
        With oleDbCnStr
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .DataSource = percorsoLocale & nomeFileDB
            .PersistSecurityInfo = False
        End With
        oleDbCn.ConnectionString = oleDbCnStr.ToString

La Struttura della Tabella "T_Persone" è semplice, ma al tempo stesso contiene vari tipi di dato in modo da poter dimostrare come l'uso dei Parameters semplifica enormemente la costruzione delle Query :

id ( PK ) - Numerico
nome - Testo
cognome - Testo
datanascita - Data/ora
contatti - Numerico

Faccio notare che "id" NON è di tipo Contatore.
Nell'esempio ho inserito anche un semplice sistema per ridurre al minimo il rischio di errore di duplicazione della PK.

--> Applicazione :

L'Applicazione è costituita da 2 Classi : un Modulo "ModuloPublics" e una Form "FormMain", che è l'oggetto di avvio.

- Codice ModuloPublics :

Module ModuloPublics

    'Percorso Locale in cui è situato il DB Access
    Public percorsoLocale As String = Application.StartupPath & "\"
    'Percorso LAN in cui è situato il DB Access
    Public nomeMacchina As String = "nomeMacchinaLAN"
    Public nomeDirCondivisa As String = "nomeDirCondivisaLAN"
    Public percorsoLan As String = "\\" & nomeMacchina & "\" & nomeDirCondivisa & "\"
    'Nome file Access compresa estensione
    Public nomeFileDB As String = "testDB.mdb"

    Public oleDbCnStr As New OleDb.OleDbConnectionStringBuilder
    Public oleDbCn As New OleDb.OleDbConnection

    Public F As New Font(FontFamily.GenericMonospace, 12, FontStyle.Bold)

End Module

- FormMain :

I Controlli utilizzati sono, dall'alto a sinistra :

--> Button : btn_selectparampclan
--> Button : btn_selectparam
--> Button : btn_selectparamdgv

--> Button : btn_selectmaxid
--> Button : btn_selectstar
--> Button : btn_selectstardgv

--> Button : btn_insertparam
--> Button : btn_updateparam
--> Button : btn_deleteparam

--> TextBox : txt_risultati
--> DataGridView : DGV

- Codice FormMain :

Public Class FormMain

    Private maxId As Integer

    Private Sub setMaxId()

        Dim strSql As String = "SELECT MAX(id) FROM T_Persone"
        Using CMD As New OleDb.OleDbCommand(strSql, oleDbCn)
            oleDbCn.Open()
            maxId = CMD.ExecuteScalar
            oleDbCn.Close()
        End Using

    End Sub

    Private Sub FormMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'OleDb.OleDbConnectionStringBuilder
        With oleDbCnStr
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .DataSource = percorsoLocale & nomeFileDB
            .PersistSecurityInfo = False
        End With
        oleDbCn.ConnectionString = oleDbCnStr.ToString

        With txt_risultati
            .Font = F
            .Multiline = True
        End With

        With DGV
            .AllowUserToAddRows = False
            .ReadOnly = True
            .Font = F
        End With

        setMaxId()

    End Sub

    Private Sub btn_selectparampclan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_selectparampclan.Click

        'Modifica SOLO al .DataSource di oleDbCnStr
        oleDbCnStr.DataSource = percorsoLan & nomeFileDB

        Dim risultati As New System.Text.StringBuilder
        Dim strSql As String = "SELECT * FROM T_Persone WHERE datanascita>=@datanascita"

        Using CMD As New OleDb.OleDbCommand(strSql, oleDbCn)
            With CMD
                'Definizione / Assegnazione
                .Parameters.Add("@datanascita", OleDb.OleDbType.Date).Value = New DateTime(1967, 12, 25)
                '... altri Parametri
            End With

            oleDbCn.Open()
            Using RDR As OleDb.OleDbDataReader = CMD.ExecuteReader

                'Lettura Campi DataReader
                For i As Integer = 0 To RDR.FieldCount - 1
                    risultati.Append(RDR.GetName(i).PadRight(19, " "))
                Next
                risultati.AppendLine().AppendLine()
                'Lettura Campi Query
                While (RDR.Read())
                    For i As Integer = 0 To RDR.FieldCount - 1
                        risultati.Append(RDR(i).ToString.PadRight(19, " "))
                    Next
                    risultati.AppendLine()
                End While
            End Using
            oleDbCn.Close()
        End Using

        txt_risultati.Text = risultati.ToString

    End Sub

    Private Sub btn_selectparam_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_selectparam.Click

        Dim risultati As New System.Text.StringBuilder
        Dim strSql As String = "SELECT * FROM T_Persone WHERE datanascita>=@datanascita AND cognome LIKE @cognome"

        Using CMD As New OleDb.OleDbCommand(strSql, oleDbCn)
            With CMD
                'Definizione / Assegnazione
                .Parameters.Add("@datanascita", OleDb.OleDbType.Date).Value = New DateTime(1967, 12, 25)
                .Parameters.Add("@cognome", OleDb.OleDbType.VarChar).Value = "Ro" & "%"
                '... altri Parametri
            End With

            oleDbCn.Open()
            Using RDR As OleDb.OleDbDataReader = CMD.ExecuteReader

                'Lettura Campi DataReader
                For i As Integer = 0 To RDR.FieldCount - 1
                    risultati.Append(RDR.GetName(i).PadRight(19, " "))
                Next
                risultati.AppendLine().AppendLine()
                'Lettura Campi Query
                While (RDR.Read())
                    For i As Integer = 0 To RDR.FieldCount - 1
                        risultati.Append(RDR(i).ToString.PadRight(19, " "))
                    Next
                    risultati.AppendLine()
                End While
            End Using
            oleDbCn.Close()
        End Using

        txt_risultati.Text = risultati.ToString

    End Sub

    Private Sub btn_selectparamdgv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_selectparamdgv.Click

        'Reset DGV
        With DGV
            .Rows.Clear()
            .Columns.Clear()
        End With

        Dim dgvr As DataGridViewRow
        Dim strSql As String = "SELECT * FROM T_Persone WHERE datanascita>=@datanascita AND cognome LIKE @cognome"

        Using CMD As New OleDb.OleDbCommand(strSql, oleDbCn)
            With CMD
                'Definizione / Assegnazione
                .Parameters.Add("@datanascita", OleDb.OleDbType.Date).Value = New DateTime(1967, 12, 25)
                .Parameters.Add("@cognome", OleDb.OleDbType.VarChar).Value = "Ro" & "%"
                '... altri Parametri
            End With

            oleDbCn.Open()
            Using RDR As OleDb.OleDbDataReader = CMD.ExecuteReader
                'Lettura Campi DataReader
                For i As Integer = 0 To RDR.FieldCount - 1
                    DGV.Columns.Add(RDR.GetName(i).ToString, RDR.GetName(i).ToString)
                Next
                'Lettura Campi Query
                While (RDR.Read())
                    dgvr = New DataGridViewRow
                    dgvr.CreateCells(DGV)
                    For i As Integer = 0 To RDR.FieldCount - 1
                        dgvr.Cells(i).Value = RDR(i).ToString
                    Next
                    DGV.Rows.Add(dgvr)
                End While
            End Using
            oleDbCn.Close()
        End Using

    End Sub

    Private Sub btn_selectmaxid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_selectmaxid.Click

        setMaxId()
        MessageBox.Show(maxId)

    End Sub

    Private Sub btn_selectstar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_selectstar.Click

        Dim risultati As New System.Text.StringBuilder
        Dim strSql As String = "SELECT * FROM T_Persone"

        Using CMD As New OleDb.OleDbCommand(strSql, oleDbCn)
            oleDbCn.Open()
            Using RDR As OleDb.OleDbDataReader = CMD.ExecuteReader
                'Lettura Campi DataReader
                For i As Integer = 0 To RDR.FieldCount - 1
                    risultati.Append(RDR.GetName(i).PadRight(19, " "))
                Next
                risultati.AppendLine().AppendLine()
                'Lettura Campi Query
                While (RDR.Read())
                    For i As Integer = 0 To RDR.FieldCount - 1
                        risultati.Append(RDR(i).ToString.PadRight(19, " "))
                    Next
                    risultati.AppendLine()
                End While
            End Using
            oleDbCn.Close()
        End Using

        txt_risultati.Text = risultati.ToString

    End Sub

    Private Sub btn_selectstardgv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_selectstardgv.Click

        'Reset DGV
        With DGV
            .Rows.Clear()
            .Columns.Clear()
        End With

        Dim dgvr As DataGridViewRow
        Dim strSql As String = "SELECT * FROM T_Persone"

        Using CMD As New OleDb.OleDbCommand(strSql, oleDbCn)
            oleDbCn.Open()
            Using RDR As OleDb.OleDbDataReader = CMD.ExecuteReader
                'Lettura Campi DataReader
                For i As Integer = 0 To RDR.FieldCount - 1
                    DGV.Columns.Add(RDR.GetName(i).ToString, RDR.GetName(i).ToString)
                Next
                'Lettura Campi Query
                While (RDR.Read())
                    dgvr = New DataGridViewRow
                    dgvr.CreateCells(DGV)
                    For i As Integer = 0 To RDR.FieldCount - 1
                        dgvr.Cells(i).Value = RDR(i).ToString
                    Next
                    DGV.Rows.Add(dgvr)
                End While
            End Using
            oleDbCn.Close()
        End Using

    End Sub

    Private Sub btn_insertparam_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_insertparam.Click

        Dim strSql As String = "INSERT INTO T_Persone (id, nome, cognome, datanascita, contatti) " & _
                               "VALUES (@id, @nome, @cognome, @datanascita, @contatti)"

        Using CMD As New OleDb.OleDbCommand(strSql, oleDbCn)
            With CMD
                'Definizione / Assegnazione
                .Parameters.Add("@id", OleDb.OleDbType.Integer).Value = maxId + 1
                .Parameters.Add("@nome", OleDb.OleDbType.VarChar).Value = "insertNome"
                .Parameters.Add("@cognome", OleDb.OleDbType.VarChar).Value = "insertCognome"
                .Parameters.Add("@datanascita", OleDb.OleDbType.Date).Value = New DateTime(1970, 1, 31)
                .Parameters.Add("@contatti", OleDb.OleDbType.Integer).Value = 12

                oleDbCn.Open()
                .ExecuteNonQuery()
                oleDbCn.Close()
            End With
        End Using
        setMaxId()
        MessageBox.Show("INSERT OK")

    End Sub

    Private Sub btn_updateparam_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_updateparam.Click

        Dim strSql As String = "UPDATE T_Persone SET nome=@nome, cognome=@cognome, datanascita=@datanascita, contatti=@contatti " & _
                               "WHERE id=@id"

        Using CMD As New OleDb.OleDbCommand(strSql, oleDbCn)
            With CMD
                'Definizione / Assegnazione
                .Parameters.Add("@nome", OleDb.OleDbType.VarChar).Value = "updateNome"
                .Parameters.Add("@cognome", OleDb.OleDbType.VarChar).Value = "updateCognome"
                .Parameters.Add("@datanascita", OleDb.OleDbType.Date).Value = New DateTime(1968, 8, 8)
                .Parameters.Add("@contatti", OleDb.OleDbType.Integer).Value = 8
                .Parameters.Add("@id", OleDb.OleDbType.Integer).Value = maxId

                oleDbCn.Open()
                .ExecuteNonQuery()
                oleDbCn.Close()
            End With
        End Using
        MessageBox.Show("UPDATE OK")

    End Sub

    Private Sub btn_deleteparam_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_deleteparam.Click

        Dim strSql As String = "DELETE FROM T_Persone WHERE id=@id"

        Using CMD As New OleDb.OleDbCommand(strSql, oleDbCn)
            With CMD
                'Definizione / Assegnazione
                .Parameters.Add("@id", OleDb.OleDbType.Integer).Value = maxId

                oleDbCn.Open()
                .ExecuteNonQuery()
                oleDbCn.Close()
            End With
        End Using
        setMaxId()
        MessageBox.Show("DELETE OK")

    End Sub

End Class

NOTE :
In conclusione, dopo aver replicato correttamente e provato il codice, ci si può fare un'idea su molti aspetti della gestione di un DB su file Access :

- Uso di OleDbConnectionStringBuilder.
- UNA Connessione sola, che viene riutilizzata a livello di Progetto.
- Utilizzo di un DataReader per le operazioni di lettura dati.
- Utilizzo di Named Parameters per TUTTE le operazioni.
- Uso del corretto Metodo Command a seconda del caso, e in particolare :
-- .ExecuteReader() per letture ( SELECT ... ) che prevedono la restituzione di più records.
-- .ExecuteScalar() per letture ( SELECT MAX, SELECT COUNT, ... ) che prevedono con certezza la restituzione di UN solo valore.
-- .ExecuteNonQuery() per le 3 Query di Azione ( INSERT, UPDATE, DELETE ).

Faccio Infine notare che una certa prolissità del codice Form è dovuta al fatto che, come faccio spesso per evitare lunghe liste di controlli-proprietà da impostare a Design, le Proprietà importanti sono definite nel codice stesso. Se oltre a ciò si rende davvero elementare la visualizzazione dei risultati ( ad esempio eliminando la "txt_risultati" ) il codice veramente essenziale si riduce ulteriormente.

+ Fine Articolo.

---

Un Click su "Mi Piace" è il modo migliore per ringraziare l'autore di questo articolo.

---

Posted in: VB.NET