Fast SQL-Insert with C# and SqlBulkCopy

Fast SQL-Insert with C# and SqlBulkCopy

In .NET, there is a very fast and easy way to pump a variety of entities into an SQL database: SqlBulkCopy

public void BulkInsert<TEntity>(string connectionString, string tableName, IList<TEntity> entities, params string[] columns)
{
    using SqlBulkCopy sqlCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls)
    using ObjectReader reader = ObjectReader.Create(entities, columns);
    {
        sqlCopy.DestinationTableName = tableName;
        sqlCopy.WriteToServer(reader);
    }
}

Usage:

public class MyEntity
{
    public Id {get;set}
    public Name {get;set;}
    public EMail {get;set;}
}

public class MySqlExporter
{
    public void Export()
    {
        string connectionString = "your sql connection string";
        string sqlTableName = "MyEntitiesTable";
        List<MyEntity> myEntities = ... // here your code to read your existing entities or source

        string[] copyParameters = {
            nameof(MyEntity.Id)
            nameof(MyEntity.Name),
            nameof(MyEntity.EMail),
        }

        BulkInsert(connectionString, sqlTableName, myEntities, copyParameters);
    }
}