Get the line number on which the request to database ended with an error
c# reflection sqlbulkcopy sql-server
Question
I use this code to send a SQL request:
SqlBulkCopy bulkCopy = new SqlBulkCopy(Connection);
foreach (DataColumn column in dt.Columns)
{
bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
bulkCopy.DestinationTableName = "nsi." + classifierData.Info.TableName;
bulkCopy.WriteToServer(dt);
and get this exception :
Received an invalid column length from the bcp client for colid
Is there any way to identify which row is causing the error?
I've tried to use this, but it doesn't work (values are always the same):
FieldInfo currentRow = typeof(SqlBulkCopy).GetField("_currentRowLength", BindingFlags.NonPublic | BindingFlags.GetField | BindingFlags.Instance);
var currentRowNumber = currentRow.GetValue(bulkCopy);
FieldInfo _rowsCopiedField = typeof(SqlBulkCopy).GetField("_rowsCopied", BindingFlags.NonPublic | BindingFlags.GetField | BindingFlags.Instance);
var currentRowN = _rowsCopiedField.GetValue(bulkCopy);
Please, help me anyone ...
Accepted Answer
I believe the only way to identify the row in error from purely SqlBulkCopy is using the NotifyAfter property + SqlRowsCopied event + BatchSize = 1.
From these, you can now easily have an Index property that you increment for every successfully copied row and you will be able to find the index in error.
For example, an error is thrown and you re-try with NotifyAfter = 1
bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
bulkCopy.NotifyAfter = 1;
bulkCopy.BatchSize = 1;
Obviously, not the best performance wise solution but you will find your information.
EDIT: Answer comment
Sorry, but can I get a value of row-column with error?
I don't think so. We don't know exactly which column have an issue with the error message.