How can I use Fast Member to Bulk Copy data into a table with inconsistent column names?

Question

I have a Person table with following column names:

Id, Name, Dob

I also have a poco as:

public class Person
{
    public int Id {get; set;}
    public string Name {get; set;}
    public string DateOfBirth {get; set;}
}

I am then trying:

var people = new List<Person>();
... // added a bunch of people

using (var bcp = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.TableLock, transaction))
using (var reader = ObjectReader.Create(people, "Id", "Name", "Dob"))
{
    bcp.BulkCopyTimeout = 120;
    bcp.BatchSize = 0;
    bcp.DestinationTableName = "Person";
    bcp.WriteToServer(reader);
}

However since Column name Dob does not match the property name DateOfBirth I get an Index out of range thrown by FastMember, how can I solve the problem without having to rename the property or the column.

Please note that I need an answer which can work with both property and column names only known at run time as I am currently using ServiceStack Ormlite to retrieve the table metadata at runtime and FastMember to tap into the ObjectReader again at runtime.

Any help is much appreciated.

score 9 · Accepted Answer · answered Mar 18 '15 at 22:08

At the end it turned out to be much simpler than I thought:

// Get valid columns from the [targetTable] on the db at runtime
// and produce a simple mapping
// validColumns is an IDictionary<string, string>
var membersExposedToReader = validColumns.Keys.ToArray();

// data is an IEnumerable<T>           
using (var bcp = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.TableLock, tran))
using (var reader = ObjectReader.Create(data, membersExposedToReader))
{
    foreach (var member in membersExposedToReader)
    {
        bcp.ColumnMappings.Add(member, validColumns[member]);
    }

    bcp.BulkCopyTimeout = 120;
    bcp.BatchSize = 0;
    bcp.DestinationTableName = targetTable;
    bcp.WriteToServer(reader);
}

score 1 · Answer 2 · answered Mar 18 '15 at 16:57

1

With a simple LINQ projection you could do:

var people2 = people.Select(x => new { x.Id, x.Name, Dob = x.DateOfBirth });

and then

using (var reader = ObjectReader.Create(people2, "Id", "Name", "Dob"))

answered Mar 18 '15 at 16:57

xanatos

109,618
12
197
280

How would this work if the name of both the properties and the table columns are only known at runtime? – MaYaN Mar 18 '15 at 16:59
@MaYaN It wouldn't. You would use some other library. – xanatos Mar 18 '15 at 17:00
And that is why I am asking the question :-) I need something that can work at run time. – MaYaN Mar 18 '15 at 17:05
1

@MaYaN Then you should learn to write what you want in the question, not in the comments to the answers. – xanatos Mar 18 '15 at 17:06

How can I use Fast Member to Bulk Copy data into a table with inconsistent column names?

2 Answers2

Linked

Related