DBKit

Introduction

DBKit is a module of classes and example projects designed to show and help you make it easier to build desktop and web applications that are front-ends to databases. Specifically, it makes it easy to connect a database table to the user interface controls on a layout. DBKit also handles a lot of the interface for you. For example, it will enable/disable the controls you provide for editing data. It will enable/disable buttons for saving, deleting, etc. It automatically confirms with the user when they attempt to delete a record or switch records after editing without saving first.

Once you set it up, adding controls for columns in your database is very simple. Most will require no code at all. They will just work.

Unlike most other example projects, this one both demonstrates DBKit and provides the source to the DBKit module itself that you can then use in your own projects.

DBKit is designed to be used with interfaces where there's a list of rows in a DBKit.Table that the user can then select from to edit either in controls on the same layout or a separate layout. It can also be used in cases where no list of rows are displayed. See the DBKit-Desktop and DBKit-Web example projects for examples of the various ways in which it can be used.

Supported project types

It currently support desktop and web projects. Mobile project support will be coming in a later update.

Supported Databases

DBKit supports MySQL, PostgreSQL and SQLite. It comes with all the source so you could potentially add support yourself for other database engines.

Supported Xojo versions

DBKit requires 2026r3 or later.

DBKit version

Read the release notes for details about specific releases.

Downloading DBKit

The current version of DBKit is available from Xojo's Project Chooser. In Xojo, choose File > New Project then click on Examples > Databases to find them.

Testing DBKit

Run the DBKit-Desktop or DBKit-Web projects. Both have three examples of DBKit in action:

Name	Description
Customers	A single layout example with search results. Displays a Table with customer records that when selected can be edited via various controls on the same layout.
Invoices	A two layout example. Displays the Table by itself. Double-clicking on a row will then display a separate layout for editing. This example also shows how to display rows from two different tables on the same layout by setting the Table Name property on the controls bound to the second table.
Products	A single layout example without search results. Performs a query then displays the first row in the EntryWindow/EntryPage layout without the need for a Table displaying rows for the user to choose from.
Maintenance	An example of embedding a DBKit.Container in a window	webpage or other container.

Installing DBKit

For Desktop projects, open the DBKit-Desktop example project. Click on the DBKit Module, choose Edit > Copy then go to your project and choose Edit > Paste.

For Web projects, open the DBKit-Web example project. Click on the DBKit Module, choose Edit > Copy then go to your project and choose Edit > Paste.

Important

Always copy DBKit from the DBKit-Desktop project for desktop projects and from the DBKit-Web project for web projects. They are identical but there's an issue in the Xojo IDE at the moment copying from on project type to another.

No Row locking

If you are going to be using DBKit with a database server and multiple, simultaneous users, be aware that this version of DBKit does not attempt to do any row locking. The user experience of requiring the user to deliberately indicate they wish to edit by clicking the Edit button is designed to help limit occurrences where two users accidentally editing the same row at the same time. For now, if this does happen, the version of the row saved by the last user to save will be the one stored.

Getting started

In most cases, there are a only a few things you'll need to know about DBKit to use it:

How to connect to your database.
How to make your window, webpage or container DBKit-aware.
How to connect controls on your layout to columns in your database.

It is highly recommended you work through the desktop or web tutorial first as it will teach you all of this. It will take about 30 minutes.

How DBKit fits together

There are only a few ideas behind everything DBKit does:

The layout is in charge. A DBKit.Window, DBKit.Page or DBKit.Container connects to the database, knows which table it is working with, and keeps track of the current row.
Controls on the layout show and edit the columns of the current row. You connect a control to a column by changing its Super to the DBKit version of the control and giving it the column name.
A DBKit.Table shows the rows as a list. Clicking a row makes it the current row, which loads it into the entry controls.
A DBKit.SearchField filters the rows shown in the Table as the user types.

Automatic behavior like enabling buttons, confirming deletes, and warning about unsaved changes all follows from these ideas: DBKit always knows the current row and whether it is being edited.

Connecting to a database

DBKit primarily works with Xojo's DatabaseConnection class. Add a DatabaseConnection item to your project by choosing Insert > DatabaseConnection then choosing the type of database you are using. Configure the Database connection as appropriate. You will then be able to assign it to any layout you will be using with DBKit.

Making your layouts DBKit-aware

For DBKit to do its magic layouts that will contain DBKit controls must be DBKit-aware. To make them aware, change their Super to the equivalent DBKit type. Just click the Pencil icon next to the Super property in the Inspector. For windows, DBKit.Window, for WebPages, DBKit.Page and for Containers, DBKit.Container.

Supported controls

DBKit includes a several subclasses of the existing controls. These subclasses are designed to handle the work of connecting a database column to that particular type of control. Most of them you will only use to designate a control as being a DBKit-enabled control. You do this by dragging a control from the Library to your layout, then clicking the Pencil icon next to the Super property in the Inspector and choosing the DBKit control class that appears.

The following controls are supported for binding to database columns:

Note

For DesktopDateTimePicker and WebDatePicker the database column must be Text, and cannot be null. DesktopDateTimePicker TimeOnly mode is not currently supported.

Displaying rows with a DBKit.Table

A DBKit.Table displays the rows of the layout's table. To set one up:

Drag a Table (desktop) or ListBox (web) from the Library to your layout and change its Super to DBKit.Table.
In the Inspector, set Column Count to the number of columns you want to show, then give each column a heading.
Set the Column Names property to the database columns to display, separated by commas, in the order you want them shown. For example, firstName,lastName displays those two columns.

Use the Column Formats property to control how each column's values are displayed, with one format per column in the same order as Column Names. For example, String,DateTime.FormatStyles.Short,"$###,##0.00" shows the first column as text, the second as a short date, and the third as currency.

When the user clicks a row, the layout makes it the current row and loads it into the bound entry controls. If the current row has unsaved changes, the layout asks the user to confirm before switching rows.

Searching with a DBKit.SearchField

Add a DBKit.SearchField to let the user filter the rows shown in the Table. As the user types, the rows are filtered to the matching ones. By default every displayed column is searched; set the SearchField's Columns property to a comma-separated list to search only specific columns, for example firstName,lastName.

Text matches from the beginning of the value. You can also search for an exact value by surrounding it with double quotes ("Smith"), use > and < for numeric comparisons (>1000), and separate two values with .. for a range (10..50). To search a specific column, start with the column name. For example, typing Last Name=Smi finds every row where the last name begins with "Smi", while Last Name="Smith" finds only rows where the last name is exactly "Smith". The SearchField's tooltip shows this syntax while the app runs.

You don't need to write any code: just change a SearchField's Super to DBKit.SearchField and put it on a DBKit-aware layout.

Connecting data entry controls to database columns

To connect a database column to a control, you must first change the control to a DBKit control. The simplest way to do that is to:

Select your control. click on the Pencil next to the Super property and choose the DBKit control displayed.
In the DBKit section of the Inspector, enter the database column name into the Column Name property or simply name the control itself the name of the database column.

You can either name your controls to match the names of the columns from the database table or put the column name for the control in its Column Name property in the Inspector. DBKit controls have a Table Name property as well. This is only required in cases where you are using two different tables on the same layout. In that case, all controls should designate both their Column and their Table names.

If you leave a control's Hint empty, DBKit automatically uses the database column's display name as the hint.

Loading the selected row into the bound entry controls

When the user clicks on a row in a DBKit.Table, the DBKit Layout will first determine if the row already displayed is in the middle of being edited. If it is, DBKit layout will display a confirmation dialog box allowing the user to cancel the row change in order to continue editing the current row or lose those changes and switch to the new row. If the user decides to switch to the new row, it will load that row automatically.

Reacting to row actions

DBKit-aware layouts raise events as the user works with rows. Add code to these events when you need to react to what is happening:

Event	Description
RowLoaded	A row was loaded into the entry controls.
RowEditing	The user started editing the current row.
RowSaving	The current row is about to be saved.
RowSaved	The current row was saved.
RowUndone	The user undid their changes to the current row.
RowCreated	A new row was created.
RowDeleted	A row was deleted.
RowChanged	A different row became the current row.
RowNotSelected	There is no current row.
Error	Something went wrong, such as a database error.

Most of these events pass you the row as a DBKit.Row. You can also get the current row at any time from the layout's CurrentRow property, which is useful when you need to read or change column values in code.

Filtering and sorting rows

Use the layout's Where property to limit which rows DBKit loads. It holds the filter used when loading rows from the database table, for example Country = 'USA' to show only customers from the USA.

Use the OrderBy property to control the sort order used when loading rows, for example LastName, FirstName to sort by last name, then first name.

Using separate layouts for searching and editing

For a larger interface you can put the search Table on one layout and the entry controls on another, as the Invoices example does. The search layout opens the entry layout when the user double-clicks a row, telling it which row was selected so the entry layout can load that row.

See the Invoices example in the DBKit-Desktop or DBKit-Web example project for the complete pattern.

Adding Edit, Undo, Delete and New buttons

You may wish to have buttons to allow the user to edit, undo changes, delete the record and/or make new records. You can use a DesktopButton or WebButton. To do this just drag out a SegmentedButton control then change its Super to a DBKit.EditingButton (which can include Delete, New, Undo and Edit or any combination of these in a single button) or drag out individual buttons and change their Super to the type of DBKit button action you want (DBKit.DeleteButton, DBKit.NewButton, DBKit.UndoButton, DBKit.EditButton).

There's no need to indicate the Table Name property as DBKit will automatically use the DBKit layout's TableName property.

If you prefer a toolbar to individual buttons, change a Toolbar's Super to DBKit.Toolbar. It displays Delete, Edit, Undo and New buttons that act on the current row of the layout's table.

Adding navigation buttons

You may wish to have buttons to allow the user to move to the first, previous, next and last rows of the search results. To do this just drag out a SegmentedButton control then change its Super to a DBKit.NavigationButton (which can include First, Previous, Next and Last or any combination of these in a single button) or drag out individual buttons and change their Super to the type of DBKit button action you want (DBKit.MoveToFirstRowButton, DBKit.MoveToPreviousRowButton, DBKit.MoveToNextRowButton, DBKit.MoveToLastRowButton).

In all cases, if the row has been edited but not saved, clicking one of these buttons will display the save confirmation dialog box.

These buttons will all auto-enable and auto-disable appropriately based upon which row is being displayed. For example, if the first row is being displayed the First and Previous buttons will automatically disable.

Using an ImageViewer control for adding/changing images

For desktop projects, the DBKit.ImageViewer will accept JPEG and PNG files dropped on to it.

The example projects demonstrate this.

Testing Your Project

Testing a database application often involves making changes to the data. To facilitate this, if you are using SQLite, you can add a Copy Files Build Step to your project to put a copy of your database in the debug build when running from the IDE. If you've never done this before, checkout either DBKit Tutorial as they will have to do this as one of the steps.

Localizing DBKit

Any text displayed by DBKit that the end user is likely to see is localizable. You will find localizable strings as properties of the various layouts and controls in the DBKit section of the Inspector. Those whose message has a value parameter such as <$1> indicate where the name of the parameter will be displayed in the message. You can move this parameter and it will continue to be replaced with the proper value.

The wording of DBKit's confirmation dialogs is customizable too. The layout has properties such as DeleteConfirmMessage and UnsavedChangesTitle that control the text the user sees.