Does SqlBulkCopy support Graphtables in MsSql 2017?

Question

I am trying out the new graphdatabase support that was added to Microsoft SQL Server 2017 I wanted to use SqlBulkCopy to insert a couple thousand nodes into a node table. However I always the error: Column '$node_id_DB218B0EAE294E37804103CF4E82BCD2' does not allow DBNull.Value.

My tables are create likes this

CREATE TABLE [Product] (
[id] bigint,
[name] nvarchar(max),
[partsNum] bigint,
[price] float) AS NODE;

CREATE TABLE [DependsOn] (
[weight] float,
[id] bigint) AS EDGE;`

I prepare my a datatable with all attributes and call SqlBulkCopy like this:

using (var bulkCopy = new SqlBulkCopy(Connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers, null)
{
    DestinationTableName = "Product"
})
{
    bulkCopy.WriteToServer(_dataTable);
}

Now I am wondering if I am doing something wrong or if this is just not supported yet.

You're not assigning any `.ColumnMapping`s. You probably want to do that, otherwise `SqlBulkCopy` will do bad things like assume all columns need bulk copying, and all in exactly the order as given in the table. Graph tables contain some internally created columns that will definitely spoil this. (You might think `SqlBulkCopy` is clever enough to map columns by name without you having to tell it when you pass a `DataTable`. Unfortunately, it's not.) — Jeroen Mostert, Jan 23 '19 at 16:07
Ohh, I was expecting it to not require a mapping if the datatable has exactly the same colums. Let me try adding it. — Bluuu, Jan 23 '19 at 16:19
You were right. It was the missing mappings... Do you want to post an answer so I can mark it? — Bluuu, Jan 23 '19 at 16:42
I feel like there should be a duplicate of this question (not necessarily specifically about graph tables), but as per usual, it seems impossible to find one on SO... so I may as well. If anyone finds the dupe, close appropriately. — Jeroen Mostert, Jan 23 '19 at 16:47

score 1 · Accepted Answer · answered Jan 23 '19 at 16:50

SqlBulkCopy has no special handling for DataTable; it will map the columns to copy from source to destination by ordinal position, just as it does for the other overloads that take other sources. So setting up an identity mapping by name isn't optional:

foreach (DataColumn c in dataTable.Columns) {
    sqlBulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
}

With graph and node tables the problem is a little more noticeable than usual because the internal columns supporting the structures (which you normally don't use explicitly) appear at the start of the column list, so that almost guarantees failure.

Does SqlBulkCopy support Graphtables in MsSql 2017?

1 Answers1