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

Question

我使用SqlBulkCopy插入/更新从一个。net DataTable对象到一个SQL Server表，其中包括一个sql_variant列。然而，SqlBulkCopy坚持将DateTime值存储到该列中，作为sql类型"DateTime"，当我需要的是"datetime2"。

My DataTable的定义如下:

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在批量加载类型为。的数据表列时会失败将SqlDateTime转换为SQL Server列，该列的类型为SQL Server 2008新增日期/时间类型

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

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