Tipi di operazione di accesso ai dati

Capitolo 6
Query
Nel precedente capitolo abbiamo visto come collegarsi a un database. Abbiamo
parlato dell’oggetto Connection, delle sue proprietà e dei suoi metodi. Abbiamo
visto come usare tale oggetto per aprire una connessione a un database, ma
soprattutto abbiamo visto come gestire la connessione, ovvero come aprirla,
usarla e chiuderla. Si apre una connessione perché si intende compiere una o
più operazioni sul database. In questo capitolo vederemo che tipo di operazioni si possono eseguire e come possono essere eseguite con l’ausilio della tecnologia .NET.
In particolare parleremo dei seguenti argomenti:
• Quali tipi di operazione si possono eseguire su un database
• L’oggetto Command di ADO.NET: proprietà e metodi
• Query di lettura dei dati
• Query di scrittura dei dati
• Query parametriche
• Possibilità di query
Tipi di operazione di accesso ai dati
Le operazioni di accesso ai dati eseguite da un’applicazione client-server sono
essenzialmente di due tipi:
• operazioni di lettura: si esegue un’operazione di lettura dei dati per
recuperare informazioni; le operazioni di lettura non modificano la struttura
o il contenuto informativo di un database;
• operazioni di scrittura: si esegue un’operazione di scrittura per modificare o cancellare dati o per inserirne dei nuovi; le operazioni di scrittura,
quindi, modificano il contenuto informativo di un database.
Vi è, in realtà, un terzo tipo di operazioni che possono essere eseguite su un
database: sono le operazioni che modificano la struttura del database stesso.
114
Capitolo 6
Attraverso tali operazioni è possibile creare dei nuovi oggetti (ad esempio,
tabelle, viste, indici e vincoli), modificare le caratteristiche degli oggetti già
esistenti (ad esempio, modificare il formato di una colonna di una tabella o
rinominare una colonna) oppure eliminare fisicamente alcuni oggetti del database (ad esempio, droppare una tabella o una vista). Queste operazioni rientrano nella categoria dei comandi DDL del linguaggio SQL di cui si è parlato in
dettaglio nel Capitolo 3. Le operazioni DDL, però, data la portata strutturale dei
loro interventi di modifica, non sono generalmente eseguite attraverso un’applicazione client-server, ma sono demandate e riservate all’esercizio esclusivo del
DBA che le esegue attraverso l’utilizzo dei tool specifici di governo e controllo
del database. In questo capitolo, quindi, dedicheremo particolare attenzione
alle operazioni tipicamente eseguite da un’applicazione client-server, ovvero le
operazioni di lettura e scrittura dei dati (eseguite attreverso comandi di tipo
DML), pur non tralasciando qualche breve cenno alle operazioni di tipo strutturale (comandi DDL). La Figura 6.1 illustra le relazioni tra i tipi di operazione fin
qui descritti.
Le operazioni di lettura hanno essenzialmente lo scopo di estrarre da una fonte
di dati un set di informazioni. L’estrazione dei dati può essere fine a se stessa,
ovvero soddisfare le sigenze meramente informative dell’utilizzatore, ad esempio, l’interrogazione di un database per ricavare una precisa informazione, oppure può essere propedeutica e preliminare a una successiva operazione di
scrittura dei dati. In tal caso la fase di lettura è indispensabile per conoscere lo
stato dell’informazione che si intende modificare: ad esempio, l’estrazione e
visualizzazione dei dati anagrafici di un cliente o fornitore è propedeutica e
preliminare all’aggiornamento degli stessi.
Possiamo ulteriormente suddividere le operazioni di lettura, distinguendo, nell’ambito delle interrogazioni della fonte dati a fini esclusivamente informativi, le
estrazioni di dati puntuali dalle estrazioni di dati di sintesi. Le prime restituisco-
Applicazioni
transazionali
DBA
DML
DDL
Lettura dati:
• estrazione
Scrittura dati:
• modifica
• cancellazione
• inserimento
Figura 6.1
Relazioni tra tipi di operazione e modalità di gestione.
Modifica struttura/
oggetti:
• modifica
• cancellazione
• creazione
Query
115
no informazioni di dettaglio relative a un oggetto del database, come lo stato di
avanzamento di un ordine o l’indirizzo di un cliente; le seconde, invece, forniscono informazioni sintetiche relative a un fenomeno aziendale misurabile attraverso un’estrazione di dati aggregati, come la distribuzione degli ordini nelle
varie aree geografiche dei mercati trattati. In Figura 6.2 sono illustrati i diversi
tipi di operazione di lettura.
Una prima classificazione delle operazioni di scrittura è quella che tiene conto
del tipo di intervento che eseguono sui dati. Possiamo, pertanto, distinguere tra
operazioni di modifica, inserimento e cancellazione dei dati. Un’ulteriore classificazione è quella che tiene conto del modo in cui è organizzata la logica
dell’operazione di scrittura. Questa cambia radicalmente a seconda del numero
di oggetti interessati. L’intervento sui dati, infatti, può riguardare un singolo
record di una tabella, ad esempio l’inserimento di un nuovo cliente nella relativa anagrafica, o un set coerente di dati, ad esempio tutte le fatture non ancora
pagate intestate a un dato cliente. La differenza tra i due interventi non è solo di
tipo numerico, ma attiene al modo di organizzare un’operazione più o meno
complessa di scrittura dei dati. Quando l’operazione di scrittura non presenta
particolari complessità logiche, è generalmente ottenuta con un’istruzione SQL
direttamente eseguita dall’applicazione; negli altri casi l’esecuzione dell’istruzione SQL è demandata a una stored procedure.
Tipo di lettura
Scopo
Estrazione di dati puntuali
• Consultazione puntuale
• Preliminare alla modifica
Estrazione di dati aggregati
• Consultazione di sintesi
(reporting)
Figura 6.2
Tipi di operazioni di lettura dei dati.
Oggetto Command di ADO.NET
Un’applicazione che fa riferimento ai dati contenuti in un database, in particolare un’applicazione client-server, nel corso del suo funzionamento esegue un
gran numero di operazioni di accesso alla fonte dei dati. Sono sia operazioni di
lettura sia operazioni di scrittura. In entrambi i casi l’operazione vera e propria
non è eseguita dall’applicazione richiedente, ma dal DBMS gestore dei dati.
L’applicazione, ovvero il lato client del sistema client-server, si limita a generare
una richiesta di accesso ai dati che trasmette al sistema gestore dei dati stessi. È
116
Capitolo 6
quest’ultimo che si occupa di eseguire l’operazione e dare una risposta al richiedente. Il DBMS, in particolare, riceve la richiesta di accesso ai dati, ne
controlla la correttezza formale, la esegue e restituisce all’applicazione richiedente l’esito e il risultato dell’operazione stessa.
Esito e risultato sono due concetti distinti. L’esito dell’operazione è un’informazione sintetica che ci dice se l’operazione è stata portata a termine positivamente o se si sono generati errori. Il risultato, invece, nel caso in cui l’operazione
abbia avuto esito positivo, può essere sia il contenuto informativo di cui l’applicazione aveva chiesto l’estrazione, sia un riepilogo sintetico dell’operazione
eseguita, ad esempio, il numero di record modificati.
L’oggetto Command ha la funzione di trasmettere al DBMS una richiesta di accesso ai dati e di riportare all’applicazione richiedente l’esito e il risultato dell’operazione eseguita dal motore del database. Potremmo immaginarlo come un
messaggero che veicola il comando dall’applicazione al database, attende l’esito e la conclusione delle operazioni e riporta al richiedente le informazioni
richieste in caso di operazioni di lettura e, in ogni caso, l’esito posisivo o negativo della transazione. Il funzionamento dell’oggetto Command è schematizzato
in Figura 6.3.
Il framework .NET ci mette a disposizione tre diversi oggetti di tipo Command:
• SqlCommand: è progettato per interfacciare e trasmettere comandi a database di tipo Microsoft SQL Server di versione pari o superiore alla 7.0;
• OleDbCommand: è progettato per interfacciare e trasmettere comandi ai fornitori di dati del tipo OLEDB Managed Providers, come database di tipo SQL
Server 6.5 e precedenti, database Oracle e database Access;
• OdbcCommand: è progettato per interagire con una fonte dati ODBC.
Applicazione richiedente
DBMS
testo del
comando
Command
motore
• trasmette
comando
• riceve
comando
• riceve
esito e
risultato
• controlla
• esegue
• restituisce
esito e
risultato
Figura 6.3
Funzionamento dell’oggetto Command.
dati
Query
117
Creazione del comando
Per creare un oggetto Command è necessario aver prima importato il namespace
corrispondente al tipo di oggetto che si intende istanziare. Abbiamo visto come
si usa l’istruzione Imports nel capitolo precedente, quando abbiamo spiegato
come creare una connessione attraverso l’oggetto Connection. Saltiamo, quindi,
la fase di import del namespace e vediamo come istanziare e creare un oggetto
Command. Anche per l’oggetto Command, così come per l’oggetto Connection, c’è
un modo esplicito e un modo implicito per creare un nuovo oggetto.
La creazione esplicita passa attraverso l’uso dell’istruzione New:
Dim myCommand As System.Data.SqlClient.SqlCommand
myCommand = New SqlClient.SqlCommand()
oppure, avendo specificato di importare il namespace System.Data.SqlClient,
si può creare più semplicemente un nuovo oggetto Command nel seguente modo:
Dim myCommand As SqlCommand
myCommand = New SqlCommand()
La creazione implicita, invece, passa attraverso l’uso dell’oggetto Connection. In
particolare, bisogna invocarne il metodo CreateCommand:
Dim myConnection As SqlClient.SqlConnection
Dim myCommand As SqlClient.SqlCommand
‘ creazione esplicita della connessione
myConnection = New SqlClient.SqlConnection()
‘ creazione implicita del comando
‘ con il metodo CreateCommand
myCommand = Connection.CreateCommand()
Proprietà dell’oggetto Command:
la stringa di comando
La proprietà CommandText è una delle proprietà più importanti dell’oggetto Command. Contiene il testo del comando che si intende far eseguire al database.
Ovviamente il comando deve essere scritto con la corretta sintassi SQL del
DBMS che dovrà eseguire le operazioni sul database.
La proprietà può essere valorizzata al momento della creazione dell’oggetto
Command, specificando subito il testo del comando come parametro dell’istruzione di creazione, o in un secondo momento assegnando alla proprietà una stringa contenente il comando SQL. Nell’esempio che segue vediamo come valorizzare la proprietà in modo contestuale alla creazione dell’oggetto:
Dim myCommand As SqlCommand
Dim mySQL As String
‘ scrittura dell’istruzione SQL
118
Capitolo 6
mySQL = “SELECT * FROM Impiegati”
‘ creazione esplicita del comando
‘ e contestuale assegnazione della stringa SQL
myCommand = New SqlCommand(mySQL)
In alternativa, la proprietà può essere valorizzata in un secondo momento,
come vediamo nell’esempio che segue:
Dim myConnection As SqlClient.SqlConnection
Dim myCommand As SqlClient.SqlCommand
Dim mySQL As String
‘ scrittura dell’istruzione SQL
mySQL = “SELECT * FROM Impiegati”
‘ creazione esplicita della connessione
myConnection = New SqlClient.SqlConnection()
‘ creazione implicita del comando
myCommand = myConnection.CreateCommand()
‘ passaggio della stringa SQL
myCommand.CommandText = “SELECT * FROM Impiegati”
Nel secondo esempio abbiamo istanziato l’oggetto Command in modo implicito e
poi ne abbiamo valorizzato la proprietà CommandText. Non avremmo potuto
fare altrimenti perché la creazione implicta del comando non prevede il passaggio
di alcun parametro. Nel caso di creazione esplicita del comando, invece, è possibile
scegliere se passare subito la stringa SQL o se passarla in un secondo momento.
Proprietà dell’oggetto Command: la connessione
Un’applicazione può eseguire operazioni di accesso ai dati solo se è attiva una
connessione presso la fonte dati di destinazione. La connessione, infatti, rappresenta il canale attraverso cui l’applicazione interagisce con il database. L’oggetto Command, invece, è lo strumento che consente di veicolare un’istruzione
SQL dall’applicazione alla fonte dati e di riceverne il risulato. Questo, però, può
funzionare, ovvero trasmettere la sua richiesta di esecuzione di un comando al
DBMS di riferimento, solo se è attiva una connessione sul database. La dipendenza dell’oggetto Command dalla connessione è resa evidente dalla presenza
della poprietà Connection.
La proprietà Connection deve essere valorizzata con un oggetto valido di tipo
Connection. Per intenderci, la proprietà Connection è di tipo Connection. Per
valorizzarla, quindi, è necessario creare una nuova connessione e assegnarla
alla proprietà stessa. Vediamo un esempio:
Private Function LeggiImpiegati()
Dim myConnection As SqlClient.SqlConnection
Dim myCommand As SqlClient.SqlCommand
Query
Dim myStrConn As String
Dim mySQL As String
Dim myDataReader As SqlClient.SqlDataReader
Try
‘ scrittura dell’istruzione SQL
mySQL = “SELECT cognome, nome FROM Impiegati”
‘ creazione esplicita del comando
myCommand = New SqlClient.SqlCommand()
‘ creazione esplicita del DataReader
myDataReader = New SqlClient.SqlDataReader()
‘ creazione esplicita della connessione
myConnection = New SqlClient.SqlConnection()
‘ composizione della stringa di connessione
myStrConn = BuildConnectionString()
‘ assegnazione della stringa di connessione
myConnection.ConnectionString = myStrConn
‘ apertura della connessione
myConnection.Open()
‘ passaggio della connessione attiva
‘ all’oggetto Command
myCommand.Connection = myConnection
‘ assegnazione del comando SQL
myCommand.CommandText = mySQL
‘ esecuzione del comando di lettura
myDataReader = myCommand.ExecuteReader()
With myDataReader
‘ legge una riga alla volta
‘ fino alla fine dei dati estratti
Do While .Read = True
Console.Write(.Item(“Cognome”) & “, “)
Console.Write(.Item(“Nome”) & Chr(13))
Loop
End With
‘ chiusura della connessione
myConnection.Close()
Catch thisExcept As Exception
‘ se si verifica un errore
‘ viene visualizzata l’eccezione
MessageBox.Show(thisExcept.Message, _
“LeggiImpiegati()”)
End Try
End Function
119
120
Capitolo 6
La funzione LeggiImpiegati estrae i campi nome e cognome contenuti nella
tabella Impiegati e ne visualizza il contenuto nella console di output. La funzione utilizza l’oggetto myDataReader per ricevere dalla fonte dati le informazioni
estratte. L’oggetto appartiene al tipo DataReader, di cui parleremo nel dettaglio
nel prossimo capitolo, per ora ci basti sapere che è un oggetto che consente di
ricevere e gestire il risultato di una query di lettura. I dati contenuti nel DataReader possono essere solo letti, scorrendo il cursore in maniera sequenziale
dal primo record estratto all’ultimo.
Analizziamo nel dettaglio il codice appena visto. Nella prima parte sono dichiarati gli oggetti e le variabili che saranno usate nella routine. Sono necessari un
oggetto Connection, un oggetto Command e due variabili stringa, una per contenere i parameti di connessione, l’altra per contenere l’istruzione SQL:
Dim
Dim
Dim
Dim
myConnection As SqlClient.SqlConnection
myCommand As SqlClient.SqlCommand
myStrConn As String
mySQL As String
Sono valorizzate le variabili stringa contenenti i parametri di connessione e
l’istruzione SQL:
‘ scrittura dell’istruzione SQL
mySQL = “SELECT cognome, nome FROM Impiegati”
‘ composizione della stringa di connessione
myStrConn = BuildConnectionString()
Sono istanziati gli oggetti myCommand, myDataReader e myConnection:
‘ creazione esplicita del comando
myCommand = New SqlClient.SqlCommand()
‘ creazione esplicita del DataReader
myDataReader = New SqlClient.SqlDataReader()
‘ creazione esplicita della connessione
myConnection = New SqlClient.SqlConnection()
È assegnata la stringa di connessione all’oggetto myConnection ed è aperta la
connessione:
‘ assegnazione della stringa di connessione
myConnection.ConnectionString = myStrConn
‘ apertura della connessione
myConnection.Open()
A questo punto la connessione attiva può essere assegnata all’oggetto myCommand:
‘ passaggio della connessione attiva
‘ all’oggetto Command
myCommand.Connection = myConnection
Query
121
Il comando SQL è trasmesso alla fonte dati invocando il metodo ExecuteReader:
‘ esecuzione del comando di lettura
myDataReader = myCommand.ExecuteReader()
il risultato dell’operazione di lettura è assegnato all’oggetto myDataReader.
Si noti che al momento dell’esecuzione del comando la proprietà Connection
dell’oggetto myCommand è valorizzata con una connessione attiva. Se la connessione non fosse stata attiva, l’esecuzione del comando ExecuteReader avrebbe
generato un errore. Non è importante che la connessione sia attivata prima del
passaggio all’oggetto myCommand, l’attivazione, infatti, può avvenire anche in un
secondo momento, purché sia prima dell’esecuzione del metodo ExecuteReader:
‘ esecuzione del metodo Open
‘ della proprietà Connection
myCmd.Connection.Open()
I dati estratti sono visualizzati nella console di output di Visual Studio:
With myReader
‘ legge una riga alla volta
‘ fino alla fine dei dati estratti
Do While .Read = True
Console.Write(.Item(“Cognome”) & “, “)
Console.Write(.Item(“Nome”) & Chr(13))
Loop
End With
L’istruzione Chr(13) è il CharCode del codice 13 (corrispondente al tasto Invio),
serve per andare alla riga successiva dopo ogni record visualizzato.
Infine, prima della conclusione della funzione, la connessione deve essere chiusa
invocandone il metodo Close:
‘ chiusura della connessione
myConnection.Close()
In alternativa, il metodo Close poteva essere invocato attraverso la proprietà
Connection dell’oggetto myCommand:
‘ chiusura della connessione
‘myCommand.Connection.Close()
Proprietà dell’oggetto Command: il tipo di comando
L’oggetto Command è in grado di gestire sia istruzioni SQL sia chiamate a stored
procedure. In entrambi i casi il comando deve essere scritto nella proprietà
CommandText. L’oggetto Command, però, ha bisogno di sapere come interpretare
il testo di comando contenuto nella sua proprietà. L’informazione sul tipo di
comando è specificata nella proprietà CommandType.
122
Capitolo 6
La proprietà può assumere uno dei tre valori definiti per il CommandType. Di
seguito sono elencati tali valori con la relativa descrizione:
Tabella 6.1
Valore
Descrizione
Text
È il valore assegnato di default alla proprietà; fa sì che l’oggetto
Command interpreti il testo del comando come un’istruzione SQL.
Indica che il testo del comando contiene la chiamata a una stored
procedure.
Il valore non è valido in caso di Managed Provider di tipo SQL Server;
se il tipo di comando è TableDirect è necessario che la proprietà
CommandText contenga il nome di una tabella; in tal caso il metodo
ExecuteReader restituisce tutti i campi e tutti i record della tabella
referenziata.
StoredProcedure
TableDirect
Nella funzione LeggiImpiegati dell’esempio precedente abbiamo usato un comando di tipo SQL, ma non abbiamo valorizzato la proprietà CommandType perché il suo valore era per default già impostato su Text. Vediamo come avremmo
dovuto scrivere il codice nel caso in cui non fosse previsto il valore di default:
‘ definizione del tipo di comando per istruzioni SQL
myCommand.CommandType = CommandType.Text
Se, invece di un’istruzione SQL, avessimo voluto eseguire una chiamata a una
stored procedure avremmo dovuto definire la proprietà CommandType in questo
modo:
‘ definizione del tipo di comando per
‘ chiamate a stored procedure
myCommand.CommandType = CommandType.StoredProcedure
Possiamo fare un esempio di chiamata di stored procedure, provando a
usare la stored procedure Clienti e fornitori per città del database
Northwind, che abbiamo fatto migrare alla versione SQL Server nel precedente capitolo. La procedura è molto semplice: esegue due operazioni di
SELECT, una sulla tabella Clienti e una sulla tabella Fornitori, e riunisce i
dati estratti in un unico set di dati con un’istruzione UNION. È possibile
leggere il codice del-la stored procedure attraverso il tool Server Explorer
fornito da Visual Studio .NET: basta navigare all’interno della struttura ad
albero raffigurante i vari tipi di oggetti del database Northwind e aprire il
dettaglio della voce Stored Procedures; se siete fortunati troverete la procedura Clienti e fornitori per città ; per visualizzarne il codice è sufficiente selezionare la voce Modifica Stored Procedure (o Edit Stored Procedure se avete la versione inglese di Visual Studio); dovreste riuscire a vedere
il codice riportato nella Figura 6.4.
Query
123
Figura 6.4
Stored procedure “Clienti e fornitori per città”.
Riprendiamo, quindi, la precedente funzione LeggiImpiegati e modifichiamola
per leggere la stored procedure Clienti e fornitori per città; le cambiamo
il nome e la chiamiamo LeggiClientiFornitori:
Private Function LeggiClientiFornitori()
Dim
Dim
Dim
Dim
Dim
myCommand As SqlClient.SqlCommand
myConnection As SqlClient.SqlConnection
myDataReader As SqlClient.SqlDataReader
myStrConn As String
mySQL As String
Try
‘ il testo del comando contiene la
‘ chiamata alla stored procedure
mySQL = “[Clienti e fornitori per città]”
‘ creazione esplicita del comando
‘ e assegnazione della stringa SQL
myCommand = New SqlClient.SqlCommand(mySQL)
‘ creazione implicita del comando
‘myCommand= myConnection.CreateCommand()
‘ creazione esplicita della connessione
myConnection = New SqlClient.SqlConnection()
myStrConn = BuildConnectionString()
myConnection.ConnectionString = myStrConn
myConnection.Open()
‘ passaggio della connessione attiva
‘ all’oggetto Command
myCommand.Connection = myConnection
‘myCommand.Connection.Open()
‘ definizione del tipo di comando
myCommand.CommandType = CommandType.StoredProcedure
124
Capitolo 6
‘ esegue il comando di lettura
myDataReader = myCommand.ExecuteReader()
With myDataReader
‘ legge una riga alla volta
‘ fino alla fine dei dati estratti
Do While .Read = True
Console.Write(.Item(“Città”) & “, “)
Console.Write(.Item(“NomeSocietà”) & “, “)
Console.Write(.Item(“Contatto”) & “, “)
Console.Write(.Item(“Relazione”) & Chr(13))
Loop
End With
‘ invoca il metodo Close per chiudere la connessione
myConnection.Close()
Catch thisExcept As Exception
‘ se il tentativo di connessione fallisce
‘ viene visualizzata l’eccezione
MessageBox.Show(thisExcept.Message, _
“LeggiClientiFornitori()”)
End Try
End Function
Commentiamo solo le parti di codice nuove e tralasciamo quelle note. Innanzitutto, è cambiato il testo del comando, non contiene più un’istruzione SQL, ma
la chiamata alla stored procedure:
‘ il testo del comando contiene la
‘ chiamata alla stored procedure
mySQL = “[Clienti e fornitori per città]”
Il tipo di comando è impostato sul valore StoredProcedure:
‘ definizione del tipo di comando
myCommand.CommandType = CommandType.StoredProcedure
Nella console di output sono visualizzati i campi pubblicati dalla stored procedure:
With myDataReader
‘ legge una riga alla volta
‘ fino alla fine dei dati estratti
Do While .Read = True
Console.Write(.Item(“Città”) & “, “)
Console.Write(.Item(“NomeSocietà”) & “, “)
Console.Write(.Item(“Contatto”) & “, “)
Console.Write(.Item(“Relazione”) & Chr(13))
Loop
End With
Query
125
Proprietà dell’oggetto Command:
il tempo di esecuzione
Può essere settato un tempo massimo, espresso in secondi, di esecuzione del
comando. Il valore è definito valorizzando la proprietà CommandTimeout. La
proprietà ha per default il valore di trenta secondi. Se l’esecuzione del comando
supera il tempo massimo definito dalla proprietà CommandTimeout l’oggetto genera un’eccezione di tipo SqlException.
Negli esempi precedenti avremmo potuto definire un tempo massimo di esecuzione inferiore al valore di default:
‘ definizione del tempo massimo di esecuzione
myCommand.CommandTimeout = 10
Potete provare a eseguire il codice della funzione LeggiImpiegati dopo aver
aggiunto la riga di codice appena vista prima dell’esecuzione del comando di
lettura.
I metodi dell’oggetto Command:
l’esecuzione del comando
I metodi più importanti dell’oggetto Command sono quelli che eseguono il comando specificato nella proprietà CommandText. Sappiamo che in realtà il comando vero e proprio è eseguito dal DBMS gestore della fonte dati, per cui
l’invocazione del metodo di esecuzione ha solo la funzione di trasmettere al
database la richiesta di esecuzione dell’operazione sui dati.
Possiamo classificare i metodi di esecuzione del comando in due gruppi: metodi che eseguono comandi di lettura e metodi che eseguono operazioni di scrittura. Al primo tipo appartengono i metodi ExecuteReader ed ExecuteXmlReader. I due metodi si assomigliano molto, la differenza principale sta nel fatto
che il metodo ExecuteXmlReader restituisce i dati sotto forma di un oggetto
XML. Per il significato e le caratteristiche degli oggetti XML vi rimandiamo all’appendice D, per il momento ci basti sapere che il contenuto informativo non
cambia, cambia solo il modo in cui i dati sono rappresentati e trasmessi. Per
quanto riguarda l’esecuzione di comandi di scrittura, invece, abbiamo solo il
metodo ExecuteNonQuery.
Negli esempi precedenti abbiamo usato metodi di esecuzione dei comandi sia
di scrittura sia di lettura. Nel capitolo 5, infatti, abbiamo inserito dei record nella
tabella Impiegati, mentre negli esempi proposti in questo capitolo abbiamo
visto come leggere tali dati. Abbiamo usato, infatti, sia il metodo ExecuteReader
sia il metodo ExecuteNonQuery. Vediamo ora nel dettaglio le caratteristiche di
entrambi.
Il metodo ExecuteReader trasmette alla fonte dati di riferimento il comando di
lettura contenuto nella proprietà CommandText e trasferisce i dati estratti in un
oggetto di tipo DataReader. È necessario che la proprietà CommandType sia settata con il valore Text. L’esecuzione del comando consente di specificare un
126
Capitolo 6
parametro che specifica il comportamento (CommandBehavior) dell’oggetto DataReader generato dal metodo ExecuteReader. Di seguito elenchiamo i possibili valori del parametro CommandBehavior:
Tabella 6.2
Valore
Descrizione
CloseConnection
Fa sì che la connessione associata al comando, ovvero l’oggetto
Connection associato alla proprietà Connection, sia chiusa non appena terminata la lettura del DataReader.
Fa sì che il Data Provider non restituisca alcun record di dati ma solo
la struttura dei campi letti.
Fa sì che a ogni lettura di dati sia sempre aggiunta una colonna con la
primary key dei dati estratti
Il Data Provider restituisce un risultato singolo.
Il Data Provider restituisce solo i dati del primo record letto.
SchemaOnly
KeyInfo
SingleResult
SingleRow
Proviamo qualche CommandBehavior. Facciamo una prova, ad esempio, con il
valore SingleRow. Riprendiamo la funzione LeggiImpiegati e invochiamo il
metodo ExecuteReader passando il valore SingleRow come parametro CommandBehavior:
‘ esecuzione del comando di lettura
myDataReader = _
myCommand.ExecuteReader(CommandBehavior.SingleRow)
Proviamo a eseguire il codice. Nella console di output di Visual Studio saranno
visualizzati i dati di un solo impiegato.
Il metodo ExecuteNonQuery deve essere usato, invece, nei casi in cui il comando contenuto nella proprietà CommandText non contenga un’istruzione SQL di
lettura. In alternativa la proprietà può contenere un’istruzione di scrittura di dati
o un’istruzione di intervento sulla struttura del database (istruzione SQL di tipo
DDL). In entrambi i casi la fonte dati non restituisce alcun set di dati, ma
un’informazione sintetica sull’esito dell’operazione. Nel caso di istruzioni di
scrittura dei dati (INSERT, UPDATE, DELETE) il metodo restituisce l’informazione sul numero di record interessati dall’operazione; nel caso di istruzioni di tipo
DDL, invece, il metodo restituisce l’informazione sull’esito positivo o negativo
dell’istruzione.
Cominciamo con un esempio di istruzione SQL di tipo DML, proviamo cioè a
inserire dei record nella tabella Impiegati.
Private Function InserisciImpiegato()
Dim
Dim
Dim
Dim
myCommand As SqlClient.SqlCommand
myConnection As SqlClient.SqlConnection
myStrConn As String
mySQL As String
Query
127
Dim nroRecord As Integer
Try
‘ il testo del comando contiene
‘ un’istruzione SQL di inserimento record
mySQL = “INSERT INTO Impiegati (cognome,nome) “ & _
“VALUES(‘Padovano’,’Antonio’);” & _
“INSERT INTO Impiegati (cognome,nome) “ & _
“VALUES(‘Bellè’,’Paolo’)”
‘ creazione esplicita del comando
‘ e assegnazione della stringa SQL
myCommand = New SqlClient.SqlCommand(mySQL)
‘ creazione implicita del comando
‘myCommand= myConnection.CreateCommand()
‘ creazione esplicita della connessione
myConnection = New SqlClient.SqlConnection()
myStrConn = BuildConnectionString()
myConnection.ConnectionString = myStrConn
myConnection.Open()
‘ passaggio della connessione attiva
myCommand.Connection = myConnection
‘ definizione del tipo di comando
myCommand.CommandType = CommandType.Text
‘ esegue il comando di scrittura e
‘ l’esito è assegnato alla variabile nroRecord
nroRecord = myCommand.ExecuteNonQuery()
Console.Write(“Nro record inseriti: “ & nroRecord)
‘ invoca il metodo Close per chiudere la connessione
myConnection.Close()
Catch thisExcept As Exception
‘ se il tentativo di connessione fallisce
‘ viene visualizzata l’eccezione
MessageBox.Show(thisExcept.Message, _
“InserisciImpiegato()”)
End Try
End Function
La funzione InserisciImpiegato è sostanzialmente uguale alle altre già viste in
questo capitolo, commentiamo, quindi, solo le parti nuove. La proprietà CommandText contiene un’istruzione SQL che inserisce due record nella tabella Impiegati:
‘ il testo del comando contiene
‘ un’istruzione SQL di inserimento record
mySQL = “INSERT INTO Impiegati (cognome,nome) “ & _
“VALUES(‘Padovano’,’Antonio’);” & _
128
Capitolo 6
“INSERT INTO Impiegati (cognome,nome) “ & _
“VALUES(‘Bellè’,’Paolo’)”
Il metodo di esecuzione del comando è specifico per le istruzioni di scrittura e
il risultato dell’operazione è assegnato alla variabile numerica nroRecord:
‘ esegue il comando di scrittura e
‘ l’esito è assegnato alla variabile nroRecord
nroRecord = myCommand.ExecuteNonQuery()
Il numero di record inseriti, ovvero il valore della variabile nroRecord, è visualizzato nella console di output:
Console.Write(“Nro record inseriti: “ & nroRecord)
Possiamo provare ora a eseguire un’istruzione SQL di tipo DDL. Scriviamo una
funzione che crei la tabella Nipoti con le seguenticaratteristiche :
Tabella 6.3
Campo
Tipo (datatype)
Cognome
Nome
Città
varchar 30 caratteri
varchar 30 caratteri
varchar 30 caratteri
Di seguito, il codice della funzione CreaTabella:
Private Function CreaTabella()
Dim
Dim
Dim
Dim
Dim
myCommand As SqlClient.SqlCommand
myConnection As SqlClient.SqlConnection
Esito As Integer
myStrConn As String
mySQL As String
Try
‘ il testo del comando contiene
‘ un’istruzione SQL di tipo DDL
mySQL = “CREATE TABLE Nipoti “ & _
“(cognome VARCHAR(30), “ & _
“nome VARCHAR(30), “ & _
“città VARCHAR(30))”
‘ creazione esplicita del comando
‘ e assegnazione della stringa SQL
myCommand = New SqlClient.SqlCommand(mySQL)
‘ creazione implicita del comando
‘myCommand= myConnection.CreateCommand()
‘ creazione esplicita della connessione
myConnection = New SqlClient.SqlConnection()
Query
129
myStrConn = BuildConnectionString2()
myConnection.ConnectionString = myStrConn
myConnection.Open()
‘ passaggio della connessione attiva
myCommand.Connection = myConnection
‘myCommand.Connection.Open()
‘ definizione del tipo di comando
myCommand.CommandType = CommandType.Text
‘ esegue il comando di creazione della tabella
‘ e l’esito è assegnato alla variabile Esito
Esito = myCommand.ExecuteNonQuery()
Console.Write(“Esito DDL: “ & Esito)
‘ invoca il metodo Close per chiudere la connessione
myConnection.Close()
Catch thisExcept As Exception
‘ se il tentativo di connessione fallisce
‘ viene visualizzata l’eccezione
MessageBox.Show(thisExcept.Message, _
“CreaTabella()”)
End Try
End Function
Anche stavolta tralasciamo il codice che già conosciamo e analizziamo solo le
parti nuove. Cominciamo dall’istruzione SQL:
‘ il testo del comando contiene
‘ un’istruzione SQL di tipo DDL
mySQL = “CREATE TABLE Nipoti “ & _
“(cognome VARCHAR(30), “ & _
“nome VARCHAR(30), “ & _
“città VARCHAR(30))”
L’istruzione crea la tabella Nipoti composta dai campi cognome, nome e città;
tutti e tre i campi sono del tipo VARCHAR e hanno una lunghezza di 30 caratteri. Un campo dichiarato VARCHAR(30), a differenza di uno definito CHAR(30),
se occupato da una stringa di lunghezza inferiore a 30 caratteri occupa soltanto
il numero di byte effettivi della stringa. È consigliabile utilizzare il datatype
VARCHAR, quindi, nei casi in cui si prevede una grande variabilità nella popolazione di un campo.
Il metodo di esecuzione del comando è anche stavolta quello specifico per le
istruzioni di scrittura. Il risultato dell’operazione di creazione della tabella è
assegnato alla variabile numerica Esito:
‘ esegue il comando di creazione della tabella
‘ e l’esito è assegnato alla variabile Esito
Esito = myCommand.ExecuteNonQuery()
130
Capitolo 6
L’esito della creazione della tabella, ovvero il valore della variabile Esito, è
visualizzato nella console di output:
Console.Write(“Esito DDL: “ & Esito)
A conferma del buon esito dell’istruzione DDL il valore del codice di ritorno
deve essere –1, per cui nella console di output si dovrebbe leggere il seguente
messaggio:
Esito DDL: -1
Se ancora non vi fidate e volete avere la certezza di aver creato la tabella, potete
controllare usando il Server Explorer: troverete la tabella Nipoti con i campi
cognome, nome e città all’interno della sezione Tables del database Northwind.
Ricordiamo, comunque, che è alquanto improbabile che in un’applicazione
client-server interfacciata da utenti operativi si eseguano operazioni di tipo DDL.
Tipicamente queste sono demandate al DBA, che le esegue usando i tool specifici rilasciati dal DBMS su cui opera e solo per interventi strutturali sul database.
Query parametriche
Negli esempi di accesso ai dati che abbiamo visto finora non abbiamo mai
avuto bisogno di specificare l’insieme dei dati su cui intendevamo eseguire le
nostre operazioni. Abbiamo inserito dei record nella tabella Impiegati, ne abbiamo letto tutti i record, abbiamo creato la tabella Nipoti.
In particolare, negli esempi di operazioni di lettura non abbiamo avuto bisogno
di filtrare i dati da estrarre, così come non abbiamo avuto bisogno di specificare
su quali dati intendevamo intervenire negli esempi di operazioni di scrittura. La
realtà lavorativa è molto più varia. È molto frequente, infatti, che sia necessario
leggere o modificare solo alcuni record di una tabella, ponendo, cioè, dei filtri
alle query che si intendono eseguire verso il database e definendo dei criteri in
grado di delimitare il set di dati oggetto dell’operazione. Quest’esigenza si soddisfa generalmente apponendo delle clausole WHERE o HAVING alle istruzioni
SQL che si eseguono su un database. Nel capitolo 3 abbiamo visto vari esempi
di utilizzo di tali clausole, per cui in questo capitolo ne faremo uso senza
soffermarci troppo sul loro significato e sulle loro differenze. La costruzione
delle clausole WHERE o HAVING prevede la concatenazione di tre elementi: il
nome del campo da filtrare, un operatore logico di confronto e un valore di
confronto, che può essere anche il nome di un altro campo.
Non solo, quando abbiamo eseguito delle operazioni di inserimento di record
in una tabella, abbiamo scritto all’interno della query quelli che dovevano essere i valori che avrebbero dovuto assumere alcuni campi dei nuovi record. Abbiamo, cioè, composto la stringa di comando in modo che fosse specifica per
l’inserimento di quel determinato record. Se volessimo, ora, riciclare quel codice per inserire un record diverso, dovremmo riscrivere la stringa di SQL specificando valori differenti.
Query
131
Facciamo subito alcuni esempi. Riprendiamo la funzione InserisciImpiegato
e modifichiamo la stringa di comando per inserire un nuovo impiegato:
‘ istruzione SQL per l’inserimento di un nuovo record
mySQL = “INSERT INTO Impiegati (cognome,nome) “ & _
“VALUES(‘Menozzi’,’Alessandro’)”
Eseguiamo il codice. Il risultato è l’inserimento nella tabella Impiegati di un
nuovo record con i campi cognome e nome valorizzati. Notiamo che, per eseguire un’operazione pressoché identica alla precedente, dobbiamo modificare
parte del codice.
Facciamo un altro esempio. La tabella Impiegati possiede svariati campi che
nella precedente operazione di inserimento non abbiamo valorizzato. Ci proponiamo, quindi, in una successiva operazione di aggiornamento, di valorizzarne
alcuni. Creiamo, pertanto, la funzione AggiornaImpiegati che ci consente di
aggiornare i campi del record appena inserito. Copiamo il codice della funzione
InserisciImpiegato e sostituiamo la stringa di comando con la seguente:
‘ il testo del comando contiene
‘ un’istruzione SQL di modifica di un record
mySQL = “UPDATE Impiegati “ & _
“SET posizione = ‘collega’, “ & _
“città = ‘Zibello’, “ & _
“zona = ‘Pieveottoville’ “ & _
“WHERE cognome = ‘Menozzi’ “ & _
“AND nome = ‘Alessandro’”
Sostituiamo inoltre la scrittura del messaggio nella console con il seguente:
‘ visualizzazione del nro di record aggiornati
Console.Write(“Nro record aggiornati: “ & nroRecord)
Eseguiamo il codice. Vedremo nella console di output il seguente messggio:
Nro record aggiornati: 1
Il comando SQL è l’unico elemento di novità della funzione cha abbiamo appena creato. Analizziamone in dettaglio la struttura. Nella prima parte troviamo
l’istruzione UPDATE seguita dai campi oggetto della modifica, ognuno associato al valore che si vuole che il campo assuma. Nella seconda parte sono specificate due clausole WHERE legate dall’operatore logico AND, una relativa al
campo cognome, l’altra relativa al campo nome. Il comando, quindi, chiede al
DBMS di riferimento di modificare tutti i record della tabella Impiegati in cui il
campo cognome contiene il valore ‘Menozzi’ e il campo nome il valore ‘Alessandro’, scrivendo il valore ‘collega’ nel campo posizione, il valore ‘Zibello’ nel
campo città e il valore ‘Pieveottoville’ nel campo zona.
Le funzioni InserisciImpiegato e AggiornaImpiegati che abbiamo appena
visto sono due pessimi esempi di scrittura di codice. Sono, infatti, costruite su
misura per l’inserimento e la modifica di un record con specifici valori. Se
volessimo utilizzare la stessa struttura per trattare record con valori differenti,
132
Capitolo 6
dovremmo riscriverle modificando la stringa SQL. Ovviamente, avendo gli esempi
una valenza prettamente didattica, ci sentiamo in un certo senso perdonati, ma
per fare ammenda ci proponiamo di suggerire delle modalità più dinamiche per
strutturare il codice.
Ad esempio, potremmo rendere più dinamica l’interazione tra codice e database separando la parte di codice dedicata all’accesso alla fonte dati dalla parte di
logica applicativa, che determina il contenuto della stringa di comando e decide l’accesso ai dati. Modifichiamo, pertanto, la precedente funzione AggiornaImpiegati e creiamo la funzione EseguiWrite specifica per l’esecuzione della
query di scrittura:
Private Function EseguiWrite( _
ByVal pSQL As String) As Integer
Dim
Dim
Dim
Dim
myCommand As
myConnection
nroRecord As
myStrConn As
SqlClient.SqlCommand
As SqlClient.SqlConnection
Integer
String
Try
‘ creazione esplicita del comando
‘ e assegnazione della stringa SQL
myCommand = New SqlClient.SqlCommand(pSQL)
‘ creazione esplicita della connessione
myConnection = New SqlClient.SqlConnection()
myStrConn = BuildConnectionString()
myConnection.ConnectionString = myStrConn
myConnection.Open()
‘ passaggio della connessione attiva
‘ all’oggetto Command
myCommand.Connection = myConnection
‘ definizione del tipo di comando
myCommand.CommandType = CommandType.Text
‘ esecuzione del comando di scrittura
EseguiWrite = myCommand.ExecuteNonQuery()
‘ invoca il metodo Close per chiudere la connessione
myConnection.Close()
Catch thisExcept As Exception
‘ se il tentativo di connessione fallisce
‘ viene visualizzata l’eccezione
MessageBox.Show(thisExcept.Message, _
“EseguiWrite()”)
End Try
End Function
Query
133
Private Sub AggiornaImpiegati()
Dim nroRecord As Integer
Dim mySQL As String
Try
‘ il testo del comando contiene
‘ un’istruzione SQL di modifica di un record
mySQL = “UPDATE Impiegati “ & _
“SET posizione = ‘collega’, “ & _
“città = ‘Zibello’, “ & _
“zona = ‘Pieveottoville’ “ & _
“WHERE cognome = ‘Menozzi’ “ & _
“AND nome = ‘Alessandro’”
‘ chiamata della funzione che esegue
‘ l’operazione di scrittura
nroRecord = EseguiWrite(mySQL)
MessageBox.Show(“Nro Record aggiornati: “ & _
nroRecord, “AggiornaImpiegati()”)
Catch thisExcept As Exception
‘ se il tentativo di connessione fallisce
‘ viene visualizzata l’eccezione
MessageBox.Show(thisExcept.Message, _
“AggiornaImpiegati()”)
End Try
End Sub
La funzione EseguiWrite ha come parametro di input la stringa pSQL destinata
a contenere il comando SQL. La funzione contiene tutti i comandi specifici
dell’accesso ai dati. Il comando di esecuzione della query è eseguito invocando
il metodo ExecuteNonQuery e l’esito del comando, ovvero il numero di record
aggiornati, è asssegnato alla funzione stessa.
‘ esecuzione del comando di scrittura
EseguiWrite = myCommand.ExecuteNonQuery()
Nella funzione AggiornaImpiegati, invece, è scomparso ogni riferimento al
codice specifico dell’accesso ai dati. Rimane solo la scrittura della stringa SQL
nella variabile mySQL. L’esecuzione del comando è demandata alla funzione
EseguiWrite chiamata trasferendo la variabile mySQL come parametro di input.
L’esito dell’operazione è comunicato alla procedura chiamante che provvede a
registrare il valore nella variabile nroRecord.
‘ chiamata della funzione che esegue
‘ l’operazione di scrittura
nroRecord = EseguiWrite(mySQL)
134
Capitolo 6
Per ultimo, la funzione AggiornaImpiegati segnala il numero di record aggiornati mediante un messaggio che visualizza la variabile nroRecord:
MessageBox.Show(“Nro Record aggiornati: “ & _
nroRecord, “AggiornaImpiegati()”)
La funzione EseguiWrite può essere chiamata da qualsiasi procedura dell’applicazione purché sia passato come parametro di input un comando SQL sintatticamente corretto. L’onere della definizione del comando è comunque demandato alla procedura chiamante, che deve comporre in modo dinamico una
stringa di comando da passare alla funzione che eseguirà l’accesso ai dati. È
senz’altro un passo avanti, ma si può fare di meglio.
Un altro modo per rendere più dinamica la scrittura di codice di accesso ai dati
è l’utilizzo di query parametriche. In una query parametrica, al posto del valore
dei campi o dei filtri, è indicato il nome di un parametro. Facciamo subito un
esempio. L’istruzione SQL di inserimento di un record vista prima nella funzione AggiornaImpiegati diventa:
‘ istruzione SQL parametrica
mySQL = “UPDATE Impiegati “ & _
“SET posizione = @posizione, “ & _
“città = @città, “ & _
“zona = @zona “ & _
“WHERE cognome = @cognome “ & _
“AND nome = @nome”
Come si vede, al posto delle stringhe contenenti i valori dei campi posizione,
città e zona che si vogliono aggiornare e al posto del valore dei filtri sui campi
cognome e nome, si trovano dei nomi di variabili preceduti dal simbolo @. Le
variabili in questione sono i parametri della query. Prima di fare ulteriori esempi di scrittura di query parametriche, però, è necessario spiegare il significato e
il funzionamento dei parametri all’interno dell’oggetto Command.
Proprietà dell’oggetto Command:
la collection dei parametri
L’oggetto Command possiede la proprietà Parameters. È una proprietà di tipo
collection e può essere popolata con oggetti di tipo Parameter. L’oggetto
Parameter, quindi, è il componente elementare della collection dei parametri e
delle query parametriche. Vediamo quali sono le sue proprietà:
Tabella 6.4
Proprietà
Descrizione
ParameterName
È il nome del parametro.
È il datatype generico del parametro; può essere scelto tra uno di quelli
specificati dall’elenco del framework .NET System.Data.DbType.
DbType
Query
135
Proprietà
Descrizione
OleDbType
È il datatype specifico del data provider .NET; le proprietà DbType e
OleDbType sono correlate internamente, per cui se si cambia il valore
della prima si aggiorna automaticamente il valore della seconda.
È il valore che si assegna al parametro.
Indica se il parametro è di input, di output o se è un parametro di
ritorno di una stored procedure.
Indica se il parametro può accettare valore nullo.
Indica la grandezza massima in byte del parametro.
In caso di parametri numerici, può indicare il numero massimo di cifre
intere.
In caso di parametri numerici, può indicare il numero massimo di cifre
decimali.
Value
Direction
IsNullable
Size
Precision
Scale
La proprietà Parameters possiede dei metodi e delle proprietà comuni a tutte le
collection di Visual Basic. In particolare, possiede il metodo Add, attraverso
cui è possibile aggiungere un nuovo Item (nel nostro caso di tipo Parameter);
possiede ovviamente la poprietà Item, che consente di referenziare un particolare elemento della collection; possiede i metodi Insert e Remove, attraverso
i quali è possibile inserire o rimuovere un Item preciso dalla collection, e il
metodo Clear, che consente di svuotare rapidamente l’intera collection. Facciamo alcuni esempi. Un oggetto parametro si istanzia in questo modo:
Dim myParameter As SqlClient.SqlParameter
‘ creazione esplicita
myParameter = New SqlClient.SqlParameter()
oppure, avendo importato il namespace, più semplicemente:
Dim myParameter As SqlParameter
‘ creazione esplicita
myParameter = New SqlParameter()
Si può istanziare un oggetto Parameter anche in modo implicito, usando il
metodo CreateParameter dell’oggetto Command:
Dim myCommand As SqlCommand
Dim myParameter As SqlParameter
myCommand = New SqlCommand()
‘ creazione implicita
myParameter = myCommand.CreateParameter()
L’aggiunta di un parametro alla collection dei parametri, ovvero alla proprietà
Parameters, è molto semplice; basta invocare il metodo Add passando l’oggetto
Parameter:
Dim myCommand As SqlCommand
Dim myParameter As SqlParameter
myCommand = New SqlCommand()
136
Capitolo 6
myParameter = New SqlParameter()
‘ aggiunge un parametro in coda all’elenco
myCommand.Parameters.Add(myParameter)
Si può aggiungere un parametro alla collection specificando l’esatta posizione
in cui inserirlo usando il metodo Insert. Nell’esempio che segue si inserisce un
parametro nella posizione 0 della collection, ovvero in cima all’elenco:
Dim myCommand As SqlCommand
Dim myParameter As SqlParameter
myCommand = New SqlCommand()
myParameter = New SqlParameter()
‘ aggiunge un parametro in cima all’elenco
myCommand.Parameters.Insert(0, myParameter)
Per rimuovere il parametro di cui si conosce l’esatta posizione si può usare il
metodo RemoveAt:
Dim myCommand As SqlCommand
Dim myParameter As SqlParameter
myCommand = New SqlCommand()
myParameter = New SqlParameter()
‘ aggiunge un parametro in cima all’elenco
myCommand.Parameters.Insert(0, myParameter)
‘ rimuove il primo parametro della lista
myCommand.Parameters.RemoveAt(0)
Per svuotare la collection dei parametri è sufficiente invocare il metodo Clear:
Dim myCommand As SqlCommand
myCommand = New SqlCommand()
‘ svuota l’intera collection dei parametri
myCommand.Parameters.Clear()
Ora che abbiamo le idee un po’ più chiare sulle proprietà dell’oggetto Parameter e sul funzionamento della collection Parameters possiamo vedere come
sfruttarne le potenzialità per gestire in modo efficiente l’accesso ai dati. Possiamo, infatti, strutturare il codice separando la parte di definizione della query
dalla parte specifica di connessione ed esecuzione del comando. Per quanto
riguarda la parte di definizione della query, possiamo evitare di riscrivere il
testo del comando SQL, in quanto, grazie alle query parametriche, possiamo
limitarci a passare all’oggetto Command l’elenco dei parametri richiesti dalla query specificando il valore di ognuno di essi.
Private Function AggiornaImpiegatiParametrica()
Dim nroRecord As Integer
Dim mySQL As String
Dim myCommand As SqlClient.SqlCommand
Try
Query
‘ il testo del comando contiene
‘ un’istruzione SQL di inserimento record
mySQL = “UPDATE Impiegati “ & _
“SET posizione = @posizione, “ & _
“città = @città, “ & _
“zona = @zona “ & _
“WHERE cognome = @cognome “ & _
“AND nome = @nome”
myCommand = New SqlClient.SqlCommand()
‘ invoca la routine che aggiunge un parametro alla
‘ collection dei parametri dell’oggetto myCommand
AggiungiParametro(myCommand, “@posizione”, & _
SqlDbType.VarChar, “collega”)
AggiungiParametro(myCommand, “@città”, & _
SqlDbType.VarChar, “Zibello”)
AggiungiParametro(myCommand, “@zona”, & _
SqlDbType.VarChar, “Pieveottoville”)
AggiungiParametro(myCommand, “@cognome”, & _
SqlDbType.VarChar, “Menozzi”)
AggiungiParametro(myCommand, “@nome”, & _
SqlDbType.VarChar, “Alessandro”)
‘ invocazione della funzione che esegue la query
nroRecord = EseguiWriteParametrica(mySQL, myCommand)
MessageBox.Show(“Nro Record aggiornati: “ & _
nroRecord, “ AggiornaImpiegatiParametrica()”)
Catch thisExcept As Exception
‘ se il tentativo di connessione fallisce
‘ viene visualizzata l’eccezione
MessageBox.Show(thisExcept.Message, _
“ AggiornaImpiegatiParametrica()”)
End Try
End Function
Private
ByRef
ByVal
ByVal
ByVal
Sub AggiungiParametro( _
pCommand As SqlClient.SqlCommand, _
pNomeParametro As String, _
pDbType As SqlDbType, _
pValore As String)
Dim myParameter As SqlClient.SqlParameter
Try
‘ creazione implicita del parametro
myParameter = pCommand.CreateParameter()
‘ valorizzazione delle proprietà del parametro
myParameter.ParameterName = pNomeParametro
137
138
Capitolo 6
myParameter.SqlDbType = pDbType
myParameter.Value = pValore
‘ aggiunge il parametro alla collection
pCommand.Parameters.Add(myParameter)
Catch thisExcept As Exception
‘ se il tentativo di connessione fallisce
‘ viene visualizzata l’eccezione
MessageBox.Show(thisExcept.Message, _
“AggiungiParametro()”)
End Try
End Sub
Private Function EseguiWriteParametrica( _
ByVal pSQL As String, _
ByVal pCommand As SqlClient.SqlCommand) As Integer
Dim myConnection As SqlClient.SqlConnection
Dim nroRecord As Integer
Dim myStrConn As String
Try
‘ creazione esplicita della connessione
myConnection = New SqlClient.SqlConnection()
myStrConn = BuildConnectionString()
myConnection.ConnectionString = myStrConn
myConnection.Open()
‘ passaggio della connessione attiva
‘ all’oggetto Command
pCommand.Connection = myConnection
‘ definizione del tipo di comando
pCommand.CommandType = CommandType.Text
‘ assegnazione della stringa comando
pCommand.CommandText = pSQL
‘ esegue il comando di scrittura
EseguiWriteParametrica = pCommand.ExecuteNonQuery()
‘ invoca il metodo Close per chiudere la connessione
myConnection.Close()
Catch thisExcept As Exception
‘ se il tentativo di connessione fallisce
‘ viene visualizzata l’eccezione
MessageBox.Show(thisExcept.Message, _
“EseguiWriteParametrica()”)
End Try
End Function
Query
139
La funzione AggiornaImpiegatiParametrica compone la stringa della query
parametrica, istanzia l’oggetto myCommand e invoca la routine AggiungiParametro per ogni parametro richiesto dalla query.
‘ invoca la routine che aggiunge un parametro alla
‘ collection dei parametri dell’oggetto myCommand
AggiungiParametro(myCommand, “@posizione”, & _
SqlDbType.VarChar, “collega”)
La routine AggiungiParametro riceve come parametri l’oggetto Command destinato a eseguire la query parametrica e le informazioni necessarie a valorizzare le
proprietà del parametro: il nome (la stringa pNomeParametro), il tipo (la variabile pDbType di tipo SqlDbType) e il valore (la stringa pValore). Si limita a istanziare un nuovo oggetto di tipo parametro invocando il metodo CreateParameter
dell’oggetto pCommand:
‘ creazione implicita del parametro
myParameter = pCommand.CreateParameter()
valorizza alcune proprietà del parametro appena creato:
‘ valorizzazione delle proprietà del parametro
myParameter.ParameterName = pNomeParametro
myParameter.SqlDbType = pDbType
myParameter.Value = pValore
aggiunge il parametro alla collection dei parametri dell’oggetto pCommand:
‘ aggiunge il parametro alla collection
pCommand.Parameters.Add(myParameter)
La funzione AggiungiParametro è invocata per ogni parametro richiesto dalla
query parametrica. A questo punto la collection dei parametri è completa e la
query può essere eseguita. Per eseguirla si invoca la funzione EseguiWriteParametrica che resti-tuisce il numero di record interessati dalla query di scrittura.
‘ invocazione della funzione che esegue la query
nroRecord = EseguiWriteParametrica(mySQL, myCommand)
La funzione EseguiWriteParametrica si limita a eseguire il comando di accesso
ai dati, preoccupandosi di gestire la connessione. Riceve il testo del comando
SQL nel parametro pSQL e l’oggetto comando nel parametro pCommand:
‘ assegnazione della stringa di comando
pCommand.CommandText = pSQL
Il comando eseguito è di scrittura, infatti il metodo invocato è ExecuteNonQuery:
‘ esegue il comando di scrittura
EseguiWriteParametrica = pCommand.ExecuteNonQuery()
140
Capitolo 6
Può essere utile scrivere una funzione specifica per l’esecuzione di query di
lettura, da usare negli esempi che proporremo nel prosieguo del libro. Per
coerenza con la funzione di scrittura la chiameremo EseguiReadParametrica.
Private Function EseguiReadParametrica( _
ByVal pSQL As String, _
ByVal pCommand As SqlClient.SqlCommand) _
As SqlClient.SqlDataReader
Dim myConnection As SqlClient.SqlConnection
Dim nroRecord As Integer
Dim myStrConn As String
Try
‘ creazione esplicita della connessione
myConnection = New SqlClient.SqlConnection()
myStrConn = BuildConnectionString()
myConnection.ConnectionString = myStrConn
myConnection.Open()
‘ passaggio della connessione attiva
‘ all’oggetto Command
pCommand.Connection = myConnection
‘ definizione del tipo di comando
pCommand.CommandType = CommandType.Text
‘ assegnazione della stringa comando
pCommand.CommandText = pSQL
‘ esegue il comando di lettura
EseguiReadParametrica = pCommand.ExecuteReader
Catch thisExcept As Exception
‘ se il tentativo di connessione fallisce
‘ viene visualizzata l’eccezione
MessageBox.Show(thisExcept.Message, _
“EseguiReadParametrica()”)
End Try
End Function
Vediamo quali sono le differenze rispetto alla precedente funzione EseguiWriteParametrica. Innanzitutto il valore restituito non è un semplice valore numerico, ma un oggetto complesso di tipo SqlDataReader. Inoltre, il metodo di esecuzione del comando è quello specifico delle query di lettura, ovvero il metodo
ExecuteReader. Un’ultima cosa: la connessione è lasciata attiva perché la routine chiamante deve avere la possibilità di leggere l’oggetto DataReader, se si
chiude la connessione, infatti, la lettura non è più possibile. Per il resto non è
cambiato nulla.
Query
141
Possibilità di query
Nei paragrafi precedenti abbiamo visto come usare la tecnologia .NET per eseguire dei comandi di lettura o di scrittura verso un database. In questo paragrafo, invece, ci proponiamo di fornire qualche esempio complesso di accesso ai
dati per mettere alla prova le nozioni di SQL che abbiamo appreso nel corso del
Capitolo 3.
Abbiamo classificato le operazioni di accesso ai dati facendo una prima distinzione tra operazioni di scrittura e operazioni di lettura. Queste ultime, poi, le
abbiamo ulteriormente suddivise tra lettura di dettaglio e lettura di sintesi. Nell’esempio che segue proponiamo la lettura di sintesi dei dati contenuti su più
tabelle del database Northwind. In particolare, vogliamo conoscere il numero
di ordini e il fatturato complessivo di ogni impiegato.
Le tabelle interessate dall’estrazione sono: la tabella Ordini, la tabella Dettagli
Ordini e la tabella Impiegati. Prima di scrivere la query che estrae i dati vediamo quali sono le informazioni che ci interessano e quali sono le relazioni che
intercorrono tra le varie tabelle. La tabella Ordini e la tabella Dettagli Ordini
sono legate da una relazione uno a molti: a ogni record della tabella Ordini,
infatti, corrispondono uno o più record della tabella Dettagli Ordini. La tabella
Ordini contiene le informazioni specifiche dell’oggetto ordine, ad esempio, la
data dell’ordine, il codice del cliente, il codice dell’impiegato, la data di spedizione, mentre la tabella Dettagli Ordini contiene le informazioni specifiche
dell’oggetto riga d’ordine, ovvero dei prodotti venduti nell’ordine, ad esempio,
il codice del prodotto, il prezzo, la quantità venduta e lo sconto praticato. La
tabella Impiegati contiene, oltre al codice, le informazioni anagrafiche dei vari
impiegati.
Per estrarre le informazioni di cui abbiamo bisogno dobbiamo mettere in join le
tre tabelle: colleghiamo la tabella Ordini con la tabella Dettagli Ordini attraverso il campo idordine e colleghiamo la tabella Ordini con la tabella Impiegati
attraverso il campo idimpiegato. Per ottenere dei dati di sintesi è necessario
raggruppare i record estratti e aggregare i dati numerici mediante le funzioni di
conteggio e di somma. Vediamo il codice SQL della query:
SELECT
Impiegati.cognome, Impiegati.nome,
COUNT(DISTINCT [Dettagli ordini].idordine) AS nr_ordini,
SUM(([Dettagli Ordini].prezzounitario *
[Dettagli Ordini].quantità) *
(1 - [Dettagli Ordini].sconto)) AS importo
FROM Ordini, [Dettagli Ordini], Impiegati
WHERE
Ordini.idordine = [Dettagli Ordini].idordine AND
Ordini.idimpiegato = Impiegati.idimpiegato
GROUP BY
Impiegati.cognome, Impiegati.nome
Analizziamo nel dettaglio la query appena vista. Le dimensioni di analisi, ovvero le informazioni in cui si vuole articolare la sintesi, sono i campi cognome e
nome; le misure dell’analisi, ovvero le informazioni quantitative di sintesi, sono
142
Capitolo 6
il conteggio degli ordini e la somma degli importi dei vari ordini. Il campo
nr_ordini è ottenuto mediante la funzione COUNT. Si noti che, se non avessimo specificato l’opzione DISTINCT, la funzione COUNT non avrebbe restituito
il numero di ordini, ma il numero di righe d’ordine di ogni impiegato. Il campo
importo, invece, è ottenuto moltiplicando la quantità ordinata (il campo quantità) per il prezzo di vendita (il campo prezzounitario) e sottraendo lo sconto,
determinato come fattore di riduzione (1 – sconto).
Possiamo scrivere una routine che esegue la query.
Private Sub OrdiniPerImpiegato()
Dim mySQL As String
Dim myCommand As SqlClient.SqlCommand
Dim myDataReader As SqlClient.SqlDataReader
Try
‘ il testo del comando contiene
‘ un’istruzione SQL di lettura di dati aggregati
mySQL = “SELECT “ & _
“Impiegati.cognome, Impiegati.nome, “ & _
“COUNT(DISTINCT [Dettagli ordini].idordine) “ & _
“AS nr_ordini, “ & _
“SUM(([Dettagli Ordini].prezzounitario * “ & _
“[Dettagli Ordini].quantità) * “ & _
“(1 - [Dettagli Ordini].sconto)) AS importo “ & _
“FROM Ordini, [Dettagli Ordini], Impiegati “ & _
“WHERE “ & _
“Ordini.idordine = [Dettagli Ordini].idordine “ & _
“AND “ & _
“Ordini.idimpiegato = Impiegati.idimpiegato “ & _
“GROUP BY “ & _
“Impiegati.cognome, Impiegati.nome”
myCommand = New SqlClient.SqlCommand()
myDataReader = _
EseguiReadParametrica(mySQL, myCommand)
With myDataReader
‘ legge una riga alla volta
‘ fino alla fine dei dati estratti
Do While .Read = True
Console.Write(.Item(“cognome”) &
Console.Write(.Item(“nome”) & “,
Console.Write(.Item(“nr_ordini”)
Console.Write(.Item(“importo”) &
Loop
End With
‘ la connessione può essere chiusa
‘ solo dopo aver letto il DataReader
myCommand.Connection.Close()
“, “)
“)
& “, “)
Chr(13))
Query
143
Catch thisExcept As Exception
‘ se il tentativo di connessione fallisce
‘ viene visualizzata l’eccezione
MessageBox.Show(thisExcept.Message, _
“OrdiniPerImpiegato()”)
End Try
End Sub
Facciamo un altro esempio. Supponiamo di voler interrogare il database Northwind per estrarre gli ordini il cui importo di vendita sia superiore alla media.
Potremmo, con una prima query, calcolare il valore della media degli ordini e
poi, con una seconda query, estrarre i dati dei soli ordini il cui importo è
maggiore del valore della media precedentemente estratta. In alternativa, possiamo ottenere lo stesso risultato con una sola query, che utilizza la clausola
HAVING sull’importo di vendita, associata a una SUBQUERY che estrae il valore
medio dell’importo degli ordini. Vediamo il codice SQL:
SELECT Ordini.idordine,
SUM(([Dettagli ordini].prezzounitario *
[Dettagli ordini].quantità) *
(1 - [Dettagli ordini].sconto)) AS importo
FROM Ordini, [Dettagli ordini]
WHERE
Ordini.idordine = [Dettagli ordini].idordine
GROUP BY Ordini.idordine
HAVING
(SUM(([Dettagli ordini].prezzounitario *
[Dettagli ordini].quantità) *
(1 - [Dettagli ordini].sconto)) >
(SELECT
AVG(([Dettagli ordini].prezzounitario *
[Dettagli ordini].quantità) *
(1 - [Dettagli ordini].sconto)) AS media
FROM [Dettagli ordini] , Ordini
WHERE
[Dettagli ordini].idordine = Ordini.idordine))
Facciamo a meno di scrivere una routine specifica per l’esecuzione della query
e lasciamo al lettore l’esercizio di adattare allo scopo il codice delle funzioni già
viste. Commentiamo, invece, la struttura della query. Nella prima parte abbiamo una semplice clausola SELECT, che estrae il numero d’ordine (il campo
idordine) e l’importo dell’ordine (il campo importo, calcolato con le modalità
che abbiamo visto nella query precedente); la clausola WHERE è utilizzata per
mettere in join le tabelle interessate dalla query (la tabella Ordini e la tabella
Dettagli Ordini); la clausola GROUP BY elenca i campi in cui saranno raggruppati i valori estratti dalla query, nel nostro caso il solo campo idordine; infine,
abbiamo la clausola HAVING che consente di filtrare i dati raggruppati dalla
query scegliendo di estrarre solo gli ordini il cui importo supera il valore estratto da un’ulteriore query; l’ultima query, infatti, ha il solo scopo di restituire il
valore dell’importo medio degli ordini attraverso la funzione AVG, valore che
144
Capitolo 6
sarà confrontato con l’importo dell’ordine per selezionare quelli da restituire al
richiedente.
Facciamo un altro esempio. La fonte dati di riferimento è ancora il database
Northwind. Supponiamo di voler conoscere il fatturato ottenuto nelle varie nazioni e, per ogni nazione, vogliamo conoscere il contributo fornito da ogni
impiegato. Potremmo estrarre i dati aggregati per nazione e impiegato, esportarli in un foglio Excel ed elaborarli con gli strumenti specifici del foglio di
calcolo, per ottenere le aggregazioni e le elaborazioni che desideriamo. Purtroppo, dobbiamo realizzare applicazioni facilmente fruibili e fornire ai nostri
utenti dati già elaborati e organizzati secondo un layout immediatamente comprensibile. Fortunatamente abbiamo a disposizione gli strumenti SQL, che ci
consentono con una sola query di estrarre i dati elaborati e organizzati in modo
da fornirci le informazioni che desideriamo. Vediamo la query:
SELECT
Dettaglio.PaeseDestinatario,
Dettaglio.cognome, Dettaglio.nome,
Dettaglio.importo,
Sintesi.imp_totale
ROUND(
(Dettaglio.importo / Sintesi.imp_totale * 100), 2
) as percentuale
FROM
(SELECT
paesedestinatario,
Impiegati.cognome, Impiegati.nome,
SUM(([Dettagli Ordini].prezzounitario *
[Dettagli Ordini].quantità) *
(1 - [Dettagli Ordini].sconto)) AS importo
FROM Ordini, [Dettagli Ordini], Impiegati
WHERE
Ordini.idordine = [Dettagli Ordini].idordine AND
Ordini.idimpiegato = Impiegati.idimpiegato
GROUP BY PaeseDestinatario,
Impiegati.cognome, Impiegati.nome) Dettaglio,
(SELECT
paesedestinatario,
SUM(([Dettagli Ordini].prezzounitario *
[Dettagli Ordini].quantità) *
(1 - [Dettagli Ordini].sconto)) AS imp_totale
FROM Ordini, [Dettagli Ordini]
WHERE
Ordini.idordine = [Dettagli Ordini].idordine
GROUP BY PaeseDestinatario) Sintesi
where
Dettaglio.paesedestinatario = Sintesi. paesedestinatario
Anche stavolta lasciamo al lettore l’esercizio di scrivere il codice per eseguire la
query. Commentiamone, invece, la struttura. La query è composta da due subquery: la prima, che abbiamo chiamato Dettaglio, estrae il fatturato per impiegato e nazione di destinazione; la seconda, che abbiamo chiamato Sintesi, estrae,
invece, il fatturato raggruppato soltanto per nazione. Nelle tabelle che seguono
possiamo vedere il contenuto delle due subquery.
Query
145
Tabella 6.5
Subquery Dettaglio
paesedestinatario
cognome
nome
importo
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
…
Callahan
Davolio
Dodsworth
Fuller
King
Leverling
Peacock
Suyama
…
Laura
Nancy
Anne
Andrew
Robert
Janet
Margaret
Michael
…
4125750
1030050
1416750
715500
2303700
478800
1994100
114000
…
Tabella 6.6
Subquery Sintesi
Paesedestinatario
imp_totale
Argentina
Austria
Belgio
Brasile
Canada
Danimarca
…
12178650
192005757,75
50737282,5
160388664,75
75294435
48991533,75
…
La query principale mette in join le due subquery estraendo la nazione (il
campo paesedestinatario), l’impiegato (i campi nome e cognome), il fatturato
realizzato da ogni impiegato per nazione (il campo importo), il fatturato totale
realizzato per nazione (il campo imp_totale) e la percentuale del fatturato realizzato dall’impiegato per nazione sul totale della nazione (il campo percentuale). Si noti che abbiamo arrotondato il valore del campo percentuale alla seconda cifra decimale usando la funzione ROUND. Vediamo il contenuto della query complessiva:
Tabella 6.7
paesedestinatario
cognome
nome
importo
imp_totale
percentuale
Argentina
Argentina
Argentina
Argentina
Argentina
Callahan
Davolio
Dodsworth
Fuller
King
Laura
Nancy
Anne
Andrew
Robert
4125750
1030050
1416750
715500
2303700
12178650
12178650
12178650
12178650
12178650
33,88
8,46
11,63
5,88
18,92
(continua)
146
Capitolo 6
Tabella 6.7
(seguito)
paesedestinatario
cognome
nome
importo
imp_totale
percentuale
Argentina
Argentina
Argentina
…
Leverling
Peacock
Suyama
…
Janet
Margaret
Michael
…
478800
1994100
114000
…
12178650
12178650
12178650
…
3,93
16,37
0,94
…
Dagli esempi visti in questo capitolo si intuisce che le possibilità di query offerte dal linguaggio SQL standard sono veramente enormi. Si tenga conto, inoltre,
che i DBMS più sofisticati, con le ultime versioni dei loro prodotti, forniscono
funzioni di ricerca e di aggregazione sempre più avanzate. L’invito, quindi, è
quello di non limitarsi all’uso delle funzionalità più comuni dell’SQL, ma di
esplorare anche le funzionalità avanzate fornite dal DBMS, pur tenendo conto
delle possibili incompatibilità con gli altri DBMS in caso di migrazione del
database.