SqlBulkCopy using …

Home » BI / SQL » SqlBulkCopy using …

SqlBulkCopy using …

20. Februar 2015

Im Rahmen von stark datengetriebenen Anwendungen kann es vorkommen, dass eine Liste von Objekten in einer SQL Server Datenbank abgespeichert werden muss. Die Liste der Datenobjekte stammt dabei aus fremden Datenquellen, liegt als Liste oder Enumeration vor und die Datenmenge kann durchaus mehrere (hundert-)tausend Elemente erreichen.

Um die Datenmenge performant in der SQL Server Datenbank abzuspeichern, bietet sich hier natürlich ein BulkCopy an, welches aus C# heraus mit der Klasse SqlBulkCopy ermöglicht wird. Diese Klasse bietet die Methode WriteToServer an, wobei sie im Wesentlichen entweder eine DataTable oder ein IDataReader als Datenquelle erwartet.

In dem folgenden Beispiel wird ein Businessobjekt mit den Eigenschaften fachliche ID, Name, Preis und Datum verwendet. Die Datenbanktabelle nimmt diese 4 Attribute plus eine Identity-Column als technischen Key auf.

Als erstes betrachten wir die Methode mit einer DataTable als Parameter. Hierfür muss nun aus der Enumeration der Objekte eine DataTable erzeugt werden, wobei die Struktur der DataTable der Datenbanktabelle entsprechen muss. Um keine Mappings anlegen zu müssen, ist es erforderlich die Tabelle auch in der Spaltenreihenfolge identisch zu halten.

private static DataTable CreateDataTable()
{
  var table = new DataTable(TABLENAME);
  
  table.Columns.Add(DC_KEY, typeof(int));
  table.Columns.Add(DC_ID, typeof(int));
  table.Columns.Add(DC_NAME, typeof(string));
  table.Columns.Add(DC_PRICE, typeof(double));
  table.Columns.Add(DC_DATE, typeof(DateTime));
  
  return table;
}

Dabei ist auch zu beachten, dass die Identity-Column mit anzugeben ist. Ist die DataTable erzeugt, können die Daten übertragen werden und dann per WriteToServer in der Datenbank abgespeichert werden.

public void SaveUsingDataTable(IEnumerable<BusinessItem> businessItems)
{
  var strConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
  var insertTable = CreateDataTable();
  
  foreach (var businessItem in businessItems)
  {
    var tableRow = insertTable.NewRow();
  
    tableRow[DC_ID] = businessItem.ID;
    tableRow[DC_NAME] = businessItem.Name;
    tableRow[DC_PRICE] = businessItem.UnitPrice;
    tableRow[DC_DATE] = businessItem.Date;
  
    insertTable.Rows.Add(tableRow);
  }
  
  using (var tableBulkCopy = new SqlBulkCopy(strConnectionString, SqlBulkCopyOptions.UseInternalTransaction))
  {
    tableBulkCopy.BatchSize = insertTable.Rows.Count;
    tableBulkCopy.DestinationTableName = insertTable.TableName;
    tableBulkCopy.WriteToServer(insertTable);
  }
}

Beobachtet man den Ressourcen Verbrauch (Working Set (Memory)) und die Dauer, dann ergibt sich folgendes Bild:

Datensätze	10.000	100.000	1.000.000
Zuwachs Arbeitsspeicher (K)	5.000	22.000	150.000
Zeit (s)	0,2 – 0,3	0,8-0,9	7,5-8,0

Betrachtet man den Code so werden die Daten durch die Erzeugung der DataTable im Arbeitsspeicher „quasi verdoppelt“. Im Sinne einer Ressourcen schonenden Verarbeitung ist dies nicht der Königsweg. Daher ist eventuell die zweite Variante der WriteToServer Methode mit einem IDataReader als Parameter eine Alternative.

Ziel muss es also sein, einen DataReader für die Liste der Objekte bereitzustellen. Wie dies geht zeigen unter anderem die Beispiele hier oder hier. Nun hat die Schnittstelle IDataReader nicht wenige Methoden definiert, doch werden durch die SqlBulkCopy Methode WriteToServer nur sehr wenige genutzt, nämlich die Eigenschaft FieldCount und die Methoden Read() sowie GetValue(int i). Mit diesen Informationen lässt sich eine maßgeschneiderte Hilfsklasse implementieren:

internal class BulkCopyHelper<T> : IDataReader
{
  private readonly IEnumerator<T> enumerator;
  private readonly Func<T, object>[] getters;
  
  public BulkCopyHelper(IEnumerable<T> input, 
    params Func<T, object>[] getters)
  {
    this.enumerator = input.GetEnumerator();
    this.getters = getters;
  }
  
  public int FieldCount
  {
    get { return this.getters.Length; }
  }
  
  public bool Read()
  {
    return this.enumerator.MoveNext();
  }
  
  public object GetValue(int i)
  {
    return this.getters[i](this.enumerator.Current);
  }
  
  public void Dispose()
  {
    if (this.enumerator != null)
    {
      this.enumerator.Dispose();
    }
  }
  
...
  // sonstiges IDataReader-Methoden
}

Mit einer zusätzlichen Extension-Method

public static class EnumerationBulkCopyExtension
{
  public static IDataReader AsDataReader<T>(this IEnumerable<T> items, 
    params Func<T, object>[] propertyGetters)
  {
    return new BulkCopyHelper<T>(items, propertyGetters);
  }
}

ist dann der Aufruf für den SqlBulkCopy

public void SaveUsingDataReader(IEnumerable<BusinessItem> businessItems)
{
  var strConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
  var reader = businessItems.AsDataReader(
    item => -1, // Identity column
    item => item.ID,
    item => item.Name,
    item => item.UnitPrice,
    item => item.Date);
  
  using (var readerBulkCopy = new SqlBulkCopy(strConnectionString, SqlBulkCopyOptions.UseInternalTransaction))
  {
    readerBulkCopy.BatchSize = 10000;
    readerBulkCopy.DestinationTableName = TABLENAME;
    readerBulkCopy.WriteToServer(reader);
  }
}

Die Funktionen, die zur Ermittlung der Daten eines Elementes dienen, müssen zum Layout der Datenbanktabelle passen. Hier ist die Identity-Column nicht zu vergessen. Beobachtet man nun den Ressourcen Verbrauch (Working Set (Memory)) und die Dauer, dann ergibt sich folgendes Bild:

Datensätze	10.000	100.000	1.000.000
Zuwachs Arbeitsspeicher (K)	5.000	5.000	6.000
Zeit (s)	0,2 – 0,3	0,6-0,7	5,5 -6,0

Fazit

Sind Massendaten aus einer .NET Anwendung heraus in eine SQL Server Datenbanktabelle einzufügen und liegen die Daten als Enumeration vor, so ist es empfehlenswert die SqlBulkCopy Klasse zu verwenden und dabei den Weg über das Interface IDataReader zu gehen. Bei großen Datenmengen benötigt sie weniger Arbeitsspeicher und ist noch etwas schneller als die DataTable Variante.