IDataReader implementation failure with SqlCeBulkCopy

c# idatareader sqlbulkcopy sql-server-ce

Question

I'm trying to bulk load data from a .DAT file (output from SQL Server) to a SQL CE 4.0 file. I've been using ErikEJ's SqlCeBulkCopy class to load data perfectly from SQL Server, but from .DAT has been an issue.

I created a class that implements IDataReader to read from .DAT, so I can pass the reader into the SqlCeBulkCopy.WriteToServer method. The reader mostly seems to be fine, but I'm having problems with nulls; an exception is thrown when a null has been read from the .DAT file and is being inserted into a nullable column in the destination table.

I'm tempted to believe I have implemented the IDataReader interface wrong in some way. I have the values stored in an array of System.Object, and the method IsDBNull is implemented in this way:

public bool IsDBNull(int i)
{
   return Values[i] == null;
}

It's worth noting that I've put a breakpoint here and the method is not being called.

Otherwise, my relevant methods are:

    public object GetValue(int i)
        {
            return Values[i] ?? DBNull.Value;
        }
    object IDataRecord.this[int i]
        {
            get { return GetValue(i); }
        }

    object IDataRecord.this[string name]
        {
            get
            {
                return GetValue(GetOrdinal(name));
            }
        }

The exception I get is a FormatException with the message "Input string was not in a correct format." Anybody have any idea where I'm going wrong?

This is the stack trace from the exception:

  at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)

at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) at System.Byte.Parse(String s, NumberStyles style, NumberFormatInfo info) at System.Convert.ToByte(String value, IFormatProvider provider) at System.Data.SqlServerCe.SqlCeUpdatableRecord.SetClrTypeValue(Int32 ordinal, Object value, String method) at System.Data.SqlServerCe.SqlCeUpdatableRecord.SetValue(Int32 ordinal, Object value) at ErikEJ.SqlCe.SqlCeBulkCopy.WriteToServer(ISqlCeBulkCopyInsertAdapter adapter) at ErikEJ.SqlCe.SqlCeBulkCopy.WriteToServer(IDataReader reader) at MySoftware.Modules.Analysis.SqlCeFileConfiguration.SqlCeBulkDataStreamer.CopyFromReader(IDataReader reader, String destinationConnectionString, String tableName) in E:\Code\Desktop MySoftware\software\desktop_and_web\MySoftware\MySoftware.Analysis\SqlCeFileConfiguration\SqlCeBulkDataStreamer.cs:line 42 at MySoftware.Modules.Analysis.Services.SqlCeDataManagerService.InsertData(String connectionString, String tempWorkFolder, TableInfo tableInfo) in E:\Code\Desktop MySoftware\software\desktop_and_web\MySoftware\MySoftware.Analysis\Services\SqlCeDataManagerService.cs:line 742

Accepted Answer

It turns out the problem was something I missed; the source was not null, it was actually an empty string. It seems when I read from the .DAT file I was converting at the wrong point.


Popular Answer

Do you have any Numeric values in your import? Sounds like its trying to convert/parse a int and its failing.



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