如何在SQLbulkcopy中的InvalidOperationException时获取列和表名称 [英] How to get the column and table name when InvalidOperationException in SQLbulkcopy

问题描述

我正在使用下面的代码，同时插入我得到的数据;



实际输出：

InvalidOperationException：数据源中String类型的给定值无法转换为指定目标列的int类型

我有多个int行类型，我怎么能捕获哪一行在catch区块中出现此错误。



所需输出：

给定值来自数据源的String类型无法在OWN_ACCT_NUM中转换为int类型

或

数据源中String类型的给定值不能在OWN_NUM中转换为int类型



请帮帮我，错误预防不是我的目标，需要显示哪一列出现此错误，是我的终极目标。

i am using below code , while insert the data I got;

Actual output:
InvalidOperationException: The given value of type String from the data source cannot be converted to type int of the specified target column
I have more than one type int rows, how can i catch which row rises this Error in catch block.

Desired output:
The given value of type String from the data source cannot be converted to type int in OWN_ACCT_NUM
or
The given value of type String from the data source cannot be converted to type int in OWN_NUM

Please help me, Error prevention is not my aim, Need show the which column rises this error, is my ultimate aim.

SqlConnection DestinationConn = new SqlConnection(DestConnString);

bc.DestinationTableName = mytable;
bc.ColumnMappings.Add(2, "OWN_NUM");
bc.ColumnMappings.Add(2, "OWN_ACCT_NUM");
bc.ColumnMappings.Add(3, "OWNER_ADDR_1");
bc.ColumnMappings.Add(4, "OWNER_ADDR_2");
bc.ColumnMappings.Add(5, "OWNER_CITY");
bc.ColumnMappings.Add(6, "OWNER_NAME_1");
bc.ColumnMappings.Add(7, "OWNER_NAME_2"); bc.WriteToServer(dtable);

推荐答案

数据源中String类型的给定值无法转换为 OWN_ACCT_NU



数据源中String类型的给定值无法转换为 OWN_NUM 中的int类型>


列名明确说明。为什么你需要帮助来阅读它们？



为什么不使用整数类型而不是字符串？将每个变量作为字符串处理是一种可怕的习惯，在任何情况下都应该避免。 .NET Framework为您提供了所需的所有类型，因此请相应地使用它们。

The given value of type String from the data source cannot be converted to type int in OWN_ACCT_NU

The given value of type String from the data source cannot be converted to type int in OWN_NUM

Column names are stated explicitely. Why do you need help to read them?

And why not using integer type instead of string? Handling every variable as string is a terrible habit that should be avoided in any case. .NET Framework provides you all types you need, so please use them accordingly.

实际上我的代码从excel加载数据并且必须将该数据插入表中。如果用户在（Excel列）excel文件中放入字符串值而不是整数值。那个时候，我必须向用户显示错误消息在列类型的int类型列中不能接受字符串值。我能够收到错误消息，但需要显示该消息的列名。



例如：我能够在捕获SqlException时获取列名收到了无效的列长度使用下面的代码。



string pattern = @\d +;

////返回ex.me



匹配匹配= Regex.Match（ex.Message.ToString（），pattern）;

var index = Convert.ToInt32（匹配.Value） - 1;



FieldInfo fi = typeof（SqlBulkCopy）.GetField（_ sortedColumnMappings，BindingFlags.NonPublic | BindingFlags.Instance）;

var sortedColumns = fi.GetValue（bc）;

var items =（Object []）sortedColumns.GetType（）。GetField（_ items，BindingFlags.NonPublic | BindingFlags.Instance）。 GetValue（sortedColumns）;



FieldInfo itemdata = items [index] .GetType（）。GetField（_ metadata，BindingFlags .NonPublic | BindingFlags.Instance）;

var metadata = itemdata.GetValue（items [index]）;



var column = metadata.GetType（） .GetField（column，BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance）.GetValue（metadata）;

var length = metadata.GetType（）。GetField（length，BindingFlags。 Public | BindingFlags.NonPublic | BindingFlags.Instance）.GetValue（metadata）;

retvalue = String.Format（Column：{0}包含长度大于：{1}的数据，列，长度）;



同上，必须在我的错误信息中显示列名。

Actually the my code loading the data from the excel and have to insert that data into table. If user put string values instead of integer value in the(Excel Column) excel file. That time, I have to show the error message to the user as "String value cannot be accepted in int type column with column name". i was able to get the error message but need to show the column name with that message.

Ex : I can able to get column name while catching SqlException "Received an invalid column length" using below code.

string pattern = @"\d+";
////return ex.me

Match match = Regex.Match(ex.Message.ToString(), pattern);
var index = Convert.ToInt32(match.Value) - 1;

FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance);
var sortedColumns = fi.GetValue(bc);
var items = (Object[])sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(sortedColumns);

FieldInfo itemdata = items[index].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance);
var metadata = itemdata.GetValue(items[index]);

var column = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
var length = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
retvalue = String.Format("Column: {0} contains data with a length greater than: {1}", column, length);

Same as above have to show Column name in my error message.

这篇关于如何在SQLbulkcopy中的InvalidOperationException时获取列和表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！