Index value issue in IDataReader implementation

c# idatareader sqlbulkcopy

Question

I have implemented a class using IDataReader as part of a project which uses SqlBulkCopy. I have posted some of the relevant code here

public bool Read()
{
    var result = !fileStream.EndOfStream;
    if (result)
    {
        delimRow = fileStream.ReadLine();    
        splitRowValues = delimRow.Split(_delimiters);
        readRowCount++;
    }    
    return result;
}

private string[] Row
{
    get { return splitRowValues; }
}

public object GetValue(int i)
{
    return Row[i];
}

Code snippet which uses SqlBulkCopy and the IDataReader

SqlBulkCopy bulkInsert = new SqlBulkCopy(Constants.DBConnection, SqlBulkCopyOptions.UseInternalTransaction);
bulkInsert.BatchSize = 500;
bulkInsert.DestinationTableName = Constants.DestinationTable;
bulkInsert.WriteToServer(reader);

When the WriteToServer method is run, I noticed that first call to the GetValue() starts with index value as 1 and the final value of the index is one less than the number of columns. Due to this issue bulk load ends up skipping one column.

I'm not able to find why the index in GetValue() does not take values from 1 to the column count. Actually, the value should have started from 0 and gone to one less than column count. Please let me know if anybody help me find the cause.

Accepted Answer

You do not specify the mapping. So by default SqlBulkCopy may try to automap by ordinal with the database.

By example, if your first column in the database is an identity column (which have great chance to be the case), it will skip the index 0 and start with the index 1 instead.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why