Questo articolo, pubblicato in parallelo all’articoloSQL Server Nozioni di base (parte 1) mostra come generare tramite una libreria di classi un Database all’interno di SQL Server. La costruzione del codice per la creazione database ci da modo di iniziare a costruire una ulteriore libreria di base che si aggiunge a quelle create in Classi di uso comune, che fornisce le funzionalità necessarie al dialogo diretto con SQL Server. Queste funzionalità vanno dalla creazione e test di una connection string, alla esecuzione di query di tipo scalare, alla esecuzione di query di comando senza valori di ritorno.
Ovviamente, questa libreria si presterà a divenire una libreria di tipo Work In progress, a cui aggiungere funzionalità mentre impariamo nuovi concetti relativi al mondo di ADO.
Introduzione
In questo articolo vedremo come gettare le basi per una libreria che utilizza ADO per compiere operazioni dirette sui database con focalizzazione su SQL Server 2005, ma usando la libreria OleDb o la libreria ODBC ciò che facciamo qui potrebbe essere applicato a qualsiasi altro tipo di Database.
Le classi che costruiremo sono tutte classi di tipo Helper, ovvero classi non instanziabili che forniscono solo dei metodi che compiono azioni specifiche invece che generare oggetti.
Creiamo una soluzione vuota utilizzando il progetto di tipo Blank Solution di Visual Studio 2005 che chiamiamo Dnw.AdoSqlHelper.
Il progetto Dnw.SqlHelper
All’interno della soluzione, Generiamo un progetto che chiamiamo Dnw.SqlHelper.cs o Dnw.SqlHelper.vb apriamo la cartella Properties (MyProject) E modifichiamo il Namespace e il nome assembly in modo da ottenere un dato indipendente dal linguaggio che utilizzeremo.
La classe ConnectionHelper
La prima cosa che serve per poter utilizzare un database via ADO.NET è una stringa di connessione valida, pertanto la prima classe che costruiamo si occupa di generare e fornire i metodi di base per generare una stringa di connessione a database SQL Server.
//C#
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
namespace Dnw.SqlHelper
{
public static class ConnectionHelper
{
private static readonly string mClassName =
System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.Name;
}
}
'VB
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data.SqlClient
Namespace Dnw.SqlHelper
Public NotInheritable Class ConnectionHelper
Private Sub New()
End Sub
Private Shared ReadOnly mClassName As String = _
System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.Name
End Class
End Namespace
La struttura base della classe Helper, in C# definita come static, in VB come NotInheritable con costruttore privato.
private const string FMP_ConnectionSql = "data source={0};persist security info=True;initial
catalog={1};user id={2};password={3};";
private const string FMP_ConnectionTrusted = "integrated security=SSPI;data source={0};
initial catalog={1};";
private const string EXC_NomeServerVuoto = "Il nome del server non può essere vuoto!";
private const string EXC_NomeDbVuoto = "Il nome del database non può essere vuoto!";
private const string EXC_NomeUserVuoto = "Lo Username non può essere vuoto!";
Private Const FMP_ConnectionSql As String = "data source={0};persist security info=True;
initial catalog={1};user id={2};password={3};"
Private Const FMP_ConnectionTrusted As String = "integrated security=SSPI;data source={0};
initial catalog={1};"
Private Const EXC_NomeServerVuoto As String = "Il nome del server non può essere vuoto!"
Private Const EXC_NomeDbVuoto As String = "Il nome del database non può essere vuoto!"
Private Const EXC_NomeUserVuoto As String = "Lo Username non può essere vuoto!"
Generiamo le 2 costanti che fanno da “format provider” per la costruzione dei due tipi possibili di connessione a SQL Server, la connessione di tipo SQL con username e password, e la connessione Trusted, ottenuta mappando in SQL Server gli utenti o i gruppi di Windows degli utenti che usano l’applicazione.
Le altre tre costanti sono le stringhe relative alle eccezioni che solleveremo nel caso i dati di connessione non fossero corretti.
public static string BuildConnectionString(string pServer, string pDatabase)
{
if (pServer == null || pServer.Trim().Length == 0)
{
throw new ServerNameInvalidException(EXC_NomeServerVuoto);
}
if (pDatabase == null || pDatabase.Trim().Length == 0)
{
throw new DbNameInvalidException(EXC_NomeDbVuoto);
}
return (string.Format(FMP_ConnectionTrusted, pServer, pDatabase));
}
Public Shared Function BuildConnectionString(ByVal pServer As String, _
ByVal pDatabase As String) As String
If pServer Is Nothing OrElse pServer.Trim().Length = 0 Then
Throw New ServerNameInvalidException(EXC_NomeServerVuoto)
End If
If pDatabase Is Nothing OrElse pDatabase.Trim().Length = 0 Then
Throw New DbNameInvalidException(EXC_NomeDbVuoto)
End If
Return (String.Format(FMP_ConnectionTrusted, pServer, pDatabase))
End Function
Il primo metodo di costruzione della stringa di connessione, usa solo Nome Server e Nome Database per generare una connessione di tipo Trusted. Come potete vedere, utilizziamo delle Eccezioni tipizzate per gestire i parametri errati, per quanto in modo minimale, questo per seguire le linee guida di sviluppo .NET che consigliano questa metodologia per permettere poi a chi utilizza le librerie di intercettare e gestire le eccezioni in modo semplice. Le mini classi delle eccezioni tipizzate sono commentate nell’apposito paragrafo, e sono simili a quelle utilizzate nella serie di librerie Fritto Misto.
public static string BuildConnectionString(string pServer,
string pDatabase, string pUsername, string pPassword)
{
if (pServer == null || pServer.Trim().Length == 0)
{
throw new ServerNameInvalidException(EXC_NomeServerVuoto);
}
if (pDatabase == null || pDatabase.Trim().Length == 0)
{
throw new DbNameInvalidException(EXC_NomeDbVuoto);
}
if (pUsername == null || pUsername.Trim().Length == 0)
{
throw new UserNameInvalidException(EXC_NomeUserVuoto);
}
if (pPassword == null) pPassword = string.Empty;
return (string.Format(FMP_ConnectionSql, pServer,
pDatabase, pUsername, pPassword));
}
Public Shared Function BuildConnectionString(ByVal pServer As String, _
ByVal pDatabase As String, ByVal pUsername As String, _
ByVal pPassword As String) As String
If pServer Is Nothing OrElse pServer.Trim().Length = 0 Then
Throw New ServerNameInvalidException(EXC_NomeServerVuoto)
End If
If pDatabase Is Nothing OrElse pDatabase.Trim().Length = 0 Then
Throw New DbNameInvalidException(EXC_NomeDbVuoto)
End If
If pUsername Is Nothing OrElse pUsername.Trim().Length = 0 Then
Throw New UserNameInvalidException(EXC_NomeUserVuoto)
End If
If pPassword Is Nothing Then
pPassword = String.Empty
End If
Return (String.Format(FMP_ConnectionSql, pServer, pDatabase, pUsername, pPassword))
End Function
Il secondo metodo di costruzione della stringa di costruzione, con quattro parametri, Nome Server, Nome Database, Nome Utente e Password. Anche qui, utilizziamo le eccezioni tipizzate per i controlli di base di coerenza.
public static void TestConnection(string pCnString)
{
using (SqlConnection cn = new SqlConnection(pCnString))
{
cn.Open();
cn.Close();
}
}
Public Shared Sub TestConnection(ByVal pCnString As String)
Using cn As New SqlConnection(pCnString)
cn.Open()
cn.Close()
End Using
End Sub
Il metodo di test più semplice per una connessione, apre e chiude la connessione richiesta, se non vi riesce scatena una eccezione che potrà essere gestita e usata per segnalare i problemi all’utente.
Le Eccezioni Tipizzate
Le minuscole classi delle eccezioni tipizzate, sono tutte uguali nella nostra libreria, derivano tutte dalla ApplicationException di base e forniscono un messaggio per informare relativamente a qual’è il problema verificatosi.
using System;
using System.Collections.Generic;
using System.Text;
namespace Dnw.SqlHelper
{
public class ServerNameInvalidException : System.ApplicationException
{
public ServerNameInvalidException(string pMessage)
: base(pMessage)
{
}
}
}
Imports System
Imports System.Collections.Generic
Imports System.Text
Namespace Dnw.SqlHelper
Public Class ServerNameInvalidException
Inherits System.ApplicationException
Public Sub New(ByVal pMessage As String)
MyBase.New(pMessage)
End Sub
End Class
End Namespace
Se aprite le classi di tutte le altre eccezioni, noterete come la sola cosa che cambia è il nome dell’eccezione. Ovviamente, possono esservi dei metodi ove le eccezioni possono fornire ulteriori dati, oltre al messaggio di avviso, ad esempio se sviluppassimo delle eccezioni tipizzate per la gestione di conflitti di aggiornamento su database, potremmo passare tramite queste classi i valori delle righe di tabella o dei campi incoerenti per utilizzarli in un’interfaccia utente che ne permetta la gestione.
La classe SqlExecHelper
La seconda cosa che ci serve per utilizzare SQL Server via ADO.NET è un metodo per eseguire delle query SQL, quindi ne costruiremo subito un paio, visto che il nostro scopo è generare un database, useremo solo 2 tipi di query per ora, le Query DDL che eseguono operazioni sul database senza avere alcun dato di ritorno, quindi un metodo farà semplicemente un ExecuteNonQuery, inoltre, per verificare se un DB esiste prima di cercare di generarlo, utilizzeremo una Query di tipo scalare, che ritornerà un singolo valore, quindi predisporremo un metodo che effettua una ExecuteScalar.
La struttura base della classe è la solita, quindi non la riporto per evitare codice ripetitivo.
public static void ExecSqlCommand(string pCnString, string pCommandText,
CommandType pCommandType, SqlParameter[] pSqlParameters)
{
SqlConnection cn = null;
try
{
cn = new SqlConnection(pCnString);
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = pCommandText;
if (pSqlParameters != null)
{
cmd.Parameters.AddRange(pSqlParameters);
}
cmd.CommandType = pCommandType;
cmd.Connection = cn;
cmd.ExecuteNonQuery();
}
finally
{
if (cn != null && cn.State == ConnectionState.Open)
{
cn.Close();
}
}
}
Public Shared Sub ExecSqlCommand(ByVal pCnString As String, ByVal pCommandText As String, _
ByVal pCommandType As CommandType, ByVal pSqlParameters As SqlParameter())
Dim cn As SqlConnection = Nothing
Try
cn = New SqlConnection(pCnString)
cn.Open()
Dim cmd As New SqlCommand()
cmd.CommandText = pCommandText
If pSqlParameters IsNot Nothing Then
cmd.Parameters.AddRange(pSqlParameters)
End If
cmd.CommandType = pCommandType
cmd.Connection = cn
cmd.ExecuteNonQuery()
Finally
If cn IsNot Nothing AndAlso cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Sub
Il metodo ExecSqlCommand, permette di eseguire un comando SQL sul server, accettando se necessario un array di parametri. Per dare all’utente modo di non passare alcun parametro, utilizziamo il null (Nothing) come valore ammissibile per l’array di parametri quando non ve ne sono. Se siete bravi e volete fare codice più elegante, farete un metodo con 3 soli parametri in Overload che non fa altro che chiamare questo metodo passando null (Nothing) come array di parametri. Un ottimo uso del polimorfismo.
public static object ExecSqlCommandScalar(string pCnString, string pCommandText,
CommandType pCommandType, SqlParameter[] pSqlParameters)
{
object retObj = null;
SqlConnection cn = null;
try
{
cn = new SqlConnection(pCnString);
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = pCommandText;
if (pSqlParameters != null)
{
cmd.Parameters.AddRange(pSqlParameters);
}
cmd.CommandType = pCommandType;
cmd.Connection = cn;
retObj = cmd.ExecuteScalar();
}
finally
{
if (cn != null && cn.State == ConnectionState.Open)
{
cn.Close();
}
}
return (retObj);
}
Public Shared Function ExecSqlCommandScalar(ByVal pCnString As String, _
ByVal pCommandText As String, ByVal pCommandType As CommandType, _
ByVal pSqlParameters As SqlParameter()) As Object
Dim retObj As Object = Nothing
Dim cn As SqlConnection = Nothing
Try
cn = New SqlConnection(pCnString)
cn.Open()
Dim cmd As New SqlCommand()
cmd.CommandText = pCommandText
If pSqlParameters IsNot Nothing Then
cmd.Parameters.AddRange(pSqlParameters)
End If
cmd.CommandType = pCommandType
cmd.Connection = cn
retObj = cmd.ExecuteScalar()
Finally
If cn IsNot Nothing AndAlso cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
Return (retObj)
End Function
Il metodo ExecSqlCommandScalar, è identico al precedente, però ritorna un object che contiene il valore restituito da SQL Server.
Un particolare da far notare a chi sta iniziando è il seguente: in entrambi i metodi la SqlConnection viene definita fuori dalla Try Catch e sia che tutto vada bene, sia che si verifichi un’eccezione (che qui non viene gestita ma viene demandata al chiamante), la connection viene testata ed eventualmente chiusa.
La classe CreateDropDbHelper
Finalmente la classe che fa quel che ci serve, ovvero generare o eliminare un database dal vostro server SQL. Anche in questo caso ometto la struttura di base della classe per non ripetere codice già commentato, e passo alle costanti che ci forniscono il codice SQL.
private const string SQL_CreateDbSimple = "CREATE DATABASE [{0}]";
private const string SQL_DropDb = "DROP DATABASE [{0}]";
private const string SQL_CreateDbStandard = @"CREATE DATABASE [{0}]
ON PRIMARY
( NAME = N'{0}'
, FILENAME = N'{1}\{0}_dat.mdf'
, SIZE = {2}
, MAXSIZE = {3}
, FILEGROWTH = {4} )
LOG ON
( NAME = N'{0}_log'
, FILENAME = N'{1}\{0}_log.LDF'
, SIZE = {5}
, MAXSIZE = {6}
, FILEGROWTH = {7})";
private const string SQL_ExistDatabase = @"
SELECT count(Name) AS Number
FROM sys.databases
WHERE Name = @DbName";
Private Const SQL_CreateDbSimple As String = "CREATE DATABASE [{0}]"
Private Const SQL_DropDb As String = "DROP DATABASE [{0}]"
Private Const SQL_CreateDbStandard As String = _
"CREATE DATABASE [{0}]" & _
"ON PRIMARY ( NAME = N'{0}'" & _
", FILENAME = N'{1}\{0}_dat.mdf' " & _
", SIZE = {2} " & _
", MAXSIZE = {3}" & _
", FILEGROWTH = {4} )" & _
"LOG ON " & _
"( NAME = N'{0}_log'" & _
", FILENAME = N'{1}\{0}_log.LDF' " & _
", SIZE = {5} " & _
", MAXSIZE = {6} " & _
", FILEGROWTH = {7})"
Private Const SQL_ExistDatabase As String = _
"SELECT count(Name) AS Number " & _
"FROM sys.databases " & _
"WHERE Name = @DbName"
Implementiamo i 2 metodi di base che abbiamo visto da SQL Management Studio e da semplice Query per la generazione e la cancellazione del database, inoltre implementiamo la query per la verifica dell’esistenza del database. In questo caso, utilizziamo due diversi approcci alle query, il primo, per le query di creazione/cancellazione sostituendo i dati nella query, metodo che può essere soggetto a SQL Injection è vero, ma dipende sempre da come lo useremo, infatti, la SQL Injection implica una interfaccia utente da cui inserire dati a mano, ma questo tipo di Query, usualmente la piloteremo noi da programma di installazione indicando tutti i parametri o al limite facendo definire dei dati all’utente ma inserendo opportuni controlli.Il secondo approccio è quello effettuato via parametri SQL solitamente il più corretto, ma non sono certa si possano inserire dei parametri come valori al posto dei placeholders nella create e nella drop pertanto invito chi legge a provare a modificarla e farmi sapere il risultato.
public static bool ExistDatabase(string pCnString, string pDbName)
{
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter( "@DbName", pDbName )
};
return( ((int)SqlExecHelper.ExecSqlCommandScalar(pCnString, SQL_ExistDatabase,
CommandType.Text, sqlParams)) > 0 );
}
Public Shared Function ExistDatabase(ByVal pCnString As String, ByVal pDbName As String) As Boolean
Dim sqlParams As SqlParameter() = New SqlParameter() {New SqlParameter("@DbName", pDbName)}
Return (CInt(SqlExecHelper.ExecSqlCommandScalar(pCnString, _
SQL_ExistDatabase, CommandType.Text, sqlParams)) > 0)
End Function
Il metodo che controlla l’esistenza del database, molto semplice, richiede una connection string che deve aprire il Database Master di SQL Server, quindi chi usa questo metodo deve avere diritti amministrativi sul server, richiede inoltre il nome del database di cui verificare l’esistenza. Ritorna True o False.
public static void CreateDatabase(string pCnString, string pDbName)
{
if (ExistDatabase(pCnString, pDbName))
{
throw new DnwDatabaseAlreadyExistsException(
string.Format( "Il database {0} esiste già su questo server.", pDbName ));
}
string sql = string.Format( SQL_CreateDbSimple, pDbName );
SqlExecHelper.ExecSqlCommand( pCnString, sql, CommandType.Text, null );
}
Public Shared Sub CreateDatabase(ByVal pCnString As String, ByVal pDbName As String)
If ExistDatabase(pCnString, pDbName) Then
Throw New DatabaseAlreadyExistsException(String.Format( _
"Il database {0} esiste già su questo server.", pDbName))
End If
Dim sql As String = String.Format(SQL_CreateDbSimple, pDbName)
SqlExecHelper.ExecSqlCommand(pCnString, sql, CommandType.Text, Nothing)
End Sub
Il metodo Create database nella versione più semplice, richiede una stringa di connessione ed il nome database, utilizza la stringa SQL Simple per la creazione del database e il metodo ExecSqlCommand della classe SqlHelper.
Prima di lanciare la creazione controlliamo che il DB non esista già e se esiste, lanciamo un’eccezione fra quelle tipizzate da noi generate.
public static void CreateDatabase(string pCnString, string pDbName, string pPath,
string pDataSize, string pMaxDataSize, string pDataGrowth, string pLogSize,
string pMaxLogSize, string pLogGrowth )
{
if (ExistDatabase(pCnString, pDbName))
{
throw new DatabaseAlreadyExistsException(
string.Format( "Il database {0} esiste già su questo server.", pDbName ));
}
string sql = string.Format( SQL_CreateDbStandard, pDbName, pPath,
pDataSize, pMaxDataSize, pDataGrowth, pLogSize, pMaxLogSize, pLogGrowth);
SqlExecHelper.ExecSqlCommand( pCnString, sql, CommandType.Text, null );
}
Public Shared Sub CreateDatabase(ByVal pCnString As String, _
ByVal pDbName As String, ByVal pPath As String, ByVal pDataSize As String, _
ByVal pMaxDataSize As String, ByVal pDataGrowth As String, _
ByVal pLogSize As String, ByVal pMaxLogSize As String, ByVal pLogGrowth As String)
If ExistDatabase(pCnString, pDbName) Then
Throw New DatabaseAlreadyExistsException(String.Format( _
"Il database {0} esiste già su questo server.", pDbName))
End If
Dim sql As String = String.Format(SQL_CreateDbStandard, _
pDbName, pPath, pDataSize, pMaxDataSize, pDataGrowth, _
pLogSize, pMaxLogSize, pLogGrowth)
SqlExecHelper.ExecSqlCommand(pCnString, sql, CommandType.Text, Nothing)
End Sub
Il metodo CreateDatabase nella sua versione full, con la richiesta di tutti i parametri per la generazione dello stesso. Se in futuro ne avremo necessità , potremmo aggiungere ulteriori versioni del metodo con la possibilità di usare gli altri parametri che abbiamo visto essere possibili per il metodo Create Database di SQL Server. Prima di lanciare la creazione controlliamo che il DB non esista già e se esiste, lanciamo un’eccezione fra quelle tipizzate da noi generate.
public static void DropDatabase(string pCnString, string pDbName)
{
if (!ExistDatabase(pCnString, pDbName))
{
throw new DatabaseNotExistException(string.Format(
"Il Database {0} non esiste su questo server, impossibile cancellarlo.", pDbName));
}
string sql = string.Format(SQL_DropDb, pDbName);
SqlExecHelper.ExecSqlCommand(pCnString, sql, CommandType.Text, null);
}
Public Shared Sub DropDatabase(ByVal pCnString As String, ByVal pDbName As String)
If Not ExistDatabase(pCnString, pDbName) Then
Throw New DatabaseNotExistException(String.Format(_
"Il Database {0} non esiste su questo server, impossibile cancellarlo.", pDbName))
End If
Dim sql As String = String.Format(SQL_DropDb, pDbName)
SqlExecHelper.ExecSqlCommand(pCnString, sql, CommandType.Text, Nothing)
End Sub
Il metodo Drop Database, similmente al metodo più semplice per creare il database, necessita solo della stringa di connessione e del nome del database. Abbiamo aggiunto il controllo di Esistenza e una delle nostre eccezioni tipizzate per segnalare se il database da cancellare non esistesse.
Il progetto esempio che contiene le classi discusse in questo articolo, così come gli altri relativi alla serie Classi di uso comune:
