Press "Enter" to skip to content

Implementare un Data Provider SQL Server usando ADO.Net

Nei post di quest’anno, iniziando ad aprile, ho creato un piccolo database SQL Server per dimostrare come utilizzare gli script T-SQL per generare gli oggetti dentro a SQL Server, poi ho proseguito, creando un sistema composto da un Servizio Windows e da una Console WPF che permettono di simulare una versione minimale dell’Agent di SQL Server per eseguire operazioni schedulate su SQL Server ed ho usato lo sviluppo di queste due applicazioni per spiegare una serie di concetti, spero utili, costruendo mattone per mattone l’applicazione WPF ed il Servizio Windows funzionanti ed installabili su qualsiasi PC o Server. Adesso è venuto il momento di riprendere in mano il Database definito nella serie iniziale di Post di quest’anno, e implementare per prima cosa una interfaccia WPF che ci permetta di inserire e modificare il suo contenuto.

I post che definiscono il database:

La User Interface dell’ applicazione che si occupa di manipolare il contenuto di un database, può essere scritta con uno qualsiasi dei sistemi di UI messi a disposizione dal framework, quindi Windows Forms, WPF, ASP.Net Webforms, ASP.Net MVC, Html5 e Jquery, allo stesso modo, la parte dell’applicazione che si occupa di interagire con il Database SQL Server potrebbe essere scritta con una qualsiasi delle tecnologie create allo scopo, quindi ADO.Net, Linq to SQL, Entity Framework o qualsiasi altro framework a noi gradito.

Per iniziare a lavorare con le cose più semplici, visto che ne abbiamo già introdotto un pezzettino per poter eseguire i Job schedulati su SQL Server, ho deciso che inizierò da ADO.Net, cercando di mostrare come con POCO (che non vuol dire solo Plain Old COde) codice si possa lavorare su un semplice database anche senza tirare in ballo dei framework complessi (e molto utili quando il database non è così semplice).

Pertanto in questo post iniziamo a vedere come creare una classe Data Provider Base che ci permetta poi di creare le classi specializzate per la manipolazione delle tabelle del database.

Caratteristiche

  • Le classi Data provider saranno disegnate per manipolare una singola tabella
  • Tutto quello che sono le operazioni uguali per tutti saranno definite nella classe base.
  • Tutte le operazioni che devono per forza essere definite per ogni tabella saranno definite sulle classi figlie.
  • Cercheremo di guidare chi implementa la classe derivata nel suo lavoro.
  • Tutte le operazioni specifiche potranno essere aggiunte alla classe derivata in seguito, anche con l’uso delle partial classes.

La classe DpCore

La nostra classe base per implementare un data provider ADO.Net per SQL Server, si chiama DpCore e sarà definita nel progetto DnwLibraries.sln, all’interno del progetto Dnw.Base.Data.Sqlserver, di seguito l’immagine della soluzione.

DpCore_Solution_01

Vediamo ora come è fatta la classe e perché.

using System.Collections.Generic;
using System.Data;
namespace Dnw.Base.Data.SqlServer.DataProviders
{
    public abstract class DpCore<T> where T : class
    {
........
    }
}

Abbiamo definito la classe all’interno del progetto creando un namespace apposito, e la nostra classe è stata definita come classe abstract, quindi classe non instanziabile e inoltre come classe Generic. Perché abbiamo voluto implementare un generic? Perché in questo modo, potremo costruire delle classi derivate tipizzate che si agganceranno direttamente alle Entity che utilizzeremo poi a livello di UI per manipolare i dati ed eventualmente a livello di servizi (HTTP o WCF) per la trasmissione dei dati (serializzazione XML o JSON).

protected static string Sql_Insert;
protected static string Sql_Delete;
protected static string Sql_Update;
protected static string Sql_SelectAll;

La prima cosa che inseriamo nella nostra classe sono 4 variabili, sono statiche e conterranno i 4 command base SQL per la manipolazione della tabella.

Aggiungiamo una serie di metodi abstract, che dovranno quindi essere implementati forzatamente in tutte le classi derivate, questi metodi che ovviamente dovranno essere tipizzati a seconda della tabella a cui fa riferimento il Data Provider sono i seguenti:

protected abstract T FromDataRow(DataRow row);

Il metodo per la conversione di una datarow in un oggetto entity del tipo della classe derivata.

public abstract List<T> GetAll();

Il metodo che legge sul database una lista di tutto il contenuto della tabella e la fornisce come collection di oggetti tipizzati.

public abstract void InsertRow(T item);

Il metodo che inserisce un item nella tabella database.

public abstract void UpdateRow(T item);

Il metodo che aggiorna un item nella tabella database.

public abstract void DeleteRow(T item);

Il metodo che cancella un item dalla tabella database.

        
public virtual void InsertRows(List<T> items)
{
    foreach (T item in items)
    {
        InsertRow(item);
    }
}

public virtual void UpdateRows(List<T> items)
{
    foreach (T item in items)
    {
        UpdateRow(item);
    }
}

public virtual void DeleteRows(List<T> items)
{
    foreach (T item in items)
    {
        DeleteRow(item);
    }
}

Infine, i tre metodi che inseriscono, modificano e cancellano una collezione di items nella tabella database, questi tre metodi sono implementabili in modo standard, quindi contengono il codice che esegue un ciclo sugli elementi della collection usando la funzione relativa al singolo item, sono comunque marcati come virtual in modo che sia possibile se necessario effettuarne l’override.

L’applicazione

Per la gestione del nostro Database, creiamo una nuova Solution con Visual Studio in cui creiamo 3 progetti, Recipes che sarà un progetto Windows applilcation di tipo WPF che discuteremo nel prossimo post. E due progetti di tipo Class Library che chiameremo RecipesData e RecipesEntities.

Separiamo su due progetti le Entities, perché essendo queste l’oggetto ideale per gestire i dati lato UI, potranno essere riutilizzate in più progetti, infatti, in questa versione, agganceremo direttamente la User Interface WPF a SQL Server, perché è il metodo più semplice per lavorare con una applicazione Desktop, ma in seguito, potremo voler creare una applicazione in grado di parlare ad un servizio WCF o HTTP e quindi una applicazione su PC locale con Dati in Remoto. Le Entity in quel caso saranno usate sia per implementare il servizio sia per implementare la User Interface ma la User Interface non saprà nulla di quale sia il database utilizzato dal servizio. Da qui il motivo per fare in modo che la libreria dei Data Provider sia separata dal resto del progetto.

recipes_solution_01

La configurazione dei progetti

RecipesEntities_project_config_01

La configurazione del nome assembly e del namespace di base per il progetto delle entities.

RecipesData_project_config_01

La configurazione del nome assembly e del namespace di base del progetto dei data provider.

La classe MeasureUnit

Per iniziare a creare un data provider che funziona sul database reale, prendiamo la più semplice delle tabelle del Database Recipes e prima di iniziare a creare il suo Data Provider, creiamo la classe Entity che ci permetterà di creare poi la User Interface per manipolare il contenuto della tabella.

using Dnw.Base;
using Dnw.Base.Entities;
using System;
using System.ComponentModel;
using System.Runtime.Serialization;
using System.Text;
 
namespace Dnw.Recipes.Entities
{
 
    [DataContract]
    public partial class MeasureUnit : IEntity
    {

...........
    }
}

La classe che rappresenta un unità di misura, implementa l’interfaccia IEntity (definita in uno dei primi articoli della serie dedicata alle librerie base, quest’interfaccia prevede l’implementazione dell’ evento PropertyChanged e della property readonly IsValid boolean, che indica se il contenuto della classe è valido oppure no.

[DataMember(Name = "ID", EmitDefaultValue = false)]
public string ID
{
    get
    {
        return (mID);
    }
    set
    {
        mID = value;
        OnPropertyChanged(FLD_ID);
    }
}

[DataMember(Name = "DD", EmitDefaultValue = false)]
public string Description
{
    get
    {
        return (mDescription);
    }
    set
    {
        mDescription = value;
        OnPropertyChanged(FLD_Description);
    }
}

[DataMember(Name = "DU", EmitDefaultValue = false)]
public string DestinationUnitID
{
    get
    {
        return (mDestinationUnitID);
    }
    set
    {
        mDestinationUnitID = value;
        OnPropertyChanged(FLD_DestinationUnitID);
    }
}

[DataMember(Name = "CM", EmitDefaultValue = false)]
public decimal ConversionMultiplier
{
    get
    {
        return (mConversionMultiplier);
    }
    set
    {
        mConversionMultiplier = value;
        OnPropertyChanged(FLD_ConversionMultiplier);
    }
}

Le property della classe, predisposte per la serializzazione JSON e con l’implementazione di PropertyChanged che verrà utilizzata dal ViewModel WPF per pilotare il binding dei dati sulla User Interface. Rappresentano i dati relativi ad una riga della tabella sul database.

public string DestinationUnitsInSource
{
    get
    {
        if (this.DestinationUnitID.XDwIsNullOrTrimEmpty())
        {
            return (Properties.Resources.txtMUDestinationConversionNotSet);
        }
        else
        {
            return string.Format(Properties.Resources.txtMUDestinationConversionString,
                this.ID, this.ConversionMultiplier, this.DestinationUnitID);
        }
    }
}

public string SourceUnitsInDestination
{
    get
    {
        if (this.DestinationUnitID.XDwIsNullOrTrimEmpty())
        {
            return (string.Empty);
        }
        else
        {
            decimal des = 0;
            if (ConversionMultiplier != 0)
            {
                des = Math.Round(1 / ConversionMultiplier, 5);
            }
            return string.Format(Properties.Resources.txtMUDestinationConversionString,
                this.DestinationUnitID, des, this.ID);
        }
    }
}

Due properties di supporto alla UI, compongono una stringa che permette di visualizzare sui dati della lista delle unità di misura, un messaggio che permette di sapere, quando sia definita un unità di misura di conversione, quante unità espresse nell’unità di misura destinazione sono contenute in una unità dell’unità di misura presente e viceversa, quante unità di misura principali, sono contenute in una unità di destinazione. E’ più difficile a scrivere che a guardare a video.

public bool IsValid
{
    get
    {
        return (!ID.XDwIsNullOrTrimEmpty());
    }
}

La property a sola lettura che indica se l’unità di misura è valida.

public void CopyTo(MeasureUnit destination)
{
    destination.ID = this.ID;
    destination.Description = this.Description;
    destination.DestinationUnitID = this.DestinationUnitID;
    destination.ConversionMultiplier = this.ConversionMultiplier;

}

public MeasureUnit Clone()
{
    MeasureUnit result = new MeasureUnit();

    CopyTo(result);

    return result;
}

public override int GetHashCode()
{
    return base.GetHashCode();
}

public override bool Equals(object obj)
{
    MeasureUnit other = obj as MeasureUnit;

    if (other == null) return false;

    if (other.ID != this.ID) return false;

    return (true);
}

Le funzioni di base dell’entity, creazione di una copia, clonazione, funzione di comparazione di base, funzione di Hash (A proposito del GetHashCode vi invito a leggere l’articolo che parla di questo metodo e dei problemi a cui può portare la sua implementazione nel funzionamento dei componenti WPF. Lo trovate QUI.)

In questa discussione, abbiamo omesso le dichiarazioni delle variabili private, delle costanti ed i metodi di servizio che sono comunque definiti nella classe.

La classe DpMeasureUnits

Rappresenta il nostro Data Provider, che permetterà di effettuare le operazioni di CRUD (CReate, Update, Delete) sulla tabella database.

using Dnw.Base;
using Dnw.Base.Data.SqlServer;
using Dnw.Base.Data.SqlServer.DataProviders;
using Dnw.Recipes.Entities;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
 
namespace Dnw.Recipes.Data
{
    public partial class DpMeasureUnits : DpCore<MeasureUnit>
    {
.......
    }
}

La classe è un implementazione derivata dalla nostra classe base, tipizzata con l’ oggetto MeasureUnit precedentemente definito.

static DpMeasureUnits()
{
    Sql_Delete = @"
                DELETE FROM [dbo].[TbMeasureUnits]
   WHERE IDMeasureUnit = @IDMeasureUnit";
    Sql_Insert = @"
                INSERT INTO [dbo].[TbMeasureUnits]
    ([IDMeasureUnit]
    ,[Description]
    ,[IDMeasureUnitTo]
    ,[ConversionMultiplier])
  VALUES
    (@IDMeasureUnit 
    ,@Description 
    ,@IDMeasureUnitTo 
    ,@ConversionMultiplier)";
    Sql_Update = @"
                UPDATE [dbo].[TbMeasureUnits]
    SET [Description] = @Description
   ,[IDMeasureUnitTo] = @IDMeasureUnitTo
   ,[ConversionMultiplier] = @ConversionMultiplier
  WHERE IDMeasureUnit=@IDMeasureUnit";
    Sql_SelectAll = @"
                SELECT [IDMeasureUnit]
   ,[Description]
   ,[IDMeasureUnitTo]
   ,[ConversionMultiplier]
   FROM [dbo].[TbMeasureUnits]";
}

public DpMeasureUnits(string connectionString)
{
    mConnectionString = connectionString;

}

Abbiamo implementato 2 costruttori, il primo è un costruttore statico, verrà chiamato dal sistema una sola volta, la prima volta che viene fatto un riferimento al nostro DpMeasureUnits e inizializzerà i 4 command contenenti i comandi SQL per il CRUD.

Il secondo costruttore è invece quello che verrà utilizzato per la creazione di un istanza della classe, passeremo al costruttore la stringa di connessione a SQL Server per le operazioni sul database.

protected override MeasureUnit FromDataRow(System.Data.DataRow row)
{
    return new MeasureUnit()
    {
        ID = row[FLD_IDMeasureUnit].XDwNull<string>(),
        Description = row[FLD_Description].XDwNull<string>(),
        DestinationUnitID = row[FLD_IDMeasureunitTo].XDwNull<string>(),
        ConversionMultiplier = row[FLD_ConversionMultiplier].XDwNull<decimal>()
    };
}

L’implementazione del metodo FromDataRow, che è in grado di convertire una DataRow in un oggetto MeasureUnit, con i controlli relativi ai DbNull. Se non avete mai visto la funzione .XDwNull<T>() che abbiamo utilizzato, la trovate spiegata nelle librerie base. Si tratta di un metodo Extension della classe object che abbiamo costruito per permetterci di trasformare il DbNull fornito da SQL Server in un valore accettabile da un dato .NET.

public override List<MeasureUnit> GetAll()
{
    List<MeasureUnit> ret = null;
    DataTable dt = SqlHelper.ExecuteDataTable(mConnectionString, Sql_SelectAll);
    if (dt != null && dt.Rows.Count > 0)
    {
        ret = new List<MeasureUnit>();
        foreach (DataRow row in dt.Rows)
        {
            ret.Add(FromDataRow(row));
        }
    }
    return (ret);
}

public override void DeleteRow(MeasureUnit item)
{
    SqlParameter[] sparam = new SqlParameter[]{
        new SqlParameter( PAR_IDMeasureUnit, item.ID),
        };
    SqlHelper.ExecuteNonQuery(mConnectionString, Sql_Delete, sparam, -1);
}

public override void InsertRow(MeasureUnit item)
{
    SqlParameter[] sparam = new SqlParameter[]{
        new SqlParameter( PAR_IDMeasureUnit, item.ID.XDwTryParseToDBNull<string>()),
        new SqlParameter( PAR_Description, item.Description.XDwTryParseToDBNull<string>()),
        new SqlParameter( PAR_IDMeasureunitTo, item.DestinationUnitID.XDwTryParseToDBNull<string>()),
        new SqlParameter( PAR_ConversionMultiplier, item.ConversionMultiplier.XDwTryParseToDBNull<decimal>()),
        };
    SqlHelper.ExecuteNonQuery(mConnectionString, Sql_Insert, sparam, -1);
}

public override void UpdateRow(MeasureUnit item)
{
    SqlParameter[] sparam = new SqlParameter[]{
        new SqlParameter( PAR_IDMeasureUnit, item.ID),
        new SqlParameter( PAR_Description, item.Description),
        new SqlParameter( PAR_IDMeasureunitTo, item.DestinationUnitID),
        new SqlParameter( PAR_ConversionMultiplier, item.ConversionMultiplier),
        };
    SqlHelper.ExecuteNonQuery(mConnectionString, Sql_Update, sparam, -1);
}

L’implementazione tipizzata dei 4 metodi abstract della classe base, per fornire le operazioni di CRUD sui dati. Effettuano rispettivamente la lettura della lista di tutto il contenuto della tabella, la cancellazione di una riga della tabella, l’inserimento di una nuova riga della tabella e la modifica di una riga della tabella.

Potete notare come abbiamo utilizzato i parametri per passare i valori dei campi, non semplicemente dei placeholder all’interno delle stringhe SQL è importante farlo perché altrimenti i campi numerici ed i campi di tipo datetime potrebbero contenere valori espressi in un formato non compatibile con quello richiesto dalla versione installata di ADO.Net, del client SQL Server e del Server SQL e dare origine ad errori di formato, l’uso dei parametri SQL evita tutto questo perché è ADO che pensa a convertire i valori correttamente nel formato richiesto dal server dati.

La classe Data provider termina qui, così come questo post.

Il codice del progetto esempio relativo alla nuova versione delle librerie di uso comune che comprende quanto spiegato in questo articolo è disponibile al link seguente:

Per qualsiasi domanda, approfondimento o curiosità , usate il link al modulo di contatto oppure il link al forum Microsoft dove troverete me ed altri esperti in grado di rispondervi.

Ricordiamo altresì che le librerie di uso comune vengono regolarmente aggiornate, pertanto se aveste scaricato una vecchia versione delle stesse, potrebbe non contenere tutte le classi usate in questo articolo, quindi aggiornate sempre anche le librerie di uso comune prima di compilare un progetto di test, scaricate sempre le librerie di uso comune dal link presente sull’articolo, saranno certamente quelle contenenti il codice sufficiente e necessario a compilare ed eseguire senza errori il progetto relativo all’articolo.