SQL SERVER

1. SQL SERVER Modulo 4 Relatore: Stefano Furlan

2. 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.

3. 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

4. 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

5. 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 foreignkey(es: decodifica di parametri numerici) • JOIN • Anomalia se il pannello non esiste

6. 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

7. 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 • ( • idPannelloint • Nome varchar(200) • )

8. TEMP TABLES • Dentro alla procedura possono essere tranquillamente usate come se fossero tabelle vere • Al termine della procedura è buona norma eliminarle • DROP TABLE #tabellaTemporanea

9. 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

10. 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

11. 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

12. Altre cose da tenere a mente • Il Backup • Disasterrecovery • (problema TLOG)

13. 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»!

14. 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

15. Installazione sql server • Useremo versione 2008 Express

16. Utilizzo di C# per le chiamate a SQL SERVER

17. Architettura di un sistema di telecontrollo Dati Sistema di controllo Controllo Pannelli fotovoltaici Utente Applicazione di configurazione (CRUD) Collettore dati DB Sistema di reportistica

18. 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)

19. 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

20. 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)

21. Apertura della connessione • usingSystem.Data.SqlClient; • SqlConnectionconn; • conn=new SqlConnection(«stringadiconnessione»); • conn.Open(); • Stringadiconnessione=«Server=localhost\SQLLOCAL;Database=dbTestFotovoltaico;User Id=utenteApplicazione;Password=aspirapolvere»;

22. Dichiarazione del Command • SqlCommandcommand; • command=new SqlCommand(«testocomando»,conn); • «testocomando» è una query • INSERT INTO tabPannelli (descrizione) VALUES (‘pannellocreatodac’)

23. Esecuzione di una query che non ritorna dati • command.ExecuteNonQuery();

24. Esecuzione di una query che ritorna dati • SqlDataAdapter da = new SqlDataAdapter(); • da.SelectCommand= cmd; • DataSetds = new DataSet(); • da.Fill(ds);

25. Chiamata a procedura con parametri • SqlCommandcmd = new SqlCommand("spVerificaDatiTrasmissione", conn); • cmd.CommandType= System.Data.CommandType.StoredProcedure; • SqlParameterkey = new SqlParameter("@idTrasmissione", SqlDbType.SmallInt); • key.Value= 1; //id della trasmissione!!!! • key.Direction= ParameterDirection.Input; • cmd.Parameters.Add(key); • SqlParametermsg = new SqlParameter("@msg", SqlDbType.VarChar); • msg.Size= 255; • msg.Direction= ParameterDirection.Output; // This is important! • cmd.Parameters.Add(msg); • cmd.ExecuteNonQuery();

26. Se la procedura ritorna dati… • SqlConnectionconn = new SqlConnection("Data Source=nomeserver;InitialCatalog=dbTestFotovoltaico ;User=utenteApplicazione;Password=aspirapolvere"); • conn.Open(); //apro la connessione • SqlCommandcmd = new SqlCommand("spVerificaTrasmissione2", conn); //creo l’oggetto command • cmd.CommandType = CommandType.StoredProcedure; //tipo del comando: per la storedproc è obbligatorio! • //creo il parametro • SqlParameteridTrasmissione = 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(); //esecuzione della store • SqlDataAdapterda = new SqlDataAdapter(); //recupero dei dati tornati dalla store • da.SelectCommand = cmd; • DataSetds = new DataSet(); • da.Fill(ds); • conn.Close(); //chiudo la connessione