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. One line of code connects a control to a column in the database and another checks if the data in that control has changed.

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 ListBox that the user can then select from to edit either in controls on the same layout or a separate layout.

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 2023r2 or later.

DBKit version

DBKit is in beta. 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 two examples of DBKit in action. The Single button will display a listbox with customer records that when selected can be edited via various controls on the same layout. The Separate button displays the listbox by itself. Double-clicking on a row will then display a separate layout for editing.

Because DBKit is new, make sure you test it well. If you run into any bugs or have feature requests, report them at https://issues.xojo.com and make sure to put DBKit in the title.

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 configure DBKit.Connector class (which only requires a few method calls).
How to indicate which controls on your layout are to be used with DBKit.

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 20 minutes.

The rest of this page contains details you may want to know but certainly don't need to know.

The DBKit.Connector Class

This class is the heart of DBKit. To use it you will drag it to a layout then use its methods and properties.

Because of its design, you won't likely have to use many of DBKit.Connector's methods and properties as it will automatically interrogate your layout and connect the columns from the database table you specify to the your controls.

If you are connecting to your database in your App or Session Opening events and storing a reference to the connection in a property, DBKit.Connector instances on your layouts will automatically find that connection and use it. You can of course override this by assigning the Connection property yourself.

Note

Only the public properties, methods and events of DBKit.Connector are documented as they are the only ones you would directly use. The various private class members are not documented here though you can explore them by looking through the source code itself.

DBKit.Connector properties

Name	Description
Connection	The connection to the database.
CreateNewRow	Indicates that a new row should be created when the layout opens.
Table	The name of the database table being used. This can be set in the Inspector.

DBKit.Connector methods

Name	Description
BindControls	Finds all the DBKit controls on the layout then connects them to their designated columns in the database.
BindDeleteButton	Makes the passed control the button to be automatically managed for deleting rows.
BindEntryControl	Binds the column passed to the control passed. If the control is named identically to the column, only the control needs to be passed.
BindListBoxColumn	Binds the database column passed to the Listbox column specified.
BindListBoxControl	Makes the passed control the that will display rows that can be selected for editing.
BindEditButton	Makes the passed control the button to be automatically managed for editing/saving changes to rows and saving new rows as well.
BindNewButton	Makes the passed control the button to be automatically managed for creating new rows.
BindUndoButton	Makes the passed control the button to be automatically managed for reverting/undoing changes to rows.
ConfirmDeleteRow	Deletes the current row after confirming first with the user.
CheckForRowChange	Call in events that change control data to allow DBKitConnector to know if the row has changed or not.
Connected	Connects to the database passed returning True if the connection was successful and False if it was not.
EditRow	Allows the user to edit the record by enabling/making read-write the bound entry controls and changes the Edit button caption to Done.
LoadSelectedRow	Loads the selected row in the bound ListBox into the bound entry controls.
PrimaryKeyColumn	Returns the name of the database column that contains the table's primary keys.
NewRow	Sets all entry controls to their null values in preparation for creating a new row.
RemoveSelectedRow	After confirming with the user, the selected row is removed/deleted.
UndoRowChanges	Reloads the controls with the values from the row when it was loaded, undoing any changes the user has made.

DBKit.Connector events

Name	Description
Closing	The layout (window, webpage) is closing.
ControlsStateChanged	Indicates when DBKitConnector has enabled or disabled the entry controls.
LoadingRow	The row in the RowSet passed is being loaded. Return False to prevent it from loading.
Opening	The layout (window, webpage) is opening.
SavingRow	The row in the RowSet passed is being saved. Return False to prevent it from saving.

Notes

Desktop versus web projects

DBKit was created to be used nearly identically in desktop and web projects. The DBKit.Connector class is identical in for both desktop and web projects. You can copy it from one to the other. The included control subclasses, while nearly identical, are not cross-project compatible because their super classes are specific to the project type. Having said that, if you look at the way DBKit is used in the Desktop and Web example projects, you will see that the DBKit code is nearly identical. The only place where things are different is where they have to be, for example, on the web the WebFileUploader must be used to add photos.

Connecting to a database

To connect to a database, call DBKit.Connector's Connected function. For SQLite, pass it the database file. For MySQL and PostgreSQL, pass it a DBKit.Connector.DatabaseServer enumeration that indicates that the connection is MySQL or PostgreSQL followed by the IP address of the server, the port, the database name, the user and the password. If the connection succeeds, the Connected function returns True.

This code from the DBKit-Desktop example project connects to the SQLite database being passed to it:

app.db = New DBKit.Connector
If app.db.Connected(SpecialFolder.Resources.child("EddiesElectronics.sqlite")) Then
  Var w As New SingleWindow
Else
  System.Beep
  MessageBox("The database could not be reached due to an error.")
End If

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:

CheckBox
ComboBox
DateTimePicker/DatePicker
ImageViewer
Label
ListBox
PopupMenu
RadioGroup
Slider
TextArea
TextField

Note

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

DBKit.ComboBox

Property	Description
AutoPopulate	Populates the ComboBox with the unique values from the database column in its Column property.
AutoPopulateColumn	If AutoPopulate is True, this column will be used to populate the InitialValue property of the control. If this property is empty, the table in the Column property is used.
AutoPopulateTable	If AutoPopulate is True, this table will be used to populate the InitialValue property of the control. If this property is empty, the table in the Table property is used. Column, The database column from which to exchange data.
Table	The database table name from which to get the columns.
Column	The database column from which to get the value.

DBKit.SearchResultsListBox

Property	Description
Columns	The database columns (comma-separated) from which to exchange data. To skip a ListBox column, add the comma without entering a column name. For example, FirstName,,LastName.

Method	Description
AddRowsFromRowSet	Removes all rows from the ListBox and then creates rows from the RowSet passed.
BindDatabaseColumns	Used to connect database columns to columns of the ListBox if the Columns property is not populated.

DBKit.PopupMenu

Property	Description
AutoPopulate	Populates the PopupMenu with the unique values from the database column in its Column property.
AutoPopulateColumn	If AutoPopulate is True, this column will be used to populate the InitialValue property of the control. If this property is empty, the table in the Column property is used.
AutoPopulateTable	If AutoPopulate is True, this table will be used to populate the InitialValue property of the control. If this property is empty, the table in the Table property is used. Column, The database column from which to exchange data.
Table	The database table name from which to get the columns.
Column	The database column from which to get the value.

DBKit.RadioGroup

Property	Description
AutoPopulate	Populates the RadioGroup with the unique values from the database column in its Column property.
AutoPopulateColumn	If AutoPopulate is True, this column will be used to populate the InitialValue property of the control. If this property is empty, the table in the Column property is used.
AutoPopulateTable	If AutoPopulate is True, this table will be used to populate the InitialValue property of the control. If this property is empty, the table in the Table property is used. Column, The database column from which to exchange data.
Table	The database table name from which to get the columns.
Column	The database column from which to get the value.

DBKit.ImageUploader

Used in web projects to upload images to be displayed in a DBKit.ImageViewer.

Property	Description
TargetImageViewer	The DBKit.ImageViewer control an uploaded image should be sent to once the upload is complete.

Binding data entry controls to database columns

Binding is, for the most part, automatic. Calling the BindControls method will do the work for you. 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 ColumnName property in the Inspector. DBKit controls have a TableName property as well. This is only required in cases where you are using two DBKit.Connectors for two different tables on the same layout. In that case, all controls should designate both their ColumnName and their TableName.

However, if you find a situation in which you need to bind controls manually, you'll use the BindEntryControl method.

To bind a control to a database column, use DBKit.Connector's BindEntryControl method. If you name your controls to match the names of the columns, you only need pass the control itself as the first parameter. If the control names do not match the column names, pass the column name as the second parameter: For example:

Me.BindEntryControl(LastName)

or

Me.BindEntryControl(LastName, "SurName")

Loading the selected row into the bound entry controls

When the user clicks on a row in your bound Listbox, DBKit.Connector will first determine if the row already displayed is in the middle of being edited. If it is, DBKit.Connector 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.

While this is done automatically, if you need to load the row manually, in the ListBox.SelectionChanged event, call DBKit.Connector's LoadSelectedRow method:

Connector1.LoadSelectedRow

Intercepting the loading and saving of rows

DBKit.Connector has a LoadRowing event that is called before the row is loaded into bound entry controls. This is a good place to put code that needs to modify or view the row before it's loaded. You can also stop the process of loading the row by returning False from this event. DBKit.Connector also has a SavingRow event that is called just before the row is saved to the database. This event gives you an opportunity to change values and to prevent the row from being saved by return False.

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 designate a button as being a Delete, New, Undo or Edit button:

Drag a button to the layout.
In the Inspector, click on the Pencil icon next to the Super property then select the appropriate button type:

Name	Description
DBKit.DeleteButton	Deletes the selected row. It will ask the user to confirm before deleting.
DBKit.EditButton	Makes all DBKit data entry controls on the layout ready for editing by the user.
DBKit.NewButton	Resets all DBKit data entry controls on the layout to their default state so they are ready for the user to make a new database entry.
DBKit.UndoButton	Reloads the entire row as it was originally into the data entry controls, undoing all changes the user has made.

While DBKit can bind these buttons automatically for you using the steps above, if you need to do it in code, you can do so. In all cases, pass the control as the only parameter:

'Indicate the buttons that handle Delete, New, Undo and Edit
Me.BindDeleteButton(DeleteButton)
Me.BindNewButton(NewButton)
Me.BindUndoButton(UndoButton)
Me.BindEditButton(EditButton)

Handling the Edit button

If you set a button's Super property to the DBKit.EditButton class, the edit button function will be handled automatically. If you need to handle it manually, call the DBKit.Connector's EditRow method:

Try
  Connector1.EditRow
Catch error As DatabaseException
  System.Beep
  MessageBox("An error occurred while attempting to save the record.")
End Try

Handling the Undo button

If you set a button's Super property to the DBKit.UndoButton class, the undo button function will be handled automatically. If you need to handle it manually, call the DBKit.Connector's UndoRowChanges method:

Try
  Connector1.UndoRowChanges
Catch error As DatabaseException
  System.Beep
  MessageBox("An error occurred while attempting undo the changes you made.")
End Try

Handling the New button

If you set a button's Super property to the DBKit.NewButton class, the new button function will be handled automatically. If you need to handle it manually, call the DBKit.Connector's NewRow method:

Try
  Connector1.NewRow
  FirstName.SetFocus
Catch error As DatabaseException
  System.Beep
  MessageBox("An error occurred while attempting create the new record.")
End Try

Handling the Delete button

If you set a button's Super property to the DBKit.DeleteButton class, the delete button function will be handled automatically. If you need to handle it manually, call the DBKit.Connector's ConfirmDeleteRow method:

Try
  Connector1.ConfirmDeleteRow
Catch error As DatabaseException
  System.Beep
  MessageBox("An error occurred while attempting delete the record.")
End Try

Using an ImageViewer control for adding/changing images

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

The example projects demonstrate these.

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 all of the localizable strings in DBKit.Connector > Localizable Strings.

Save Changes confirmation dialog box

The localizable parts of the confirmation dialog that appears when the user attempts to exit a row they have edited before saving changes are:

Name	Description
ConfirmCancel	The Cancel button caption.
ConfirmContinue	The Continue button caption.
ConfirmExplanation	The explanatory message.
ConfirmMessage	The primary message.
ConfirmTitle	The dialog title.

Delete confirmation dialog box

The localizable parts of the confirmation dialog that appears when the user attempts to delete the current row are:

Name	Description
DeleteConfirmCancel	The Cancel button caption.
DeleteConfirmContinue	The Continue button caption.
DeleteConfirmExplanation	The explanatory message.
DeleteConfirmMessage	The primary message.
DeleteConfirmTitle	The dialog title.

Edit button caption

The Edit button's Edit and Done captions can be localized:

Name	Description
EditButtonDoneCaption	The caption that appears when the user clicks the Edit button. The default is Done.
EditButtonEditCaption	The initial caption that appears when the layout is not in editing mode. The default is Edit.

Bug reports and feature requests

If you find a bug in DBKit or have a feature request, use our Issues reporting system to give us the details. Make sure you prefix the title of your issue with DBKit.

Supporting more controls and platforms

To support controls that are not currently supported, the following DBKit.Connector methods must be updated:

CurrentRowChanged
EditRow
SaveRow
SetControlValue

Control subclasses must implement the DBKit.Control class interface and include TableName As String, ColumnName As String and Connector as DBKit.Connector properties. Examine the existing control subclasses in DBKit for details.

To support entirely new platforms, the following DBKit.Connector methods must be updated:

Constructor
DesktopControlForColumn/WebControlForColumn
EditRow
LoadSelectedRow
NewRow
NoRowSelected
SaveRow
SetButtonEnabledState

DBKit Release notes

Known issues

Web: WebFileUploader is not enabling when the row is in the editing state.
Desktop: In the example after clicking the Edit button, tabbing from the SearchCustomers control to FirstName does not work. You can click on FirstName and then tab through the controls but once you table out of the DBKit.Desktop.DBTextFields, you can't tab back into them again. Tabbing between SearchCustomers and the Taxable CheckBox however, does work.
A regular ListBox control separate from the SearchResults listbox is needed.

Beta 6 (August 24th, 2023)

IMPORTANT: It turns out there are too many issues with trying to have a single DBKit module that includes all the DBKit controls for all project types. As a result, the version of DBKit in the Desktop example is only for desktop projects. The version in the web example is only for web projects. The core code in DBKit itself is still the same in both projects. This also means that you will have to reset the super property of any DBKit controls on your layouts. This is a one-time only change.
IMPORTANT: DBKitConnector is now Public (instead of Global) and is now simply Connector. This means that when you replace a previous version of DBKit with this one, you will need to reset the Super of any DBKitConnector class instances to DBKit.Connector. This is a one time-only change.
IMPORTANT: DBKit control subclasses are no longer in their own project type-specific modules (Desktop or Web). As a result, they are no longer namespaced as Desktop or Web. Thus you will need to reset the super on any DBKit controls you're using on your layouts. This is a one time-only change.
Versions for the desktop and web are now separate because the Xojo IDE isn't really designed to have classes from different platforms in the same project file. Make sure to copy from the appropriate example project.
Fixed a few controls whose DBKit properties didn't appear correctly in the Inspector.
Edit/Done button now enables when making a new row for a table with no rows.
If you attempt to use a DBKit.Connector class without first assigning a table name to the Table property, an exception is now raised.

Beta 5 (August 15th, 2023)

When editing a row or adding a new row, the focus is now automatically set to the first TextField or TextArea in the tab order.
Fixed a bug that caused the Edit button to be disabled after pressing the Undo button.
DBKitSearchResultsListBox columns can now be bound via the Columns property in the DBKit section of the Inspector instead of having to do it in code with the BindDatabaseColumns method though that too still works.
It is no longer necessary to set up the connection for a DBKitConnector on a layout if one has already been established and stored in an App class (desktop) or the Session (web) classes. DBKitConnector looks for the connection in those objects and will use it if it finds it. You can still create the connection in code if needed.

Beta 4 (July 20th, 2023)

TableConnector has been renamed DBKitConnector.
All new control subclasses that nearly eliminate all the code needed to set up database controls. It's highly recommended that you go through the tutorial again to get a good idea of how different this version is. The tutorial now only takes about 20 minutes compared to 30 with the previous beta as there's a LOT less code to enter.
DBComboBox, DBPopupMenu and DBRadioGroup now all have an AutoPopulate As Boolean property that appears in the DBKit section of the Inspector. Setting this to true will auto populate the control with the unique values from the column. If you prefer the values to come from another column or another table and column, provide those in the AutoPopulateTable and AutoPopulateColumn properties.
BindListBoxColumns now allows you to bind two columns (example: FirstName and LastName) to the same single column of a listbox. To do so, pass two columns names separated by a comma "firstname,lastname" as a column parameter.

Beta 3 (June 26th, 2023)

Removed the transaction used while testing in the IDE since, at least for SQLite, we are copying the database each time your run from the IDE.

Beta 2 (June 26th, 2023)

Fixed a bug with the locking of some of the TextFields in the example projects.
Fixed a bug that resulted in an exception when a row had been modified and saved more than once.
Replaced icons in example projects with nicer ones from Jérémie Leroy (thanks Jérémie!).
Added TableConnector.ControlsStateChanged event that fires when the entry controls are enabled or disabled by TableController allowing the user to enable/disable other controls that are not bound to the database table.
Fixed a bug that caused TableConnector running in a web project to think stored images had changed when they had not changed.
Changed the behavior of returning False from the SavingRow event. It no longer reloads the original row.
Fixed a bug that prevented the saved record from being unlocked.
Changed the behavior of editing rows. Entry controls are now disabled or read-only (in the case of TextFields and TextAreas) by default. The user clicks the Edit button to begin editing. The Edit button's caption then changes to Done. This enables the controls or in the case of TextFields and TextAreas makes them read-write. When the user is done editing, they click the Done button. This avoids accidental edits and is in preparation for a future version of DBKit that will provide the option to allow editing of any specific record by one user at a time. In the example projects, the button is now named EditButton and has Edit as its default caption. The two possible captions (Edit and Done) are localizable strings.
Because of the change to how rows are edited, use EditRow now instead of SaveRow (which is now private).
The BindSaveButton method is now BindEditButton. When you update your projects to use DBKit 1b2, you'll get some compiler errors as a result in your TableConnector opening events. Changing BindSaveButton to BindEditButton will fix that.
To improve clarity, the TableConnector.BindListControl method has been renamed BindListBoxControl. When you update to this version, you'll need to update any places where you call BindListControl. Thankfully, the compiler will point these out to you when you run your project.

Beta 1 (June 21st, 2023)

Initial pre-release