SqlBulkCopy - How the default mapping work
Hey SqlBulkCopy can map column automaticality, it’s not is great?
At first sight yes, but once you understand how it works, you will always map all your columns explicitly!
Under the hood
Unit of Test
The following DataTable to map
to the following SQL table:
Let say we want to insert only the following column:
The first step of the default mapping is simply performing an auto-mapping by ordinal:
The maping become:
Problem is without a doubt coming! Wrong DataTable column are mapped to wrong destination column.
The second step will check destination column type and identity column.
The default mapping will discover the DestinationOrderId is an identity column and should not be mapped unless you have the option KeepIdentity turned on.
The default mapping will try to map the SourceTransactionId to the next column available.
- It will try to map to DestinationDateCreated but the type doesn’t match
- It will try to map to DestinationInvoiceId and will find a perfect match!
The mapping is now:
Without a doubt, this mapping will lead to many error. The most common error is a FOREIGN KEY.