Sistemi Informativi e Telemedicina
Prof. Mauro Giacomini
Anno Accademico 2008-2009
Sommario
definizione di stored procedure
stored procedure che restituiscono record
passaggio di parametri
stored procedure che NON restituiscono record
Definizione di Stored Procedure
Una stored procedure è una sequenza di istruzioni SQL
memorizzate in un database server;
si usa quando le modifiche da apportare ad un DataSet
sono più complesse di un semplice aggiornamento
(per il quale basta un oggetto DataAdapter);
consentono un maggior livello di sicurezza per il
database;
possono restituire o meno record.
Stored Procedure che restituiscono
record (1)
Per invocare una stored procedure occorre creare un
oggetto Command, associarlo ad una connessione,
impostare la proprietà CommandText al nome della
stored procedure e assegnare alla proprietà
CommandType il valore
CommandType.StoredProcedure.
Poniamo di avere la seguente stored procedure :
Procedure ListaCategorie As
SELECT IDCategoria, NomeCategoria
FROM Categorie
Stored Procedure che restituiscono
record (2)
Il seguente codice utilizza un oggetto Connection e un
oggetto DataAdapter per invocare la stored procedure
ListaCategorie:
Dim da as New SqlDataAdapter
da.SelectCommand = New SqlCommand()
da.SelectCommand.Connection = Conn
da.SelectCommand.CommandText = “ListaCategorie”
da.SelectCommand.CommandType =
CommandType.StoredProcedure
Stored Procedure che restituiscono
record (3)
A questo punto per eseguire la stored procedure, è
sufficiente invocare il metodo Fill dell’oggetto
SQLDataAdapter:
da.Fill(Ds, “Categorie”)
Dopo aver invocato la stored procedure, avremo quindi nel
DataSet Ds un oggetto DataTable individuato dalla stringa
“Categorie” che si può gestire come un qualsiasi DataTable.
Passaggio di parametri (1)
Usando le stored procedure è possibile passare dei parametri in
modo da personalizzare l’esecuzione della procedura.
Gli oggetti Parameter rappresentano i valori da passare alla
stored procedure al momento dell’invocazione.
Le possibili direzioni per i parametri di una stored procedure
sono:
-Input: si inviano valori alla stored procedure
-Output: la stored procedure restituirà un valore in questo
parametro
-InputOutput: possibilità di inviare dei dati alla stored procedure
e ricevere dei risultati
-ReturnValue: indica il parametro in cui la stored procedure
memorizzerà l’eventuale valore di ritorno.
Passaggio di parametri (2)
L’oggetto Parameter deve avere lo stesso nome e tipo di
dato previsto dalla stored procedure.
Dopo aver creato l’oggetto Parameter occorre impostare la
sua proprietà direction.
Poniamo di avere la seguente stored procedure:
Procedure ProdottiPerCategoria(@IDCategoria int)
As
SELECT IDProdotto, Descrizione, CostoUnitario
FROM Prodotti
WHERE IDCategoria = @IDCategoria
Passaggio di parametri (3)
La stored procedure accetta un parametro di tipo intero che
rappresenta l’identificativo della categoria dei prodotti.
Vediamo come invocare questa stored procedure nella
nostra applicazione:
da As New SQLDataAdapter(“ProdottiPerCategoria”,
Conn)
da.SelectCommand.CommandType =
CommandType.StoredProcedure
Dim Param As New SQLParameter (“@IDCategoria”,
SQLDbType.Int)
Param.Direction = ParameterDirection.Input
Param.Value = 40
Passaggio di parametri (4)
A questo punto l’oggetto preparato deve essere associato
alla stored procedure, ciò avviene aggiungendolo alla
collection Parameters dell’oggetto SelectCommand:
Da.SelectCommand.Parameters.Add(Param)
Se la stored procedure prevede più parametri essi vengono
inseriti nella collection uno dopo l’altro.
Quindi l’inserimento del risultato della stored procedure in
un DataSet segue l’approccio standard:
Dim Ds as New DataSet
da.Fill(Ds, “Prodotti”)
Stored Procedure che non
restituiscono record (1)
Le stored procedure possono anche eseguire delle azioni di
modifica, cancellazione o aggiunta di record, quindi senza
restituire nessun insieme di record.
In questo caso per eseguirle si utilizzano direttamente gli
oggetti Command e il metodo ExecuteNonQuery.
Poniamo di avere la seguente stored procedure:
Procedure NumeroOrdini(@IDCliente int, @NOrdini
int OUTPUT)
As
SELECT @NOrdini = COUNT(IDOrdine)
FROM Ordini
WHERE IDCliente = @IDCliente
Stored Procedure che non
restituiscono record (2)
Dim cmd as New SQLCommand(“NumeroOrdini”, Conn)
cmd.CommandType = CommandType.StoredProcedure
Dim Param1 as New SQLParameter(“@IDCliente”,
SQDbType.int)
Param1.Direction = ParamDirection.Input
Param1.Value = 12
cmd.Parameters.Add(Param1)
Dim Param2 as New SQLParameter(“@NOrdini”,
SQLDbType.int)
Param2.Direction = ParamDirection.Output
Cmd.Parameters.Add(Param2)
Conn.Open()
cmd.ExecuteNonQuery()
Conn.Close()
Stored Procedure che non
restituiscono record (3)
Da notare che l’esecuzione del metodo ExecuteNonQuery
richiede che la connessione sia aperta.
Si può utilizzare direttamente la collection Parameters per
accedere al valore restituito da una stored procedure in un
parametro di output, ad esempio:
n = cmd.Parameters(“@NOrdini”).Value
