sql-server - 使用 SqlBulkCopy 插入 sql_variant 列时如何设置列类型

Question

我正在使用 SqlBulkCopy 从 .net DataTable 对象插入/更新到包含 sql_variant 列的 SQL Server 表。但是，当我需要的是“datetime2”时，SqlBulkCopy 坚持将放入该列的 DateTime 值存储为 sql 类型“datetime”。

我的数据表是这样定义的:

DataTable dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn("VariantValue", typeof(object))); //this represents my sql_variant column

然后我在那里抛出一些需要“datetime2”来存储的数据。

DataRow row = dataTable.NewRow();
row[0] = DateTime.MinValue;
dataTable.Rows.Add(row);

然后我使用 SqlBulkCopy 将该数据导入 Sql Server:

using (SqlBulkCopy bulk = new SqlBulkCopy(myConnection))
{
     bulk.DestinationTableName = "tblDestination";     
     bulk.WriteToServer(dataTable);     
}

如果数据表中的 DateTime 值超出 sql 'datetime' 类型(例如 '1/1/0001')的范围，我的批量复制将失败。这就是为什么该列需要是“datetime2”类型的原因。

当您编写插入 sql_variant 列的普通插入语句时，您可以使用 CAST 或 CONVERT 控制变体列的类型。例如:

insert into [tblDestination] (VariantValue) values (CAST('1/1/0001' AS datetime2))

然后，如果您要像这样显示变体列的实际类型:

SELECT SQL_VARIANT_PROPERTY(VariantValue,'BaseType') AS basetype FROM test

您会看到它确实被存储为“datetime2”。

但我正在使用 SqlBulkCopy，据我所知，没有地方告诉它 .net DateTime 对象应该存储在“datetime2”而不是“datetime”类型的列中。据我所知，DataTable 对象上也没有地方可以声明这一点。谁能帮我弄清楚如何让这件事发生？

Answer 1

根据 SqlBulkCopy 的 MSDN 页面(在“备注”下):

SqlBulkCopy will fail when bulk loading a DataTable column of type SqlDateTime into a SQL Server column whose type is one of the date/time types added in SQL Server 2008.

所以， SqlBulkCopy将无法处理 DateTime2值。相反，我建议两种选择之一:

单独插入每一行(即在 DataTable 上使用 foreach)，处理那里的数据类型。 (使用存储过程包装插入并利用 SqlCommand.Parameters 输入插入数据可能会有所帮助。)

批量插入字符串临时表，然后在 SQL 中将数据传输到主表(根据需要转换数据类型)。 (我认为这会变得不必要地复杂，但您可以为大型数据集寻找一些性能)