SQLite.Net, C# equivalent for SQLite bit datatype

Question

I declare following class in c#

[Table("Employee")]
public class Employee
{
    [PrimaryKey,AutoIncrement]
    public int EmployeeId { get; set; } 
    public DateTime DateOfJoining { get; set; }
    public string Address{ get; set; }
}

and i invoke this method to create equivalent table in my SQLite database

public async Task CreateTable()
{
   SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path);
   await conn.CreateTableAsync<Employee>();
}

So it creates a table in SQLite as follows

[EmployeeId] int,
[DateOfJoining] [datetime],
[CallType] [varchar]

I wanted To create a a column, which is bit

[IsActive] [bit]

For this I tried

public bool IsActive { get; set; } 

and

public Boolean IsActive { get; set; }

Both these properties result in a column which is an integer

[IsActive] integer

So how should I declare my IsActive property to get a column with bit as datatype.

I have one more question, If i declare property and specify it as not null

[NotNull]
public bool Address{ get; set; }

Then it gives me an error when I invoke CreateTable() saying, "No default value specified for Not Null attribute".

I tried to initialise this property in constructor, but it didnt work.

How do I go about these issues? Please Help

Answer 1

As far I see in SQLite Docs there is no bit datatype for SQLite

and in SQLite.Net all kind of byte/boolean/ints get mapped to integer: see this line

for the NotNull Error, let me guess:

• You have already some entries in your Table
• You now add a new Column with NotNull-Attribute
• SQLite tries to alter the tablet and it crashes, because multiple entries where the new column has now null values

I think this is the only situation where to have to alter table in this order:

• Add Column without NotNull
• Add some column values
• only once all table entries has the value for this Column --> now you can add the NotNull Attribute

If your Table is empty then the NotNull Parameter would work right from the start.

---

Edit easier Solution:

• Add Column without NotNull

ALTER TABLE Employee ADD COLUMN IsActive integer default 0; 

• now you can add the NotNull Attribute and call CreateTable again

[NotNull]
public bool Address{ get; set; }

await conn.CreateTableAsync<Employee>();