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：字符串无法识别为有效的 DateTime。索引 0 处有一个未知单词。

我进行了调试并发现所有数据都已正确加载到 DataTable 中。 以下是 CSV 文件中数据行的示例：

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

以及我的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

- Leo

---

顺便提一下，使用 typeof(string) 要比 System.Type.GetType("System.String") 更高效 - 同理 typeof(DateTime)。 - Marc Gravell

非常感谢您的推荐 :) - Leo

Answer 1

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

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

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

请注意，从数据中无法确定日期是dd/MM还是MM/dd格式，所以您可能需要进行微调。

- Marc Gravell

---

你说得对，我尝试添加了一个名为RowID的虚拟列，结果成功了！ - Leo

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;
        }

- Papa Burgundy

Answer 3

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

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

- Dennis