使用SqlBulkCopy插入sql_variant列时如何设置列类型 [英] How can I set column type when using SqlBulkCopy to insert into a sql_variant column

问题描述

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

I'm using SqlBulkCopy to insert/update from a .net DataTable object to a SQL Server table that includes a sql_variant column. However SqlBulkCopy insists on storing DateTime values put into that column as sql type 'datetime' when what I need is 'datetime2'.

我的数据表的定义如下：

My DataTable is defined like this:

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

然后我在其中放入一些需要'datetime2'的数据

Then I throw some data in there that requires a 'datetime2' to store.

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

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

And then I use SqlBulkCopy to get that data into Sql Server:

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

如果数据表中存在DateTime值，则批量复制将失败不在sql'datetime'类型的范围内（例如'1/1/0001'）。这就是为什么该列需要为'datetime2'类型的原因。

My bulk copy will fail if a DateTime value is present in the data table that falls outside the range of the sql 'datetime' type (such as '1/1/0001'). That's why the column needs to be of type 'datetime2'.

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

When you're writing normal insert statements that insert into a sql_variant column you can control what the type of the variant column is by using CAST or CONVERT. For example:

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

然后，如果要显示变量列的实际类型，如下所示：

Then if you were to display the actual type of the variant column like this:

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

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

You'd see that indeed it is being stored as a 'datetime2'.

但是我正在使用SqlBulkCopy，据我所知，没有地方告诉它.net DateTime对象应该存储在 datetime2类型而不是 datetime类型的列中。据我所知，在DataTable对象上也没有位置声明它。有人可以帮我弄清楚如何做到这一点吗？

But I'm using SqlBulkCopy and, as far as I know, there's no place to tell it that .net DateTime objects should be stored in columns of type 'datetime2' and not 'datetime'. There's no place on the DataTable object, that I know of, to declare this either. Can anyone help me figure out how to get this to happen?

推荐答案

根据 SqlBulkCopy （在备注下）：

将
SqlDateTime类型的DataTable列批量加载到类型为
日期/时间类型之一的SQL Server列中时，SqlBulkCopy将失败在SQL Server 2008中添加了。

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 值。相反，我建议使用以下两个选项之一：

So, SqlBulkCopy won't be able to handle DateTime2 values. Instead, I'd suggest one of two options:

1. 分别插入每一行（即使用 foreach 在您的DataTable上），在那里处理数据类型。 （使用存储的proc包装插入内容并利用 SqlCommand.Parameters 键入插入数据可能会有所帮助。）


2. 批量插入临时表中，然后在SQL中将数据传输到主表中（根据需要转换数据类型）。 （我认为这会不必要地变得复杂，但是您也许能够为大型数据集找到一些性能）

1. Insert each row individually (i.e. use a foreach on your DataTable), handling the datatype there. (It might help to use a stored proc to wrap the insert and utilize SqlCommand.Parameters to type the data for the insert.)
2. Bulk insert into a temp table of strings, then transfer the data to your primary table (converting data types as necessary) in SQL. (which I think will get unnecessarily complicated, but you may be able to eek out some performance for large datasets)

这篇关于使用SqlBulkCopy插入sql_variant列时如何设置列类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！