I passaggi descritti in questo articolo illustrano come connettersi a un'origine dati nell'IDE di Visual Studio e, più in dettaglio, come creare e configurare database SQL Server utilizzando script T-SQL, un approccio fondamentale per ogni DBA o sviluppatore.
Introduzione ai Database .mdf e .ldf
Il file .mdf è l'acronimo di Master Database File ed è un database SQL inserito in un file. Questi file hanno un associato .ldf (file di database di log) che contiene i log per tale database. È possibile creare nuovi database archiviati nei file .mdf aggiungendo un elemento database basato sul servizio al progetto.
È possibile usare direttamente i dati in Visual Studio. È possibile eseguire query, modificare i dati, creare e modificare le tabelle e le altre proprietà dello schema, modificare le stored procedure e le funzioni, i trigger e così via.
Connessione ai Database in Visual Studio
L'origine dati può essere un database locale, un servizio dati online o un database aperto da un file .mdf. È possibile aprire una connessione a un database o a un servizio o a un database LocalDB aperto da un file .mdf e visualizzare e modificare tabelle e righe di dati tramite Esplora server o Esplora oggetti di SQL Server.
Esplora server ed Esplora oggetti di SQL Server
La funzionalità di queste finestre si sovrappone in qualche misura. Esplora server, installato per impostazione predefinita in Visual Studio, può essere usato per testare le connessioni e visualizzare i database di SQL Server, qualsiasi altro database con un provider di ADO.NET installato e alcuni servizi di Azure. Mostra anche oggetti di basso livello, ad esempio contatori delle prestazioni di sistema, registri eventi e code di messaggi.
Esplora oggetti di SQL Server è installato con SQL Server Data Tools e visibile nel menu Visualizza. Se non viene visualizzato, passare a Programmi e funzionalità nel Pannello di controllo, trovare Visual Studio e quindi selezionare Cambia per eseguire di nuovo il programma di installazione dopo aver selezionato la casella di controllo per SQL Server Data Tools. Usare Esplora oggetti di SQL Server per visualizzare i database SQL (se hanno un provider di ADO.NET), creare nuovi database, modificare gli schemi, creare stored procedure, recuperare stringhe di connessione, visualizzare i dati e altro ancora.
Aggiunta di una Connessione al Database
Per creare una connessione al database, fare clic sull'icona Connetti al database in Esplora server oppure fare clic con il pulsante destro del mouse in Esplora server nel nodo Connessioni dati e selezionare Aggiungi connessione.
Viene visualizzata la finestra di dialogo Aggiungi connessione. Scegliere un'origine dati e selezionare Continua per passare alla finestra di dialogo Aggiungi connessione. Dopo aver scelto l'origine dati, fare clic su Test connessione. Se la connessione ha esito positivo, scegliere OK. Se l'origine dati non è quella desiderata, fare clic sul pulsante Cambia per scegliere una nuova origine dati o un nuovo provider di dati ADO.NET. È possibile scegliere tra diversi tipi di autenticazione che coprono un'ampia gamma di scenari.
Se non si ha accesso a un altro database e non è installato LocalDB, è possibile installare LocalDB tramite il programma di installazione di Visual Studio, come parte del carico di lavoro Archiviazione ed elaborazione dei dati, del carico di lavoro di sviluppo ASP.NET e Web o come singolo componente.
Configurazione delle Opzioni di Sicurezza (Visual Studio 2022 v17.8 e Successive)
In Visual Studio 2022 versione 17.8 e successive, la finestra di dialogo di connessione include due nuove opzioni (Crittografa e Certificato del server di attendibilità) che entrano nella stringa di connessione e influiscono sulle impostazioni di sicurezza usate per la connessione. Queste opzioni supportano le funzionalità di sicurezza più rigorose del driver di database Microsoft.Data.SqlClient 4.0. La procedura di sicurezza consigliata consiste nell'usare la crittografia e installare un certificato nel server. Vedere Crittografia e convalida dei certificati.
Se non si imposta Crittografa su facoltativo con Visual Studio 17.8 o versione successiva, che usano la versione 4.0 di Microsoft.Data.SqlClient, per impostazione predefinita la crittografia è obbligatoria. Questa è una modifica sostanziale rispetto al comportamento nelle versioni precedenti. Se si verifica l'errore "È stata stabilita una connessione con il server, ma si è verificato un errore durante il processo di accesso. (provider: provider SSL, errore: 0 - La catena di certificati è stata rilasciata da un'autorità non attendibile)", è probabile che sia dovuto a queste impostazioni di sicurezza. Se è necessario impostare altre impostazioni nella stringa di connessione, è possibile usare il collegamento Avanzate, che visualizza tutte le impostazioni.
Considerazioni su 32-bit e 64-bit
Questo significa che alcuni degli strumenti di dati in Visual Studio non saranno in grado di connettersi ai database OLEDB o ODBC usando provider di dati a 32 bit. Se è necessario gestire applicazioni a 32 bit che si connettono a OLEDB o ODBC, sarà comunque possibile compilare ed eseguire l'applicazione con Visual Studio 2022 e versioni successive. Tuttavia, se è necessario usare uno degli strumenti dati di Visual Studio, ad esempio Esplora server, Creazione guidata origine dati o Progettazione set di dati, sarà necessario usare una versione precedente di Visual Studio che è ancora un processo a 32 bit. Se l'applicazione usa database di Microsoft Access e può convertire il progetto a 64 bit, è consigliabile usare il motore di database di Microsoft Access a 64 bit, denominato anche Access Connectivity Engine (ACE).
Integrazione con Applicazioni
Se si usa .NET Framework (non .NET Core o .NET 5 o versione successiva) e Windows Form o WPF, è possibile usare la finestra Origini dati, ad esempio, per configurare il data binding per i controlli nelle applicazioni Windows Form e WPF. Vedere Aggiungere nuove origini dati.
Se si usa .NET 5 o versione successiva, .NET Core o ASP.NET Core, è possibile connettere l'app al database usando Servizi connessi. Usando Servizi connessi, è possibile usare facilmente un database di sviluppo locale, ospitato da SQL LocalDB, SQL Server in esecuzione in un contenitore o un'istanza locale di SQL Server e quindi passare al database SQL di Azure quando si è pronti per la distribuzione nel cloud.
Creazione di un Database SQL Server tramite Script T-SQL
Creare un Database in SQL Server è una cosa molto facile da fare, basta utilizzare SQL Management Studio in versione Developer o in versione Express e con pochi click del mouse si ottiene un database. Tuttavia, se volessimo saperne di più, se volessimo poter calibrare il database in base all’installazione, se volessimo capire i concetti fondamentali da DBA legati alla gestione di un database, sapere come crearlo tramite script potrebbe essere una cosa interessante.
Per fare questo tipo di tuning sui database, è opportuno saperne di più. Con SQL Management Studio si può fare tutto, ma SQL Management Studio non fa altro che costruire degli Script T-SQL ed eseguirli; vediamo come costruirci uno script di base per creare qualsiasi database.
L’esempio che verrà mostrato è stato creato su un server SQL 2005, ma funziona perfettamente su 2008 e 2012 perché anche nel caso degli script, i concetti di base non cambiano, possono solo esserci dei miglioramenti funzionali e delle nuove features fornite dalle versioni più recenti di SQL Server. Gli script che vedremo funzionano allo stesso modo anche sulla versione Express di SQL Server che ha solo alcune limitazioni "fisiche" impostate, non ha limitazioni funzionali.
Struttura di Base dello Script
Per iniziare bene il nostro script, inseriamo un commento sulla prima riga, ad esempio con un ID e il commento "Ricordarsi di sostituire ##DNWDbName## con il nome…". Questo serve perché, per principio, non è bello cablare nelle applicazioni il nome del database, dato che si potrebbe avere la necessità di più database collegati all’applicazione o di installare il database su un server di produzione con una specifica nomenclatura.
Veniamo al codice vero e proprio: USE [master] è il solo vero comando di questo primo pezzo del nostro script. A cosa serve? Consente di specificare il database di contesto in cui verranno eseguiti i comandi successivi. E GO? GO non è un comando T-SQL, ma un comando riconosciuto da utilità come SQL Server Management Studio che indica la fine di un batch di istruzioni T-SQL e l'invio al server.
Dovete avere diritti amministrativi per eseguire questa porzione di script, quindi appartenere ad un ruolo SecurityAdmin oppure SysAdmin. sys.syslogins è una delle viste di sistema che permettono di effettuare delle query per vedere quali sono gli utenti che sono stati generati nel nostro server.
Creazione di Login e Utenti
La password, che si trova nella creazione del Login, va ovviamente impostata ad un valore meno banale. Il nome utente ‘dnwappuser’ è un altro parametro che si può modificare a piacere.
Lo Script di Creazione del Database: Filegroup e File di Dati
Finalmente la parte centrale dello script, lo script di creazione. Per fare un esempio interessante, abbiamo predisposto la creazione di un database che contenga due Filegroup e due file per i dati principali:
- Il Master Data File (PRIMARY), che conterrà tutte le tabelle e i dati di base.
- Il (BINARYDATA), a cui abbiamo assegnato questo nome perché in esso inseriremo tutti i dati di tipo BLOB del nostro database, quindi Image, Ntext, NVarchar(max), Varbinary(max).
A cosa serve separare i dati normali dai dati di tipo binario? Serve principalmente per ottimizzare le prestazioni I/O e la gestione dello spazio di archiviazione.
FILENAME indica il path dove questo file sarà creato e dove il server andrà a cercarlo quando necessario. Un database avrà sempre un solo file .mdf e zero o più file .ndf (secondary data files). MAXSIZE indica fino a quale dimensione il file può crescere. È opportuno limitarlo ad una dimensione massima quando usiamo un SQL Server non Express; la versione Express infatti ha una limitazione di 2, 4, 10 GB rispettivamente nelle versioni 2005, 2008 e 2012 di SQL Server.
La cartella dati del server non si trova sulla mappatura di default, che la mette in una sottocartella della cartella “Programmi” di Windows, ma è mappata ad esempio sulla cartella D:\Sql.dir\Data. Decidere dove vanno messi i dati da parte di SQL Server è una delle prime azioni da vero DBA che chiunque si avvicini a SQL Server e voglia usarlo per sviluppare applicazioni dovrebbe imparare.
Assegnazione dei Permessi agli Utenti
Infine, assegniamo all’utente mappato sul database tre ruoli che gli daranno accesso ai dati:
- db_datareader: permette di effettuare query di selezione su tutti gli oggetti.
- db_datawriter: permette di aggiungere e modificare dati su tutti gli oggetti.
- db_workuser: il ruolo da noi creato, a cui assegneremo i permessi di esecuzione su stored procedure e user defined function o altri permessi diversi da quelli dei due ruoli predefiniti.