Getting an exception writing a boolean to sql using a datatable and SqlBulkCopy

Question

It works fine for all other datatypes, but I am unable to get it to work with a 'bit' column.

This is my SQL for doing the bulk write:

using (var bulk = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls))
{
    bulk.BatchSize = 2000;
    bulk.DestinationTableName = targetTable;
    bulk.WriteToServer(dataTable);
}            

This is my datatable:

    DataTable dt = new DataTable();

    dt.Clear();

    dt.Columns.Add("MyBool", typeof(bool)); // Tried with and without typeof(bool)

    return dt;

This is how I construct the row before adding it to the datatable.

personAssociationRow["MyBool"] = true;

The exception is thrown on the WriteToServer line, and depending on if the typeof(bool) is specified is either:

Cannot insert the value NULL into column 'MyBool', table but intellisense/debugger shows the value as true

or

The given value of type String from the data source cannot be converted to type int of the specified target column. which is when the value in the intellisense/debugger becomes "True" ie a string.

In the database, the column is defined as bit and does not allow nulls.

Any ideas how I can get my boolean to work?

Edit : Just found and tried SqlBoolean as a type too but that didn't work, it said The given value of type SqlBoolean from the data source cannot be converted to type int of the specified target column. which suggests int would work, but it does not seem to.

Edit: I suspect the problem lies in it thinking the underlying database is type int when it is clear as purple crayon that the type is bit, hence the error message about it not converting to underlying type int.

Answer 1

Encountered the same issue: setting bool column to true/false was ignored by bulk copy, all values were set to null. The previous answer helped to resolve it, but I had to add mappings for all columns to make it work:

foreach (DataColumn column in table.Columns) {
    sqlBulk.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
sqlBulk.WriteToServer(table);