5

I am using my custom CSVDataReader : IDataReader {} to insert Bulk values in a Database table.

Every datatype but the Bit (from "1"/"0") is parsed perfectly. I am getting the following error " value of type String from the data source cannot be converted to type bit" while parsing 0 or 1 as bool

If I change these values to "true"/"false". It is taken without any problem.

I can't alter the CSV file. Currently I replace that specific column from "0"/"1" to "false"/"True" during Iteration. But this is not an elegant solution.

Please help !

Thanks Panks

gbn
  • 422,506
  • 82
  • 585
  • 676
Panks
  • 601
  • 1
  • 11
  • 20

2 Answers2

0

I guess you're sending "1" and "0" rather then 1 and 0

FYI, SQL Server will accept true and false for bit

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    The CSVDataReader reads the file using a StreamReader, It reads line by line and parse each line as string. So all the column values are string. this.stream = new StreamReader(fileName); in Read() method I split the line string[] currentRow = stream.ReadLine().Split(','); – Panks Oct 08 '10 at 00:57
  • SQL won't parse "true" or "false"with quotes so *something* is different – gbn Oct 08 '10 at 04:48
0

I had a similar issue where an IEnumerable of anonymous types was failing to populate my database table. It turned out that the order of the fields in my DataTable did not match the order of the columns in the database table. I do not at this point understand why the order of the columns should matter, since the column names could be used to match them correctly.

In any case, my column mismatch was a side effect of using the FastMember NuGet package to quickly populate the DataTable, which sorts the columns alphabetically under the hood! The solution was to replace my anonymous type with a small declared class that used the Ordinal attribute on its properties:

public class Row
{
    [Ordinal(0)] public string ColumnA { get; set; }
    [Ordinal(1)] public string SecondColumn { get; set; }
    [Ordinal(2)] public string ColumnC { get; set; }
}

You can read more about this ordering issue here, where user dyatchenko created a pull request to support custom property ordering. This was eventually merged back into master.

Philip Atz
  • 886
  • 1
  • 10
  • 26
  • 1
    The order of the columns matters because that's just how `SqlBulkCopy` does its business, in a mistaken attempt to model the native functionality as closely as possible (`bcp` does the same thing). You can easily override it through the `SqlBulkCopy.ColumnMappings` property, though (map every column to itself). Everything that uses bulk copying under the hood ought to offer some way of setting this property (or preferably just do the self-mapping as a matter of course). – Jeroen Mostert Oct 26 '20 at 18:25
  • Right, thanks, I had used `ColumnMappings` before but it was so long ago I had forgotten about it. Thank you for reminding me! I guess then `Ordinal` is not needed in this case. – Philip Atz Oct 26 '20 at 18:46