SqlBulkCopy 运行 WriteToServer(DataTable) 时抛出 System.FormatException

Question

目前我正在编写一种从 CSV 文件读取数据并导入到 SQL 表的方法。

        DataTable dt = new DataTable();
        String line = null;
        int i = 0;

        while ((line = reader.ReadLine()) != null)
        {
            String[] data = line.Split(',');
            if (data.Length > 0)
            {
                if (i == 0)
                {
                    foreach (object item in data)
                    {
                        DataColumn c = new DataColumn(Convert.ToString(item));
                        if (Convert.ToString(item).Contains("DATE"))
                        {
                            c.DataType = System.Type.GetType("System.DateTime");
                        }
                        else { c.DataType = System.Type.GetType("System.String"); }
                        dt.Columns.Add(c);
                    }
                    i++;
                }
                else
                {
                    DataRow row = dt.NewRow();
                    for (int j = 0; j < data.Length; j++)
                    {
                        if (dt.Columns[j].DataType == System.Type.GetType("System.DateTime"))
                        {
                            row[j] = Convert.ToDateTime(data[j]);
                        }
                        else
                        {
                            row[j] = data[j];
                        }
                    }
                    dt.Rows.Add(row);
                }
            }
        }
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[Constant.CONNECTION_STRING_NAME].ConnectionString);
        SqlBulkCopy s = new SqlBulkCopy(con);
        s.DestinationTableName = "abc";
        con.Open();
        s.WriteToServer(dt);

运行该方法时出现的问题是，s.WriteToServer(dt);总是抛出异常。说

System.FormatException：该字符串未被识别为有效的日期时间。从索引 0 开始有一个未知单词。

我调试并看到所有数据都已正确加载到 DataTable 中。 中的数据行示例：

DATA_VENDOR_ID,DATA_VENDOR_SUB_ID,DATA_VENDOR_CLIENT_ID,DATA_VENDOR_ACTIVITY_CODE,ACTIVITY_NAME,EFFECTIVE_DATE,ACTIVITY_LEVEL1,ACTIVITY_LEVEL2,ACTIVITY_LEVEL3,ACTIVITY_LEVEL4,ACTIVITY_LEVEL5,PARTICIPANT_ID,DATA_VENDOR_ALT_ID,FILE_CREATION_DATE,INC_VALUE    
V01,,22097,ABCD01,Physical Activity,10/01/2010,Entertain Kiosk,ABCD - EFG 54,30,,AB01,W1234567891,,08/07/2006,100

以下是 CSV 文件和 SQL 表架构

RowID   int Unique/AutoIncrement
DataVendorId    varchar(32) 
DataVendorSubId varchar(32) 
DataVendorClientId  varchar(32) 
DataVendorActivityCode  varchar(32) 
ActivityName    varchar(64) 
EffectiveDate   datetime    
ActivityLevel1  varchar(253)    
ActivityLevel2  varchar(253)    
ActivityLevel3  varchar(253)    
ActivityLevel4  varchar(253)    
ActivityLevel5  varchar(253)    
ParticipantID   varchar(32) 
DataVendorAltId varchar(32) 
FileCreationDate    datetime    
IncValue    varchar(5)  
CreatedDate datetime    optional/allow null
ModifiedDate    datetime    optional/allow null

Answer 1

我看到的第一个问题是您将遇到 RowID 列的问题；我预计它目前正在尝试将您的数据偏移一列 - 它不知道您忽略了它。您可以修改映射，或者（在数据表中）添加一个 RowID 列（在索引 0 处） - 但请注意，除非您启用身份插入，否则 SQL Server 将忽略这些值。

也许尝试更明确的日期时间转换：

row[j] = DateTime.ParseExact(data[j], "dd/MM/yyyy", CultureInfo.InvariantCulture);

请注意，我无法从数据中判断是 dd/MM 还是 MM/dd，因此您可能需要进行调整。

Answer 2

我遇到了类似的问题，其中列关闭，一旦定义了映射，就没有问题了：

using (SqlBulkCopy sbc = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["SQLDatabase"].ConnectionString, SqlBulkCopyOptions.KeepIdentity))
            {
                sbc.DestinationTableName = "DestinationTable";
                sbc.ColumnMappings.Add("foo", "bar");
                sbc.ColumnMappings.Add("hello", "world");
                sbc.ColumnMappings.Add("col1", "col2");
                sbc.WriteToServer(data);
            }

另外，我有一个列表到数据表转换器扩展，我用它来转换我的列表。

public static DataTable ToDataTable<T>(this IEnumerable<T> data)
        {
            PropertyDescriptorCollection properties =
                TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = GetDataValue(prop.GetValue(item));
                table.Rows.Add(row);
            }
            return table;
        }

GetDataValue() 方法清理 MinValue 日期等数据：

private static object GetDataValue(object value)
        {
            if (value == null || (value.GetType() == typeof(DateTime) && Convert.ToDateTime(value) == DateTime.MinValue) || (value.GetType() == typeof(DateTime) && Convert.ToDateTime(value) < Convert.ToDateTime("01/01/1753")))
            {
                return DBNull.Value;
            }

            return value;
        }

Answer 3

数据文件中的字段与表不匹配，即您尝试将 DataVendorId 插入 RowId 列，这会导致异常，因为您无法转换 < code>varchar(32) 到 int。

将您的身份列移至表格末尾。现在，批量插入将能够匹配所有字段直到到达标识列。