Bulk Insert Generic List into SQL Server

// ---------------------------------------------------------------------------
// Bulk Insert from Generic List into SQL Server with minimum lines of code
//
// Entity Framework and even classic ADO.NET is very slow when you need to
// perform a large number of inserts into the database.
//
// Calling 1000 times INSERT INTO is not efficient because for every INSERT
// operation, SQL Server will also need to write to the transaction log.
//
// A nice workaround for this is to use Bulk insert or SQLBulkCopy class in C#.
//
// However SQLBulkCopy method WriteToServer does not accept List<T> or
// IEnumerable, IList as parameter.
//
// https://www.radenkozec.com/bulk-insert-generic-list-sql-server-minimum-lines-code/
// ---------------------------------------------------------------------------

public static class IEnumerableExtensions
{
    public static DataTable AsDataTable<T>(this IEnumerable<T> data)
    {
        var table = new DataTable();
        PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));

        foreach (PropertyDescriptor prop in properties)
        {
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        }

        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
            {
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            }
            table.Rows.Add(row);
        }

        return table;
    }
}

// ---------------------------------------------------------------------------
// Example Usage:
// ---------------------------------------------------------------------------

var listPerson = new List<Person>
{
    new Person() { Id = 1 },
    new Person() { Id = 2 },
    new Person() { Id = 3 },
    new Person() { Id = 4 }
};

//
// Optimal BatchSize depends on the number of items you need to insert, row
// size, network bandwidth and latency.
//
// After doing some testing I can recommend BatchSize of 100 when you need to
// insert few thousand items into the database.
//
// The best way to find optimal BatchSize value is to experiment yourself.

using(var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SomeConnectionString"].ConnectionString))
{
    connection.Open();
    SqlTransaction transaction = connection.BeginTransaction();

    using(var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
    {
        bulkCopy.BatchSize = 100;
        bulkCopy.DestinationTableName = "dbo.Person";

        try
        {
            bulkCopy.WriteToServer(listPerson.AsDataTable());
        }
        catch (Exception)
        {
            transaction.Rollback();
            connection.Close();
        }
    }

    transaction.Commit();
}