CODEWITHSUNDEEP
c#asp.netsql-serverfastmember
crunchy
crunchy

Reputation: 703

Using Fastmember to bulk insert only selected columns

I have an application that is being used to insert large amounts of data (up to about 250,000 records per file) from a file to a table that has several computed columns. Is there any way to select which columns fastmember inserts the data so I don't try writing into the computed columns?

Upvotes: 0

Views: 2140

Answers (2)

Raju Padhara
Raju Padhara

Reputation: 707

Model class:

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

List Of Model:

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

Bulk insert using Fastmember with column mapping:

  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);
  }

Remember:

Insert data with identity field do not forget to use KeepIdentity.

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

Insert data with auto increment identity field, remove auto increment column mapping field. like property1 is auto increment column in database so skip this column while insert data. 

      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);
      }

Upvotes: 1

Tim P.
Tim P.

Reputation: 2942

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);
                }
            }

Upvotes: 1

Related Questions