C# SqlBulkCopy - 无法将来自数据源的 String 类型的给定值转换为指定目标列的 money 类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18140012/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SqlBulkCopy - The given value of type String from the data source cannot be converted to type money of the specified target column
提问by Ricketts
I'm getting this exception when trying to do an SqlBulkCopy from a DataTable.
尝试从 DataTable 执行 SqlBulkCopy 时遇到此异常。
Error Message: The given value of type String from the data source cannot be converted to type money of the specified target column.
Target Site: System.Object ConvertValue(System.Object, System.Data.SqlClient._SqlMetaData, Boolean, Boolean ByRef, Boolean ByRef)
I understand what the error is saying, but how I can I get more information, such as the row/field this is happening on? The datatable is populated by a 3rd party and can contain up to 200 columns and up to 10k rows. The columns that are returned depend on the request sent to the 3rd party. All of the datatablecolumns are of string type. The columns in my database are not all varchar, therefore, prior to executing the insert, I format the datatable values using the following code (non important code removed):
我明白错误在说什么，但是我怎样才能获得更多信息，例如发生这种情况的行/字段？数据表由第 3 方填充，最多可包含 200 列和 10k 行。返回的列取决于发送给第 3 方的请求。所有的数据表列是字符串类型。我的数据库中的列并不都是 varchar，因此，在执行插入之前，我使用以下代码格式化数据表值（删除了不重要的代码）：
//--- create lists to hold the special data type columns
List<DataColumn> IntColumns = new List<DataColumn>();
List<DataColumn> DecimalColumns = new List<DataColumn>();
List<DataColumn> BoolColumns = new List<DataColumn>();
List<DataColumn> DateColumns = new List<DataColumn>();
foreach (DataColumn Column in dtData.Columns)
{
//--- find the field map that tells the system where to put this piece of data from the 3rd party
FieldMap ColumnMap = AllFieldMaps.Find(a => a.SourceFieldID.ToLower() == Column.ColumnName.ToLower());
//--- get the datatype for this field in our system
Type FieldDataType = Nullable.GetUnderlyingType(DestinationType.Property(ColumnMap.DestinationFieldName).PropertyType);
//--- find the field data type and add to respective list
switch (Type.GetTypeCode(FieldDataType))
{
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64: { IntColumns.Add(Column); break; }
case TypeCode.Boolean: { BoolColumns.Add(Column); break; }
case TypeCode.Double:
case TypeCode.Decimal: { DecimalColumns.Add(Column); break; }
case TypeCode.DateTime: { DateColumns.Add(Column); break; }
}
//--- add the mapping for the column on the BulkCopy object
BulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(Column.ColumnName, ColumnMap.DestinationFieldName));
}
//--- loop through all rows and convert the values to data types that match our database's data type for that field
foreach (DataRow dr in dtData.Rows)
{
//--- convert int values
foreach (DataColumn IntCol in IntColumns)
dr[IntCol] = Helpers.CleanNum(dr[IntCol].ToString());
//--- convert decimal values
foreach (DataColumn DecCol in DecimalColumns)
dr[DecCol] = Helpers.CleanDecimal(dr[DecCol].ToString());
//--- convert bool values
foreach (DataColumn BoolCol in BoolColumns)
dr[BoolCol] = Helpers.ConvertStringToBool(dr[BoolCol].ToString());
//--- convert date values
foreach (DataColumn DateCol in DateColumns)
dr[DateCol] = dr[DateCol].ToString().Replace("T", " ");
}
try
{
//--- do bulk insert
BulkCopy.WriteToServer(dtData);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
//--- handles error
//--- this is where I need to find the row & column having an issue
}
This code should format all values for their destination fields. In the case of this error, the decimal, the function that cleans that up will remove any character that is not 0-9 or . (decimal point). This field that is throwing the error would be nullable in the database.
此代码应格式化其目标字段的所有值。在此错误的情况下，十进制，清除它的函数将删除任何不是 0-9 或 . （小数点）。这个抛出错误的字段在数据库中可以为空。
The level 2 exception has this error:
2 级异常有这个错误：
Error Message: Failed to convert parameter value from a String to a Decimal.
Target Site: System.Object CoerceValue(System.Object, System.Data.SqlClient.MetaType, Boolean ByRef, Boolean ByRef, Boolean)
and the level 3 exception has this error:
并且级别 3 异常有这个错误：
Error Message: Input string was not in a correct format
Target Site: Void StringToNumber(System.String, System.Globalization.NumberStyles, NumberBuffer ByRef, System.Globalization.NumberFormatInfo, Boolean)
Does anyone have any ideas to fix? or any ideas to get more info?
有没有人有任何想法要解决？或者有什么想法可以获取更多信息？
采纳答案by Ricketts
@Corey - It just simply strips out all invalid characters. However, your comment made me think of the answer.
@Corey - 它只是简单地去除所有无效字符。但是，您的评论让我想到了答案。
The problem was that many of the fields in my database are nullable. When using SqlBulkCopy, an empty string is not inserted as a null value. So in the case of my fields that are not varchar (bit, int, decimal, datetime, etc) it was trying to insert an empty string, which obviously is not valid for that data type.
问题是我的数据库中的许多字段都是可以为空的。使用 SqlBulkCopy 时，不会将空字符串作为空值插入。因此，在我的字段不是 varchar（位、整数、十进制、日期时间等）的情况下，它试图插入一个空字符串，这显然对该数据类型无效。
The solution was to modify my loop where I validate the values to this (repeated for each datatype that is not string)
解决方案是修改我的循环，在那里我验证这个值（对每个不是字符串的数据类型重复）
//--- convert decimal values
foreach (DataColumn DecCol in DecimalColumns)
{
if(string.IsNullOrEmpty(dr[DecCol].ToString()))
dr[DecCol] = null; //--- this had to be set to null, not empty
else
dr[DecCol] = Helpers.CleanDecimal(dr[DecCol].ToString());
}
After making the adjustments above, everything inserts without issues.
进行上述调整后，一切都可以正常插入。
回答by Robotnik
For the people stumbling across this question and getting a similar error message in regards to an nvarchar instead of money:
对于那些在这个问题上绊倒并收到关于 nvarchar 而不是钱的类似错误消息的人：
The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.
无法将来自数据源的 String 类型的给定值转换为指定目标列的 nvarchar 类型。
This could be caused by a too-short column.
这可能是由太短的列引起的。
For example, if your column is defined as nvarchar(20)and you have a 40 character string, you may get this error.
例如，如果您的列定义为nvarchar(20)并且您有一个 40 个字符的字符串，您可能会收到此错误。
回答by Joshy Joseph
Please use SqlBulkCopyColumnMapping.
请使用 SqlBulkCopyColumnMapping。
Example:
例子：
private void SaveFileToDatabase(string filePath)
{
string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["MHMRA_TexMedEvsConnectionString"].ConnectionString.ToString();
String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
//Create Connection to Excel work book
using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
{
//Create OleDbCommand to fetch data from Excel
using (OleDbCommand cmd = new OleDbCommand("Select * from [Crosswalk$]", excelConnection))
{
excelConnection.Open();
using (OleDbDataReader dReader = cmd.ExecuteReader())
{
using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
{
//Give your Destination table name
sqlBulk.DestinationTableName = "PaySrcCrosswalk";
SqlBulkCopyColumnMapping AdmissionPaySrcID=new SqlBulkCopyColumnMapping("AdmissionPaySrcID","AdmissionPaySrcID");
sqlBulk.ColumnMappings.Add(AdmissionPaySrcID);
SqlBulkCopyColumnMapping TMHP_Detail = new SqlBulkCopyColumnMapping("TMHP_Detail", "TMHP_Detail");
sqlBulk.ColumnMappings.Add(TMHP_Detail);
SqlBulkCopyColumnMapping PaySrcType = new SqlBulkCopyColumnMapping("PaySrcType", "PaySrcType");
sqlBulk.ColumnMappings.Add(PaySrcType);
SqlBulkCopyColumnMapping AgencyID = new SqlBulkCopyColumnMapping("AgencyID", "AgencyID");
sqlBulk.ColumnMappings.Add(AgencyID);
SqlBulkCopyColumnMapping CountyCode = new SqlBulkCopyColumnMapping("CountyCode", "CountyCode");
sqlBulk.ColumnMappings.Add(CountyCode);
SqlBulkCopyColumnMapping EntityID = new SqlBulkCopyColumnMapping("EntityID", "EntityID");
sqlBulk.ColumnMappings.Add(EntityID);
sqlBulk.WriteToServer(dReader);
}
}
}
}
}
回答by Md Kauser Ahmmed
Check The data you are writing to Server. May be data has delimiter which is not used.
检查您正在写入服务器的数据。可能是数据有未使用的分隔符。
like
喜欢
045|2272575|0.000|0.000|2013-10-07
045|2272585|0.000|0.000;2013-10-07
your delimiter is '|'but data has a delimiter ';'. So for this you are getting the error.
你的分隔符是“|” 但数据有一个分隔符';' . 因此，为此您会收到错误消息。
回答by santhoshraj
Make sure that the column values u added in entity class having get set properties also in the same order which is present in target table.
确保您在实体类中添加的列值也以与目标表中相同的顺序获取设置属性。
回答by Medhat Makram
There is another issue you have to take care of it when you try mapping column which is string length,
for example TK_NO nvarchar(50)you will have to map to
the same length as the destination field.
当您尝试映射字符串长度的列时，您还需要注意另一个问题，例如，TK_NO nvarchar(50)您必须映射到与目标字段相同的长度。
回答by Suamere
Since I don't believe "Please use..." plus some random code that is unrelated to the questionis a good answer, but I do believe the spirit was correct, I decided to answer this correctly.
由于我不相信这"Please use..." plus some random code that is unrelated to the question是一个好的答案，但我确实相信精神是正确的，我决定正确回答这个问题。
When you are using Sql Bulk Copy, it attempts to align your input data directly with the data on the server. So, it takes the Server Table and performs a SQL statement similar to this:
当您使用 Sql Bulk Copy 时，它会尝试将您的输入数据直接与服务器上的数据对齐。因此，它获取服务器表并执行类似于以下的 SQL 语句：
INSERT INTO [schema].[table] (col1, col2, col3) VALUES
Therefore, if you give it Columns 1, 3, and 2, EVEN THOUGH your names may match (e.g.: col1, col3, col2). It will insert like so:
因此，如果你给它列 1、3 和 2，即使你的名字可能匹配（例如：col1、col3、col2）。它会像这样插入：
INSERT INTO [schema].[table] (col1, col2, col3) VALUES
('col1', 'col3', 'col2')
It would be extra work and overhead for the Sql Bulk Insert to have to determine a Column Mapping. So it instead allows you to choose... Either ensure your Code and your SQL Table columns are in the same order, or explicitly state to align by Column Name.
Sql Bulk Insert 必须确定列映射将是额外的工作和开销。因此，它允许您选择...要么确保您的代码和您的 SQL 表列的顺序相同，要么明确声明按列名对齐。
Therefore, if your issue is mis-alignment of the columns, which is probably the majority of the cause of this error, this answer is for you.
因此，如果您的问题是列未对齐（这可能是导致此错误的主要原因），则此答案适合您。
TLDR
TLDR
using System.Data;
//...
myDataTable.Columns.Cast<DataColumn>().ToList().ForEach(x =>
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(x.ColumnName, x.ColumnName)));
This will take your existing DataTable, which you are attempt to insert into your created BulkCopy object, and it will just explicitly map name to name. Of course if, for some reason, you decided to name your DataTable Columns differently than your SQL Server Columns... that's on you.
这将采用您现有的 DataTable，您试图将其插入到您创建的 BulkCopy 对象中，它只会显式地将名称映射到名称。当然，如果出于某种原因，您决定将 DataTable Columns 命名为与 SQL Server Columns 不同的名称……那由您决定。
回答by Ashetynw
Not going to be everyone's fix, but it was for me:
不会是每个人的解决方案，但它适合我：
So, i ran across this exact issue. The problem I seemed to have was when my DataTable didnt have an ID column, but the target destination had one with a primary key.
所以，我遇到了这个确切的问题。我似乎遇到的问题是我的 DataTable 没有 ID 列，但目标目的地有一个带有主键的列。
When i adapted my DataTable to have an id, the copy worked perfectly.
当我调整我的 DataTable 以获得一个 id 时，该副本运行良好。
In my scenario, the Id column isnt very important to have the primary key so i deleted this column from the target destination table and the SqlBulkCopy is working without issue.
在我的场景中，Id 列对于拥有主键并不是很重要，因此我从目标目标表中删除了此列，并且 SqlBulkCopy 工作正常。
