Store data with SQLite

Build the schema first, keep values out of SQL text and refresh the interface from committed records.

Choose an application-owned database#

Use AppPaths.DataFile for writable application data, not the application bundle or the current working directory. Open the database asynchronously. Give sample projects their own identity so their data does not accidentally overlap with a real application.

Startup sequence from LibraryDesk

Set mDb = Await Sqlite.OpenAsync(AppPaths.DataFile("librarydesk.db"))
version = Await mDb.ApplyMigrationsAsync(App.Migrations)

Apply migrations before querying#

Declare migration files in the project. Apply them before the first SELECT that needs their tables. Once a migration has been applied in a real installation, add a later migration for a change rather than silently editing the old one. Test both a fresh database and an existing database upgraded through the new step.

Separate values from SQL#

Inside the LibraryDesk Async save handler

Set command = mDb.CreateCommand("INSERT INTO books(title, author, first_publish_year) VALUES(@title, @author, @year)")
command.AddParameter "@title", Trim$(txtTitle.Text)
command.AddParameter "@author", Trim$(txtAuthor.Text)
command.AddParameter "@year", Null
Await command.ExecuteAsync()

The SQL structure is fixed; parameters carry entered values. Use a database Null for a missing optional value rather than the literal string “Null”. Use transactions when several writes must succeed or fail together, and keep the transaction boundary smaller than the entire UI interaction.

Bind a query result#

QueryAsync returns a Recordset. Bind converts the loaded rows to a BindingSource for a data-aware control. Set DisplayMember and ValueMember for a choice control deliberately. Requery after a committed write if the screen should reflect the authoritative stored result.

Separate filtering from querying#

A grid’s local filter sees the loaded rows. A database WHERE clause searches the underlying dataset. For paging, choose a stable order that includes a unique tie-breaker, then handle the first and last pages. The paging lab uses Name,ID and makes the loaded/visible distinction visible.

Complete the workflow#

Use CustomerManager for create, read, update and delete; CustomerDesk for list/detail Forms and cancellation; and LibraryDesk for remote search plus local persistence. Finish by reopening the application: a label that says “saved” is not proof that data survived a restart.

Source baseline 20f4bfed6b · Build manifest