Technology Experience

Contenuti gestiti da Igor Damiani
posts - 949, comments - 2741, trackbacks - 15120

My Links

News

  • Questo blog si propone di raccogliere riflessioni, teoriche e pratiche, su tutto quello che riguarda il world-computing che mi sta attorno: programmazione in .NET, software attuale e futuro, notizie provenienti dal web, tecnologia in generale, open-source.

    L'idea è quella di lasciare una sorta di patrimonio personale, una raccolta di idee che un giorno potrebbe farmi sorridere, al pensiero di dov'ero e cosa stavo facendo.

    10/05/2005,
    Milano

Archives

Post Categories

Generale

[MCAD.28] Creare un SqlCommand per eseguire una sp con parametri di output

Nel post [MCAD.26] abbiamo visto come creare un SqlCommand per eseguire operazioni sul nostro database. Nel caso specifico, in quel post il SqlCommand è stato definito per eseguire una INSERT INTO (per aggiungere records alla tabella ListBirthdays) e una TRUNCATE TABLE (per svuotare la tabella ListBirthdays prima di popolarla).

Ieri sera mi sono divertito a guardare meglio il SqlCommand, soprattutto nell'ottica di richiamare una stored-procedure che ritorni al chiamante uno o più parametri di output , ovvero parametri che vengono passati in input durante la chiamata, e che vengono successivamente ritornati una volta valorizzati. Mi spiego meglio: partiamo dal presupposto di avere la tabella ListBirthdays come descritto nel post [MCAD.23] e dintorni. In breve: un campo id (contatore), un campo name ed un campo birthday. La tabella in questione viene popolata dalla nostra funzione di esportazione che abbiamo creato nel post [MCAD.26]. Supponiamo quindi di avere in questa tabella un certo numero di records con i compleanni della nostra famiglia: genitori, zii, zie, cugini e cugine, etc.

A questo punto supponiamo di voler chiedere alla nostra applicazione qual'è il compleanno più vicino, il prossimo che arriva. Io ho risposto a questa domanda semplicemente creando una sp chiamata GetNearestBirthday, il cui codice T-SQL è il seguente:

ALTER PROCEDURE GetNearestBirthday
    @Name varchar(100) OUTPUT,
    @Birthday smalldatetime OUTPUT
AS

DECLARE @Ret 
int

SELECT TOP 1
    @Name = name,
    @Birthday = CONVERT(varchar(4), YEAR(GETDATE())) + 
'-'
        
+ CONVERT(varchar(2), MONTH(birthday)) + '-'
        
+ CONVERT(varchar(2), DAY(birthday)),
    @Ret = DATEDIFF(day, GETDATE(),
    CONVERT(varchar(4), YEAR(GETDATE())) + 
'-'
        
+ CONVERT(varchar(2), MONTH(birthday)) + '-'
        
+ CONVERT(varchar(2), DAY(birthday)))
FROM
    ListBirthdays
WHERE
    DATEDIFF(day, GETDATE(),
    CONVERT(varchar(4), YEAR(GETDATE())) + 
'-'
        
+ CONVERT(varchar(2), MONTH(birthday)) + '-'
        
+ CONVERT(varchar(2), DAY(birthday))) > 0
ORDER BY
    DATEDIFF(day, GETDATE(),
    CONVERT(varchar(4), YEAR(GETDATE())) + 
'-'
        
+ CONVERT(varchar(2), MONTH(birthday)) + '-'
        
+ CONVERT(varchar(2), DAY(birthday)))

RETURN @Ret

Questa sp è stata creata nel database age. La sp accetta due parametri di output, che vengono ritornati al chiamante valorizzati con il nome della persona, e la data del suo compleanno. La RETURN ritorna un valore intero che indica quanti giorni mancano al compleanno stesso. A questo punto, lo scopo è quello di creare un SqlCommand che possa eseguire questa sp e memorizzare da qualche parte i valori restituiti. Siccome vogliamo divertirci un po', vedremo questa volta di scrivere poco codice ed usare (per quanto possibile) l'IDE di Visual Studio. Vediamo, passo per passo, come raggiungere l'obiettivo prefissato.

Aggiungiamo una nuova Windows Forms
Ho creato una nuova WF chiamata frmNearestBirthday, molto semplice: una sola TextBox (txtNearestBirthday) e due Button (un btnGet ed un btnOk). Il click sul Button Ok chiude semplicemente la form. Lo screenshot (provvisorio) della form è qui:

A noi interessa in particolare gestire il click sul Button Get: bisognerà eseguire il SqlCommand e mostrare nella TextBox qual'è il compleanno più vicino in ordine di tempo. Abbiamo detto che non vogliamo scrivere codice (per chi volesse farlo, può guardarsi il post [MCAD.26] per vedere come creare il Command ed i relativi parametri), per cui facciamo quanto segue:

  1. Usando il Server Explorer, connettiamoci al database age
  2. Una volta stabilita la connessione, nell'IDE di VS.NET possiamo esplorare gli oggetti del database (tabelle, viste, stored-procedure, etc.)
  3. Espandiamo il nodo Stored Procedure. Dovremmo vederne due: SettingUp e GetNearestBirthday (quest'ultima creata con il T-SQL postato più sopra). Trasciniamo la GetNearestBirthday sulla Windows Form: VS.NET crea automaticamente un oggetto sqlConnection1 e sqlCommand1.

Se siamo curiosi, possiamo adesso andare a vedere il codice generato automaticamente dal nostro amico VS.NET: la mitica InitializeComponent contiene codice che definisce la SqlConnection e il SqlCommand. Quest'ultimo, in particolare, incorpora 3 parametri: @RETURN_VALUE, @Name e @Birthday. Il codice è un po' più prolisso di quello che abbiamo scritto noi nel [MCAD.26], ma concettualmente è la stessa cosa (in realtà e per ovvi motivi preferisco scrivermelo io - perchè appesantire la InitializeComponent (e quindi l'apertura della Windows Form) quando posso creare gli oggetti che mi servono solo quando effettivamente necessario? ). La collection Parameters contiene 3 oggetti SqlParameter, che specificano nome, tipo di dato, direzione (input, output o input/output), etc. Vediamo adesso come utilizzare questo SqlCommand per ottenere il risultato che vogliamo. Gestiamo quindi il click del Button btnOk.

Clicchiamo su Ok...qual'è il prossimo regalo che devo fare?
Posto qui sotto il codice, che poi commenterò passo-passo:

private void btnGet_Click(object sender, System.EventArgs e)
{
    
this.sqlConnection1.Open();
    
this.sqlCommand1.ExecuteNonQuery();
    
this.sqlConnection1.Close();

    
int days = (intthis.sqlCommand1.Parameters["@RETURN_VALUE"].Value;
    
string name = (stringthis.sqlCommand1.Parameters["@Name"].Value;
    DateTime birthday = Convert.ToDateTime(
this.sqlCommand1.Parameters["@Birthday"].Value);

    
string testo = String.Format("Next Birthday : {0}, {1} days left ({2})", name, days, birthday.ToShortDateString());
    
this.txtNearestBirthday.Text = testo;
}

Gli oggetti SqlConnection e SqlCommand sono già definiti e pronti all'uso. Non devo far altro che aprire la connessione al database, eseguire il comando e chiudere la connessione: esattamente le prime 3 linee di codice della function qui sopra. Una volta eseguito il SqlCommand, posso leggere i valori restituiti, semplicemente leggendo la property Value di ogni singolo SqlParameter: in questo modo ottengo e valorizzo days (numero di giorni), name (nome della persona) e birthday (giorno del compleanno). Scrivere nella TextBox è un gioco da ragazzi: usando il metodo statico Format della classe String formatto la stringa.

A me compare che il prossimo compleanno è il 16 Settembre, di Sxxxxxa Pxxxxxxi. E per voi, chi è il prossimo parente che compie gli anni? Adesso faccio un bello zip, e metto online l'applicazione, magari a qualcuno può anche essere utile.

Notare, tornando seri un attimo, l'uso del metodo ExecuteNonQuery(), perchè non ci interessa che venga ritornato un recordset, o un qualche valore dal database: l'unica cosa interessante è la sp e i suoi valori di ritorno.
ExecuteReader ritorna un SqlReader per leggere in forward-only una collection di DataRow.
ExecuteScalar ritorna il valore del primo campo della prima riga ritornata.
ExecuteXmlReader esiste solo per oggetti SqlCommand (e non per OleDbCommand).

powered by IMHO 1.2

Print | posted on Monday, September 5, 2005 4:01 PM | Filed Under [ MCAD ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET