Quickly building a database client app for the web with DBKit

In this tutorial, you will recreate the single page example in the DBKit-Web example project. The tutorial takes about 20 minutes to complete. There is a desktop 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 web project.

  4. Save the project.

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

  6. Open the DBKit-Web 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-Web example project when using it with web 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. Now drag CopyDB so that it's immediately after the Build item as the app has to 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 Session object in the Navigator, create a property:

Item

Value

Name

db

Type

DBKit.TableConnection

Scope

Public

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

db = New DBKit.TableConnection
If Not db.Connected(SpecialFolder.Resources.child("EddiesElectronics.sqlite")) Then
  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 WebPage1.

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

  3. Add a SearchField to WebPage1. Position it at the top and make it fill the entire width.

  4. Change the name of the SearchField to SearchCustomers.

  5. Add a ListBox to WebPage1. Position it on the left side below the SearchField. You may need to make WebPage1 larger in the Layout Editor.

Item

Value

Name

SearchResults

ColumnCount

2

Initial Value

First Name TAB Last Name

Note

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

Tip

You might want to drag the bottom all the way to the bottom of the webpage and lock it so that it expands to fill the height of the browser window.

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

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

  3. In the Inspector, set the value of the Columns property for the ListBox to FirstName, LastName. The Columns property indicates the names of the database columns from your table that you want displayed in the SearchResults Listbox.

  4. Add a method to WebPage1 titled SearchForCustomers. Add the following code to it:

SearchResults.RemoveAllRows
Try
  Var rs As RowSet
  If SearchCustomers.Text = "" Then
    rs = TableConnection1.Connection.SelectSQL("SELECT id, firstname, lastname FROM customers")
  Else
    rs = TableConnection1.Connection.SelectSQL("SELECT id, firstname, lastname, city, stateorregion FROM customers WHERE LOWER(firstname) LIKE LOWER(?) OR LOWER(lastname) LIKE LOWER(?)", SearchCustomers.Text + "%", SearchCustomers.Text + "%")
  End If
  SearchResults.QueryRows = rs
Catch error As DatabaseException
  MessageBox("Something went wrong with the search.")
End Try

  1. Call this method from the SearchCustomers.TextChanged event.

  2. Run your project and try searching for names by typing a few characters. If you're not finding any, trying search for "ne".

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.

  1. Drag a TextField to WebPage1.

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 the user can resize the browser window and have the controls properly resize with it.

  1. Drag out a PopupMenu to display the State/Region.

Item

Value

Name

StateOrRegion

Selected Row Index

-1

Super

DBKit.PopupMenu

  1. In the Inspector, click the AutoPopulate checkbox to fill in the popup menu with all the unique values from the StateOrRegion database column.

Tip

Feel free to add a label for the PopupMenu.

  1. Add a DatePicker control below the TextFields.

Item

Value

Name

LastContact

Super

DBKit.DatePicker

  1. Add a CheckBox to the webpage.

Item

Value

Name

Taxable

Caption

Taxable

Super

DBKit.CheckBox

  1. Add an ImageViewer to the layout.

Item

Value

Name

Photo

Super

DBKit.ImageViewer

  1. To allow the user to upload images on the ImageViewer, add a ImageUploader to the page.

Item

Value

Name

ImageUploader1

Super

DBKit.ImageUploader

  1. In its Opening event, add the following code:

TargetImageViewer = Photo

  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.

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 WebPage1:

Item

Value

Name

EditButton

Super

DBKit.EditButton

Caption

Edit

Indicator

Primary

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

Indicator

Warning

Tip

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

  1. Run your project. Try changing and customer 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

Undo

Indicator

Danger

  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 layout bigger to make room for it:

Item

Value

Name

NewButton

Super

DBKit.NewButton

Caption

New

Indicator

Success

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

Show all customers by default

It might be handy to have all customers displayed by default. This is actually quite easy.

  1. Go to the TableConnection1.Opening event and add the following line of code:

SearchForCustomers

  1. Run your project and verify that you now see all customers in the SearchResults Listbox.

Separating the search results and entry controls

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

If you examine the SearchPage layout, you will see a few important changes from the page 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 event is used and the code is different:

'Make a new entry page
Var p As New EntryPage
'Bind this listbox to its DBKit TableConnection so it knows where to get the rows from
p.TableConnection1.BindListBoxControl(Me)
'Show the page
p.Show

This code:

  1. Creates a new page.

  2. Binds the DBKit.TableConnection on the new page to the ListBox.

  3. Shows the new page.

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

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

This code:

  1. Creates a new EntryPage instance.

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

  3. Shows the new EntryPage.

The EntryPage 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 SearchPage:

'Indicate which buttons handle Delete, Undo and Edit
Me.BindDeleteButton(DeleteToolbarButton)
Me.BindUndoButton(UndoToolbarButton)
Me.BindEditButton(EditToolbarButton)

'Create a new row (if this property was set before the web page opened) or load the selected row
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 EntryPage loads the selected row.

You will also notice that the EntryPage uses a toolbar rather than individual buttons. This is just to show that DBKit can use Toolbar buttons as easily as regular buttons. If you want to use a toolbar in your web app, check out the toolbar on this page to see how to set it up and what methods to call to perform the delete, undo and save actions.

Next Steps

  1. Explore the Separate page example in the DBKit-Web example project.

  2. Review the DBKit Details.

  3. Start using DBKit in your own projects.