Postgresql e Visual Studio 2005 un esempio di gestione

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