SQLite default values to boolean column, not working as expected

Question

I have one SQLite Table with following entity:

[Table ("employee")]
public class Employees
{
    [Column ("employee_id")]
    [PrimaryKey]
    public int EmployeeId { get; set; }

    [Column ("username")]
    public string UserName { get; set; }

    [Column ("password")]
    public string Password { get; set; }

    [Column ("first_name")]
    public string FirstName { get; set; }

    [Column ("last_name")]
    public string LastName { get; set; }

    [Column ("is_wah_allowed")] //Newly added Column
    public bool IsWAHAllowed { get; set; }  
}

When App starts, I am creating a Table.

conn.CreateTable<Employees>();

I want to add new boolean column with default value set to 0 - false in SQLite as it stores bool as 0 or 1.

I tried it following ways:

Case 1 :

[Column ("is_wah_allowed")] //Newly added Column

Output : This shows Null into DB Browser for SQLite

Case 2 :

[Column ("is_wah_allowed"), Default (false)] //Newly added Column

Output : This throws exception at conn.CreateTable(); - Can not convert from bool to int.

Case 3 :

[Column ("is_wah_allowed"), Default (value : 0)] //Newly added Column

Output : This shows False values into DB Browser for SQLite, instead of 0. When server returns me data, I save/update them and then it shows them as 0 or 1 in updated rows, because server handle this as integer values.

I want to know :

Case 1 : Why it shows Null instead of 0 default value?

Case 2 : Why it throws exception?

Case 3 : Why it shows False instead of 0?

Thanks in advance!!

Answer 1

There is no boolean exists in SQLite. Rather you store the boolean value in the database as int in SQLite. The values that you can use are 0 for false and 1 for true. When you go and select the int from the database. According to 0 & 1 you can right your if else block.

Edit 1

Boolean Datatype SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

SQLite data types

For more information visit this