Quickly building a database client app for the desktop with DBKit

In this tutorial, you will recreate the single window example in the DBKit-Desktop example project. The tutorial takes about 30 minutes to complete. There is a web tutorial as well.

Note

If you haven't completed the Xojo Tutorial, it is suggested you do that first.

The end result will look like this:

../../_images/desktop_finished_layout_v2.png

Creating the project

  1. Download the DBKitDatabase file. This contains the database you will need to complete the tutorial.

  2. Uncompress the file which will create the EddiesElectronics.sqlite file.

  3. Create a new desktop project.

  4. Save the project.

  5. Open the DBKit-Desktop example project (choose File > New Project, then click Examples > Databases > DBKit-Desktop) then copy the DBKit module from the example into your project.

Important

Always copy DBKit from the DBKit-Desktop example project when using it with desktop projects as that will always be the latest version.

Next we need to copy the EddiesElectronics.sqlite database file into your app when it is built.

  1. In the Build Settings section of the Navigator, select the OS your are going to build for (Linux, macOS or Windows).

  2. Add a Copy Files Build Step (Insert > Build Step > Copy Files).

  3. In the Inspector, change the name to CopyDB.

  4. From the DBKit Resources folder you downloaded, drag the EddiesElectronics.sqlite database file into the area in the middle of the IDE below where it says "Drag files into the list below for this step".

  5. In the Inspector, change the App Parent Folder to Resources Folder.

Because the database is being copied each time you run your project, you will be able to edit it all you'd like without worrying that your changes are permanent. So add, edit and delete to your heart's content.

Connecting to the database

Now let's get connected to the database.

  1. From the menubar, choose Insert > DatabaseConnection > SQLiteDatabase.

  2. In the Inspector, rename the DatabaseConnection to Eddies.

  3. In the Navigator, expand the Eddies DatabaseConnection.

  4. Select the Development database target.

  5. In the Inspector, click the pencil icon next to the File property.

  6. In the open file dialog box that appears, find the DBKit Resources folder and choose the EddiesElectronics.sqlite database inside it.

You now have a DatabaseConnection to the EddiesElectronics SQLite database.

Tip

This DatabaseConnection could connect to a MySQL, ODBC or PostgreSQL database as well. The mechanism for connecting to any database is essentially the same. In a single project, you can use DBKit to connect to any number of databases of any of the supported types.

Turning the window into a DBKit window

The next step is to turn Window1 into a DBKit-enabled Window.

  1. Click on Window1 to select it.

  2. In the Inspector, set the Name property to Customers

  3. In the Inspector, set the Title property to Customers.

  4. In the Inspector, click the pencil icon next to the Super property.

../../_images/set_super_property_dialog_box_desktop.png

  1. In the Select Super dialog box that appears, choose DBKit.Window then click the Select button.

The Customers window is now a DBKit.Window instead of just a regular DesktopWindow. This adds a lot of database functionality.

  1. Scroll the Inspector down to the bottom. Notice that there's now a DBKit section.

  2. In the DBKit section, set the Database property to Eddies.

  3. Set the Table Name to customers.

The Customers window can now connect to the Eddies database and access the Customers table.

Adding the ability to search for customers

Now let's add the ability to search for customers.

  1. First, let's make the window a little bigger. In the Inspector, set the Width property to 700 and the Height property to 450.

  2. Set the Minimum Width to 700 and the Minimum Height to 450.

  3. Click the Library icon in the toolbar to display the Library.

  4. Drag a SearchField to the Customers window.

  5. Position it in the upper-left corner.

  6. Resize it so that it's as wide as the window.

  7. Next let's configure this control. Click the Inspector button in the toolbar to show the Inspector.

  8. In Inspector, lock the right side of the control.

  9. Now we need to make this a DBKit.SearchField. Click the pencil icon next to the Super property.

  10. Select DBKit.SearchField then click the Select button. Notice this control now has a DBKit section in the Inspector.

Displaying the search results

Now you can search customers but you will need a place to display the results so let's add a Table.

  1. Switch back to the Library and drag a Table from the Library to the window.

  2. Position it on the left-side of the window below the SearchField.

  3. Resize it so that the bottom extends to the bottom guideline of the window.

  4. In the Inspector, set the name of the Table to SearchResults.

  5. Lock the bottom of the control.

  6. Now we need to make this a DBKit.Table. Click the pencil icon next to the Super property.

  7. Select DBKit.Table then click the Select button. Notice that this control has a DBKit section in the Inspector.

  8. The Table is a bit wide. Let's make it more narrow. In the Inspector, set the Width property to 190.

  9. In the Inspector, set the Column Count property to 2.

  10. Click the Pencil icon that appears when you hover over the SearchResults Table in the Window Editor.

  11. Click the Add button then set the two column headers to First and Last.

  12. In the Inspector, set the Column Names property to firstName,lastName.

  13. This would be a good time to save your project so do that now.

  14. You can now search and display the results. Let's give it a try. Run your project.

  15. If all is well, the Table will display all of the customers by default. If either column is blank, you may have mistyped the Column Names so go back to the Inspector and check that the Column Names property is set to firstname,lastname.

  16. In the SearchField, try typing first few characters of any first or last name you see. The SearchField automatically searches as you type, looking in all the columns of the DBKit.Table (if there is one in the window) for values that begin with the value you have typed.

Displaying the selected row

You are going to need room for both controls to display customer information and to display their invoices so let's add a TabPanel:

  1. Drag a TabPanel to the Customers window.

  2. Position it below the SearchField and to the left of the Table.

  3. Resize the TabPanel so that the right side is aligned with the right margin of the window.

  4. Resize the bottom of the TabPanel to match the bottom of the Table.

  5. In the Inspector, turn on LockRight and LockBottom.

Next we will add some controls that can display other database columns (address, city, etc.) when the user clicks on a customer in the SearchResults Table. By naming the controls to match the column names in the customers table of the database, DBKit will automatically populate them.

Let's start by showing the customer's photo:

  1. Drag an ImageViewer to the TabPanel in the Customers window.

  2. Position it in the upper-left corner of the TabPanel.

  3. Set the following properties in the Inspector.

Item

Value

Name

Photo

Super

DBKit.ImageViewer

Width

110

Height

110

  1. Run your project and click on some customers to see their photos.

Note

If you see the Debugger instead of your project, that means an Exception occurred. Chances are, the name of the control isn't matching the name of the column from the database. Make sure it's Photo. The Debugger is only displayed because you are running your project from the IDE. In a built app an error message would be displayed.

Now let's add controls to display the first name and last name.

  1. Drag a TextField to the TabPanel.

  2. Position it to the right of the Photo ImageViewer.

  3. Resize it so that the right side is at the right margin guide of the TabPanel.

  4. Set the following properties in the Inspector.

Item

Value

Name

FirstName

Super

DBKit.TextField

Lock

Right

Hint

First Name

Text Case

Titlecase

Note

At any time if you want to try out your project after adding a control, go ahead and run it. Remember if you get an error, check the control names as they need to match the column names from the database.

Now let's add a LastName field.

  1. To save time, duplicate the FirstName field you already added.

  2. Position the duplicate underneath the FirstName field.

  3. Set the Name property to LastName.

  4. Set the Hint property to Last Name.

Note

You may have noticed that you can select the contents of the fields but not edit them. We will get to that in a bit.

Now let's add a control to display the customer's taxable status.

  1. Drag a CheckBox to the TabPanel.

  2. Position it just below and aligned to the left side of the LastName TextField.

  3. Set the following properties in the Inspector.

Item

Value

Name

Taxable

Super

DBKit.CheckBox

Caption

Taxable

Height

22

Now let's add a control to display the customer's discount percentage.

  1. Drag a TextField to the TabPanel.

  2. Position to the right of the Taxable CheckBox with the right side aligned with the LastName TextField above it.

  3. Set the Name property to Discount.

  4. Set the Hint property to Discount.

This field needs to only allow numbers to be entered so it will be a DBKit.NumberField. To make it a DBKit.NumberField:

  1. Set the Super property as you have before but this time, click on DBKit.TextField in the Set Super dialog box, then click on then DBKit.NumberField.

  2. Click the OK button to set it.

  3. In the DBKit section, set the Type property to Percentage.

  4. Unlock the left side which will automatically lock the right side.

  5. Now it needs a label so drag a Label to the TabPanel and position it to the left of the Discount field.

  6. Set the Text property to Discount.

  7. Set the Height property to 22.

  8. Set the Text Alignment property to Right.

  9. Unlock the left side of the Label.

Now let's add a DesktopSeparator control to separate these controls from the address controls you are about to add.

  1. Drag a DesktopSeparator control to the TabPanel.

  2. Position it below the other controls.

  3. Resize it so the right side aligns with the right side of the Discount NumberField.

  4. Lock the right side.

Now let's add TextField to display the customer's address.

  1. Duplicate the LastName TextField and position it below the Line control, aligned left with the Photo field.

  2. Resize it so that its right side is at the right margin guide of the window.

  3. Set the following properties in the Inspector.

Item

Value

Name

Address

Hint

Address

Now let's add a TextField to display the customer's city.

  1. Duplicate the Address TextField and position it below the Address TextField and aligned to its left side.

  2. Set the following properties in the Inspector.

Item

Value

Name

City

Width

200

LockRight

Off

Hint

City

Now let's add a PopupMenu to display the customer's state or region.

  1. Drag a PopupMenu to the TabPanel and position it to the right of the City TextField.

  2. Set the following properties in the Inspector.

Item

Value

Name

StateOrRegion

Super

DBKit.PopupMenu

Height

22

Tip

After setting the Height to 22 you will find that it's easier to position the PopupMenu.

Rather than type in all the US state abbreviations, we are going to automatically populate this PopupMenu with rows from a table in the database.

  1. To automatically populate the the PopupMenu, set the AutoPopulate Table Name property to States.

  2. Set the AutoPopulate Column Name to Abbreviation.

  3. Run your project at this point.

You should see the PopupMenu defaulting to the state abbreviation for the customer's state.

Note

Remember if you get an error, check the AutoPopulate Table Name and AutoPopulate Column Name values as they need to match the column names from the database.

Now let's add a TextField to display the customer's postal code.

  1. Duplicate the City TextField and position it to the right of the StateOrRegion PopupMenu.

  2. Resize the right size to align it with the right side of the Address TextField.

  3. Set the following properties in the Inspector.

Item

Value

Name

PostalCode

Hint

PostalCode

LockLeft

Off

Now let's add a Textfield to display the customer's email address.

  1. Duplicate the Address TextField.

  2. Place the duplicate below the City TextField, leaving it the same width.

  3. Change the Name property to Email.

  4. Change the Hint property to Email Address.

Now let's add another PopupMenu this time to display the customer's sales rep.

  1. Drag a label to the TabPanel placing it below the Email TextField and left-aligned with it.

  2. Set the following properties in the Inspector.

Item

Value

Height

22

Text

Sales Rep

  1. Reduce the Width so that it's only as wide as necessary to display the text.

  2. Drag a PopupMenu to the TabPanel, placing it to the right of the Label you just added.

  3. Set the following properties in the Inspector.

Item

Value

Name

SalesRep

Super

DBKit.PopupMenu

Height

22

AutoPopulate Table Name

SalesReps

AutoPopulate Column Name

Name

The customer row does not store the sale's reps name. It stores the primary key for the assigned sales rep row in the SalesReps table. Fortunately, DBKit.PopupMenu is designed to handle this.

  1. Set the Auto Populate Storage Type to PrimaryKey.

Now let's add a DateTimePicker control to display the LastContact column. DBKit provides a custom DateTimePicker control that provides the same user experience on all desktop platforms.

  1. Drag a DesktopCanvas to the TabPanel.

  2. Set the following properties in the Inspector.

Item

Value

Name

LastContact

Super

DBKit.DateTimePicker

Height

22

  1. Drag the LastContact control so that it's right-aligned with the Email control and horizontally aligned with the SalesRep PopupMenu.

  2. Drag a DesktopLabel control to the TabPanel. This will be the label for the LastContact DateTimePicker control.

  3. Set the following properties in the Inspector.

Item

Value

Height

22

LockLeft

Off

Text

Last Contact

Text Alignment

Right

  1. Position it to the left of the LastContact DateTimePicker.

  2. Run the project.

Now you have all the customer information controls ready. The TabPanel is going to show those and a list of the customer's invoices. So let's update the tabs accordingly.

  1. Click the TabPanel to select it.

  2. In the Inspector, click the Edit button for the Panels property.

  3. Change Tab0 to Info.

  4. Change Tab1 to Invoices.

  5. Click the OK button to close the dialog box.

Next we want the user to be able to see a list of the customer's invoices. To make it possible for DBKit to automatically load the invoices for the displayed customer, it needs to know the relationship between the Customers table and the Invoices table.

  1. Click on the Customers window to select it.

  2. In the Inspector, scroll down until you see the DBKit section.

  3. Click on the Pencil icon next to the TableRelationships property.

  4. In the dialog box that appears, type invoices.customerid=customers.id.

  5. Click the OK button to close the dialog box.

This tells DBKit how invoices are related to customers: by the customerid column. This allows DBKit to automatically load the related invoice rows.

Now let's add a Table so the user can see the invoices for this customer.

  1. Click the Invoices tab on the TabPanel.

  2. Drag a Table from the Library to the TabPanel.

  3. Position it in the upper-left corner of the TabPanel.

  4. Resize it to fill the TabPanel.

  5. Set the following properties in the Inspector.

Item

Value

Super

DBKit.Table

LockRight

On

LockBottom

On

ColumnCount

3

Table Name

invoices

Column Names

invoiceno,invoicedate,invoiceamount

Column Formats

String,DateTime.FormatStyles.Short,"\$###,##0.00"

As you have likely guessed, the Column Names property tells DBKit.Table the columns you wish to display and order you wish them displayed in.

The Column Formats property tells DBKit.Table the format of each column. Don't forget the double quotes around the InvoiceAmount format.

Now let's add the Table column headers.

  1. Click the Pencil icon that appears on the Table when you hover over it in the Window Editor.

  2. Click the Add button to add the header row.

  3. Set the headings of the columns to Number, Date, and Amount.

  4. Run the project. Click on different customers to see that their invoices are automatically displayed.

Up to this point the custom records have been in read-only mode. The last step is to make it possible to edit the selected customer.

To do that, we need to add a DBKit.EditingButton.

  1. We need to make sure room for this control so drag the bottom of the TabPanel up to leave just enough room for a border below the Sales Rep PopupMenu and the Last Contact DateTimePicker.

  2. From the Library drag out a SegmentedButton control.

  3. Position in on the Customers window, below the TabPanel and left-aligned with it.

  4. Stretch the SegmentedButton so that it's as wide as the TabPanel.

  5. Set the following properties in the Inspector.

Item

Value

Super

DBKit.EditingButton

LockTop

Off

LockRight

On

Delete Row

On

New Row

On

Undo Row Changes

On

Edit Row

On

  1. Click the Edit button next to the Segments property.

  2. Change the first segment (One) to Delete.

  3. Change the second segment (Two) to New.

  4. Add a third segment and set it's caption to Undo.

  5. Add a fourth segment and set it's caption to Edit.

  6. Click the OK button to close the Segment Editor.

  7. Run your project. Try editing customers, deleting customers, and making new customers.

As you can see, DBKit provides a lot of functionality without requiring that you write any code at all. That's its job: getting data from your database into the user interface.

Next Steps

  1. Explore the Invoices, Products and Maintenance examples in the DBKit-Desktop example project. They show off how to create an interface where the list of rows is in a separate window from the details of a row, how to create an interface that doesn't display rows in a Table and how to use DBKit.Container to display DBKit controls inside a window that is not a DBKit.Window.

  2. Explore the DBKit-Web example project.

  3. Review the DBKit Overview.

  4. Review the documentation for the various DBKit classes.

  5. Start using DBKit in your own projects.

  6. If you have an existing project that uses DBKit v1, check out the DBKit v2 Conversion Guide.