Totem's Lair: Guida al Vb.Net, Approccio ai database

C15. Approccio ai database

I database sono potenti piattaforme di supporto per immagazzinare gran quantità di dati ordinati. In questo capitolo mi occuperò in particolare dell'uso di Database Relazionali di Microsoft Access, ma gli stessi meccanismi possono essere applicati anche a diversi motori di gestione, quali Microsoft SQL od Oracle.

Introduzione ai database relazionali

Il modello relazionale non è stato il primo in assoluto ad essere usato per la gestione dei database, ma è stato introdotto più tardi, negli anni '70, grazie alle idee di E. F. Codd.. Ad oggi, è il modello più diffuso e utilizzato per la sua semplicità.
La base del modello relazionale è costituita, appunto, dalla relazione, una tabella a due dimensioni costituita da righe (o record o tuple) e colonne (o attributi). Teoricamente parlando, la relazione è il tipo astratto di tabella, mentre ogni entità definita dalle tuple al suo interno è un'istanza di relazione. Si può fare un facile parallelismo con la programmazione ad oggetti:

Database                    Vb.Net
Relazione              ->   Classe
Tupla                  ->   Oggetto
Attributo              ->   Proprietà dell'oggetto
Istanza di relazione   ->   Istanza di classe (= Oggetto)

Per definire una relazione, basta fornirne il nome e gli attributi. Ad esempio, riprendendo la ormai famosa classe Person già discussa in varie occasioni, per definire una relazione di Person si useranno questi dati:

Person (FirstName, LastName, Birthday)

In molti casi si è visto come due oggetti dello stesso tipo siano differenti, e identificati in maniera diversa: persino due istanza identiche dal punto di vista formale non possono dirsi uguali, ossia la stessa istanza. Anche nei database relazionali, si deve aggirare questo problema, e lo si fa con l'introduzione di una chiave primaria per ogni tupla, che si potrebbe associare al Guid degli oggetti in ambiente .Net. Essa permette di identificare univocamente una e una sola entità all'interno della banca dati, senza possibilità di errore: praticamente sempre è un contatore che assume valori interi positivi. L'insieme di tutti gli attributi di un'istanza di relazione viene detto chiave candidata, poiché, siccome sono rari i casi in cui si trovino due record identici che corrispondono a persone diverse, possono costituire nella maggior parte dei casi un metodo per trovare una specifica riga tra le altre.
Ogni attributo della relazione, inoltre, è contraddistinto da un nome e un dominio. Quest'ultimo si identifica facilmente con il tipo da usare e può trattarsi di un tipo base (numeri, valori booleani, date, eccetera...), un enumeratore o un'altra relazione. Dato che non è possibile stipare un'intera relazione in una cella e, appunto, i valori degli attributi devono essere atomici, si deve trovare un altro modo per collegare i due elementi. In questi casi si ricorre all'uso di una chiave esterna, ossia la combinazione di attributi della nuova relazione con la chiave primaria della vecchia. Così si avrà che i figli di una persona che ha chiave primaria 27 saranno contraddistinti anch'essi dall'indice 27, con specificazione degli altri attributi. Ad esempio:

Relazione Person
ID       FirstName       LastName        BirthDay        Son
11       Carlo           Rossi           23/11/1914      1
...
25       Pinco           Pallino         15/07/1995      0
26       Tizio           Caio            12/01/1956      10
27       Mario           Rossi           02/12/1945      11
...
68       Guidobaldo      Rossi           07/08/1975      27
69       Clodia          Rossi           08/09/1978      27

Da questa tabella si evince che: Mario Rossi è figlio di Carlo Rossi, poichè il suo attributo "Son" coincide con l'identificativo univoco di Carlo; Guidobaldo Rossi e Clodia Rossi sono entrambi figli di Mario Rossi, poiché il loro attributo "Son" coincide con l'identificativo univoco di Mario.
E con l'introduzione abbiamo finito.

Descrizione dei componenti

Per strutturare bene un'applicazione .Net in grado di gestire i database occorre conoscere questi quattro componenti: DataSet, OleDbConnection, OleDbDataAdapter e OldDbCommand. Gli ultimi tre appartengono al namespace System.Data.OleDB.
Il DataSet può essere visto come la rappresentazione in memoria del database: per questo contiene una collezione di tabelle (sotto forma di DataTable) nelle quali sono presenti tutti i dati. Di seguito le proprietà e i metodi più importanti:

AcceptChanges: applica tutti i cambiamenti apportati al dataset
CaseSensitive: indica se maiuscole e minuscole fanno distinzione quando si comparano elementi all'interno delle sue tabelle
DataSetName: imposta o restituisce il nome del dataset
GetXml: restituisce una stringa contenente il codice XML che rappresenta il dataset
GetXmlSchema: restituisce una stringa contenente il codice XML che definisce le regole e la grammatica che devono essere usate per scrivere documenti XML inerenti a quel dataset. La spiegazione e l'uso degli Schema XML esulano dagli scopi di questa guida, perciò vi rimando a questa pagina.
Merge(x): unisce il dataset x al dataset corrente
RejectChanges: annulla ogni modifica effettuata dopo una chiamata a AcceptChanges
Reset: annulla tutti i cambiamenti effettuati dopo la creazione del dataset
Tables: restituisce una collezione di tabelle costituenti il dataset
WriteXml(f): scrive sul file f (che può essere una stringa indicante il percorso del file o un oggetto IO.Stream) la rappgresentazione XML del dataset
WriteXmlSchema(f): scrive sul file f la rappresentazione dello schema Xml del dataset

OleDbConnection rappresenta la connessione al database nella memoria del computer e ha il compito di rendere effettive tutte le modifiche al database. Se la connessione al database è chiusa nessuna operazione può essere effettuata su di esso. Per ora possiamo concentrarci solo sul costruttore e sui due metodi fondamentali. Il costruttore New accetta come parametro una stringa di connessione: tale stringa deve specificare il provider che rende possibile la manipolazione del database e il percorso di quest'ultimo sul computer.

Stringa_di_connessione = "Provider=[provider];Data Source=[nome file]"

Se state usando Windows XP, il provider predefinito è Microsoft.JET.OLEDB.4.0. Per aprire la connessione bisogna richiamare la procedura Open(), mentre per chiuderla Close(). Tramite le proprietà ConnectionString, Provider e Database è possibile riprendere queste informazioni anche dopo la chiamata al costruttore.
OleDbDataAdapter ha il compito di trasferire i dati dal database al dataset ed è quindi il componente che effettivamente legge il database e ne permette l'analisi. Il suo costruttore accetta due parametri. Il primo è un'istruzione SQL SELECT che indica le tabelle e i campi da prelevare, mentre il secondo è un oggetto OldDbConnection che indica quale connessione a database utilizzare per questo scopo. In questo capitolo fornirò solamente le istruzioni SQL più importanti. Il codice da usare per caricare una tabella dal database è

SELECT * FROM [nome tabella]

Ricordate che se [nome tabella] contiene degli spazi, è necessario racchiuderlo tra due accenti gravi '`'.
OleDbCommand è l'oggetto che permette di eseguire, sempre attraverso istruzioni SQL, tutte le operazioni possibili sul database: rimozione, aggiunta, modifica, aggiornamento ecc... Il suo costruttore accetta due parametri: il primo è costituito da una stringa contenente il comando da eseguire, mentre il secondo è sempre l'oggetto OleDbConnection. La sua funzione più importante è ExecuteNonQuery(), che esegue il comando SQL dato e restituisce 0 nel caso l'esecuzione sia fallita, altrimenti un numero maggiore di 0. È possibile ricavare e modificare il comando in ogni momento mediante la proprietà CommandText (la stessa cosa con Connection).
Nonostante abbia definito i costruttori di tutti i componenti come procedure a uno o due parametri, ne esistono altre versioni modificate tramite overloading anche senza parametri: in questo modo si può cambiare ogni dettaglio agendo solo sulle proprietà.

Esempio: Agenda

Prima di scrivere il codice, bisogna creare il database. Per fare questo bisogna usare Microsoft Access. Dopo aver scelto Nuovo->Database vuoto, selezionare "Crea una nuova tabella in visualizzazione struttura", come mostrato in figura:

Nuova tabella

Quindi modificare i campi, aggiungendo Nome (testo), Cognome (testo 70 caratteri), Anno di nascita (data), Telefono (testo) e Indirizzo (testo 150 caratteri).

Campi tabella

Dopo aver chiuso la finestra, verrà chiesto se salvare i cambiament: scegliere Sì e proseguire. Apparirà una finestra di dialogo richiedente il nome della tabella: scegliere "Persons". DOpo aver completato il tutto, un nuovo messaggio avverte della mancanza di una chiave primaria: scegliere Sì per aggiungere all'inizio della relazione un nuovo attributo ID che funge da chiave primaria. Fatto ciò si possono inserire alcuni contatti di prova per verificare la funzione di caricamente del programma:

Contatti di prova

Ora è possibile iniziare a preoccuparsi dell'interfaccia. A un nuovo progetto, si aggiungano quattro pulsanti: cmdAdd (per aggiungere un nuovo record), cmdRemove (per rimuoverne uno o più), cmdSearch (per cercare una persona nell'agenda) e cmdReturn (per ricaricare tutto il database dopo aver visualizzato i risultati della ricerca). Inoltre, bisogna aggiungere anche una label lblInfo, che conterrà un riassunto dei dati della tupla selezionata e, ovviamente, un controllo DataGridView dgvPersons per rappresentare il database. Di questo bisogna impostare alcune caratteristiche non presenti nell'elenco della finestra delle proprietà. Nel Designer, selezionare dgvPersons: nell'angolo in alto a destra appare una piccola freccia, che al click apre un menù Dal mnù si devono deselezionare le voci Enable Adding ed Enabled Deleting, poiché si restringerà la facoltà di aggiungere o rimuovere delle righe solo al codice del programma (infatti in questo modo si possono gestire molto meglio le interazioni col database). Ecco il codice:

Imports System.Data
Public Class Form2
    'Connessione al database: viene aperta all'inizio del 
    'programma e chiusa alla fine. 
    Private dbCon As OleDb.OleDbConnection
    'Oggetto che permette di eseguire istruzioni SQL sul database.
    'Tutte le modifiche apportate tramite questo oggetto sono
    'permanenti e perciò non c'è bisogno di una
    'procedura per salvare i cambiamenti
    Private dbCmd As OleDb.OleDbCommand
    'L'adapter fa da intermezzo tra il database e l'applicazione:
    'permette di estrarre le informazioni richieste con
    'SELECT e riempie il dataset
    Private dbAdp As OleDb.OleDbDataAdapter
    'Una copia in memoria del database.
    Private Data As New DataSet

    'Questa procedura esegue un comando SQL SELECT verso il database,
    'azzera il DataSet e lo ripopola secondo i nuovi criteri
    Private Sub SqlSelect(ByVal SQL As String)
        'Memorizza l'istruzione
        dbCmd.CommandText = String.Format(SQL)
        dbAdp.SelectCommand = dbCmd
        'Cancella i vecchi dati e rimpie il dataset con ii nuovi
        'risultati
        Data.Clear()
        dbAdp.Fill(Data)
        dgvPersons.DataSource = Data.Tables(0)
    End Sub

    'Aggiorna il controllo dopo che il database è stato
    'modificato. Infatti non è posssibile aggiungere
    'niente al controllo se è statoc collegato (bound)
    'a un datatable in precedenza
    Private Sub RefreshDataGridView()
        Me.SqlSelect("SELECT * FROM Persons")
    End Sub

    Private Sub Form2_Shown(ByVal sender As Object, ByVal e As EventArgs) _ 
        Handles MyBase.Shown
        'Una volta aperto il form, si carica il database
        'predefinito all'interno della cartella bin\Debug
        Dim Database As String = Application.StartupPath & "\Persons.mdb"

        'Crea la connessione
        dbCon = New OleDb.OleDbConnection( _
        "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & Database)
        'Apre la connessione
        dbCon.Open()
        'Crea il DataAdapter: la stringa comunica di prelevare tutti 
        'i campi della tabella Persons
        dbAdp = New OleDb.OleDbDataAdapter( _
        "SELECT * FROM Persons", dbCon)
        'E tramite questo riempie il dataset
        dbAdp.Fill(Data)
        'Crea un nuovo oggetto OleDbCommand che servir� in seguito
        'Per modificare il database
        dbCmd = New OleDb.OleDbCommand()
        'Imposta la connessione per il comando
        dbCmd.Connection = dbCon

        'Preleva l'unica tabella dal dataset, la tabella 'Persons'
        Dim Table As DataTable = Data.Tables(0)
        'Collega la tabella alla datagridview come visto nel 
        'capitolo relativo
        dgvPersons.DataSource = Table
        dgvPersons.AutoResizeColumns()
    End Sub

    Private Sub dgvPersons_RowEnter(ByVal sender As Object, _ 
        ByVal e As DataGridViewCellEventArgs) Handles dgvPersons.RowEnter
        'Quando si seleziona una riga, la label sotto il DataGridView
        'visualizza le informazioni del contatto separatamente
        Dim Row As DataGridViewRow = dgvPersons.Rows(e.RowIndex)

        lblInfo.Text = ""
        For Each Cell As DataGridViewCell In Row.Cells
            'Accoda il testo della colonna, ossia il nome dell'attributo
            lblInfo.Text &= dgvPersons.Columns(Cell.ColumnIndex).HeaderText
            'E il valore corrispondente della cella
            lblInfo.Text &= " : " & Cell.Value & vbCrLf
        Next
    End Sub

    Private Sub cmdSearch_Click(ByVal sender As Object, _
        ByVal e As EventArgs) Handles cmdSearch.Click
        'Cerca le persone il cui nome completo contenga la parola
        'in input o nel nome o nel cognome
        Dim Word As String
        Word = InputBox("Immettere il nome o il cognome della " & _
            "persona da cercare:", Me.Text)

        'Controlla che la stringa non sia vuota o nulla
        If String.IsNullOrEmpty(Word) Then
            MessageBox.Show("Ricerca annullata!", Me.Text, _
                MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        Me.SqlSelect(String.Format("SELECT * FROM Persons " & _ 
            WHERE Nome = '{0}' OR Cognome = '{0}';", Word))
    End Sub

    Private Sub Form2_FormClosing(ByVal sender As Object, _ 
        ByVal e As FormClosingEventArgs) Handles MyBase.FormClosing
        'Chiude la connessione al database
        dbCon.Close()
    End Sub

    Private Sub cmdReturn_Click(ByVal sender As Object, _ 
        ByVal e As EventArgs) Handles cmdReturn.Click
        'Dopo aver fatto una ricerca, sono presenti solo i
        'record trovati. Questo pulsante permette di ripristinare
        'l'insieme di tutti i record del database
        Me.SqlSelect("SELECT * FROM Persons")
    End Sub

    Private Sub cmdRemove_Click(ByVal sender As Object, _ 
        ByVal e As EventArgs) Handles cmdRemove.Click
        'Cancella tutti i record selezionati, anche dal dataset,
        'premurandosi di salvare la configurazione corrente

        'Lista degli ID da eliminare
        Dim Indices As New List(Of Int16)
        For Each Row As DataGridViewRow In dgvPersons.SelectedRows
            'L'ID è il primo elemento della tupla
            Indices.Add(Row.Cells(0).Value)
        Next

        'Crea l'istruzione SQL
        Dim SqlDelete As New System.Text.StringBuilder
        Dim Result As Int16
        SqlDelete.Append("DELETE FROM Persons WHERE")
        For Each ID As Int16 In Indices
            'Se non si tratta del primo elemento, aggiunge una
            'clausola OR prima della condizione
            If ID <> Indices(0) Then
                SqlDelete.Append(" OR")
            End If
            SqlDelete.AppendFormat(" ID = {0}", ID)
        Next
        'Finisce l'istruzione con un ;
        SqlDelete.Append(";")

        dbCmd.CommandText = SqlDelete.ToString
        'Esegue il comando direttamente sul database
        Result = dbCmd.ExecuteNonQuery()
        'Aggiorna il controllo
        Me.RefreshDataGridView()

        'Controlla se l'operazione è riuscita
        If Result = 0 Then
            MessageBox.Show("Operazione fallita!", Me.Text, _
                MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End If
    End Sub

    Private Sub dgvPersons_CellEndEdit(ByVal sender As Object, _ 
        ByVal e As DataGridViewCellEventArgs) Handles dgvPersons.CellEndEdit
        'Quando l'utente finisce di immettere i valori,
        'aggiorna subito il database

        'Ottiene la chiave primaria di questa riga
        Dim ID As Int16 = dgvPersons.Rows(e.RowIndex).Cells(0).Value
        'Ottiene il nome dell'attributo modificato
        Dim Name As String = dgvPersons.Columns(e.ColumnIndex).HeaderText
        'Ottiene il nuovo valore della cella
        Dim Value As Object = _
            dgvPersons.Rows(e.RowIndex).Cells(e.ColumnIndex).Value

        'L'ID è una chiave primaria e perciò non
        'aggiornabile. Se l'attributo in modifica è l'ID,
        'termina la procedura
        If Name = "ID" Then
            Exit Sub
        End If
        'Se l'attributo modificato è una data, si occupa
        'lo stesso controllo di verificarne la coerenza

        'Aggiorna il database
        dbCmd.CommandText = _
            String.Format("UPDATE Persons SET `{0}` = '{1}' " & _ 
            "WHERE ID = {2}", Name, Value.ToString, ID)
        dbCmd.ExecuteNonQuery()
    End Sub

    Private Sub cmdAdd_Click(ByVal sender As Object, _ 
        ByVal e As EventArgs) Handles cmdAdd.Click
        'Aggiunge una nuova riga

        'Preleva l'ID dalla riga precedente
        Dim ID As Int16 = _
            dgvPersons.Rows(dgvPersons.RowCount - 1).Cells(0).Value

        'Crea l'istruzione SQL
        Dim SqlInsert As New System.Text.StringBuilder
        SqlInsert.Append("INSERT INTO Persons VALUES(")
        SqlInsert.AppendFormat("{0}, '', '', '{1}', '' , '');", _
            ID + 1, Date.Now.ToShortDateString)
        dbCmd.CommandText = SqlInsert.ToString
        'Esegue il comando: aggiunge una nuova riga con ID
        'valido e data corrente
        dbCmd.ExecuteNonQuery()
        'Aggiorna il DataGrdiView
        Me.RefreshDataGridView()
    End Sub

    Private Sub dgvPersons_ColumnHeaderMouseClick(ByVal sender As Object, _ 
        ByVal e As DataGridViewCellMouseEventArgs) _ 
        Handles dgvPersons.ColumnHeaderMouseClick
        'Quando si clicca suell'Header (ossia sull'intestazione)
        'di una colonna, ordina tutti i dati in base a quel
        'campo
        dgvPersons.Sort(dgvPersons.Columns(e.ColumnIndex), _
            System.ComponentModel.ListSortDirection.Ascending)
    End Sub
End Class

Fondamenti di SQL

Nell'esempio precedente ho usato molte istruzioni SQL sconosciute, il cui contesto applicativo avrebbe dovuto comunque farne capire l'utilizzo. Tuttavia, non mi sembra inopportuno presentare qui una breve lista dei comandi SQL comunemente più usati:

Caricare
Per prendere in considerazione una data gamma di attributi da una certa tabella, si usa l'istruzione SELECT. Questa keyword deve essere seguita dal nome e/o dai nomi, separati da virgole, degli attributi da caricare; successivamente si deve specificare la clausola FROM, al seguito della quale si indica la tabella da cui prelevare tali attributi. La sintassi generale è
```
SELECT [Attributi] FROM [Tabella]; 
```
Opzionalmente, è possibile aggiungere la clausola WHERE, che indica di caricare solamente i record che soddisfano determinate condizioni, ad esempio dove un attributo assume un determinato valore:
```
SELECT [Attributi] FROM [Tabella] WHERE [Condizioni]; 
```
Se le condizioni sono più di una, si possono utilizzare i connettivi logici OR e AND proprio come in vb.net. Ecco un esempio pertinente ai nostri dati:
```
'Seleziona solo nome e cognome di tutti 
'i contatti che si chiamano "Aldo"
SELECT Nome, Cognome FROM Persons WHERE Nome = 'Aldo'; 
```
Le istruzioni, in particolare quelle molte lunghe, ma anche le altre in genere, sono solite essere scritte usando una sola keyword per riga:
```
SELECT 
    Nome, Cognome 
FROM 
    Persons 
WHERE 
    Nome = 'Aldo'
    OR Nome = 'Eugenio'; 
```
Bisogna inoltre ricordarsi che il carattere jolly asterisco (*) seleziona automaticamente tutti gli attributi, mentre per nomi che comprendono caratteri di spazio, bisogna racchiudere l'identificatore tra accenti gravi (`).
Inserire
Per inserire una nuova riga all'interno del database, si usa l'istruzione INSERT INTO, alla quale si fa seguire un elenco di attributi racchiusi dalla keyword VALUES. Opzionalmente, è possibile specificare prima di questa il corrispeetivo elenco dei nomi delle colonne:
```
INSERT INTO [Tabella] [Elenco opzionale attributi] VALUES ([Valori]); 
```
Ad esempio:
```
INSERT INTO 
    Persons
VALUES 
    ('Pinco', 'Pallino', '17/08/1956', '0371 87 561', 'Viale Siccomario 23, Binasco'); 
```
Eliminare
Per eliminare uno o più valori viene usata l'istruzione DELETE FROM, seguita dal nome della tabella e opzionalmente dalla clausola WHERE che specifica le condizioni sotto le quali si debba eseguire l'eliminazione. Anche in questo caso, è sempre possibile usare i connettivi logici per produrre controlli complessi. La sintassi generale:
```
DELETE FROM [Tabella] WHERE [Condizioni]; 
```
Delete costituisce un caso speciale in cui si può anche omettere il carattere asterisco per cancellare tutto. Le seguenti espressioni sono quindi equivalenti ed eliminano ogni record dalla tabella:
```
DELETE FROM Persons;
'e
DELETE * FROM Persons; 
```
Aggiornare
Una volta inseriti o eliminati i record desiderati, non resta altro che aggiornarli con le nuove modifiche dell'utente. Per questo scopo, ci viene in aiuto l'istruzione UPDATE, che ha il compito di reimpostare i valori nelle celle. La sua sintassi è
```
UPDATE [Tabella] SET [Attributo/i = Valore/i] WHERE [Condizioni]; 
```
Anche questa, come le altre, supporta la clausola WHERE, mentre il blocco SET ha il compito di inserire i nuovi valori negli attributi. Ad esempio:
```
UPDATE 
    Persons
SET
    Telefono = '354 78 10 253',
    Indirizzo = 'Vicolo Biancospino 2B, Napoli'
WHERE   
    Nome = 'Tizio'
    AND Cognome = 'Caio'; 
```
Bisogna ricordarsi che per aggiornare un solo valore si può ricorrere alla chiave primaria.

The Totem's Lair - Copyright (C) 2009
È vietata la riproduzione sia totale che parziale del sito.