title: Column does not allow DBNull.Value - No KeepNulls - Proper Column Mappings
body: <p>I am using c# with .NET 4.5.2, pushing to SQL Server 2017 14.0.1000.169</p>
  
  <p>In my database, I have a table with a DateAdded field, of type <code>DateTimeOffset</code>.</p>
  
  <p>I am attempting to BulkCopy with the following code:</p>
  
  <pre><code>private Maybe BulkCopy(SqlSchemaTable table, System.Data.DataTable dt, bool identityInsertOn)
  {
      try
      {
          var options = SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction; //  | SqlBulkCopyOptions.CheckConstraints; // Tried CheckConstraints, but it didn't change anything.
          if (identityInsertOn) options |= SqlBulkCopyOptions.KeepIdentity;
          using (var conn = new SqlConnection(_connString))
          using (var bulkCopy = new SqlBulkCopy(conn, options, null))
          {
              bulkCopy.DestinationTableName = table.TableName;
              dt.Columns.Cast&lt;System.Data.DataColumn&gt;().ToList()
                  .ForEach(x =&gt; bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(x.ColumnName, x.ColumnName)));
  
              try
              {
                  conn.Open();
                  bulkCopy.WriteToServer(dt);
              }
              catch (Exception ex)
              {
                  return Maybe.Failure(ex);
              }
          }
      }
      catch (Exception ex)
      {
          return Maybe.Failure(ex);
      }
  
      return Maybe.Success();
  }
  </code></pre>
  
  <p>The two possible reasons I know of for the <code>does not allow DBNull</code> error are:</p>
  
  <ol>
  <li>Columns are in the wrong order, which is solved by either putting them in the same order as their Database Ordinal, or by performing a Column Mapping.</li>
  <li>KeepNulls is enabled, and <code>DBNull.Value</code> (or <code>null</code>?) are set in the DataTable.</li>
  </ol>
  
  <p>But I am Mapping correctly and NOT ever setting KeepNulls.</p>
  
  <p>Yet I am receiving the error: </p>
  
  <blockquote>
    <p>Column DateAdded does not allow DBNull.Value</p>
  </blockquote>
  
  <p><strong>EDIT</strong> I also tried just NOT SETTING anything, including <code>null</code>, <code>DBNull.Value</code>, and <code>DefaultValue</code>... just literally not setting that column at all.</p>
  
  <p>Also, if I Remove the DateAdded column from the DataTable, it Works.  But I don't want that.  Of the 100,000 records, maybe 20 of them have data.  So in my batches of 500, sometimes None have data in the DateAdded field, sometimes one or two have data.</p>
  
  <p>So I'd like to keep the column in my DataTable but let it use the DefaultValue.</p>
  
  <p>One last note: I have alternated between setting the DataColumn's Value to <code>DBNull.Value</code> versus <code>dt.Columns[x.ColumnName].DefaultValue</code>.  Both ways give the same error.</p>
  
  <p><strong>Edit 2</strong></p>
  
  <p>This is the code I'm using to populate the data in my Data Table:</p>
  
  <pre><code>foreach (var column in table)
  {
      System.Data.DataRow newRow = dt.NewRow();
      foreach (var field in column)
      {
          if (!IsNull(field.Value) &amp;&amp; !IsEmptyDateOrNumber(field.ColumnType, field.Value))
          {
              // For DateAdded, this is not hit on the first batch, though there are columns Before and After DateAdded on the same row which do have value.
              // But it WILL be hit once or twice a few batches later.  So I don't want to completely remove the definition from the DataTable.
              newRow[field.ColumnName] = field.Value;
          }
          else
          {
              // newRow[field.ColumnName] = dt.Columns[field.ColumnName].DefaultValue;
              // newRow[field.ColumnName] = DBNull.Value;
              // dt.Columns[field.ColumnName].AllowDBNull = true;
          }
      }
      dt.Rows.Add(newRow);
  }
  </code></pre>
  
  <p><code>IsNull()</code> returns <code>TRUE</code> if the value is <code>null</code> or the string <code>"null"</code>, as is required for my business requirements.</p>
  
  <p><code>IsEmptyDateOrNumber()</code> will return <code>TRUE</code> if the field is a numeric or date type, and the value is null or empty <code>""</code>.  Because while empty is valid for many string-like fields, it is never a valid numeric value.</p>
  
  <p>The condition to assign the field a value is hit exactly <strong>0 percent</strong> of the time for this particular column.  Thus nothing is set.</p>