Press "Enter" to skip to content

2 – Lavorare con i dati – Database e .net

La base di tutte le librerie del Framework che lavorano con i Database è ADO.Net, anche se oggi è di moda utilizzare dei framework che sono nati per fornire dei generatori di codice semiautomatici che generano tutta la gestione a basso livello della comunicazione con i Database. Quello più usato è Entity Framework. In realtà quello di cui parleremo è esattamente quello che i generatori di codice utilizzano infine per leggere e scrivere dati nei database.

ADO.Net fornisce due diversi strati di classi che permettono di lavorare con i Database. lo strato di dialogo di base è costituito dai Provider dati, alcuni sono forniti direttamente dal Framework, altri invece sono prodotti di terze parti. I Provider Dati forniscono un interfaccia standard che colloquia con i database attraverso le loro librerie specializzate. Vediamo quali sono i provider dati standard di .Net:

  • Sql Provider, che dialoga con SQL Server
  • OleDb Provider, che dialoga con qualsiasi fonte dati OleDb
  • ODBC Provider, che dialoga con qualsiasi fonte dati ODBC

Vi sono poi i provider di terze parti che permettono di dialogare in modo equivalente con i database maggiormente utilizzati. Oracle, DB2, Mysql e Sqlite hanno tutti un provider .Net, ma probabilmente ve ne sono altri, e comunque OleDb e ODBC sono in grado di parlare con buona parte di tutti i database relazionali.

Tutti i provider dati specifici, forniscono una serie di componenti equivalenti per comunicare con il database che sono:

  1. Connection – l’oggetto che apre una comunicazione con il database tramite una Connection String.
  2. Command – l’oggetto in grado di eseguire qualsiasi tipo di Query sul database, usando la Connection.
  3. DataReader – l’oggetto in grado di leggere dati creando un cursore read only forward only sul database.
  4. DataAdapter – un oggetto complesso che è in grado di gestire al suo interno tutto il necessario a manipolare in modo semi automatico tutti i dati di una tabella database.

Ogni provider implementa questi oggetti dando convenzionalmente un nome con un prefisso legato al driver ed il suffisso qui sopra indicato.

Pertanto in .Net abbiamo:

  • SqlConnection, OleDbConnection, OdbcConnection
  • SqlCommand, OleDbCommand, OdbcCommand
  • SqlDatareader, OleDbDatareader, OdbcDatareader
  • SqlDataAdapter, OleDbDataAdapter, OdbcDataAdapter

E nei provider di terze parti troveremo gli equivalenti.

I provider dati di base, forniscono in output o utilizzano per l’input dei dati gli oggetti generici del framework, quali la DataTable, il Dataset, la DataRow.

Quando uscì Visual Studio 2005, questi erano gli unici strumenti che permettevano di comunicare con i Database, esisteva anche un designer grafico (che ho usato solo per il mio primo progetto .Net e poi abbandonato in favore del codice scritto utilizzando un generatore di codice), che permetteva di generare automaticamente un Dataset che mappava tutte o parte delle tabelle di un database, creando degli oggetti tipizzati che mappavano i dati di ogni tabella che veniva inclusa nell’insieme gestito dal DataSet. Un Dataset poteva contenere più tabelle di un database e tramite i loro DataAdapter mappava in modo tipizzato su classi autogenerate le tabelle del Database.

Sfortunatamente, come tutti i generatori automatici, era un oggetto perfetto per gestire database “immobili” ovvero database così consolidati da non aver bisogno di essere modificati, era molto farraginoso nel lavoro di sviluppo e supporto allo sviluppo di Database non ancora consolidati, ove vi era la necessità di modificare e aggiornare le tabelle molto spesso.

A causa di tutto questo, per la mia azienda ho preferito creare un generatore automatico che costruisce una classe dati per ogni tabella del database, e può rigenerarla in ogni momento se necessario aggiungere, togliere o modificare i campi della tabella.

Allo stesso modo, molti, molto più bravi e con esigenze molto più complesse delle mie hanno iniziato a pensare a dei Framework che fossero in grado di generare il codice in modo strutturato, da qui, sono nati vari framework dati, quello con il maggior successo ad oggi è l’Entity Framework di Microsoft.

Si tratta di oggetti che astraggono dalle mani del programmatore tutta la gestione database a basso livello, facendo in modo che comunichi con delle Data Entities, ovvero delle classi tipizzate che rappresentano ogni tabella, e hanno degli strumenti che permettono di aggiornare e rigenerare il codice che colloquia con il database in modo automatico.

Non ho mai partecipato ad un progetto che utilizzasse questi strumenti, e non ho mai pensato un porting dei miei software perché essendo fra i maniaci del controllo, avendo creato un generatore di codice che mi dava completo controllo sul database e sul suo funzionamento, con la libertà di poter rigenerare il codice nel modo più semplice, non ho mai visto un reale vantaggio in Entity Framework. Di certo, se mi troverò in un nuovo progetto e qualcuno mi mostrerà quanto possiamo risparmiare in termini di tempo nel lasciar gestire tutto a Entity Framework, lo userò volentieri.

I limiti dei Dataset e dei DataAdapter che sono comunque ancora presenti ed utilizzabili, visto che anche Entity Framework nel suo codice poi andrà ad usare gli oggetti base dei provider dati, stanno nel fatto che quando si gestiscono tabelle relazionate con relazioni forti per cui l’aggiornamento di una tabella dipende da quello di un altra ad essa relazionata (Master e Detail e Master con MultiDetail) non potevano essere gestite automaticamente, pertanto era complesso e complicato iniettare il codice necessario a gestire l’aggiornamento organizzato dei dati delle tabelle complesse ove l’ordine in cui i dati venivano inseriti nel database e l’uso di contatori automatici per le chiavi primarie andava ad interferire con il funzionamento basico fornito dai provider.

Entity Framework dovrebbe aver risolto questi problemi, anche se la conferma vi deve essere data da chi lo usa, io non posso farlo.

La domanda che potreste pormi adesso è:

Ma se vi sono tutti questi strumenti, perché mai perdi tempo a spiegarci strumenti a basso livello che nessuno usa?

Perché mi piace farmi del male?

No, semplicemente perché se le interazioni sono con dei piccoli database o con alcune tabelle di un database o se le interazioni sono di tipo diretto, spesso utilizzare gli strumenti più a basso livello può essere molto più semplice che utilizzare un framework complesso. E’ un po’ come agganciare un motore fuoribordo ad un pedalò per non voler pedalare. Per questo, credo sia opportuno mettere le mani in pasta. Imparare ad impastare la pasta per le tagliatelle a mano per capire come si fa anche se in seguito utilizzeremo sempre la planetaria per farla impastare. (O acquisteremo le tagliatelle pronte).

E dopo tutto questo lungo preambolo, andiamo alle cose concrete.

La soluzione AdoNetBasics

solution

Ho creato una soluzione con un progetto WPF, per mostrare come utilizzare 3 dei database più diffusi. Access, SqlServer, SQLite. I primi due sono accessibili tramite gli oggetti standard del framework .Net, rispettivamente il SQL data provider e l’OleDb data provider. Il terzo ha un suo specifico provider.

Parliamo di SQLite

Questo database ha un proprio data provider .Net che dovete scaricare dal sito web sqlite.org, aggiungo alcune raccomandazioni per far funzionare i progetti.

In questo caso, io ho scaricato la seguente versione:

sqlite-netFx451-setup-x86-2013-1.0.103.0.exe

Che al suo interno contiene il necessario runtime di C++ The Visual C++ 2013 Update 2 runtime for x86;  fate attenzione, se in uno dei vostri progetti volete utilizzare e distribuire SQLite, oltre alle librerie che vi mostro come referenziare e copiare nel setup, è necessario fare in modo che questo runtime sia installato sulla macchina degli utenti che useranno il vostro programma. Cosa significa questo? Che quando create il setup dovrete indicarlo nei requisiti e fare in modo che sia installato dal sito Microsoft.

La versione che ho scaricato è predisposta per macchine a 32bit pertanto funziona sia sulle macchine più vecchie che su quelle a 64bit, ma SQLite ha anche una versione specifica per le macchine a 64bit, pertanto se deciderete di usarlo, dovrete decidere anche quale versione utilizzare. Oppure dovrete creare due librerie diverse che usano le due versioni e usare una o l’altra in base al tipo di sistema su cui installerete.

Se vi chiedete perché sto parlando di SQLite, è semplicemente perché è un prodotto che è stato portato sul maggior numero di device esistente nell’universo informatico, pertanto funziona su Mobile, su Desktop, su Linux, su Mac e quindi è molto probabile che lo incontriate in futuro, pertanto meglio sapere come funziona da subito.

Se guardate l’immagine della mia soluzione credo possiate notare che ho inserito dentro all’insieme dei file del progetto SQLite.Interop.dll e SQLite.Interop.pdb. Perché non ho fatto un reference nell’apposita cartella dei reference? Perché se ci provate, vi darà un errore, visto che non sono librerie .Net ma librerie C++. Pertanto, per evitare errori a runtime che dicono “Non trovo la libreria SQLite.Interop.dll”, il metodo più semplice è inserirla a progetto come “Content” contenuto e attivare l’opzione “Copy Always” che copia i due file nella cartella Bin\Debug o Bin\Release quando compilate l’applicazione.

Oltre a inserire queste due librerie, che sono il wrapper C++, dovete referenziare, in questo caso nel modo usuale, System.Data.SQLite.dll

solution_02

Che, da buoni studenti, se avete seguito gli articoli precedenti, soprattutto quello dedicato alle librerie NuGet, sapete si referenzia con

  • Add Reference
  • Browse
  • Vi spostate sulla cartella C:\Program Files (x86)\System.Data.SQLite\2013\bin
  • E selezionate System.Data.SQLite.dll

Come potrete notare, se curiosate sul sito di SQLite, ci sono anche gli strumenti e le dll a corredo per poter utilizzare Entity Framework per modellare il vostro database SQLite.

Se volete cimentarvi con altri Database, come Mysql, Oracle o altro, i loro provider sono sicuramente molto simili da installare ed utilizzare.

Il progetto AdoNetBasics

Andando al sodo, in questo primo articolo vedremo come leggere i dati da 3 database diversi, contenenti la stessa tabella, utilizzando gli oggetti base del framework.

Per aiutarvi, ho inserito nella soluzione tutti e tre i database, per far funzionare la soluzione com’è, dovete procedere nel seguente modo:

Create una cartella C:\TestAccessDb sul vostro disco e copiatevi TestAccessDb.accdb che trovate nella soluzione.

Create una cartella C:\TestSqliteDb sul vostro disco e copiatevi TestSqliteDb.sqlite che trovate nella soluzione.

Aprite il SQL Management Studio del SQLExpress o del SQL Server full che avete installato sul vostro PC ed utilizzate l’opzione Restore Database per effettuare il restore di TestSqlServerDb.Bak che trovate sempre dentro al progetto.

Dopo aver effettuato queste operazioni, siete pronti per lanciare il progetto, molto molto semplice che vi spiega come leggere i dati dai tre database.

Vediamo il codice che ho scritto sul click dei tre Button inseriti nella semplicissima interfaccia WPF.

02_ado_mainwindow_01

L’interfaccia è quella che vedete qui sopra, c’è una textbox che occupa buona parte dello spazio dove visualizzeremo i dati letti e un bottone per ognuno dei 3 diversi server e database a cui ci connetteremo.

Il Database

TheDatabase

I tre database a corredo, contengono tutti la stessa tabella, una tabella demografica dimostrativa con dati relativi a delle persone. Ho inserito l’immagine di quella di Access, ma le tabelle degli altri due database sono pressoché identiche.

Le stringhe di connessione

La prima cosa da sapere per connettersi a qualsiasi Database relazionale da .Net è che per farlo avete bisogno di una stringa di connessione. La stringa di connessione viene utilizzata dall’oggetto Connection del provider dati che utilizzerete per agganciare il server dati con cui volete parlare (o il driver a basso livello nel caso dei 2 database che non hanno un vero e proprio server) e per fornire loro le credenziali di sicurezza per l’accesso, che nel caso di SQL Server possono essere le nostre credenziali windows oppure uno UserName ed una Password, e nel caso di Access e SQLite invece una semplice password.

Se cercate una stringa di connessione e non sapete come è fatta, potete andare sul sito www.connectionstrings.com dove sono listate tutti i tipi di connessioni a tutti i database dell’universo informatico.

Nel nostro caso, le tre stringhe di connessione che ho predisposto sono le seguenti:

private const string CNS_Access = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\TestAccessDb\\TestAccessDb.accdb;Persist Security Info=True;";
private const string CNS_SqlServer = "Server=localhost;Database=TestSqlServerDb;Trusted_Connection=True;Persist Security Info=True";
private const string CNS_SqLite = "Data Source=C:\\TestSqliteDb\\TestSqliteDb.sqlite;Version=3;";

Per Access, è necessario un Provider OLEDB (è un driver che parla con le API base di office e del motore Access), solitamente viene installato con Office, potrebbe essere che quello che avete sulla vostra macchina sia di una diversa versione, verificate e in caso, se volete installare esattamente quello che ho usato io, potete fare riferimento al post che precede questo, visto che nel mio caso, il PC troppo moderno non aveva quel provider.

SQL Server invece, funziona in modo diverso, infatti essendo un vero e proprio Server, quando viene installato c’è un servizio che si occupa di far funzionare i vostri database e la comunicazione viene effettuata utilizzando il protocollo TCP/IP da qui il fatto che nella connection string utilizzo come Server localhost; fate attenzione, se avete installato SQLExpress senza guardare uno dei miei tediosi tutorial in merito, potreste dover usare un diverso nome server, tipicamente NOMEVOSTROPC\SQLEXPRESS per verificarlo aprite SQL Management Studio e connettetevi al vostro server locale, guardandone il nome.

SQLite ha una connection string sbrigativa, infatti utilizza solo il Path ed una Versione che indica quale versione delle API il vostro database utilizza.

Ho parlato di Credenziali per la connessione, per Access non ho settato alcuna password, quindi non c’è nulla al riguardo, Persist Security Info=True è un parametro che uso sempre perché altrimenti facendo 2 query dalla stessa connessione, se utilizziamo la password sarebbe necessario fornirla per ogni query.

Per SQL Server, ho utilizzato la sicurezza intrinseca di Windows, e quindi le credenziali di accesso sono fornite dal parametro Trusted_Connection=True, altrimenti dovrei fornire lo UserName e la Password. In questo caso, sempre se avete installato correttamente SQL Server (express o altro) sul vostro PC, avrete certamente incluso il vostro Utente negli utenti con privilegi di accesso e magari di amministrazione sul SQL Server locale.

Per SQLite, la password va indicata all’oggetto connection quando utilizzata, pertanto la vedremo in seguito.

La classe MainWindow.xaml.cs

Vediamo il codice vero e proprio.

public MainWindow()
{
	InitializeComponent();
	this.DataContext = this;
}

Nel costruttore, come sempre per i piccoli progetti dimostrativi, ho indicato la window come ViewModel di se stessa, assegnandola al DataContext.

public const string FLD_Data = "Data";
 
private DataTable mData;
 
public DataTable Data
{
	get
	{
		return mData;
	}
	private set
	{
		mData = value;
		OnPropertyChanged(FLD_Data);
	}
}

Ho creato una property di tipo DataTable che conterrà i dati della tabella dei nostri database. Come già accennato in precedenza, tutti i Provider dati a basso livello di ADO.net, forniscono degli oggetti generici che sono parte del framework e sono solitamente contenuti nel namespace System.Data, la DataTable è un oggetto generico che rappresenta una tabella dati, contiene delle DataColumn che definiscono le colonne della tabella, il loro nome, i dati che ospitano, e contiene delle DataRow che sono gli oggetti che fisicamente contengono i dati. Una DataTable può essere definita da codice, diciamo pure preconfezionata, oppure, come nel nostro caso, verrà generata a partire dai dati che vi inseriremo all’interno.

public const string FLD_ResultText = "ResultText";

private string mResultText;
 
public string ResultText
{
	get
	{
		return mResultText;
	}
	set
	{
		mResultText = value;
		OnPropertyChanged(FLD_ResultText);
	}
}

La seconda property generata, è la stringa in cui andremo a mettere i dati che leggeremo dai database per visualizzarli nella nostra TextBox a video, che è stata collegata in Binding a questa property.

private void LoadAccess_Click(object sender, RoutedEventArgs e)
{
	try
	{
		Data = null;
		using (OleDbConnection cn = new OleDbConnection(CNS_Access))
		{
			OleDbCommand cmd = new OleDbCommand();
			cmd.CommandText = "Select * from TbTest";
			cmd.Connection = cn;
			cn.Open();
			OleDbDataReader reader = cmd.ExecuteReader();
			if (reader.HasRows)
			{
				Data = new DataTable();
				Data.Load(reader);
			}
 
			cn.Close();
		}
		UpdateResultText("Access table contains");
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
		ResultText = "An Error has occurred" + Environment.NewLine + ex.Message;
	}
}

Il primo metodo che vediamo è quello per il caricamento dal database Access. Per caricare i dati, eseguiremo una semplice query di selezione sulla nostra tabella TbTest. Come potete notare nel codice, ho utilizzato la clausola using di C# per dichiarare la OleDbConnection, l’oggetto che fisicamente si collega al Database per trasmettere e ricevere dati. E’ sempre buona norma utilizzare questo tipo di clausola quando si utilizzano oggetti resource consuming, quali le connessioni dati, un po’ meno con database locali come Access e SQLite, molto di più con Database Relazionali come SQL Server, Mysql, o Oracle, perché il numero di connessioni disponibili verso il database è un canale finito, pertanto ogni connessione deve essere aperta e chiusa nel più breve tempo possibile, e l’oggetto riciclato dal Garbage Collector quanto prima possibile.

Ma torniamo al codice, come vedete, ho creato una OleDbConnection usando la connection string definita in precedenza, poi ho creato un OleDbCommand, l’oggetto che conterrà la nostra query, ho inserito il codice SQL per effettuare la query nel CommandText, ho indicato al Command di utilizzare la Connection già definita, ho aperto la connessione.

Per effettuare la query, ho eseguito il metodo ExecuteReader, è uno dei 3 metodi che tutti i provider implementano per eseguire le query, questo metodo restituisce un cursore Read Only Forward Only che negli standard .Net si chiama DataReader, ogni provider dati ne implementa uno. è il meno invasivo ed il più semplice mezzo per caricare i dati di una tabella (o una query su più tabelle) in un oggetto .Net.

L’operazione che compio in seguito è verificare se sono stati restituiti dei dati (attenzione il cursore read only forward only si posiziona sul primo dato restituito dalla query e non sa quante altre righe ci siano).

Se vi sono dati restituiti effettuo 2 comandi:

  1. Creo una nuova DataTable vuota e l’assegno alla property che abbiamo predisposto allo scopo.
  2. Eseguo il metodo Load della DataTable, passando come parametro il DataReader.

In questo modo, tutti i dati restituiti dalla query, vengono automaticamente caricati nella nostra DataTable.

Chiudo la connessione e chiamo il metodo che leggerà i dati aggiornando la User Interface.

In caso di errore, visualizzo il messaggio di errore e non faccio nulla.

private void LoadSqLite_Click(object sender, RoutedEventArgs e)
{
	try
	{
		Data = null;
		using (SQLiteConnection cn = new SQLiteConnection(CNS_SqLite))
		{
			cn.SetPassword("TestPwd");
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.CommandText = "Select * from TbTest";
			cmd.Connection = cn;
			cn.Open();
			SQLiteDataReader reader = cmd.ExecuteReader();
			if (reader.HasRows)
			{
				Data = new DataTable();
				Data.Load(reader);
			}
 
			cn.Close();
		}
		UpdateResultText("SQLite table contains");
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
		ResultText = "An Error has occurred" + Environment.NewLine + ex.Message;
	}
}

Le operazioni effettuate per collegarmi e leggere i dati su SQLite, sono praticamente identiche. Fornisco alla Connection la Password che ho voluto settare sul database quando l’ho generato in modo tale da poter testare questa funzionalità. Per il resto, la sola cosa che cambia in questo metodo è l’uso di una SQLiteConnection, di un SQLiteCommand e di un SQLiteDataReader.

private void LoadSqlServer_Click(object sender, RoutedEventArgs e)
{
	try
	{
		using (SqlConnection cn = new SqlConnection(CNS_SqlServer))
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = "Select * from TbTest";
			cmd.Connection = cn;
			cn.Open();
			SqlDataReader reader = cmd.ExecuteReader();
			if (reader.HasRows)
			{
				Data = new DataTable();
				Data.Load(reader);
			}
 
			cn.Close();
		}
		UpdateResultText("SQL Server table contains");
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
		ResultText = "An Error has occurred" + Environment.NewLine + ex.Message;
	}
}

Le operazioni per la connessione a SQLServer, sono identiche, anche in questo caso cambia il provider dati, per cui utilizzerò una SqlConnection, un SqlCommand e un SqlDataReader.

private void UpdateResultText(string title)
{
	try
	{
		if (Data == null || Data.Rows == null || Data.Rows.Count == 0)
		{
			ResultText = "The table of data is empty";
		}
		else
		{
			StringBuilder sb = new StringBuilder();
			sb.AppendLine(title);
			foreach (DataColumn col in Data.Columns)
			{
				sb.Append(col.ColumnName);
				sb.Append(" ");
			}
			sb.AppendLine();
			foreach (DataRow row in Data.Rows)
			{
				for (int x = 0; x < Data.Columns.Count; x++)
				{
					sb.Append(row[x].ToString());
					sb.Append(" ");
				}
				sb.AppendLine();
			}
			ResultText = sb.ToString();
		}
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
		ResultText = "An Error has occurred" + Environment.NewLine + ex.Message;
	}
}

Infine, il metodo che visualizza il contenuto della tabella letta sul database sull’interfaccia utente, si tratta semplicemente di utilizzare uno StringBuilder ove costruiamo una serie di righe di testo che visualizzano il contenuto della DataTable (la stessa indipendentemente dal database di origine). Utilizziamo la collection Columns della DataTable per visualizzare i nomi dei campi di origine e la collection Rows per leggere e convertire in stringa tutti i campi di ognuna delle righe della tabella.

Una volta terminato, mettiamo nella property ResultText, la stringa costruita.

Il risultato delle nostre 3 azioni sulla User Interface è il seguente:

Access_output

SQLite_Output

SQLServer_output

Ci sono 2 piccole differenze:

Il Database SQLite, automaticamente non visualizza i valori a zero, non so se si tratta di una regola di business interna o se è perché SQLite è nato principalmente per il mondo Mobile quindi cerca di non sprecare spazio, ma al momento, lo guardiamo e lo prendiamo come un comportamento, non influisce sullo scopo di questo articolo.

Il Database SQL Server inizia a contare da 2 l’ID dei record della tabella, questo è un mio problema, perché molto probabilmente ho inserito e poi cancellato una riga prima di aggiungere tutte le altre. I contatori automatici sono una gran bella cosa, ma sono anche un pochino permalosi. Anche in questo caso, registriamo la cosa, ma non influisce sullo scopo di questo articolo.

Un altra differenza che potreste notare è nel formato della data e ora che sulla mia macchina è settato in inglese.

Riepilogo

Cosa abbiamo spiegato in questo articolo:

  • Come funzionano gli oggetti base di .Net per parlare con i Database relazionali
  • Quali sono gli strati delle classi utilizzate.
  • Cosa sono i provider dati di base per i database relazionali.
  • Come si installa un provider di terze parti, in questo caso SQLite.
  • Cosa sono le Stringhe di Connessione.
  • Quali sono gli oggetti di base con cui chiedere dati ad un database relazionale.
  • Come utilizzare gli oggetti dati di base per leggere una tabella sui tre database esempio.

Potete scaricare il progetto di esempio dal link qui indicato:

Per qualsiasi domanda, osservazione, commento, approfondimento, o per segnalare un errore, potete usare il link alla form di contatto in cima alla pagina.