Search code examples
c#sqlbulkcopyidatareader

Index value issue in IDataReader implementation

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.

Solution

  • 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.