Postgresql e Visual Studio 2005 un esempio di gestione del DataBinding

Dopo l'esperienza con MySql ho voluto rifare la stessa esperienza con Postgresql

E' giunto ora alla release 8.2.4, circoscritto fino a qualche release ad ambienti Unix/Linux è sbarcato da qualche tempo anche su Windows in modo nativo.

Per chi non lo conoscesse Postgresql è un database estremamente robusto e versatile, nato inizialmente con il nome Postgres da Post (dopo, successivo a) + gres, per ricordare le sue origini da Ingres, database commerciale per Unix abbastanza noto 20 anni fa), il termine Sql è stato aggiunto successivamente all'adesione allo standard SQL.

Licenze di Postgresql

La licenza che accompagna Postgresql è la BSD, nata originariamente come "Berkeley Software Distribution" sul leggendario PDP11 che tanto ha dato (unix, c, vi) alla storia dell'informatica.
La licenza BSD non pone limitazioni all'uso di quanto 2sotto licenza BSD" anche in campo commerciale, ma impone di citare sempre l'autore (non mi sembra una grande limitazione ma tant'è alcuni grandi produttori di hw/sw hanno pensato bene di togliere i riferimenti alla licenza e all'autore violando in questo modo la licenza!).

Configurazione hardware e software

A disposizione per la mia prova:

1. Linux Fedora Core 6 installato su assemblato equipaggiato con un processore Intel Pentium4  2.26 gHz 2GB ram
2. Database Postgresql versione 8.1.8 installato su Linux
3. Windows Xp Sp2 installato su Acer Travelmate 8100
4. Visual Studio 2005 Team Edition for Software Developers
5. Npgsql, driver nativo di Postgresql  per .NET 
6. PgAdmin III per Windows

Installazione e configurazione di Postgresql

Ho provveduto ad installare Postgresql per linux dal cd di installazione e l'ho successivamente aggiornato con Yum.

Finita l'installazione sono entrato con l'utente di default "postgres" partendo dalla shell di root.

[root@lucy share]# psql -U postgres
Welcome to psql 8.1.8, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=#

Verificando che il tutto funzioni, ho creato un nuovo database "dbprova" a cui ho concesso i permessi.

Su Linux inoltre è necessario impostare il file /var/lib/pgsql/data/pg_hba.conf
# IPv4 local connections:
# inizio aggiunte
hostnossl all all 127.0.0.1/32 ident sameuser
hostnossl all all 192.168.1.1/32 trust sameuser
hostnossl all all 192.168.1.2/32 trust sameuser
hostnossl all all 192.168.1.3/32 trust sameuser
hostnossl all all 192.168.1.4/32 trust sameuser
#fine aggiunte

Nota: ovviamente /var/lib/pgsql/data è la cartella dove è posizionato il mio database

Installazione di Npgsql

E' ovviamente disponibile un driver ADO.NET managed "npgsql" disponibile sia per il Framework 1.1 che per il Framework 2.0 nelle versioni "Windows" e "Mono" (applicazioni su linux o senza la Gui di Microsoft), come per tutti i "prodotti" a licenza GNU è sono disponibili anche i sorgenti.

Il driver .NET si installa "scompattandolo" nella cartella, essendo già dotato di uno "strong name" è installabile nella GAC tramite Gacutil.exe.

Tra le varie cose che accompagna Npgsql troviamo anche un comodissimo help in formato html la cui lettura credo sia indispensabile per poter affrontare e risolvere velocemente i problemi di configurazione e collegamento ai dati.

Personalmente, avendo scelto la versione "beta" che verà al più presto rimpiazzata con un'altra beta o con una release ho preferito non installare la libreria nella GAC ma referenziarla all'interno del mio esperimento.

Installazione di PgAdmin III

PgAdmin III è un manager per il database Postgresql che consente di mantenere il server di Postgresql in tutti i suoi aspetti, dalla definizione ad esempio di campi e tabelle, a triggers e stored procedure, alla manutenzione del database con salvataggi e ripristini, allle query interattive.

L'installazione avviene semplicemente scaricando e facendo partire nel modo più classico il modulo di installazione.

Parlando di manager ne esiste un secondo con interfaccia Web  che però non ho mai installato ed ovviamente provato.

 

Con pgAdmin III ho generato la tabella tableprova.

Questo lo script di generazione:

-- Table: tableprova
-- DROP TABLE tableprova;
CREATE TABLE tableprova
(
id serial NOT NULL,
descrizione character varying(50),
valore numeric(10,3)
)
WITH OIDS;
ALTER TABLE tableprova OWNER TO postgres;

Come si può notare il campo id è stato definito come serial, il database ha automaticamente generato una nuova sequenza che esplicito:

Generazione della sequenza nel caso non venga creata automaticamente.
-- Sequence: tableprova_id_seq
-- DROP SEQUENCE tableprova_id_seq;
CREATE SEQUENCE tableprova_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 17
CACHE 1;
ALTER TABLE tableprova_id_seq OWNER TO postgres;

 

Lato Visual Studio 2005

Non esiste un addon per Visual Studio 2005 per cui è esclusa la possibilità di generare un TableAdapter con il Wizard partendo da una connessione con Postgresql, con Visual Express 2005 è impossibile utilizzare il wizard con database diversi da Access e/o Sql Express in locale.

Ho scelto pertanto di generare un database Access "locale" cui ho inserito la tabella che serviva per il mio esperimento.

Fatto questo primo passo ho provveduto a generare un nuovo progetto Windows Forms.

Ho allegato il database (aggiunta nuovo elemento esistente), il wizard successivamente ha generato un dataset tipizzato, ed ha aggiunto "app.config" con la relativa stringa di connessione.

Sulla toolbar mi sono ritrovato il tableadapter, ho selezionato e trascinato entrambi sulla form.

Adesso inizia la parte più difficile!.

Ovviamente i riferimenti generati dal wizard su access vanno cambiati nei punti "giusti" in modo tale che il tutto funzioni senza problemi, è vietatissimo rigenerare di nuovo il dataset, nel caso farsi una copia del progetto, copia che verrà utilizzata per  fare i cambiamenti con il wizard in sicurezza.

Ho tolto il database di database di Access dal progetto, non servirà oltre, almeno per me è stato così.

Ho inserito Npgsql tra i riferimenti del progetto.

Ho modificato il file "app.config" sostituendo la stringa con la connessione ad access con la stringa con la connessione a Postgresql, che per la mia connessione è: "Server=lucy;Port=5432;User Id=postgres;Trusted=Yes;Database=dbprova", Nota: lucy è il mio server Linux in onore della Lucy dei mitici "Peanuts", nella documentazione di Npgsql sono documentati tutti i parametri necessari per la configurazione.

Poi sono andato sul modulo dbprovaDataSet.Designer e ho cambiato tutti i riferimenti a System.Data.OleDb con i riferimenti a Npgsql e in particolar modo NpgsqlConnection al posto di OleDbConnection, NpgsqlDataAdapter al posto di OleDbDataAdapter, NpgsqlCommand al posto di OleDbCommand.

Inoltre ho provveduto a riscrivere e/o modificare tutto il codice di definizione dei Command (UpdateCommand, InsertCommand, SelectCommand e DeleteCommand) necessari affinchè il tutto funzioni.

Questo è il codice relativo per VB.NET

        Private Sub InitAdapter()
            Me._adapter = New Npgsql.NpgsqlDataAdapter
            Dim tableMapping As System.Data.Common.DataTableMapping = New System.Data.Common.DataTableMapping
            tableMapping.SourceTable = "Table"
            tableMapping.DataSetTable = "tableprova"
            tableMapping.ColumnMappings.Add("id", "id")
            tableMapping.ColumnMappings.Add("descrizione", "descrizione")
            tableMapping.ColumnMappings.Add("valore", "valore")
            Me._adapter.TableMappings.Add(tableMapping)
            Me._adapter.DeleteCommand = New Npgsql.NpgsqlCommand
            Me._adapter.DeleteCommand.Connection = Me.Connection
            Me._adapter.DeleteCommand.CommandText = "DELETE FROM tableprova WHERE (id = :Original_id)"
            Me._adapter.DeleteCommand.CommandType = System.Data.CommandType.Text
            Me._adapter.DeleteCommand.Parameters.Add(New Npgsql.NpgsqlParameter(":Original_id", NpgsqlTypes.NpgsqlDbType.[Integer], 0, "id", System.Data.ParameterDirection.Input, False, _
             CByte(0), CByte(0), System.Data.DataRowVersion.Original, Nothing))
            Me._adapter.InsertCommand = New Npgsql.NpgsqlCommand
            Me._adapter.InsertCommand.Connection = Me.Connection
            Me._adapter.InsertCommand.CommandText = "INSERT INTO tableprova (descrizione, valore) VALUES (:descrizione, :valore)"
            Me._adapter.InsertCommand.CommandType = System.Data.CommandType.Text
            Me._adapter.InsertCommand.Parameters.Add(New Npgsql.NpgsqlParameter(":descrizione", NpgsqlTypes.NpgsqlDbType.Varchar, 50, "descrizione"))
            Me._adapter.InsertCommand.Parameters.Add(New Npgsql.NpgsqlParameter(":valore", NpgsqlTypes.NpgsqlDbType.[Double], 0, "valore"))
            Me._adapter.UpdateCommand = New Npgsql.NpgsqlCommand
            Me._adapter.UpdateCommand.Connection = Me.Connection
            Me._adapter.UpdateCommand.CommandText = "UPDATE tableprova SET descrizione = :descrizione, valore = :valore WHERE id = :original_id"
            Me._adapter.UpdateCommand.CommandType = System.Data.CommandType.Text
            Me._adapter.UpdateCommand.Parameters.Add(New Npgsql.NpgsqlParameter(":descrizione", NpgsqlTypes.NpgsqlDbType.Varchar, 50, "descrizione"))
            Me._adapter.UpdateCommand.Parameters.Add(New Npgsql.NpgsqlParameter(":valore", NpgsqlTypes.NpgsqlDbType.[Double], 0, "valore"))
            Me._adapter.UpdateCommand.Parameters.Add(New Npgsql.NpgsqlParameter(":original_id", NpgsqlTypes.NpgsqlDbType.[Integer], 0, "id", System.Data.ParameterDirection.Input, False, _
           CByte(0), CByte(0), System.Data.DataRowVersion.Original, Nothing))

        End Sub

Questo invece lo stesso codice per C#

 

        private void InitAdapter() {
            this._adapter = new Npgsql.NpgsqlDataAdapter();
            System.Data.Common.DataTableMapping tableMapping = new System.Data.Common.DataTableMapping();
            tableMapping.SourceTable = "Table";
            tableMapping.DataSetTable = "tableprova";
            tableMapping.ColumnMappings.Add("id", "id");
            tableMapping.ColumnMappings.Add("descrizione", "descrizione");
            tableMapping.ColumnMappings.Add("valore", "valore");
            this._adapter.TableMappings.Add(tableMapping);
            this._adapter.DeleteCommand = new Npgsql.NpgsqlCommand();
            this._adapter.DeleteCommand.Connection = this.Connection;
            this._adapter.DeleteCommand.CommandText = "DELETE FROM tableprova WHERE (id = :Original_id)";
            this._adapter.DeleteCommand.CommandType = System.Data.CommandType.Text;
            this._adapter.DeleteCommand.Parameters.Add(new Npgsql.NpgsqlParameter(":Original_id", NpgsqlTypes.NpgsqlDbType.Integer, 0,"id",System.Data.ParameterDirection.Input,false,(byte) 0,(byte) 0,System.Data.DataRowVersion.Original,null));
            this._adapter.InsertCommand = new Npgsql.NpgsqlCommand();
            this._adapter.InsertCommand.Connection = this.Connection;
            this._adapter.InsertCommand.CommandText = "INSERT INTO tableprova (descrizione, valore) VALUES (:descrizione, :valore)";
            this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
            this._adapter.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter(":descrizione", NpgsqlTypes.NpgsqlDbType.Varchar, 0,"descrizione"));
            this._adapter.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter(":valore", NpgsqlTypes.NpgsqlDbType.Numeric, 0, "valore"));
            this._adapter.UpdateCommand = new Npgsql.NpgsqlCommand();
            this._adapter.UpdateCommand.Connection = this.Connection;
            this._adapter.UpdateCommand.CommandText = "UPDATE tableprova SET descrizione = :descrizione, valore = :valore WHERE (id = :original_id)"; 
            this._adapter.UpdateCommand.CommandType = System.Data.CommandType.Text;
            this._adapter.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter(":descrizione", NpgsqlTypes.NpgsqlDbType.Varchar, 0, "descrizione"));
            this._adapter.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter(":valore", NpgsqlTypes.NpgsqlDbType.Numeric, 0, "valore"));
            this._adapter.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter(":original_id", NpgsqlTypes.NpgsqlDbType.Integer, 0, "id", System.Data.ParameterDirection.Input, false, (byte)0, (byte)0, System.Data.DataRowVersion.Original, null));

        }

 

Originariamente sono partito da questa definizione (C#, vi risparmio il blocco originale in Vb.Net).

       private void InitAdapter() {
            this._adapter = new System.Data.OleDb.OleDbDataAdapter();
            System.Data.Common.DataTableMapping tableMapping = new System.Data.Common.DataTableMapping();
            tableMapping.SourceTable = "Table";
            tableMapping.DataSetTable = "tableprova";
            tableMapping.ColumnMappings.Add("id", "id");
            tableMapping.ColumnMappings.Add("descrizione", "descrizione");
            tableMapping.ColumnMappings.Add("valore", "valore");
            this._adapter.TableMappings.Add(tableMapping);
            this._adapter.DeleteCommand = new System.Data.OleDb.OleDbCommand();
            this._adapter.DeleteCommand.Connection = this.Connection;
            this._adapter.DeleteCommand.CommandText = "DELETE FROM `tableprova` WHERE ((`id` = ?) AND ((? = 1 AND `descrizione` IS NULL)" +
                " OR (`descrizione` = ?)) AND ((? = 1 AND `valore` IS NULL) OR (`valore` = ?)))";
            this._adapter.DeleteCommand.CommandType = System.Data.CommandType.Text;
            this._adapter.DeleteCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_id", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "id", System.Data.DataRowVersion.Original, false, null));
            this._adapter.DeleteCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("IsNull_descrizione", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "descrizione", System.Data.DataRowVersion.Original, true, null));
            this._adapter.DeleteCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_descrizione", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "descrizione", System.Data.DataRowVersion.Original, false, null));
            this._adapter.DeleteCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("IsNull_valore", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "valore", System.Data.DataRowVersion.Original, true, null));
            this._adapter.DeleteCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_valore", System.Data.OleDb.OleDbType.Numeric, 0, System.Data.ParameterDirection.Input, ((byte)(10)), ((byte)(3)), "valore", System.Data.DataRowVersion.Original, false, null));
            this._adapter.InsertCommand = new System.Data.OleDb.OleDbCommand();
            this._adapter.InsertCommand.Connection = this.Connection;
            this._adapter.InsertCommand.CommandText = "INSERT INTO `tableprova` (`descrizione`, `valore`) VALUES (?, ?)";
            this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
            this._adapter.InsertCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("descrizione", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "descrizione", System.Data.DataRowVersion.Current, false, null));
            this._adapter.InsertCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("valore", System.Data.OleDb.OleDbType.Numeric, 0, System.Data.ParameterDirection.Input, ((byte)(10)), ((byte)(3)), "valore", System.Data.DataRowVersion.Current, false, null));
            this._adapter.UpdateCommand = new System.Data.OleDb.OleDbCommand();
            this._adapter.UpdateCommand.Connection = this.Connection;
            this._adapter.UpdateCommand.CommandText = "UPDATE `tableprova` SET `descrizione` = ?, `valore` = ? WHERE ((`id` = ?) AND ((?" +
                " = 1 AND `descrizione` IS NULL) OR (`descrizione` = ?)) AND ((? = 1 AND `valore`" +
                " IS NULL) OR (`valore` = ?)))";
            this._adapter.UpdateCommand.CommandType = System.Data.CommandType.Text;
            this._adapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("descrizione", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "descrizione", System.Data.DataRowVersion.Current, false, null));
            this._adapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("valore", System.Data.OleDb.OleDbType.Numeric, 0, System.Data.ParameterDirection.Input, ((byte)(10)), ((byte)(3)), "valore", System.Data.DataRowVersion.Current, false, null));
            this._adapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_id", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "id", System.Data.DataRowVersion.Original, false, null));
            this._adapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("IsNull_descrizione", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "descrizione", System.Data.DataRowVersion.Original, true, null));
            this._adapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_descrizione", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "descrizione", System.Data.DataRowVersion.Original, false, null));
            this._adapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("IsNull_valore", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "valore", System.Data.DataRowVersion.Original, true, null));
            this._adapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_valore", System.Data.OleDb.OleDbType.Numeric, 0, System.Data.ParameterDirection.Input, ((byte)(10)), ((byte)(3)), "valore", System.Data.DataRowVersion.Original, false, null));
        }

Come si può vedere il segnaposto per i parametri per  NpgsqlParameter è :nomecampo mentre per OleDbParameter, inoltre l'apice singolo ' che delimita campi e tabelle è mal digerito e segnalato come errore di run-time da Postgresql.

Aggiunto alcuni controlli per far stare in piedi la nostra struttura lato interfaccia utente, questo il risultato:

 

Recupero Id

Come già detto il campo id (codice) definito nella tabella è di tipo "sequence", questo fa sì che l'id "vero" è attribuito dal database facendo scattare un "trigger" sul campo che richiama una funzione di incremento.

Attualmente, per il nostro lavoro ho utilizzato currval che recupera l'ultimo id attribuito, mentre nextval fa incrementare il contatore prima di restituire l'id.

La procedura per recuperare l'id prevede l'aggiunta di un evento "RowUpdated"  e del relativo gestore:

 

       void Adapter_RowUpdated(object sender, NpgsqlRowUpdatedEventArgs e)
        {
            // testiamo se il tipo di operazione è inserimento (altrimenti avremmo dei problemi!!)
            if (e.StatementType == StatementType.Insert)
            {
                Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand("SELECT currval('tableprova_id_seq')", provaTableAdapter.Connection );
                // recuperiamo il valore di ritorno da executescalar
                System.Int64 chiave = (Int64)cmd.ExecuteScalar();
                // aggiornamento dell'id "vero" attribuito dal database
                e.Row["id"] = chiave;
            }
        }

 

 

Riferimenti

Insieme a pgAdmin III si ottiene anche un ottimo help in linea (chm) che consente di approfondire i passaggi più scabrosi della documentazione.

Mailing lists
Mailing list italiana
Manuali da Amazon
Manuali da Gorilla.it

Progetto di esempio C#
Progetto di esempio VB.NET