SQL - Stefano Furlan

SQL SERVER
Modulo 4
Relatore: Stefano Furlan
Sommario
• Criteri per scrivere del buon codice TSQL
• Con cenni a Indici e performances
• Breve carrellata di funzionalità SQL server
• A conclusione della panoramica già fatta
• Cenni ad amministrazione del server
• Utenti e permissioni, setup, backup ecc.
SCRIVERE BUON CODICE TSQL
Pensiero iterativo
VS
Pensiero set –based
• SQL server non è C#
• Inefficientissimo per i cicli
• Spaventosamente efficiente per operazioni di parallelizzazione o
ricerca
SCRIVERE CODICE TSQL DI QUALITÀ
• Mai usare cicli
• Anche la generazione di dati che abbiamo fatto per prova era
fattibile in tempi brevissimi e per milioni di elementi senza usare i
cicli
• Evitare se possibile un approccio iterativo
• Evita Roundtrip tra client e Database
• Cercare di scrivere query con JOIN opportuni
• Usare in caso tabelle temporanee
PROMEMORIA
• Un campo ha un certo valore (o piccola lista di valori)
• SELECT….WHERE o WHERE IN (….)
• Una tabella è contenuta in un’altra
• SELECT ….. INNER JOIN
• Una tabella NON esiste in un’altra
• SELECT … LEFT JOIN … WHERE tabella2.campo IS NOT NULL
• Contare quante righe ci sono per un certo raggruppamento
• SELECT … GROUP BY raggruppamento HAVING condizione
• Recupero di informazioni tramite foreign key(es: decodifica di
parametri numerici)
• JOIN
• Anomalia se il pannello non esiste
ALTRE FUNZIONALITA’
• FUNZIONI
• Richiamabili da query
• Solitamente non usate: problematiche di performances
• TABLE FUNCTIONS
• «viste» parametriche
• Anche qui problemi di performances
• Tipi di dato
• Possibilità di creare tipi di dato cutsom
• NON SI USANO SPESSO
TEMP TABLES
• Si tratta di tabelle create «on the fly» in un database di
sistema: il TEMPDB
• Utili per passaggi complessi dell’elaborazione
• Vengono droppate appena la connessione si chiude
• Prefisso #
• ES: create table #tabellaTemporanea
•(
• idPannello int
• Nome varchar(200)
•)
TEMP TABLES
• Dentro alla procedura possono essere tranquillamente
usate come se fossero tabelle vere
• Al termine della procedura è buona norma eliminarle
• DROP TABLE #tabellaTemporanea
ESERCITAZIONE
• Riscrivere la store di verifica della trasmissione ma
• creando una tabella temporanea all’inizio della procedura
• in cui vengono messi i dati della sola trasmissione da controllare
• La tabella temporanea dovrà avere una colonna testuale in più in
cui metteremo il motivo dello scarto
• Limitarsi per semplicità a controllare le anomalie
• QTA<0 o >500
• Coppia idpannello-datamisurazione già presente
• Al termine, se vi sono anomalie effettuare una SELECT dei soli dati
anomali
• DROP della tabella temporanea
• Notare che la procedura scritta è molto più semplice della vecchia
versione
Ultimi esercizi
• Estrarre tutti i dati di energia prodotta per l’IdPannello 1
• Sommarla in un unico totale
• Sommarla per ogni mese in maniera da ottenere 12 righe
• Qta prodotta per tipo di pannello
• TipoPannello (descrizione testuale)
• QTA sommata
• Numero di righe di quantità rilevata
• Numero di pannelli distinti
• Quantità prodotta per ogni mese e pannello
• Idpannello
• Mese
• Qta sommata
• Ordinata per mese e idpannello
ALTRI ESERCIZI
• Con una query creare un nuovo pannello
• Scoprire l’id pannello che è stato assegnato dal database alla
•
•
•
•
•
riga appena creata
Creare una riga di energia prodotta in data odierna con 200kwh
per l’impianto creato
Verificare con la query che mostrava il totale per ogni impianto
Prendere tutti i valori di energia prodotta dall’impianto 1 per
aprile e maggio 2013 e Reinserirli in tabella energia prodotta
ma per l’impianto appena creato
Sostituire la energia prodotta di tutte le righe dell’impianto
appena creato con 0 (zero) e verificare che è successo con la
query
Eliminare tutte le righe dell’impianto creato
Altre cose da tenere a mente
• Il Backup
• Disaster recovery
• (problema TLOG)
Utenti, Ruoli e Permissioni
• MAI usare «sa»!
• Autenticazione SQL vs Autenticazione windows
• Permissioni a livello server e a livello database
• I ruoli più importanti
• Datareared
• Datawriter
• Owner
(ha diritto di vita e di morte sul db)
• Permissioni su singolo oggetto
• ES: stored procedure
• MAI usare «sa»!
INDICI
• Strutture interne al server che facilitano l’ordinamento
• Una definizione precisa è oltre alla portata del corso
• Se una clausola where viene eseguita su campo non
indicizzato
• Il server dovrà leggere tutte le righe per rispondere
• Ogni indice occupa spazio
• Se i dati sono pochi l’assenza di indici non si fa sentire
• In definitiva:
• Aggiungere indici solo su tabelle molto molto grandi
• Indicizzare solo colonne non in chiave primaria
• E su cui si pensa di dovere eseguire delle ricerche
• ES: tabella tabEnergiaProdotta
Installazione sql server
• Useremo versione 2008 Express
UTILIZZO DI C# PER LE
CHIAMATE A SQL
SERVER
Architettura di un sistema di telecontrollo
Dati
Sistema di
controllo
Controllo
Pannelli
fotovoltaici
Utente
Collettore
dati
DB
Applicazione
di
configurazio
ne (CRUD)
Sistema di
reportistica
SQL e C#
• SQL server non fornisce un’interfaccia utente di alcun
genere
• A differenza di access
• C# usato per creare quest’interfaccia
• Lettura dei dati
• Gestione delle configurazioni (CRUD)
• Utile mettere tutte le funzioni di accesso ai dati in un unico
posto
• Data Access Layer (DAL)
Interfaccia CRUD
• CR come CREATE
• Aggiungo una riga
• U come Update
• Modifico i dati
• D come DELETE
• Elimino
• Tipi di interfaccia comuni
• Edit su Tabella
• Lista con edit su form separato
• Master-Detail
Passi per la chiamata da C#
• Richiamare la libreria sqlClient
• Creare un oggetto connection
• Aprirlo
• Creare un oggetto COMMAND per la Connection così
creata
• Assegnare all’oggetto command i parametri necessari
• Chiamare il metodo di esecuzione del COMMAND
• Pulire tutto prima di sucire (dispose)
Apertura della connessione
• using System.Data.SqlClient;
• SqlConnection conn;
• conn=new SqlConnection(«stringadiconnessione»);
• conn.Open();
• Stringadiconnessione=«Server=localhost\SQLLOCAL;Dat
abase=dbTestFotovoltaico;User Id=utenteApplicazione;
Password=aspirapolvere»;
Dichiarazione del Command
• SqlCommand command;
• command=new SqlCommand(«testocomando»,conn);
• «testocomando» è una query
• INSERT INTO tabPannelli (descrizione) VALUES
(‘pannellocreatodac’)
Esecuzione di una query che non ritorna
dati
• command.ExecuteNonQuery();
Esecuzione di una query che ritorna dati
• SqlDataAdapter da = new SqlDataAdapter();
• da.SelectCommand = cmd;
• DataSet ds = new DataSet();
• da.Fill(ds);
Chiamata a procedura con parametri
• SqlCommand cmd = new SqlCommand("spVerificaDatiTrasmissione",
conn);
• cmd.CommandType = System.Data.CommandType.StoredProcedure;
• SqlParameter key = new SqlParameter("@idTrasmissione",
SqlDbType.SmallInt);
• key.Value = 1;
//id della trasmissione!!!!
• key.Direction = ParameterDirection.Input;
• cmd.Parameters.Add(key);
•
•
•
•
SqlParameter msg = new SqlParameter("@msg", SqlDbType.VarChar);
msg.Size = 255;
msg.Direction = ParameterDirection.Output; // This is important!
cmd.Parameters.Add(msg);
•
• cmd.ExecuteNonQuery();
Se la procedura ritorna dati…
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
SqlConnection conn = new SqlConnection("Data Source=nomeserver;Initial Catalog=dbTestFotovoltaico
;User=utenteApplicazione;Password=aspirapolvere");
conn.Open(); //apro la connessione
SqlCommand cmd = new SqlCommand("spVerificaTrasmissione2", conn);
//creo l’oggetto command
cmd.CommandType = CommandType.StoredProcedure;
//tipo del comando: per la stored proc è obbligatorio!
//creo il parametro
SqlParameter idTrasmissione = new SqlParameter("@idTrasmissione", SqlDbType.Int); //qui specifico il tipo dato
idTrasmissione.Direction = ParameterDirection.Input;
//direzione(INPUT o OUTPUT)
idTrasmissione.Value = 1; //il valore che sto passando alla store(qui abbiamo cablato 1 ma poteva essere una variabile)
cmd.Parameters.Add(idTrasmissione); //aggiungo il parametro all’oggetto command
//eseguo il comando
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close(); //chiudo la connessione
//esecuzione della store
//recupero dei dati tornati dalla store