sabato 25 febbraio 2012

[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.




8 commenti:

Anonimo ha detto...

Ciao esordisco facendoti i complimenti per l'articolo.
Domanda:
"molti utenti .NET, nel momento in cui si rende necessario disporre di un DB file based locale, puntano ancora istintivamente su Access."

Tu cosa consiglieresti come alternativa?

Anonimo ha detto...

Sarebbe interessante se ci fosse la possibilità di scaricare l'applicazzione descritta.

MarcoGG ha detto...

Ciao e grazie. :)
Si infatti. Access è per molti il DB File-Based per eccellenza e questa è a mio avviso anche un po' "colpa" di Microsoft, perchè due valide alternative, che tra l'altro supportano Entity Framework ( Access NON supporta EF, e stando alle intenzioni attuali di MS, non lo supporterà MAI ) ci sono già : una è SQL Server CE, che è fuori da tempo, e l'altra è l'ultimo arrivato SQL Local DB, uscito con SQL Server 2012. Alternative sono anche diversi altri DB come il ben noto SQLite, FireBird embedded, ecc... La risposta alle possibili alternative comunque sarebbe davvero molto lunga, e ci sarebbe da discutere parecchio. Sicuramente Access ha dalla sua l'immediatezza, la facile reperibilità, l'interfaccia che aiuta molto nella creazione della struttura del DB. Per contro ha un limite di File-Size ancora a 2GB, e parecchie altre scocciature che MS non può o forse meglio NON VUOLE risolvere perchè preferisce mantenerlo nel suo ruolo storico di DB-Applicazione All-In-One.
Infine aggiungo che, a mio avviso, in molti casi in cui utenti corrono subito ad aprire Access, magari per creare un banale DB mono-tabella o anche con poche tabelle e relazioni, ADO.NET mette a disposizione grandi strumenti per gestirsi un DB Locale in puro XML. Ma certo è più veloce e comodo aprire Access, invece che aprire MSDN e studiare a fondo ADO.NET e XML... ;-)

MarcoGG ha detto...

Sulla questione "Allegati" la tua osservazione è più che giusta. In realtà avevo fin da subito pensato di allegare direttamente lo Zip con i sorgenti per ogni Articolo. Ma poi mi è sorto il dubbio sul rischio che quello che io voglio sia un "Blog", non diventi un puro e semplice "Centro di download" di soluzioni già pronte : io vorrei che quello che scrivo venisse anche e soprattutto LETTO e non solo scaricato. :-)
Per ora la "regola" è sostanzialmente questa : ho collegato questo Blog alla mia Pagina FaceBook. Chi desidera il Progetto sorgente relativo ad un Articolo di suo interesse, basta che si "registri" alla Pagina FB ( in pratica basta che faccia Click su "Mi Piace" ), e lo richieda.

tommy scikkoso ha detto...

Ho un dubbio (o meglio un quesito) da porle...in una datagridView io visualizzo i dati del db in questione. Inserisco un Button con questo codice:

[code]int index = dataGridView1.SelectedRows[0].Index;
dataGridView1.Rows.RemoveAt(index);[/code]

che permette di cancellare la riga selezionata. Ma ciò non agisce sul DB...come potrei agire? Grazie

MarcoGG ha detto...

Ciao tommy ( ci si può dare del TU senza problemi ). Per poter fare quanto chiedi non bisogna popolare il DataGridView con un DataReader ( che produce un DataGridView "ReadOnly" ), ma bensì definire un OleDbDataAdapter, ottenere e popolare un DataSet mediante il Metodo Fill() e passare il DataSet stesso al DataSource del DataGridView. Questo non rientra ( a mio avviso ) nelle operazioni "base" e perciò non l'ho inserito nel presente Articolo. Infatti ci sono diversi sistemi per ottenere ciò. Potrebbe anche rendersi necessario usare un processo asincrono con uso di Delegate per grandi quantità di dati e per non bloccare la UI...

Alberto Garzonio ha detto...

Ciao Marco,
sto provando a giocare con il tuo esempio ma ho un piccolo problema. In modalità debug quando premo su un cmd (esempio il "btn_selectparampclan") mi viene generata un eccezione perché è già aperta una connessione al db. Eseguendo la modalità passo-passo ho notato che prima di visualizzare il form viene richiamato il metodo "setMaxId" che non viene terminato.
Mi spiego: sempre nel passo-passo una volta lanciato il metodo "setMaxId" esegue:
- la prima riga (apertura della connessione);
- la seconda riga (forse);
- non esegue la terza riga, termina il metodo e visualizza a schermo la form.
A questo punto non essendo stata chiuisa la connessione (terza riga del medoto "setMaxId") tutti i comandi che prevedono la riapertura della connessione generano una eccezione.

Mi sapresti aiutare?

Ciao e grz 1k.

Anonimo ha detto...

Ciao Marco
Mi chiamo Moreno e da poco mi sono avvicinato a c#.
Ho cercato in internet spiegazioni per creare applicazioni windows form appoggiandomi a mdb access. Ho trovato stupefacente la tua spiegazione e ti chiederi se fosse possibile ottenere un .zip del tuo esempio per darmi modo di studiare le tecniche usate.
Sarebbe estremamente importante avere una base come quella che hai realizzato per darmi modo di risolvere tutti i problemi che mi stanno nascendo in fase di studio di C#.
la mia mailprivata è misonsan@libero.it
Grazie per lacortese disponibilità.
Ciao
Moreno

complimenti vivissimi per il blog; professionale e esteticamente bello.

Posta un commento

 
Design by Free WordPress Themes Modificato da MarcoGG