Bulk insert with nullable bool

[79it]

I've come across an issue when trying to use InsertBulk with nullable bools saved as nullable bits in a SQL Server database.

If the first row of data sets a value (true or false) then subsequent nulls in the column are imported as expected.

However, if the first row's value is null, as soon as a subsequent row has a non-null value the following exception occurs: Cannot change DataType of a column once it has data.

This happens In SqlBulkCopyHelper - in debug you can see that the DataType has been set as {Name = "Boolean" FullName = "System.Boolean"} and wants to be changed to {Name = "Int32" FullName = "System.Int32"}

This is where the exception is occurring.

                    if (newType != null && newType != typeof (DBNull))
                    {
                        table.Columns[i].DataType = newType;
                    }

As a temporary fix, I've made a copy of SqlBulkCopyHelper and made a tweak to BuildBulkInsertDataTable where it sets the column type.

I have replaced:

            foreach (var col in cols)
            {
                bulkCopy.ColumnMappings.Add(col.Value.MemberInfoKey, col.Value.ColumnName);
                table.Columns.Add(col.Value.MemberInfoKey, Nullable.GetUnderlyingType(col.Value.MemberInfoData.MemberType) ?? col.Value.MemberInfoData.MemberType);
            } 

with:

            foreach (var colValue in cols.Select(x => x.Value))
            {
                var dataType = Nullable.GetUnderlyingType(colValue.MemberInfoData.MemberType) ?? colValue.MemberInfoData.MemberType;
                if (dataType == typeof(bool))
                {
                    dataType = typeof(int);
                }

                bulkCopy.ColumnMappings.Add(colValue.MemberInfoKey, colValue.ColumnName);
                table.Columns.Add(colValue.MemberInfoKey, dataType);
            }

This works by forcing the type to int if it's a boolean.

Is there a better way to handle InsertBulk with nullable bools?