Mettre à jour massivement une table SQL Server (2ème partie)

Dans le premier article, je vous indiquais comment faire du BulkInsert avec SQL Server. Dans cette deuxième partie, nous allons voir comment utiliser l’instruction MERGE pour faire de la mise à jour massive. On avait le schéma de classes suivant :

BulkInsert

Notre but va donc être de pouvoir faire de la mise à jour massive. Nous avons besoin pour cela d’uen table temporaire possédant la même structure que la table cible. Puis, nous allons procéder ainsi :

  1. Vider la table temporaire (par sécurité),
  2. Faire un BulkInsert des données dans la table temporaire,
  3. Faire un Merge entre la table temporaire et la table cible,
  4. Vider la table temporaire (la madame elle aime bien faire le ménage…)

 

Donc dans un premier temps, définissons une classe abstraite implémentant l’opération de Merge :

/// <summary>
/// Classe de base qui permet de faire le Merge
/// </summary>
/// <typeparam name="T">Entité</typeparam>
public abstract class RepositoryMergeBase<T> : RepositoryBulkInsertBase<T>
{
    /// <summary>
    /// Requête SQL pour le Merge à définir dans les classes dérivantes
    /// </summary>
    protected abstract string SqlMerge { get; }

    /// <summary>
    /// Nom de la table temporaire utilisée pour le Merge dans les classes dérivantes
    /// </summary>
    protected abstract string TempTableName { get; }

    protected virtual void CleanTempTable()
    {
        using (var connection = new SqlConnection(ConnectionString))
        {
            using (var command = new SqlCommand(string.Format("DELETE FROM {0}", TempTableName), connection))
            {
                connection.Open();

                command.ExecuteNonQuery();
            }
        }
    }

    public SynchronizationResult Merge(IEnumerable<T> datas, IRepositoryBulkInsertMapper mapper)
    {
        if (string.IsNullOrEmpty(SqlMerge))
            throw new InvalidOperationException("SqlMerge n'a pas été défini");

        if (string.IsNullOrEmpty(TempTableName))
            throw new InvalidOperationException("TempTableName n'a pas été défini");

        // on vide la table temporaire avant l'import (au cas ou)
        CleanTempTable();

        // importation massive des données dans la table temporaire
        BulkInsert(datas.ToList(), mapper);

        // exécution du Merge entre la table temporaire et la table cible
        var result = Merge();

        // on vide la table temporaire
        CleanTempTable();

        return result;
    }

    #region private methods
    /// <summary>
    /// Effectue le merge
    /// </summary>
    /// <returns></returns>
    private SynchronizationResult Merge()
    {
        var result = new SynchronizationResult();
        using (var connection = new SqlConnection(ConnectionString))
        {
            // SqlMerge est l'instruction Transact-SQL avec MERGE
            using (var command = new SqlCommand(SqlMerge, connection))
            {
                connection.Open();

                // l'instruction Merge retourne le nombre d'insert/update/delete effectué
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        switch ((string)reader["$action"])
                        {
                            case "INSERT":
                                result.Inserted++;
                                break;
                            case "UPDATE":
                                result.Updated++;
                                break;
                            case "DELETE":
                                result.Deleted++;
                                break;
                            default:
                                throw new NotImplementedException(
                                    "Erreur dans le merge : " +
                                    (string) reader["$action"]);
                        }
                    }

                    return result;
                }
            }
        }
    }
    #endregion

}

 

Cette classe abstraite demande aux classes qui en dérivent de définir la propriété SqlMerge et TempTableName. SqlMerge est l’instruction Transact-SQL (voir plus loin) et TempTableName est le nom de la table temporaire.

Dans la méthode Merge, on effectue les tâches décrites plus haut : vidage, bulkInsert dans la table temporaire, merge et vidage (again). En procédant ainsi, les données ne sont envoyées qu’une seule fois massivement à SQL Server, et c’est lui qui fait tout le travail. Les autres aller-retour avec le serveur ne sont que des petites instructions SQL, donc on a une rapidité optimisée.

Le plus compliqué (si l’on peut dire) consiste à implémenter cette classe, c’est-à-dire surtout à définir l’instruction Merge. Voyons pour notre exemple son implémentation :

public class PersonRepository : RepositoryMergeBase<Person>
{
    public SynchronizationResult Merge(IEnumerable<Person> rows)
    {
        return Merge(rows, new PersonBulkInsertMapper(TempTableName));
    }

    #region Overrides of RepositoryMergeBase<Person>
    /// <summary>
    /// Nom de la table temporaire utilisée pour le Merge
    /// </summary>
    protected override string TempTableName
    {
        get { return "Persons_Temp"; }
    }

    /// <summary>
    /// Requête SQL pour le Merge à définir dans les classes dérivantes
    /// </summary>
    protected override string SqlMerge
    {
        get
        {
            return @"
MERGE Persons AS target
USING Persons_Temp AS source
ON (target.Id = source.Id)
WHEN MATCHED THEN
 UPDATE
      SET
           target.Name = source.Name,
           target.Age = source.Age,
           target.CompanyId = source.CompanyId
WHEN NOT MATCHED BY TARGET THEN
 INSERT (Name, Age, CompanyId)
 VALUES (source.Name, source.Age, source.CompanyId)
OUTPUT
 $action;
";
        }
    }
    #endregion
}

 

Vous voyez que l’instruction Merge est décomposée en trois parties :

  1. L’instruction On définit quand 2 enregistrements sont considérés comme égaux
  2. L’instruction Update à utiliser quand On est vérifié
  3. L’instruction Insert à utiliser quand On n’est pas vérifié.

 

Merge nous renvoi également un tableau de résultat avec une colonne contenant l’action qu’il a effectué. On sait ainsi s’il a effectué un insert, update ou delete (NB : dans cet exemple, nous n’effectuons pas de Delete, juste du Insert/Update). On retourne cette information sous la forme d’un objet de type SynchronizationResult.

Pour utiliser cette classe, cela devient un jeu d’enfant :

var repository = new PersonRepository();

var result = repository.Merge(persons);

Console.WriteLine(
    "Inserted = {0}, Updated = {1}, Deleted = {2}", 
    result.Inserted, 
    result.Updated, 
    result.Deleted);

 

Résumons

ClassDiagram1

Pour faire notre Merge, nous n’avons besoin que de définir SqlMerge et TempTableName dans la classe dérivée.

Simple non ?

Téléchargez le source de cet exemple.

blog comments powered by Disqus