0

I am trying import some legacy CSV files into my database which were exported with bit/boolean values as 1s and 0s instead of True/False.

I'm required to interface with a system that expects DataRow and hence I am using the DataTable.Row.Add(params object[] values) overload which lets you pass in a array of objects which are then mapped into the columns, to create my DataRows. The DataTable does have the Columns set up with a schema that matches the target database.

This all works fine for every column type except these bit/boolean values where it complains it can't convert with the following exception:

System.ArgumentException: 'String was not recognized as a valid Boolean.Couldn't store <1> in IsEnabled Column. Expected type is Boolean.'

I want to find a way to customize the conversion code so that it knows how to converts the numbers (stored as strings) into bools. Is there a way to do this?

Update: Specifically, I want to avoid manually converting the data in my object[] array first, if possible.

Jack Ukleja
  • 13,061
  • 11
  • 72
  • 113
  • 1
    I'm curious what makes you believe that hacking the built in string conversion provided for a boolean DataColumn to accept a 0 or 1 (if it was even possible) is in any way less work than or preferable to processing the object array to convert the subject values to the proper boolean values. You need to implement the logic either way. You only need to process those items in the array that map to boolean. – TnTinMn May 18 '17 at 16:07

2 Answers2

0

Here are a couple of other ideas. First, you could just convert all "0" or "1" strings to bool:

// A list representing one row (for example)
var data = new List<string> { "1", "Hello World", "true" };

// Retrun all items as strings unless it can be parsed to  
// a '1' or a '0', in which case convert it to a boolean
int tmp;
object[] result = data.Select(d =>
    int.TryParse(d, out tmp) && (tmp == 1 || tmp == 0)
        ? (object) Convert.ToBoolean(tmp)
        : d)
    .ToArray();

DataTable.Row.Add(result);

Or, if you know the type of each column, you could create a "typemap" for all the items in your data row. Then, you can just convert your string data into strongly typed data, and store them in an object array:

// A list of items representing the type of each column in a row
var typeMap = new List<Type> {typeof(int), typeof(string), typeof(bool)};

// A list representing one row (for example)
var data = new List<string> {"1", "Hello World", "true"};

// The array of converted data to add to your DataTable
var result = new object[data.Count];

// Convert each string to it's corresponding type
for(int i = 0 ; i < data.Count; i++)
{
    result[i] = Convert.ChangeType(data[i], typeMap[i]);
}

DataTable.Row.Add(result);
Rufus L
  • 36,127
  • 5
  • 30
  • 43
  • Sorry I should have been clear - yes it's bleeding obvious you can manually convert using the numerous conversion methods available :). But I specifically wanted a way to customize how the `DataTable.Row.Add(params object[] values)` works such that it can do it automatically. – Jack Ukleja May 18 '17 at 00:36
  • I figured as much, but without any sample input and output data...how would it know which values to convert and which to leave? Do you want to convert ALL `0` and `1` input to `bool`? What about strings like `"true"` or `"false"`? What about other `int` values? – Rufus L May 18 '17 at 00:41
  • The `DataTable` does have the schema setup via the Columns (I am able to determine the schema from the target db) – Jack Ukleja May 18 '17 at 00:50
  • Note: this code doesn't actually work: throws exception: "String was not recognized as a valid Boolean." – Jack Ukleja May 18 '17 at 00:57
  • Wow, and I pulled that sample from the documentation! I've removed that part and added a couple of other ideas. Oh, I see your comment that you don't want to convert the items first. Not sure how to do that...will think about it – Rufus L May 18 '17 at 01:19
0

One other way to do would be by using an Add method's overload that takes a DataRow

DataTable dt  = new DataTable();
var row  = dt.NewRow();
//considering your input value can be 1 or 0 and colName is of bool type
row["colName"] = (1 == yourInputVal); 
//your other col mappings and any other customization you may need
dt.Rows.Add(row);
haku
  • 4,105
  • 7
  • 38
  • 63
  • Thanks. I've updated my question to be more specific about the solutions I'm looking for. Specifically, I'm trying to avoid a manually converting the `object[]` (which is actually all strings) into the correct types first. – Jack Ukleja May 18 '17 at 00:39