使用Fastmember仅大容量插入选定的列

Question

我有一个应用程序，用于将大量数据(每个文件最多250,000条记录)从文件插入到包含多个计算列的表中。有没有什么方法可以选择哪些列快速成员插入数据，这样我就不会尝试写入计算列？

Answer 1

using (SqlBulkCopy bcp = new SqlBulkCopy(YourConnectionString))
            {
                // +1 to Marc Gravell for this neat little library to do the mapping for us
                // because DataTable isn't available until .NET Standard Library 2.0
                using (var dataReader = ObjectReader.Create(yourListOfObjects,
                    nameof(YourClass.Property1),
                    nameof(YourClass.Property2)))
                {
                    bcp.DestinationTableName = "YourTableNameInSQL";
                    bcp.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Property1", "MyCorrespondingTableColumn"));
                    bcp.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Property2", "TableProperty2"));


                    await bcp.WriteToServerAsync(dataReader).ConfigureAwait(false);
                }
            }

Answer 2

模型类：

class ExampleModel
{
    public int property1 { get; set; }
    public string property2 { get; set; }
    public string property3 { get; set; }       
}

型号列表：

private List<ExampleModel> listOfObject = new List<ExampleModel>()
{
    new ExampleModel { property1 = 1, property2 = 'Rudra',  property3 = 'M'},
    new ExampleModel { property1 = 2, property2 = 'Shivam', property3 = 'M'}
};

使用带有列映射的Fastmember的大容量插入：

  using (var bcp = new SqlBulkCopy(SQLConnectionString))
  using (var reader = ObjectReader.Create(listOfObject))
  {
      bcp.DestinationTableName = "[dbo].[tablename]";
      bcp.ColumnMappings.Add("property1", "tableCol1");
      bcp.ColumnMappings.Add("property2", "tableCol2");
      bcp.ColumnMappings.Add("property3", "tableCol3");
      bcp.WriteToServer(reader);
  }

请记住：

插入带有identity字段的数据不要忘记使用KeepIdentity。

using (var bcp = new SqlBulkCopy(SQLConnectionString, SqlBulkCopyOptions.KeepIdentity))

插入带有自动增量标识字段的数据，删除自动增量列映射字段。如property1是数据库中的自动增量列，因此在插入数据时跳过此列。

      using (var bcp = new SqlBulkCopy(SQLConnectionString))
      using (var reader = ObjectReader.Create(listOfObject))
      {
          bcp.DestinationTableName = "[dbo].[tablename]";
          bcp.ColumnMappings.Add("property2", "tableCol2");
          bcp.ColumnMappings.Add("property3", "tableCol3");
          bcp.WriteToServer(reader);
      }