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.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
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.
DBKit is not yet aware of _rowid in MySQL tables.
Beta 7 (February 27th, 2024)
Calling BindControls in a DBKit.Connector Opening event is no longer necessary. If you used previous betas, you'll want to update your Connector instance opening events to remove the now unnecessary call to BindControls.
Fixed the bug where a bound WebFileUploader button was always enabled even when a row was not being edited.
When using DBKit with PostgreSQL, tables in schemas other than public are now supported. To designate the schema, prefix the table name assigned to the DBKit.Connector.Table property with the schema name. For example, if the schema name is "utilities" and the table name is "resources", then the Table property of the instance of the DBKit.Connector control on the layout should be assigned "utilities.resources".
The DBKit.DeleteButton controls are now disabled when a new row is being added by clicking a DBKit.NewButton. (75002)
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