来自数据源的String类型的给定值不能转换为指定目标列的int类型 [英] The given value of type String from the data source cannot be converted to type int of the specified target column

问题描述

我正在尝试从xml文件中读取值，然后使用批量复制将数据插入到我的数据库中.

I am trying to read values from xml file and then use the bulkcopy to insert the data into my database.

我正在使用一个自定义类，该类是:

I am using a custom class which is:

class Customer
    {
        public int CustomerID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int ShowsNumber { get; set; }
        public int VisitNumber { get; set; }
        public int Cancellation { get; set; }
    }

我这样读取数据:

List<Customer> customersList =
                (
                    from e in XDocument.Load(file).Root.Elements("cust")
                    select new Customer
                    {
                        CustomerID = (int)e.Attribute("custid"),
                        FirstName = (string)e.Attribute("fname"),
                        LastName = (string)e.Attribute("lname"),
                        ShowsNumber = (int)e.Attribute("count_noshow"),
                        VisitNumber = (int)e.Attribute("count_resos"),
                        Cancellation = (int)e.Attribute("count_cancel"),
                    }).ToList();

然后我将 customersList 插入到这样的数据表中:

Then I insert that customersList to a datatabe like this:

DataTable dataTable = getBasicDataTable();
for (int i = 0; i < customersList.Count; i++)
                {
                    DataRow datarows = dataTable.NewRow();
                    datarows[0] = customersList[i].CustomerID;
                    datarows[1] = customersList[i].FirstName;
                    datarows[2] = customersList[i].LastName;
                    datarows[3] = customersList[i].ShowsNumber;
                    datarows[4] = customersList[i].VisitNumber;
                    datarows[5] = customersList[i].Cancellation;
                    dataTable.Rows.Add(datarows);
                }

然后将数据插入到数据库中，如下所示:

then I insert the data to my database like this:

但是我有这个例外

using (SqlBulkCopy sbc = new SqlBulkCopy(GetConnectionString()))
            {
                sbc.DestinationTableName = XMLReader.databaseTable;
                sbc.WriteToServer(dataTable);
            }

数据源中String类型的给定值不能转换为指定目标列的int类型.

The given value of type String from the data source cannot be converted to type int of the specified target column.

如您所见，当我从我的 xml 中提取数据时，我已经使用了到 int 和 string 的转换并且它正在工作.那么，为什么在插入数据库时​​出现该异常?

as you see, when I extract the data from my xml, I already use the cast to int and to string and it is working. so why when inserting to the database I got that exception?

为了给您完整的代码，这是 getBasicDataTable 功能

In order to give you the whole code, this is the getBasicDataTable fuction

private DataTable getBasicDataTable()
        {
            DataTable dataTable = new DataTable();
            dataTable.Clear();
            dataTable.Columns.Add("customerID");
            dataTable.Columns.Add("firstName");
            dataTable.Columns.Add("lastName");
            dataTable.Columns.Add("showsNumber");
            dataTable.Columns.Add("visitNumber");
            dataTable.Columns.Add("cancellation");
            return dataTable;
        }

推荐答案

您需要在定义数据表时将列指定为整数.像这样:-

You need to specify the column as integer while defining the datatable. Like this:-

dataTable.Columns.Add("customerID", typeof(int));

修改:
我怀疑的另一个原因可能是您绑定数据表的方式(我的意思是列的顺序)与数据库表的方式不匹配.原因是我认为默认映射不是 SqlBulkCopy 中的名称到名称"，而是其索引到索引.因此，请重新检查您的数据库表顺序，它应类似于:-

The other reason i suspect is probably the way you are binding your datatable (I mean the order of columns) doesn't match with that of database table. Reason being I think default mapping is not Name to Name rather its Index to Index in SqlBulkCopy. So Kindly re-check your database table order, It should look like:-

CustomerID (INT)
FirstName (VARCHAR\NVARCHAR)
LastName (VARCHAR\NVARCHAR)
ShowsNumber (INT)
VisitNumber (INT)
Cancellation (INT)

这篇关于来自数据源的String类型的给定值不能转换为指定目标列的int类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！