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

我正在使用 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);     
}

如果数据表中存在超出 sql“datetime”类型范围（例如“1/1/0001”）的 DateTime 值，我的批量复制将会失败。这就是为什么该列需要为“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 对象上也没有地方可以声明这一点。谁能帮我弄清楚如何实现这一点？

根据 MSDN 页面SQL批量复制 http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx（在“备注”下）：

批量加载 DataTable 类型的列时，SqlBulkCopy 将失败 SqlDateTime 转换为 SQL Server 列，其类型为以下类型之一 SQL Server 2008 中添加的日期/时间类型。

So, SqlBulkCopy将无法处理DateTime2价值观。相反，我建议选择以下两个选项之一：

1. 单独插入每一行（即使用foreach在您的数据表上），处理那里的数据类型。 （使用存储过程来包装插入并利用可能会有所帮助SqlCommand.Parameters输入要插入的数据。）
2. 批量插入字符串临时表，然后在 SQL 中将数据传输到主表（根据需要转换数据类型）。 （我认为这会变得不必要的复杂，但是你may能够找出大型数据集的一些性能）