列不允许DBNull.Value-No KeepNulls-正确的列映射

Question

我正在将c#与.NET 4.5.2一起使用，并将其推送到SQL Server 2017 14.0.1000.169

在我的数据库中，我有一个带有DateAdded字段的表，类型为DateTimeOffset。

我正在尝试使用以下代码进行BulkCopy：

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<System.Data.DataColumn>().ToList()
.ForEach(x => 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();
}

据我所知，does not allow DBNull错误的两个可能原因是：

1. 列的顺序错误，这可以通过将它们按与数据库序号相同的顺序排列或执行列映射来解决
2. 启用KeepNulls，并在DataTable中设置DBNull.Value(或null？)

但我映射正确，从未设置KeepNulls。

然而，我收到了错误：

列DateAdded不允许DBNull。值

EDIT我还尝试过不设置任何内容，包括null、DBNull.Value和DefaultValue。。。只是根本没有设置该列。

此外，如果我从DataTable中删除DateAdded列，它也会起作用。但我不想那样。在10万条记录中，可能有20条有数据。因此，在我的500个批次中，有时"无"在DateAdded字段中有数据，有时一两个有数据。

因此，我想将该列保留在DataTable中，但让它使用DefaultValue。

最后一点注意：我在将DataColumn的值设置为DBNull.Value和dt.Columns[x.ColumnName].DefaultValue之间进行了切换。两种方法都会产生相同的错误。

编辑2

这是我用来填充数据表中数据的代码：

foreach (var column in table)
{
System.Data.DataRow newRow = dt.NewRow();
foreach (var field in column)
{
if (!IsNull(field.Value) && !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);
}

如果值是null或字符串"null"，则IsNull()返回TRUE，这是我的业务需求所必需的。

如果字段是数字或日期类型，并且值为null或空""，则IsEmptyDateOrNumber()将返回TRUE。因为虽然空对许多类似字符串的字段有效，但它从来都不是有效的数值。

为字段分配值的条件正好满足此特定列时间的0%。因此，没有设置任何内容。

Answer 1

简单地说，你不能随心所欲。关于BulkCopy如何使用默认值的最佳参考是Rutzky的This Answer。

问题是，BulkCopy包括一个步骤，在该步骤中它查询目标数据库并确定表的结构。如果它确定目标列是可NOT NULL的，并且您正在传递null或DBNull，那么它甚至在尝试传递数据之前就抛出异常。

如果使用SQL事件探查器，您将看到BCP调用，但不会看到数据(无论如何，数据都不会显示)。您将看到的只是定义列列表和标志的调用。

当BulkCopy最终决定传递数据时。如果该列存在，并且字段为NULL可，并且值为DBNull.value，并且该列具有默认值；大容量复制本质上传递该列的DEFAULT标志。但是，我们做出了一些决定，使得这些条件(除非字段是NOT NULL)不应该使用默认值，而是应该抛出异常。

据我所知，这是微软的错误或疏忽。

正如其他一些答案所说，常见的解决方法是通过计算代码中的值来手动处理这些值。当然，如果您计算默认值，然后DBA更改字段的实际SQL默认值，那么您的系统将不匹配。下一步是向系统中添加一个子系统，该子系统查询和/或跟踪/缓存您正在访问的SQL Server中当前指定的默认值，并分配这些值。这比应该做的工作多得多。

TLDR：你不能做你想做的事。但也有其他人指定的次优解决方案。

Answer 2

此组合不适用于SqlBulkCopy:

1. SQL表中的日期字段配置为非空
2. 您的C#数据表有未设置日期值的记录(或者有设置的值，但它们不是实际的日期/时间值)

这种组合确实有效：

1. SQL表中的日期字段被配置为允许为null，但使用默认值
2. 您的C#数据表具有未设置日期值的记录(如果C#数据表中未指定日期字段，SQL将使用默认值)

如果表中的日期字段必须配置为不接受null，那么您还有更多的工作要做(创建一个接受null的临时表，大容量插入到临时表中，调用您编写的存储过程，将其从临时表插入到常规表中)。

我认为你正在寻找的最简单的途径是：

您的表应该允许日期字段为NULL，但使用默认值：

CREATE TABLE [dbo].[MyTable](
[Field1] [varchar](50) NULL,
[MyDateField] [datetime] NULL,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Table_1_MyDateField] DEFAULT (getdate()) FOR [MyDateField]
GO

然后在C#中：

DataTable dt = new DataTable("dbo.MyTable");
dt.Columns.Add("Field1");
dt.Columns.Add("MyDateField");
DataRow row1 = dt.NewRow();
row1["Field1"] = "test row 1";
row1["MyDateField"] = DateTime.Now; //specify a value for the date field in C#
dt.Rows.Add(row1);
DataRow row2 = dt.NewRow();
row2["Field1"] = "test row 2";
//do not specify a value for the date field - SQL will use the default value
dt.Rows.Add(row2);
do the bulk copy

Answer 3

在100000条记录中，可能有20条记录有数据。所以在我的批次中共500个，有时"无"在"已添加日期"字段中有数据，有时为一个或者两个具有数据。

我猜您的一些记录在DateAdded中有null，而DateAdded被配置为不接受null。

要克服这个问题，你有多种方法：

1. (更简单的一个)只是将DateAdded列更改为接受null
2. 为列DateAdded(来自MSSQL)指定一个默认值
3. 从代码中管理记录上的null

如果您没有权限或您的工作要求指定DateAdded不能接受null或具有MSSQL or 1&2没有解决你的问题。然后，在将每个批复制到服务器之前，可以管理每个批的DateAdded列上的null。

在这种情况下，在您的代码中，当您填充数据时，您应该向该列添加一个条件，内容如下：

if(field.ColumnName == "DateAdded")
{
// Do something to handle this column if it's null 
// Set a default value 
DateTimeOffset dtoffset = DateTimeOffset.Parse("2019-01-01 00:00:00.0000000 -00:00", CultureInfo.InvariantCulture); // change it to the required offset 
string defaultDateAdded = dtoffset.ToString("yyyy-MM-dd HH:mm:ss.fffffff zzz", CultureInfo.InvariantCulture);               
// Add the default value 
newRow[field.ColumnName] = defaultDateAdded;
}
else
{
// Do something to handle the rest of columns if they're null 
}   

然后，当处理完所有列后，只需将新行添加到数据表中，然后将最终确定的数据表复制到服务器上。