1

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

Gk_999
  • 508
  • 8
  • 29

1 Answers1

2

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>();
CodeNoob
  • 984
  • 8
  • 8
  • So the finl answer is "No, thats not possible". Hats off to your research on this. And +1 for the explaination about not null. Thank you. – Gk_999 Jan 29 '15 at 11:03
  • With reference to your last point , How can i add NotNull Attribute after I fill values in all columns??? In short, how will I add NotNull attribute to a property, programatically? – Gk_999 Jan 29 '15 at 14:15
  • So you already added the column and filled the values? well then it is quite easy you could just add the NotNull Attribute to your Column and call CreateTableAsync again, this time it should work without an error – CodeNoob Jan 29 '15 at 16:03
  • But how to add NotNull attribute programatically to an already existing property??? Something like "IsActive .Attributes.Add('NotNull')"?? – Gk_999 Jan 30 '15 at 05:37
  • Or you want me to have another similar class with NotNull attribute set to IsActive property, & call the CreateTable() method for this new class?? – Gk_999 Jan 30 '15 at 05:57
  • Actually, you cannot set the attribute at runtime. @CodeNoob's updated solution is an answer. Attributes are something that is/are compiled into class and they cannot be changed at runtime. Sure you can read all attributes and modify the collection of them, but that does not affect the class in any way. In short, create a column without `not null`, fill in the (default) values, then add the `NotNull` attribute to the field and call `CreateTable` again. This creates a new table if it does not exist and updates the existing one. If there is nothing to update, it does nothing... – nurchi Feb 05 '15 at 19:34