[SOLVED] MAUI SQLite-net default value for datetime columns

MAUI SQLite-net default value for datetime columns

I believe there is a bug in MAUI SQLite-net default value for datetime columns. I did the following code:

db.Execute ("CREATE TABLE User (user_id INTEGER PRIMARY KEY AUTOINCREMENT,user_created DATETIME DEFAULT CURRENT_TIMESTAMP,-- Other columns...);

or:

db.Execute ("CREATE TABLE User (user_id INTEGER PRIMARY KEY AUTOINCREMENT,user_created DATETIME DEFAULT (datetime('now','localtime')),-- Other columns...);

And then when I did either:

db.Execute("insert into User default values");

or:

int rowsAffected = db.Insert(user);

user_created is not updated to current date time but {1/1/0001 12:00:00 am}.

user_created can be updated via:

var userToUpdate = db.Table<User>().FirstOrDefault(u => u.user_id == 1);
userToUpdate.user_created = DateTime.Now;
int rowsAffected = db.Update(userToUpdate);

but not via:

db.Execute("update User set user_created=CURRENT_TIMESTAMP");

or:

db.Execute("update User set user_created=datetime('now','localtime')");

I tested above case in Android Emulator in Windows 11.

Solution

I recommend you refer to the .NET MAUI local databases and its sample code.

The SQLite.NET library provides a simple Object Relational Map (ORM) that allows you to store and retrieve objects without writing SQL statements, as H.A.H mentioned.

The sample create a database access class to centralizes query logic and simplifies the management of database initialization and you don't have to directly write SQL statements.

You could define your model to map to the sqlite table. And it's easy to set the default time as follows:

public class TodoItem
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string Name { get; set; }

    public DateTime User_created { get; set; } = DateTime.Now;
}

And it works as expected.

Hope it helps!