A sample application written in .Net Core (3.1) which uses ADO.NET and the Repository pattern to access a SQLite3 database

1. SQLite3 has only three data types (Text, Integer, and Real)
2. Dates are represented as Integer days since November 24, 4714 BC (UTC)
3. DateTimes are represented as Integer seconds since 1970-01-01 00:00:00 UTC
4. Boolean is represented as Integer (0 = false; 1 = true)
5. SQLite3 doesn't really support async. Which is fine, since it's not really for client/server and multi-user access. However, you can run it with WAL (see below)
6. SQLite3 has a command-line shell program(sqlite3.exe), which provies bcp/sqlcmd type functionality
7. SQLite3 supports In-memory databases
8. XUnit is used as the testing framework
9. INSERT should be followed by SELECT last_insert_rowid() for tables with IDENTITY as their Primary Key
10. Returning Pages of data is accomplished via the LIMIT and OFFSET keywords: SELECT Id FROM [table] LIMIT 10 OFFSET 0;
11. SQLite3 does NOT support Stored Procedures

1. You store them as REAL and convert them to DateTime when you read them
2. You don't need to set ModifiedDt or CreateDt in the POCO. These are set in the repository during Update and Add
3. Update: UPDATE [table] SET ModifiedDt=strftime('%s','now');
4. Add: INSERT INTO [table] (ModifiedDt,CreateDt) VALUES(strftime('%s','now'),strftime('%s','now'));

• Microsoft.Data.Sqlite Overview
• Datatypes in SQLite 3
• Async limitations
• SQLite3 Write Ahead Logging(WAL)
• An Asynchronous I/O Module For SQLite3
• SQLite3 Command-line
• SQLite3 In-memory databases
• SQLite3 Command-line Shell download
• SQLite3 Date and Time Functions
• SQLite3 Dates Tutorial