[C#] 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 .NET, nel momento in cui si rende necessario disporre di un DB file based locale, puntano ancora istintivamente su Access.

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
Publics.oleDbCnStr.Provider = "Microsoft.Jet.OLEDB.4.0";
Publics.oleDbCnStr.DataSource = Publics.percorsoLocale + Publics.nomeFileDB;
Publics.oleDbCnStr.PersistSecurityInfo = false;
Publics.oleDbCn.ConnectionString = Publics.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 : una Classe Statica "Publics" e una Form "FormMain", che è l'oggetto di avvio.

- Codice Publics :

using System;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Drawing;

namespace OperazioniAccess
{
    internal static class Publics
    {
        //Percorso Locale in cui è situato il DB Access
        public static string percorsoLocale = Application.StartupPath + "\\";
        //Percorso LAN in cui è situato il DB Access
        public static string nomeMacchina = "nomeMacchinaLAN";
        public static string nomeDirCondivisa = "nomeDirCondivisaLAN";
        public static string percorsoLan = "\\\\" + nomeMacchina + "\\" + nomeDirCondivisa + "\\";
        //Nome file Access compresa estensione
        public static string nomeFileDB = "testDB.mdb";

        public static OleDbConnectionStringBuilder oleDbCnStr = new OleDbConnectionStringBuilder();
        public static OleDbConnection oleDbCn = new OleDbConnection();

        public static Font F = new Font(FontFamily.GenericMonospace, 12, FontStyle.Bold);
    }
}

- 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 :

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;

namespace OperazioniAccess
{
    public partial class FormMain : Form
    {
        public FormMain()
        {
            InitializeComponent();
        }

        private int maxId;

        private void setMaxId()
        {
            string strSql = "SELECT MAX(id) FROM T_Persone";
            using (OleDbCommand CMD = new OleDbCommand(strSql, Publics.oleDbCn))
            {
                Publics.oleDbCn.Open();
                maxId = (int)CMD.ExecuteScalar();
                Publics.oleDbCn.Close();
            }
        }

        private void FormMain_Load(object sender, EventArgs e)
        {
            //OleDb.OleDbConnectionStringBuilder
            Publics.oleDbCnStr.Provider = "Microsoft.Jet.OLEDB.4.0";
            Publics.oleDbCnStr.DataSource = Publics.percorsoLocale + Publics.nomeFileDB;
            Publics.oleDbCnStr.PersistSecurityInfo = false;
            Publics.oleDbCn.ConnectionString = Publics.oleDbCnStr.ToString();

            txt_risultati.Font = Publics.F;
            txt_risultati.Multiline = true;

            DGV.AllowUserToAddRows = false;
            DGV.ReadOnly = true;
            DGV.Font = Publics.F;

            setMaxId();
        }

        private void btn_selectparampclan_Click(object sender, EventArgs e)
        {
            //Modifica SOLO al .DataSource di oleDbCnStr
            Publics.oleDbCnStr.DataSource = Publics.percorsoLan + Publics.nomeFileDB;

            System.Text.StringBuilder risultati = new System.Text.StringBuilder();
            string strSql = "SELECT * FROM T_Persone WHERE datanascita>=@datanascita";

            using (OleDbCommand CMD = new OleDbCommand(strSql, Publics.oleDbCn))
            {
                //Definizione / Assegnazione
                CMD.Parameters.Add("@datanascita", OleDbType.Date).Value = new DateTime(1967, 12, 25);
                //... altri Parametri

                Publics.oleDbCn.Open();
                using (OleDbDataReader RDR = CMD.ExecuteReader())
                {

                    //Lettura Campi DataReader
                    for (int i = 0; i < RDR.FieldCount; i++)
                    {
                        risultati.Append(RDR.GetName(i).PadRight(19, ' '));
                    }
                    risultati.AppendLine().AppendLine();
                    //Lettura Campi Query
                    while (RDR.Read())
                    {
                        for (int i = 0; i < RDR.FieldCount; i++)
                        {
                            risultati.Append(RDR[i].ToString().PadRight(19, ' '));
                        }
                        risultati.AppendLine();
                    }
                }
                Publics.oleDbCn.Close();
            }
            txt_risultati.Text = risultati.ToString();
        }

        private void btn_selectparam_Click(object sender, EventArgs e)
        {
            System.Text.StringBuilder risultati = new System.Text.StringBuilder();
            string strSql = "SELECT * FROM T_Persone WHERE datanascita>=@datanascita AND cognome LIKE @cognome";

            using (OleDbCommand CMD = new OleDbCommand(strSql, Publics.oleDbCn))
            {
                //Definizione / Assegnazione
                CMD.Parameters.Add("@datanascita", OleDbType.Date).Value = new DateTime(1967, 12, 25);
                CMD.Parameters.Add("@cognome", OleDbType.VarChar).Value = "Ro" + "%";
                //... altri Parametri

                Publics.oleDbCn.Open();
                using (OleDbDataReader RDR = CMD.ExecuteReader())
                {

                    //Lettura Campi DataReader
                    for (int i = 0; i < RDR.FieldCount; i++)
                    {
                        risultati.Append(RDR.GetName(i).PadRight(19, ' '));
                    }
                    risultati.AppendLine().AppendLine();
                    //Lettura Campi Query
                    while (RDR.Read())
                    {
                        for (int i = 0; i < RDR.FieldCount; i++)
                        {
                            risultati.Append(RDR[i].ToString().PadRight(19, ' '));
                        }
                        risultati.AppendLine();
                    }
                }
                Publics.oleDbCn.Close();
            }

            txt_risultati.Text = risultati.ToString();
        }

        private void btn_selectparamdgv_Click(object sender, EventArgs e)
        {
            //Reset DGV
            DGV.Rows.Clear();
            DGV.Columns.Clear();

            DataGridViewRow dgvr = null;
            string strSql = "SELECT * FROM T_Persone WHERE datanascita>=@datanascita AND cognome LIKE @cognome";

            using (OleDbCommand CMD = new OleDbCommand(strSql, Publics.oleDbCn))
            {
                //Definizione / Assegnazione
                CMD.Parameters.Add("@datanascita", OleDbType.Date).Value = new DateTime(1967, 12, 25);
                CMD.Parameters.Add("@cognome", OleDbType.VarChar).Value = "Ro" + "%";
                //... altri Parametri

                Publics.oleDbCn.Open();
                using (OleDbDataReader RDR = CMD.ExecuteReader())
                {
                    //Lettura Campi DataReader
                    for (int i = 0; i < RDR.FieldCount; i++)
                    {
                        DGV.Columns.Add(RDR.GetName(i).ToString(), RDR.GetName(i).ToString());
                    }
                    //Lettura Campi Query
                    while (RDR.Read())
                    {
                        dgvr = new DataGridViewRow();
                        dgvr.CreateCells(DGV);
                        for (int i = 0; i < RDR.FieldCount; i++)
                        {
                            dgvr.Cells[i].Value = RDR[i].ToString();
                        }
                        DGV.Rows.Add(dgvr);
                    }
                }
                Publics.oleDbCn.Close();
            }
        }

        private void btn_selectmaxid_Click(object sender, EventArgs e)
        {
            setMaxId();
            MessageBox.Show(maxId.ToString());
        }

        private void btn_selectstar_Click(object sender, EventArgs e)
        {
            System.Text.StringBuilder risultati = new System.Text.StringBuilder();
            string strSql = "SELECT * FROM T_Persone";

            using (OleDbCommand CMD = new OleDbCommand(strSql, Publics.oleDbCn))
            {
                Publics.oleDbCn.Open();
                using (OleDbDataReader RDR = CMD.ExecuteReader())
                {
                    //Lettura Campi DataReader
                    for (int i = 0; i < RDR.FieldCount; i++)
                    {
                        risultati.Append(RDR.GetName(i).PadRight(19, ' '));
                    }
                    risultati.AppendLine().AppendLine();
                    //Lettura Campi Query
                    while (RDR.Read())
                    {
                        for (int i = 0; i < RDR.FieldCount; i++)
                        {
                            risultati.Append(RDR[i].ToString().PadRight(19, ' '));
                        }
                        risultati.AppendLine();
                    }
                }
                Publics.oleDbCn.Close();
            }
            txt_risultati.Text = risultati.ToString();
        }

        private void btn_selectstardgv_Click(object sender, EventArgs e)
        {
            //Reset DGV
            DGV.Rows.Clear();
            DGV.Columns.Clear();

            DataGridViewRow dgvr = null;
            string strSql = "SELECT * FROM T_Persone";

            using (OleDbCommand CMD = new OleDbCommand(strSql, Publics.oleDbCn))
            {
                Publics.oleDbCn.Open();
                using (OleDbDataReader RDR = CMD.ExecuteReader())
                {
                    //Lettura Campi DataReader
                    for (int i = 0; i < RDR.FieldCount; i++)
                    {
                        DGV.Columns.Add(RDR.GetName(i).ToString(), RDR.GetName(i).ToString());
                    }
                    //Lettura Campi Query
                    while (RDR.Read())
                    {
                        dgvr = new DataGridViewRow();
                        dgvr.CreateCells(DGV);
                        for (int i = 0; i < RDR.FieldCount; i++)
                        {
                            dgvr.Cells[i].Value = RDR[i].ToString();
                        }
                        DGV.Rows.Add(dgvr);
                    }
                }
                Publics.oleDbCn.Close();
            }
        }

        private void btn_insertparam_Click(object sender, EventArgs e)
        {
            string strSql = "INSERT INTO T_Persone (id, nome, cognome, datanascita, contatti) " + "VALUES (@id, @nome, @cognome, @datanascita, @contatti)";

            using (OleDbCommand CMD = new OleDbCommand(strSql, Publics.oleDbCn))
            {
                //Definizione / Assegnazione
                CMD.Parameters.Add("@id", OleDbType.Integer).Value = maxId + 1;
                CMD.Parameters.Add("@nome", OleDbType.VarChar).Value = "insertNome";
                CMD.Parameters.Add("@cognome", OleDbType.VarChar).Value = "insertCognome";
                CMD.Parameters.Add("@datanascita", OleDbType.Date).Value = new DateTime(1970, 1, 31);
                CMD.Parameters.Add("@contatti", OleDbType.Integer).Value = 12;

                Publics.oleDbCn.Open();
                CMD.ExecuteNonQuery();
                Publics.oleDbCn.Close();
            }
            setMaxId();
            MessageBox.Show("INSERT OK");
        }

        private void btn_updateparam_Click(object sender, EventArgs e)
        {
            string strSql = "UPDATE T_Persone SET nome=@nome, cognome=@cognome, datanascita=@datanascita, contatti=@contatti " + "WHERE id=@id";

            using (OleDbCommand CMD = new OleDbCommand(strSql, Publics.oleDbCn))
            {
                //Definizione / Assegnazione
                CMD.Parameters.Add("@nome", OleDbType.VarChar).Value = "updateNome";
                CMD.Parameters.Add("@cognome", OleDbType.VarChar).Value = "updateCognome";
                CMD.Parameters.Add("@datanascita", OleDbType.Date).Value = new DateTime(1968, 8, 8);
                CMD.Parameters.Add("@contatti", OleDbType.Integer).Value = 8;
                CMD.Parameters.Add("@id", OleDbType.Integer).Value = maxId;

                Publics.oleDbCn.Open();
                CMD.ExecuteNonQuery();
                Publics.oleDbCn.Close();
            }
            MessageBox.Show("UPDATE OK");
        }

        private void btn_deleteparam_Click(object sender, EventArgs e)
        {
            string strSql = "DELETE FROM T_Persone WHERE id=@id";

            using (OleDbCommand CMD = new OleDbCommand(strSql, Publics.oleDbCn))
            {
                //Definizione / Assegnazione
                CMD.Parameters.Add("@id", OleDbType.Integer).Value = maxId;

                Publics.oleDbCn.Open();
                CMD.ExecuteNonQuery();
                Publics.oleDbCn.Close();
            }
            setMaxId();
            MessageBox.Show("DELETE OK");
        }

    }
}

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: C#