SqlBulkCopy from a List<>

Came across a similar situation trying to insert a couple million rows into the db.

Got this done by converting the List into a DataTable and then inserting the table into the database.

private static DataTable CreateDataTableItem(List<ListItem> ItemList)
    {
        DataTable dt = new DataTable();
        try
        {
            dt.TableName = "PartPrice";

            foreach (PropertyInfo property in typeof(ListItem).GetProperties())
            {
                dt.Columns.Add(new DataColumn() { ColumnName = property.Name, DataType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType, AllowDBNull = true });
            }

            foreach (var item in ItemList)
            {
                DataRow newRow = dt.NewRow();
                foreach (PropertyInfo property in typeof(ListItem).GetProperties())
                {
                    newRow[property.Name] = item.GetType().GetProperty(property.Name)?.GetValue(item, null) ?? DBNull.Value;
                }
                dt.Rows.Add(newRow);
            }
            return dt;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            return null;
        }
    }

public class ListItem
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? NullableId { get; set; }

}

And then bulk insert using

    private void BulkInsert(DataTable dt)
    {
        string consString = _config.GetConnectionString("yourConnectionStringkey");
        using SqlConnection connection = new SqlConnection(consString);

        connection.Open();
        using var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction, null);
        sqlBulkCopy.DestinationTableName = "dbo.TargetDb";
        sqlBulkCopy.ColumnMappings.Add("Id", "Id");
        sqlBulkCopy.ColumnMappings.Add("Name", "Name");
        sqlBulkCopy.ColumnMappings.Add("NullableId", "NullableId");


        sqlBulkCopy.WriteToServer(dt);
        connection.Close();
    }

You dont have to do the column mappings given

    // Summary:
    //     Returns a collection of Microsoft.Data.SqlClient.SqlBulkCopyColumnMapping items.
    //     Column mappings define the relationships between columns in the data source and
    //     columns in the destination.
    //
    // Value:
    //     A collection of column mappings. By default, it is an empty collection.
    //
    // Remarks:
    //     If the data source and the destination table have the same number of columns,
    //     and the ordinal position of each source column within the data source matches
    //     the ordinal position of the corresponding destination column, the <xref:Microsoft.Data.SqlClient.SqlBulkCopy.ColumnMappings>
    //     collection is unnecessary. However, if the column counts differ, or the ordinal
    //     positions are not consistent, you must use <xref:Microsoft.Data.SqlClient.SqlBulkCopy.ColumnMappings>
    //     to make sure that data is copied into the correct columns. During the execution
    //     of a bulk copy operation, this collection can be accessed, but it cannot be changed.
    //     Any attempt to change it will throw an <xref:System.InvalidOperationException>.

These 2 lines lets you insert nullable values into the datatable column

 { ColumnName = property.Name, DataType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType, AllowDBNull = true }

newRow[property.Name] = item.GetType().GetProperty(property.Name)?.GetValue(item, null) ?? DBNull.Value;