Tutorial: Database CRUD

This tutorial walks you through building a complete database application with Create, Read, Update, and Delete operations using SQLite. You will connect to a database, display records in a TBrowse grid, and navigate them with TDBNavigator.

Step 1: Create the Project

1. Create a new project called ContactsDB via File → New Project.
2. Open main.prg in the Code Editor.
3. We will build a contacts manager with name, email, and phone fields.

Step 2: Connect to SQLite

Drop a TSQLite component from the Data Access palette tab onto the form, or create it in code. HarbourBuilder's SQLite component handles the connection and query execution.

#include "hbbuilder.ch"

function Main()

   local oDB, oForm

   // Open (or create) the SQLite database file
   DEFINE SQLITE oDB FILE "contacts.db"

   if .not. oDB:lConnected
      MsgAlert( "Failed to open database: " + oDB:cError )
      return nil
   endif

   CreateTable( oDB )
   BuildUI( oDB )

return nil

Step 3: Create the Table

Use Execute() to run a CREATE TABLE IF NOT EXISTS statement. This is safe to call every time the application starts.

static function CreateTable( oDB )

   oDB:Execute( "CREATE TABLE IF NOT EXISTS contacts (" + ;
      "id INTEGER PRIMARY KEY AUTOINCREMENT," + ;
      "name TEXT NOT NULL," + ;
      "email TEXT," + ;
      "phone TEXT" + ;
      ")" )

return nil

Step 4: Build the User Interface

The UI consists of a TBrowse grid to display records, input fields for editing, a TDBNavigator for record navigation, and CRUD buttons.

static function BuildUI( oDB )

   local oForm, oBrw, oNav
   local oGetName, oGetEmail, oGetPhone
   local oBtnAdd, oBtnUpdate, oBtnDelete
   local cName := "", cEmail := "", cPhone := ""

   DEFINE FORM oForm TITLE "Contacts Manager" ;
      SIZE 800, 600 FONT "Segoe UI", 10

   // --- Data grid ---
   @ 10, 10 BROWSE oBrw ;
      OF oForm SIZE 760, 300 ;
      HEADERS { "ID", "Name", "Email", "Phone" } ;
      WIDTHS  { 50, 200, 250, 150 }

   // --- Navigator bar ---
   @ 320, 10 DBNAVIGATOR oNav ;
      OF oForm SIZE 300, 32 ;
      BROWSE oBrw

   // --- Input fields ---
   @ 370, 10 LABEL oLbl1 VALUE "Name:"   OF oForm SIZE 60, 24
   @ 370, 80 GET oGetName VAR cName     OF oForm SIZE 250, 24

   @ 400, 10 LABEL oLbl2 VALUE "Email:"  OF oForm SIZE 60, 24
   @ 400, 80 GET oGetEmail VAR cEmail   OF oForm SIZE 250, 24

   @ 430, 10 LABEL oLbl3 VALUE "Phone:"  OF oForm SIZE 60, 24
   @ 430, 80 GET oGetPhone VAR cPhone   OF oForm SIZE 250, 24

   // --- CRUD buttons ---
   @ 480, 80 BUTTON oBtnAdd PROMPT "Add" ;
      OF oForm SIZE 90, 32 ;
      ACTION DoInsert( oDB, oBrw, oGetName, oGetEmail, oGetPhone )

   @ 480, 180 BUTTON oBtnUpdate PROMPT "Update" ;
      OF oForm SIZE 90, 32 ;
      ACTION DoUpdate( oDB, oBrw, oGetName, oGetEmail, oGetPhone )

   @ 480, 280 BUTTON oBtnDelete PROMPT "Delete" ;
      OF oForm SIZE 90, 32 ;
      ACTION DoDelete( oDB, oBrw )

   // Load initial data
   RefreshBrowse( oDB, oBrw )

   // When user clicks a row, populate the input fields
   oBrw:OnClick := { || OnRowSelect( oBrw, oGetName, oGetEmail, oGetPhone ) }

   ACTIVATE FORM oForm CENTERED

return nil

Step 5: Implement CRUD Operations

INSERT — Adding a New Record

static function DoInsert( oDB, oBrw, oGetName, oGetEmail, oGetPhone )

   local cName  := oGetName:GetValue()
   local cEmail := oGetEmail:GetValue()
   local cPhone := oGetPhone:GetValue()

   if Empty( cName )
      MsgAlert( "Name is required." )
      return nil
   endif

   oDB:Execute( "INSERT INTO contacts (name, email, phone) VALUES (?, ?, ?)", ;
      { cName, cEmail, cPhone } )

   RefreshBrowse( oDB, oBrw )
   MsgInfo( "Contact added." )

return nil

SELECT — Refreshing the Grid

static function RefreshBrowse( oDB, oBrw )

   local aRows := oDB:QueryToArray( "SELECT id, name, email, phone FROM contacts ORDER BY name" )

   oBrw:SetArray( aRows )
   oBrw:Refresh()

return nil

UPDATE — Modifying the Selected Record

static function DoUpdate( oDB, oBrw, oGetName, oGetEmail, oGetPhone )

   local nId    := oBrw:GetValue( 1 )  // Column 1 = id
   local cName  := oGetName:GetValue()
   local cEmail := oGetEmail:GetValue()
   local cPhone := oGetPhone:GetValue()

   if nId == 0
      MsgAlert( "Select a contact first." )
      return nil
   endif

   oDB:Execute( "UPDATE contacts SET name=?, email=?, phone=? WHERE id=?", ;
      { cName, cEmail, cPhone, nId } )

   RefreshBrowse( oDB, oBrw )
   MsgInfo( "Contact updated." )

return nil

DELETE — Removing the Selected Record

static function DoDelete( oDB, oBrw )

   local nId := oBrw:GetValue( 1 )

   if nId == 0
      MsgAlert( "Select a contact first." )
      return nil
   endif

   if MsgYesNo( "Delete this contact?" )
      oDB:Execute( "DELETE FROM contacts WHERE id=?", { nId } )
      RefreshBrowse( oDB, oBrw )
      MsgInfo( "Contact deleted." )
   endif

return nil

Row Selection — Populating Input Fields

static function OnRowSelect( oBrw, oGetName, oGetEmail, oGetPhone )

   oGetName:SetValue(  oBrw:GetValue( 2 ) )
   oGetEmail:SetValue( oBrw:GetValue( 3 ) )
   oGetPhone:SetValue( oBrw:GetValue( 4 ) )

return nil

Step 6: Using TDBNavigator

The TDBNavigator control provides standard navigation buttons (First, Previous, Next, Last) that work directly with the TBrowse grid. Drop it from the Data Access palette and set its oBrowse property to your TBrowse instance.

Application Architecture