Aujourd’hui, je ne vais pas parler de Windows 8 pour une fois mais d’une problématique que l’on peut rencontrer si l’on travaille avec des masses de données importantes.

Imaginons donc une simple table SQL Server et une application qui récupère des données et souhaite les injecter dans cette table.

Jusqu’ici, rien de bien particulier. Il existe plusieurs solutions :

• Parcourir les données une à une et faire un INSERT (pas terrible au niveau perf),
• Faire un BulkInsert (cool au niveau perf).

Seulement le BulkInsert de SQL Server ne fait pas les mises à jour. Si par hasard on a une ligne que l’on souhaite injecter qui a le même ID qu’une ligne existante dans la table (ID étant la clé de la table), alors le BulkInsert vous jettera comme un malpropre.

NB : ce n’est pas le cas pour MySQL qui autorise, lui, l’Insert/Update dans un BulkInsert. cf l’article suivant.

Il existe toutefois une solution qui permet d’effectuer un maximum d’opération du côté de SQL Server, donc avec des performances importantes.

La solution consiste a utiliser l’instruction MERGE de Transact-SQL (je crois que cette instruction n’est possible que depuis SQL Server 2008). MERGE permet de synchroniser les données de 2 tables. Il se charge de faire les INSERT, UPDATE et même les DELETE qu’il faut.

Tout d’abord, nous allons voir comment faire le BulkInsert

BulkInsert

Pour simplifier les développements de nos classes Repository, nous allons créer une classe de base qui définie le ConnectionString :

public abstract class RepositoryBase
{
    public const string ConnectionStringKey = "MyDb";
    
    #region ctor

    protected RepositoryBase()
        : this(ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString)
    {
    }

    protected RepositoryBase(string connectionString)
    {
        ConnectionString = connectionString;
    }

    #endregion
    
    /// <summary>
    /// Chaine de connection
    /// </summary>
    public string ConnectionString { get; set; }
}

Le ConnectionString est donc recherché dans le fichier de configuration avec la clé par défaut valant “MyDb” (why not!).

Ensuite, nous allons définir une classe abstraite de base pour effectuer le BulkInsert. C’est une classe générique basée sur l’entité que l’on manipule. Elle ne possède qu’une seule méthode qui attends comme arguments les données à insérer et un mappeur de propriétés. Ce dernier implémente l’interface IRepositoryBulkInsertMapper :

public interface IRepositoryBulkInsertMapper
{
    string DestinationTableName { get; }
    IEnumerable<BulkInsertColumnDescription> GetColumnDescriptions();
}

DestinationTableName est le nom de la table ou l’on souhaite insérer les données. GetColumnDescription retourne une collection de BulkInsertColumnDescription :

/// <summary>
/// Décrit une colonne utilisée pour le mapping pour le BulkInsert
/// </summary>
public class BulkInsertColumnDescription
{
    public BulkInsertColumnDescription()
    {}

    public BulkInsertColumnDescription(string columnName, Type columnType)
    {
        ColumnName = columnName;
        ColumnType = columnType;
    }

    /// <summary>
    /// Nom de la colonne dans la base de données
    /// </summary>
    public string ColumnName { get; set; }

    /// <summary>
    /// Type de la colonne
    /// </summary>
    public Type ColumnType { get; set; }
}

Exemple d’implémentation de cette interface :

public class PersonBulkInsertMapper : IRepositoryBulkInsertMapper
{
    public PersonBulkInsertMapper(string destinationTableName   )
    {
        DestinationTableName = destinationTableName;
    }

    #region Implementation of IRepositoryBulkInsertMapper

    public string DestinationTableName { get; set; }

    public IEnumerable<BulkInsertColumnDescription> GetColumnDescriptions()
    {
        return new[]
            {
                new BulkInsertColumnDescription("Id", typeof (int)),
                new BulkInsertColumnDescription("Name", typeof (string)),
                new BulkInsertColumnDescription("Age", typeof (int)),
                new BulkInsertColumnDescription("CompanyId", typeof (int))
            };
    }

    #endregion
}

On voit que ce mappeur indique que les colonnes à insérer dans la table de destination sont Id, Name, Age et CompanyId.

Revenons donc maintenant à notre classe générique :

/// <summary>
/// Classe de base qui permet le BulkInsert
/// </summary>
/// <typeparam name="T">Entité de base</typeparam>
public abstract class RepositoryBulkInsertBase<T> : RepositoryBase
{
    /// <summary>
    /// Effectue un BulkInsert d'une collection d'entité
    /// </summary>
    /// <param name="datas">Les entités à insérer</param>
    /// <param name="mapper">Le mapper entre les colonnes et les valeurs</param>
    public void BulkInsert(IEnumerable<T> datas, IRepositoryBulkInsertMapper mapper)
    {
        using (
            var bulkCopy = new SqlBulkCopy(
                ConnectionString,
                SqlBulkCopyOptions.TableLock |
                SqlBulkCopyOptions.CheckConstraints |
                SqlBulkCopyOptions.FireTriggers))
        {
            // création de la DataTable et du mapping
            var table = new DataTable();
            foreach (var description in mapper.GetColumnDescriptions())
            {
                // ajout de la colonne
                table.Columns.Add(new DataColumn(description.ColumnName, description.ColumnType));
                // ajout du mapping dans le bulkCopy
                bulkCopy.ColumnMappings.Add(description.ColumnName, description.ColumnName);
            }

            // table ou l'on souhaite insérer les données
            bulkCopy.DestinationTableName = mapper.DestinationTableName;

            // remplissage de la DataTable avec les données
            foreach (var data in datas)
            {
                var columnValues = new List<object>();

                foreach (DataColumn column in table.Columns)
                {
                    columnValues.Add(typeof (T).GetProperty(column.ColumnName).GetValue(data, null));
                }
                table.Rows.Add(columnValues.ToArray());
            }

            // envoi du BulkCopy à SQL Server
            bulkCopy.WriteToServer(table);
        }
    }
}

Cette méthode ne fait que créer dans un premier temps la structure de la DataTable grâce aux ColumnDescriptions et rempli cette DataTable avec nos données.

Implémentons maintenant cette classe abstraite (oui, il n’y a aucune ligne de code ) :

public class PersonBulkInsertRepository : RepositoryBulkInsertBase<Person>
{}

Et pour l’utiliser :

var rep = new PersonBulkInsertRepository();
rep.BulkInsert(persons, new PersonBulkInsertMapper("Persons"));

Si l'on sait que cette classe utilisera même toujours la même interface de mapping, on peut lui ajouter une méthode simplifiant son utilisation :

public class PersonBulkInsertRepository : RepositoryBulkInsertBase<Person>
{
    public void BulkInsert(IEnumerable<Person> datas)
    {
        BulkInsert(datas, new PersonBulkInsertMapper("Persons"));
    }
}

Moralité, l’insertion massive de données dans la table devient encore plus simple :

var rep = new PersonBulkInsertRepository();
rep.BulkInsert(persons);

Conclusion

Maintenant que nous savons faire du simple BulkInsert, nous verrons dans le prochain article comment l’utiliser pour faire du Merge.