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 20 minutes to complete. There is a web tutorial as well.

Creating the project

  1. Download the DBKitResources file. This contains the database and icons you'll need to complete the tutorial.

  2. Uncompress the file which will create the DBKit Resources folder.

  3. Create a new desktop project.

  4. Save the project.

  5. From the DBKit Resources folder you downloaded, drag the Icons folder onto the Navigator in your project.

  6. Open the DBKit-Desktop example project (New Project > Examples > Databases > DBKit) 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.

Note

The Icons folder includes icons you can use for toolbar buttons that DBKit will then manage for you.

Next we need to copy the EddieElectronics.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 EddieElectronics.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 Destination to Resources Folder.

  6. If you're on a Mac, drag CopyDB so that it's immediately after the Build item as the app must be built before the database can be copied to it.

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. In the Navigator, click on the App object.

  2. In the Inspector, set the App.DefaultWindow to None.

  3. In the App object in the Navigator, create a property:

Item

Value

Name

db

Type

DBKit.TableConnection

Scope

Public

  1. In the App.Opening event, add the following code to connect to the SQLiteDatabase:

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

Note

In your own apps, you should consider the Resources folder to be read-only. If you are providing the user with a database, even an empty one, your code should copy the database file from Resources to another location outside the application itself before writing to it.

Adding the ability to search for customers

Now let's add the ability to search for customers and display the results in a Listbox.

  1. Expand DBKit in the Navigator then expand Classes and drag a TableConnection to the Window1.

  2. In the Inspector, set the Table property to customers.

  3. From the DBKit module in the Navigator, drag a SearchField to Window1. Position it at the top and make it fill the entire width.

  4. Rename the SearchField to SearchCustomers.

  5. Add a ListBox to Window1. Position it on the left side below the SearchField.

  6. To tell DBKit that this ListBox will show search results, you'll need to make it a DBKit.QueryRowsListBox control. Let's do it a different way this time. Click on the pencil icon next to the Super property in the Inspector.

  7. When the dialog appears, select DBKit.QueryRowsListBox then click the Select button.

  8. Position the ListBox on the left side of the layout and stretch the bottom so that it fills the available space.

  9. Set the Width property to 230.

  10. Use the Inspector to configure the ListBox as follows:

Item

Value

Name

SearchResults

ColumnCount

2

Initial Value

First Name TAB Last Name

Requires Selection

True

Columns

FirstName, LastName

Note

TAB means to press the Tab key on the keyboard rather than literally type <tab>.

The Columns property indicates the names of the database columns from your table that you want displayed in the SearchResults Listbox.

Tip

You might want to drag the bottom all the way to the bottom of the window and lock it so that it expands when the user expands the window.

  1. Run your project and try searching for names by typing a few characters.

Displaying the selected row

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 ListBox. By naming the controls to match the column names in the customers table of the database, DBKit will automatically populate them.

The finished layout should look something like the one:

../../_images/desktop_finished_layout.png

Of course you can of course arrange it any way you want.

  1. Drag a TextField to Window1.

  2. Set the following properties in the Inspector.

Item

Value

Name

FirstName

Hint

First name

  1. To tell DBKit that this TextField will display data from the selected row in the SearchResults ListBox, click on the pencil icon next to the Super property in the Inspector. In the dialog box that appears, select DBKit.TextField then click the Select button.

  2. Duplicate the FirstName control to create TextFields for LastName, Address, City, PostalCode and Email. Make sure to change the Name and Hint properties for each one.

Tip

You might want to set the control locking for these controls so they user can resize the window and have the controls properly resize with it.

  1. Drag out a PopupMenu to display the State/Region and set the following properties:

Item

Value

Name

StateOrRegion

Super

DBKit.PopupMenu

Selected Row Index

-1

  1. In the DBKit section of the Inspector, click the AutoPopulate checkbox.

  2. In that same section, set the Auto Populate Table property to States and the Auto Populate Column property to Abbreviation to populate the popup menu from the Abbreviation column of the States table in the database.

Tip

Feel free to add a label for the PopupMenu.

  1. Add a DateTimePicker control below the TextFields and set the following properties:

Item

Value

Name

LastContact

Super

DBKit.DateTimePicker

Display Mode

DateOnly

Important

Make sure you set the DisplayMode of the DateTimePicker to DateOnly. If you miss this step, every time you switch records, DBKit will think you've edited the row because it will be comparing a date to a value that includes both the date and the time.

  1. Add a CheckBox to the window and set the following properties:

Item

Value

Name

Taxable

Super

DBKit.CheckBox

Caption

Taxable

  1. Add an ImageViewer to the layout and set the following properties:.

Item

Value

Name

Photo

Super

DBKit.ImageViewer

  1. Now it's time to see your work (and DBKit) in action. Run your project. If you have any errors, you probably didn't name your controls properly so double check those. If all went well, try searching again and clicking on rows in the SearchResults ListBox. You should see the customer information appear in the various controls.

Adding more controls for other database columns in the future

Now that DBKit is set up and configured, if you needed to add more columns you'd only need to do the following 3 steps:

  1. Drag out a control.

  2. Name it the same as the database column.

  3. Set the Super property in the Inspector to make it a DBKit control.

Tip

If you prefer not to match the name of the control to the name of the column, you can instead indicate the column name using the Column property in the DBKit section of the Inspector.

Adding an Edit button

Now we need to add an Edit button so quit your app and head back to the Layout Editor.

  1. Make some room at the bottom (if necessary) for some buttons.

  2. Add a Button to the lower-right corner of Window1:

Item

Value

Name

EditButton

Super

DBKit.EditButton

Caption

Edit

Tip

Make sure to set the locking properties appropriately.

  1. Run your project. Notice that clicking Edit button makes the entry controls editable and changes the Edit button caption to Done. Make some changes and test out the Edit/Done button. Notice that if you are in the middle of editing and try to switch to another row, DBKit automatically confirms you wish to continue as you will lose your edits if you do.

Adding an Undo button

Now let's add an Undo button the user can use to undo any changes they make.

  1. Add a Button control to the left of the Edit button:

Item

Value

Name

UndoButton

Super

DBKit.UndoButton

Caption

Undo

Tip

Set the locking properties so it moves as the window is resized.

  1. Run your project. Try changing a customer record and then pressing the Undo button. Notice the button enables/disables automatically.

Adding a Delete button

Now let's add a Delete button so the user can delete rows.

  1. Add a button to the layout. Align it with the other buttons but position it over next to the SearchResults ListBox:

Item

Value

Name

DeleteButton

Super

DBKit.DeleteButton

Caption

Delete

  1. Run your project then test out the delete button. Notice that DBKit.TableConnection automatically confirms with the user that they wish to delete the row. Try using both the Cancel and Delete buttons in the confirmation dialog box.

Adding a New button

The user needs to be able to add new customers. Let's add a New button.

  1. Add a button to the layout. Position it between the Delete button and the Undo button. You may need to make your window bigger to make room for it:

Item

Value

Name

NewButton

Super

DBKit.NewButton

Caption

New

  1. Run the project and test out the New button.

Separating the search results and entry controls

In this tutorial you created a single window app. The SearchResults Listbox is on the same window as the various entry controls. DBKit also supports multi-window user interfaces where the SearchResults ListBox and entry controls are on separate windows. You can see this in the DBKit-Desktop example project. Run it and click the Separate button. What appears is a search window. After doing a search, double-click on a row to open the entry window.

If you examine the SearchWindow layout, you will see a few important changes from the window you created in this tutorial:

Since the SearchResults ListBox displays more columns, the SearchForCustomers method includes the additional columns.

Since the user double-clicks on a row in the SearchResults ListBox, instead of using the SelectionChanged event, the DoublePressed is used and the code is different:

If Me.SelectedRowIndex > -1 Then 'If there is a row selected
  Var w As New EntryWindow
  w.TableConnection1.BindListBoxControl(Me)
  w.Show
End If

This code:

  1. Makes sure a row is selected then creates a new window.

  2. Binds the DBKit.TableConnection on the new window to the listBox.

  3. Shows the new window.

You will also notice that the SearchWindow has a New button on it for creating new customers. It's Pressed event code looks like this:

Try
  Var w As New EntryWindow
  w.TableConnection1.CreateNewRow = True 'Set to true to create a new row
  w.Show
Catch error As DatabaseException
  System.Beep
  MessageBox("An error occurred while attempting create a customer.")
End Try

This code:

  1. Creates a new EntryWindow instance.

  2. Sets the CreateNewRow property to True so that the EntryWindow will know to set itself up for a new row.

  3. Shows the new EntryWindow.

The EntryWindow also has a DBKit.TableConnection class instance. Because it's only managing the entry controls, it's Opening event is just the code that was missing from the Opening event of the DBKit.TableConnection on the SearchWindow:

'Create a new row (if that property was set to true before the window opened) or load the selected one
If Me.CreateNewRow Then
  Me.NewRow
Else
  Me.LoadSelectedRow
End If
FirstName.SetFocus

The extra code at the end checks to see if CreateNewRow was set to True and if so, calls the NewRow method to create a new customer. If it's False, then the EntryWindow loads the selected row.

You will also notice that the EntryWindow uses a toolbar rather than individual buttons. This is just to show that DBKit can use Toolbar buttons as easily as regular buttons.

Next Steps

  1. Explore the Separate window and Rows Only examples in the DBKit-Desktop example project.

  2. Explore the DBKit-Web example project.

  3. Review the DBKit Introduction.

  4. Start using DBKit in your own projects.