MAUI SQLite-net default value for datetime columns

Question

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.

Do you think there is a DateTime field in SQLite? Another question, if tomorrow SQLite is not adequate, you decide to go MS SQL, or PG, or MySQL, will you deal with that non-sense with those engines as well? I would consider moving this "default" values to the code. Ideally - an ORM should be doing this work instead of me. — H.A.H.
– H.A.H., Commented Sep 5, 2023 at 11:34
Have you tried user_created DATETIME DEFAULT (datetime('now','localtime')) ? Not date. — Felix Shen
– Felix Shen, Commented Sep 6, 2023 at 9:18
Sorry, my writing mistake. It is user_created DATETIME DEFAULT (datetime('now','localtime')) rather than date. — WWW
– WWW, Commented Sep 6, 2023 at 10:07

Felix Shen · Accepted Answer · 2023-09-08 08:31:21Z

0

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!

edited Sep 8, 2023 at 8:31

answered Sep 8, 2023 at 8:17

Felix Shen

8,7932 gold badges6 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

WWW Over a year ago

It works based on db.CreateTable<User>(). The benefit of SQL statements is we can build SQL statements at run time. Object Relational Map (ORM) requires hard coding table class and the class within <> can't be a variable.

Felix Shen Over a year ago

Yes. The TodoItem class in <> is a model class which can map the table column in sqlite. And I think if there are more tables, you could refer to this thread : Generic class method using SQLite-Net

WWW Over a year ago

I can make SQL statement work simply change public DateTime user_created { get; set; } to public string user_created { get; set; }

Collectives™ on Stack Overflow

MAUI SQLite-net default value for datetime columns

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related