SQL Server - cannot import a column of values ​​0 or 1 per bit

Question

This seems like a duplicate question. But when I searched and examined related questions and answers, I still cannot find the answer.

I am writing a bootloader program that imports data records from a CSV file into a database table. Here is a demo.

The database table is as follows:

use Test;
create table BoolTest (Name varchar(20) not null, IsValid bit not null);
insert into BoolTest (Name, IsValid) values('xx', 1);
insert into BoolTest (Name, IsValid) values('yy', 0);

The bootloader program is similar to:

class Program
{
    static void Main(string[] args)
    {
        var csvFileContent = "Name,IsValid\r\naa,true\r\nbb,false";            
        var csvLines = csvFileContent.Split(new String[] { "\r\n" }, StringSplitOptions.None);
        var columnNames = csvLines[0].Split(',');

        var table = new DataTable();
        foreach (var columnName in columnNames)
        {
            table.Columns.Add(new DataColumn(columnName));
        }

        for (var n = 1; n < csvLines.Length; ++n)
        {
            var line = csvLines[n];
            var values = line.Split(',');
            table.Rows.Add(values);
        }

        var connectionString = "Data Source=localhost;Initial Catalog=Test;Integrated Security=True";
        using (var bulkCopy = new SqlBulkCopy(connectionString)) {
            bulkCopy.DestinationTableName = "BoolTest";

            bulkCopy.ColumnMappings.Add("Name", "Name");
            bulkCopy.ColumnMappings.Add("IsValid", "IsValid");

            bulkCopy.WriteToServer(table);
        }

        Console.WriteLine("Done");
        Console.Read();
    }
}

All perfectly.

But the problem is that the CSV file belongs to a third party, and the values ​​for the "IsValid" column are 0 or 1 below

Name,IsValid
aa,1
bb,0

When the loader program tries to load this CSV file, bulkCopy.WriteToServer (table) will throw an exception

The sql server that I use is Sql 2014 server.

Any help would be appreciated. Thank.

--- update ---

, , . 0/1 false/true . , (, SQL Server SqlBulkCopy) .

sql Sql Server Management Studio,

insert into BoolTest (Name, IsValid) values('aa', 1); -- this works as we expected

insert into BoolTest (Name, IsValid) values('aa', '1'); -- this also works to my surprise

. , SQL- , IsValid 0 1. "select", IsValid 0 1.

Answer 1

, String ( DataTable) BIT, ( "0" "1" 0/1 BIT).

- :

table.Columns.Add(new DataColumn("Name", typeof(String)));
table.Columns.Add(new DataColumn("IsValid", typeof(bool)));

:

for (var n = 1; n < csvLines.Length; ++n)
{
    var line = csvLines[n];
    var values = line.Split(',');

    var rowValues = new object[values.Length];
    rowValues[0] = values[0];
    // conversion to integer is required as Boolean will not accept the string
    rowValues[1] = Convert.ToBoolean(Convert.ToInt32(rowValues[1]));
}

Answer 2

, Boolean insert, , bool, .

Answer 3

INSERT SQL:

INSERT INTO BoolTest (Name, IsValid) VALUES ('xx', 1);
INSERT INTO BoolTest (Name, IsValid) VALUES ('yy', 0);

SQL, 0 1, 0 1 ( ). , .

, :

var values = line.Split(',');

All you have to do is check for 0 and 1 and then replace them with false or true respectively.