ADO.NET e le Stored Procedure

 

È da sempre il grande tormentone: con SQL Server usate le Stored Procedures! Questa affermazione non verrà certo smentita su queste pagine ma affermazioni del genere necessitano sempre della definizione di un contesto. L’utilizzo di questa funzionalità, per altro standardizzata dall’ANSI e disponibile anche su tutti gli altri motori di database più conosciuti, dà degli innegabili vantaggi in termini di prestazioni a causa del maggior riutilizzo del piano di esecuzione (e qui si aprono una serie di distinguo). Perchè questo si avveri occorre che il piano di esecuzione presente all’interno della cache delle procedure non sia invalidato da qualche modifica fatta alla struttura delle tabelle di base, a qualche indice presente sulle tabelle stesse, o al variare di una grande quantità di dati al loro interno (distribuzione e selettività), o addirittura quando all’interno del codice della SP viene costruito ed eseguito del SQL dinamico.

 

Altri vantaggi e svantaggi possono essere, da una parte, la possibilità di aggiungere un livello di astrazione tra l’applicazione e la struttura delle tabelle, la creazione di un ulteriore livello di sicurezza ottenibile concedendo l’accesso alle informazioni solo attraverso questa modalità e non attraverso query dirette sulle tabelle, ma anche una potenziale riduzione della portabilità del codice sviluppato e una maggiore difficoltà di manutenzione di codice che risiede in parte nel sorgente del client e un po all’interno del database.

Questo lungo preambolo per dire che l’utilizzo delle SP non è certo obbligatorio, ma occorre scegliere la modalità di interrogazione ai dati sulla base del contesto e delle necessità. Il mio consiglio è naturalmente quello di utilizzarle, quando possibile, senza però farne una guerra di religione.

 

Per utilizzare le stored procedure con ADO.NET ed il SQL Server .NET Data Provider occorre ovviamente partire dalla connessione al database server e dalla creazione di un oggetto di classe SqlCommand.

L’oggetto SqlCommand avrà come proprietà CommandText, oppure come parametro passato al costruttore, il nome della procedura da eseguire. La connessione viene associata al comando sempre attraverso il costruttore oppure con la proprietà Connection. Un’altra possibilità è quella di utilizzare il metodo CreateCommand della connessione per ottenere lo stesso effetto.

 

using System.IO;
using System.Data;
using System.Data.SqlClient;
public SqlDataReader RetrieveRowsWithDataReader()
{
SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=northwind");
SqlCommand cmd = new SqlCommand("DATRetrieveProducts", conn );
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
    // CommandBehavior.CloseConnection provoca la chiusura della 
    // connessione alla chiusura del Reader
return( cmd.ExecuteReader( CommandBehavior.CloseConnection ) );
}
catch
{
conn.Close();
throw;
}
}
// Output dei dati nella console
private void DisplayProducts()
{
SqlDataReader reader = RetrieveRowsWithDataReader();
while (reader.Read())
{
Console.WriteLine("{0} {1} {2}",
reader.GetInt32(0).ToString(),
reader.GetString(1) );
}
reader.Close();
}

 

In questo esempio la procedura chiamata ritorna un set di record che viene ottenuto attraverso un oggetto di classe SqlDataReader, che rappresenta uno stream di dati in modalità read-only e forward-only. Questo particolare oggetto è indicato quando, in uno scenario “connesso”, le informazioni devono essere ottenute nella maniera più rapida, utilizzate e poi scartate (l’esempio più classico è una pagina Web). La scalabilità di questo approccio è assicurata dal fatto che un solo record, quello sul quale siamo posizionati in un dato istante, è presente in memoria. Quelli precedenti sono già stati scaricati mentre i successivi risiedono ancora nel buffer di memoria della connessione (di default 4096 bytes). Per contro, la connessione verso il database server rimane aperta durante lo scorrimento e l’utilizzo delle informazioni all’interno del reader stesso. Una soluzione per evitare quest’ultima situazione è quella di utilizzare un oggetto di tipo DataSet per creare una cache disconnessa delle informazioni. Parleremo di questo in forma dettagliata in un prossimo articolo.

 

Nel caso la procedura avesse avuto parametri di OUTPUT oppure valori di ritorno, questi sarebbero stati accessibili solo dopo la chiusura dell’oggetto SqlDataReader.

Quando si accede alle informazioni contentute nel reader è importante utilizzare le funzioni definite “typed accessor” (GetString(), GetInt32(),ecc.) per evitare dispendiose conversioni implicite. Nel caso di un unico resultset di ritorno dall’esecuzione del comando, il valore CommandBehavior.SingleResult passato come parametro del metodo ExecuteReader() può ottimizzare le operazioni su SQL Server. Il valore CommandBehavior.SingleRow invece, ha impatto solamente sul OleDb .NET Data Provider e non su quello per SQL Server, dato che permette l’utilizzo dell’interfaccia IRow al posto della meno efficace IRowset. Il valore CommandBehavior.CloseConnection passato all’esecuzione del comando, permette di ottenere la chiusura automatica della connessione utilizzata alla chiusura del reader stesso.

 

void GetProductDetails( int ProductID,
out string ProductName, out decimal UnitPrice )
{
SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=Northwind");
  // Definisco il comando
SqlCommand cmd = new SqlCommand( "DATGetProductDetailsSPOutput", conn );
cmd.CommandType = CommandType.StoredProcedure;
  // Definisco i parametri della stored procedure
  //  @ProductID int INPUT
  //  @ProductName nvarchar(40) OUTPUT
  //  @UnitPrice money OUTPUT
  // Definisco il verso dei parametri
SqlParameter paramProdID =
cmd.Parameters.Add( "@ProductID", ProductID );
paramProdID.Direction = ParameterDirection.Input;
SqlParameter paramProdName =
cmd.Parameters.Add( "@ProductName", SqlDbType.VarChar, 40 );
paramProdName.Direction = ParameterDirection.Output;
SqlParameter paramUnitPrice =
cmd.Parameters.Add( "@UnitPrice", SqlDbType.Money );
paramUnitPrice.Direction = ParameterDirection.Output;
try
{
conn.Open();
    // Utilizzo ExecuteNonQuery per eseguire il comando. 
    // I parametri di output e il valore di ritorno  
    // vengono popolati
cmd.ExecuteNonQuery( );
    // Accedo ai parametri
ProductName = paramProdName.Value.ToString();
UnitPrice = (decimal)paramUnitPrice.Value;
}
catch
{
throw;
}
finally
{
conn.Close();
}
}

 

In quest’altro esempio la stored procedure definisce un parametro di input e due parametri di OUTPUT che accoglieranno il risultato dell’elaborazione. Questo approccio è da preferirsi al precedente nel caso le informazioni da leggere siano relativamente poche per un vantaggio di performance.

Proprio le performance sono direttamente dipendenti dal livello di stress (carico di lavoro) dell’applicazione e del database server. In caso infatti di un alto carico di lavoro la soluzione dei parametri di OUTPUT supera di circa un 30% quella del SqlDataReader quando il connection pooling è abilitato, mentre con il pooling disabilitato il SqlDataReader dà circa il 20% di performance in più rispetto all’altra soluzione.

 

void GetProductNameExecuteScalar( int ProductID, out string ProductName )
{
SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=northwind");
SqlCommand cmd = new SqlCommand("LookupProductNameScalar", conn );
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ProductID", ProductID );
try
{
conn.Open();
ProductName = (string)cmd.ExecuteScalar();
}
catch
{
throw;
}
finally
{
conn.Close();
}
}

 

Nel caso di un unico valore da recuperare come OUTPUT di una procedura, esiste anche la possibilità di utilizzare il metodo ExecuteScalar() dell’oggetto SqlCommand, che necessita certamente di meno codice nella realizzazione, ma che risulta meno performante rispetto all’utilizzo dei parametri di OUTPUT della stored procedure.

 

Gestione degli errori e messaggi

 

Al contrario di quanto accadeva in precedenza, dove la gestione degli errori era influenzata dai dettagli di ogni singola tecnologia utilizzato (Es. ADO/COM, VB Runtime, ASP, ecc.) con ADO.NET la metodologia è esattamente la stessa che in ogni altro componente del .NET Framework: le eccezioni.

 

Figura 1 - Le eccezioni dei .NET Data Provider

 

Qualsiasi errore generato dalla fonte dati alla quale si è collegati viene tradotto in un particolare tipo di eccezione standard, che può essere catturata all’interno del proprio codice. Tutti i dettagli specifici dell’errore vengono resi disponibili attraverso proprietà dell’oggetto eccezione generato.

La classe base dalla quale derivano tutti i tipi di eccezioni disponibili in .NET è Exception presente nel namespace System. Il SQL Server .NET Data Provider genera delle eccezioni specifiche come ad esempio la SqlException mentre gli altri provider ne generano delle altre specifiche per la fonte dati. La presenza di ExternalException nella gerarchia esposta del OleDb provider tradisce il legame di quest’ultimo con COM Interop e con i servizi OLE-DB utilizzati.

Un classico blocco try-catch per catturare le eccezioni nel codice di accesso ai dati sarà qualcosa del genere:

 

try
{
  // Codice di accesso ai dati
}
catch (SqlException sqlex) // eccezione specifica di SQL
{
}
catch (Exception ex) // eccezione che cattura un errore generico
{
}

 

Occorrerà quindi sempre prima intercettare le eccezioni più specifiche che si vuole catturare e poi le meno specifiche per poter stabilire livelli di filtro nella cattura di un potenziale errore.

Attraverso le proprietà dell’oggetto SqlException è possibile ottenere tutte le informazioni importanti dell’errore che si è verificato per quella connessione.

 

using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
// Metodo di un componente di accesso ai dati
public string GetProductName( int ProductID )
{
SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=northwind");
  // Il codice di accesso ai dati è all’interno del blocco try
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("LookupProductName", conn );
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ProductID", ProductID );
SqlParameter paramPN =
cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 40 );
paramPN.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
    // Il codice nel blocco finally viene eseguito al ritorno dal metodo
return paramPN.Value.ToString();
}
catch (SqlException sqlex)
{
    // Gestione di errori di accesso ai dati
    // Log delle informazioni dell’errore
LogException(sqlex);
    // Incapsulamento delle informazioni riguardanti l’errore
    // in una eccezione custom da passare a chi ha utilizzato questo componente
throw new DALException(
"Unknown ProductID: " + ProductID.ToString(), sqlex );
}
catch (Exception ex)
{
    // Condizione di errore standard . . .
throw ex;
}
finally
{
conn.Close(); // Chiusura della connessione
}
}
// Routine di log dei dettagli dell’errore 
private void LogException( SqlException sqlex )
{
EventLog el = new EventLog();
el.Source = "CustomAppLog";
string strMessage;
strMessage = "Exception Number : " + sqlex.Number +
"(" + sqlex.Message + ") has occurred";
el.WriteEntry( strMessage );
foreach (SqlError sqle in sqlex.Errors)
{
strMessage = "Message: " + sqle.Message +
" Number: " + sqle.Number +
" Procedure: " + sqle.Procedure +
" Server: " + sqle.Server +
" Source: " + sqle.Source +
" State: " + sqle.State +
" Severity: " + sqle.Class +
" LineNumber: " + sqle.LineNumber;
el.WriteEntry( strMessage );
}
}

 

Nel linguaggio T-SQL esiste la funzione RAISERROR che consente di generare un errore applicativo all’interno di una stored procedure e propagarlo al client che utilizza quella procedura. È possibile creare applicativo un errore “al volo” semplicemente invocando la funzione raiserror e passando alcune informazione come il testo del messaggio di errore, la severità dell’errore e lo stato. Questo errore viene rilevato dal provider che ne rileva i messaggi e crea un oggetto del tipo SqlError all’interno della collezione Errors dell’eccezione generata. A seconda del tipo di severità il provider si comporta in modo diverso:

-         A livello 10 o inferiori la connessione non viene interrotta e non vengono generate eccezioni. I messaggi vengono considerati normali informazioni.

-         Dal 11 al 19 la connessione rimane aperta ma viene generata una eccezione

-         Oltre il 19 vengono considerati errori fatali, generata una eccezione e la connessione viene terminata

 

È possibile catturare gli errori o meglio i messaggi informativi (InfoMessage) con severità minore o uguale a 10 utilizzando un particolare handler di evento da associare al evento InfoMessage esposto dall’oggetto SqlConnection.

 

public string GetProductName( int ProductID )
{
SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=northwind");
try
{
    // Registro l’handler per questo evento
conn.InfoMessage += new SqlInfoMessageEventHandler( MessageEventHandler );
conn.Open();
    // Eseguo un comando verso SQL Server
. . .
}
catch (SqlException sqlex)
{
    // catturo una eventuale eccezione
. . .
}
  finally
{
conn.Close();
}
}
// gestore dell’handler dei messaggi
void MessageEventHandler( object sender, SqlInfoMessageEventArgs e )
{
foreach( SqlError sqle in e.Errors )
{
    // Accesso alle informazioni del messaggio tramite le proprietà di SqlError
. . .
}
}

 

L’oggetto SqlInfoMessageEventArgs viene ricevuto dall’handler come argomento e attraverso quello è possibile accedere a tutte le info che riguardano l’evento generato.

 

Sql Server Security

 

La sicurezza nella connessione di un client verso una fonte dati ha a che fare con diversi livelli di dettaglio, che vanno dal tipo di autenticazione scelta allo scenario architetturale nel quale ci si trova. Ad esempio, l’utilizzo del connection pooling per migliorare la scalabilità delle applicazioni prevede che la connessioni presenti in un pool appartengano tutte allo stesso contesto di sicurezza (stesso utente e database utilizzato), cosa che quindi rende impossibile la possibilità di aprire la connessione verso il database con l’identità del singolo client, che invaliderebbe immediatamente questa funzionalità. In uno scenario del genere l’applicazione Web o il componente di accesso ai dati che risiede sul server, utilizzeranno o la sicurezza integrata con Windows oppure quella standard di SQL Server per permettere a un numero elevato di utenti di connettersi alla fonte dati utilizzando tutti le stesse credenziali, sobbarcandosi quindi l’attività di autenticazione e la veridica delle autorizzazioni dell’utente finale. Evitare di autenticare successivamente l’utente anche sulla base dati ha come ulteriore vantaggio quello di essere più performante, dato che viene eliminata questa operazione. Un’altra operazione da evitare è l’impersonazione del client da parte di questi componenti lato server che, a sua volta, comporterebbe la perdita automatica del pooling delle connessioni e una perdita di tempo per una operazione in più da compiere.

 

L’utilizzo del modello di autenticazione integrato con Windows per l’apertura delle connessioni (“Trusted_Connection=Yes” o “Integrated Security=SSPI”) presenta diversi vantaggi rispetto alla Security Standard di SQL Server. In primo luogo evita la necessità di incapsulare nei client o nei file di configurazione le informazioni che riguardano l’identità da utilizzare per l’apertura della connessione. E ancora più importante evita che le informazioni di nome utente e relativa password transitino in chiaro sulla rete al momento dell’apertura della connessione verso SQL Server. È possibile eventualmente ridurre questo ultimo pericolo utilizzando la net-lib TCP/IP Socket e la sua funzionalità di cifratura SSL delle informazioni di connessione al DB, ma occorre una fase di configurazione abbastanza complessa nella quale installare i certificati digitali per client e server necessari a questa funzionalità.

Esiste purtroppo una piccola riduzione delle performance utilizzando la Windows authentication, rispetto alla standard, nella fase di apertura iniziale della connessione, anche se i vantaggi di comodità e di sicurezza solitamente compensano questo svantaggio.

 

Il .NET Framework introduce un ulteriore livello di sicurezza che viene applicato all’esecuzione di codice da parte di un determinato utente in una determinata condizione. Una applicazione che crea una istanza dell’oggetto SqlConnection può richiedere a tutti coloro la utilizzeranno direttamente o indirettamente, di essere autorizzati a quella operazione attraverso richieste imperative o dichiarative di credenziali. Utilizzando l’oggetto SqlClientPermission, il codice può richiedere “prova” di queste credenziali e generare una eccezione se queste non vengono soddisfatte. Attraverso l’oggetto SqlClientPermissionAttribute è possibile stabilire se l’utente ha questo genere di permission oppure no. Gli utenti e gli amministratori possono anche utilizzare il Code Access Security Policy Tool (Caspol.exe) per modificare le policy di sicurezza a livello di macchina, utente e di intera infrastruttura di rete. L’argomento della Code Access Security verrà adeguatamente approfondito in successivi articoli.

 

Altre implicazioni di sicurezza si ritrovano quando esiste la necessità di collegarsi da un client a SQL Server attraverso un firewall. In questo caso occorre configurare l’intera catena di dispositivi (client/firewall/server) per permettere questa comunicazione. Innanzitutto si utilizzerà tipicamente la net-lib TCP/IP Socket per eseguire questa forma di connessione, poi occorre stabilire quali sono le porte interessate durante il colloquio. Quando viene installata la istanza di defautl di SQL Server su una determinata macchina, le viene assegnata la porta 1433, valore che è possibile modificare sia in fase di setup sia successivamente attraverso l’apposita utility di configurazione.

 

Quando invece sulla stessa macchina vengono installate altre istanze, l’assegnazione delle porte viene eseguita dinamincamente alla partenza dei servizi all’interno di un range di porte disponibili. I processi client possono connettersi a istanze di SQL Server superiori alla prima solo se hanno una versione di ADO installata uguale o superiore alle 2.6, perchè è necessario eseguire una fase di handshake pre-login per stabilire la porta utilizzata da una determinata istanza. In questo caso il client utilizza il protocollo UDP sulla porta 1434 per negoziare con il server il nome dell’istanza e la porta associata. Questa operazione solitamente viene evitata durante la comunicazione attraverso un firewall per motivi di sicurezza, e sostituita da una assegnazione manuale di una porta ad una istanza. Porta che viene poi aperta sul firewall per consentire la comunicazione.

 

Utilizzo di campi BLOB

 

La necessità di memorizzare in SQL Server informazioni di tipo non convenzionale come ad esempio immagini, suoni o video è sempre più frequente. Dal punto di vista della memorizzazione questi dati vengono solitamente chiamati BLOB (Binary Large Objects) e SQL Server contiene informazioni di questo tipo in dati che hanno formato image oppure text. Questi tipi di dati permettono di memorizzare oggetti che hanno dimensioni da 0 a 2GB in formato binario (image) e testuale (text e ntext). Esiste anche il tipo di dato binary che ha le medesime caratteristiche ma un limite di 8K di memorizzazione che lo rende poco adeguato a questo tipo di informazioni.

 

Nonostante il supporto sia completo sia da parte di SQL Server che dalle varie tecnologie di accesso ai dati, ovviamente anche in ADO.NET, occorre essere consapevoli del fatto che questi tipi di dato sono decisamente poco efficiente dato che vengono memorizzati in strutture dati esterne rispetto al record che al quale appartengono, per superare il limite di 8k di dati che una pagina di SQL Server può contenere. I vantaggi di questa soluzione però possono risiedere nella “pulizia” dell’architettura di una applicazione che fà uso di questa tecnica, dato che le immagini o i files non risiederebbero in cartelle standard del file system sparse per la rete ma in uno unico repository centrale, soggetto a backup e ben sorvegliato.

Una volta deciso l’uso di questa tecnica, l’utilizzo da parte del client attraverso ADO.NET non prevede nulla di particolarmente complesso. Per quanto riguarda l’immissione delle informazioni nel database, una volta trasformata l’immagine in un array di byte, utilizzando un oggetto di tipo FileStream, il resto dell’operazione è esattamente uguale a qualsiasi altro comando inviato alla fonte dati.

 

//Scrivere un BLOB
public void StorePicture( string filename )
{
  // Leggo il file in un array di byte attraverso un FileStream
FileStream fs = new FileStream( filename, FileMode.Open, FileAccess.Read );
byte[] imageData = new Byte[fs.Length];
fs.Read( imageData, 0, (int)fs.Length );
fs.Close();
SqlConnection conn = new SqlConnection("");
SqlCommand cmd = new SqlCommand("StorePicture", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@filename", filename );
cmd.Parameters["@filename"].Direction = ParameterDirection.Input;
cmd.Parameters.Add("@blobdata", SqlDbType.Image);
cmd.Parameters["@blobdata"].Direction = ParameterDirection.Input;
  // passo l’array al parametro della procedura
cmd.Parameters["@blobdata"].Value = imageData;
  try
{
conn.Open();
cmd.ExecuteNonQuery();
}
  catch
{
throw;
}
  finally
{
conn.Close();
}
}

 

Anche la procedura per estrarre le informazioni dal database non presenta particolari difficoltà e sfrutta il metodo GetBytes() dell’oggetto SqlDataReader per ottenere con letture successive il contenuto del campo e inserirlo nell’array di byte che verrà successivamente utilizzato dall’applicazione.

 

//Leggere un BLOB
// Il comando (già creato in precedenza) legge attaverso una SELECT l’immagine
conn.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
reader.Read();
// Ottengo la dimensione in byte dell’immagine
long bytesize = reader.GetBytes(0, 0, null, 0, 0);
// Alloco l’array di byte per contenere l’immagine
byte[] imageData = new byte[bytesize];
long bytesread = 0;
int curpos = 0;
while (bytesread < bytesize)
{
  // chunkSize è un valore arbitrario definito dall’applicazione
bytesread += reader.GetBytes(0, curpos, imageData, curpos, chunkSize);
curpos += chunkSize;
}
// l’array di byte 'imageData' contiene l’immagine

 

 

In questo articolo abbiamo approfondito la conoscenza con il SQL Server .NET Data Provider, con particolare attenzione all’utilizzo di quest’ultimo in uno scenario “connesso” alla fonte dati SQL Server utilizzando soprattutto le Stored Procedure come strumento di programmazione lato server. Abbiamo volutamente tralasciato l’utilizzo del provider in condizione “disconnessa” insieme a componenti quali il SqlDataAdapter o il DataSet, che verranno trattati in un successivo articolo particolarmente focalizzato su quello scenario. Anche l’accesso via XML e HTTP verrà sviscerato adeguatamente nei prossimi articoli.

 

Stay tuned!!!