title: SqlBulkCopy into table that Default column values fails when source DataTable row has DBNull.Value
body: <p>Update: <a href="https://stackoverflow.com/questions/35783422/sqlbulkcopy-into-table-with-default-columns-when-source-datatable-has-rows-with">Here is my solution</a></p>
  
  <p>I have a table defined as:</p>
  
  <pre><code>CREATE TABLE [dbo].[csvrf_References]
  (
      [Ident] [int] IDENTITY(1,1) NOT NULL,
      [ReferenceID] [uniqueidentifier] NOT NULL DEFAULT (newsequentialid()),
      [Type] [nvarchar](255) NOT NULL,
      [Location] [nvarchar](1000) NULL,
      [Description] [nvarchar](2000) NULL,
      [CreatedOn] [datetime] NOT NULL DEFAULT (getdate()),
      [LastUpdatedOn] [datetime] NOT NULL DEFAULT (getdate()),
      [LastUpdatedUser] [nvarchar](100) NOT NULL DEFAULT (suser_sname()),
  
      CONSTRAINT [PK_References] PRIMARY KEY NONCLUSTERED ([ReferenceID] ASC)
  ) ON [PRIMARY]
  </code></pre>
  
  <p>I have a <code>DataTable</code> with columns that match the table column names and data types.  The <code>DataTable</code> is filled out with <code>DBNull.Value</code> in <code>CreatedOn</code>, <code>LastUpdatedOn</code> and <code>LastUpdatedUser</code>.  <code>ReferenceID</code> is already generated.  When I call the following code I get the error below.</p>
  
  <p>Code:</p>
  
  <pre><code>SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, bulkCopyTran);
  bulkCopy.DestinationTableName = table.TableName;
  bulkCopy.ColumnMappings.Clear();
  foreach (DataColumn col in table.Columns) bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
  bulkCopy.WriteToServer(table);
  </code></pre>
  
  <p>Error:</p>
  
  <blockquote>
    <p>Error trying to BulkCopy table csvrf_References<br>
    System.InvalidOperationException: Column 'CreatedOn' does not allow DBNull.Value.<br>
    at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean&amp; isSqlType, Boolean&amp; coercedToDataFeed)  </p>
  </blockquote>
  
  <p>I have looked all over and I can't seem to find an answer for this. The <code>SqlBulkCopy</code> class seems not to honor default values even though it says it does.  What am I doing wrong here?</p>