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