Developing a Database Application — Bringing Front-End and Back-End Together

6. Developing a Database Application — Bringing Front-End and Back-End Together

This section pulls the whole chapter together by building one small application from start to finish. Suppose a shop wants a simple program to keep its customer records — to add a new customer, search for a customer, and see the list of all customers. Watch how the front-end, the back-end and the connectivity you have studied come together into one working system.

Step 1 — Analyse and design. First decide what data is needed and what the user must be able to do. For a customer register we need, for each customer, an identity number, a name, a city and a phone number; and the user must be able to add, search and list customers. This gives us the plan for both the table and the screen.

Step 2 — Build the back-end (the table). Create the table that will hold the data, choosing a data type for each column and a primary key to identify each row:

CREATE TABLE Customer (
    CustID   INTEGER      PRIMARY KEY,
    Name     VARCHAR(50)  NOT NULL,
    City     VARCHAR(30),
    Phone    VARCHAR(15)
);

Step 3 — Build the front-end (the form). Design a form with a label and a text box for each field (CustID, Name, City, Phone), and three buttons — Add, Search and Show All — together with a grid to display records. This is the presentation layer the user will see.

Step 4 — Connect the two (write the event handlers). For each button, write an event handler that opens a connection, runs the right SQL, and shows the result.

Adding a customer (the Add button) — after validating that the boxes are filled:

WHEN Add button is clicked:
    validate that CustID, Name are not empty
    open connection to database
    execute:  INSERT INTO Customer (CustID, Name, City, Phone)
              VALUES (:id, :name, :city, :phone)   -- values taken safely from the boxes
    show message "Customer added"
    close connection

Searching for a customer (the Search button):

WHEN Search button is clicked:
    open connection to database
    result = execute:  SELECT Name, City, Phone FROM Customer WHERE CustID = :id
    IF result has a row THEN put its values into the Name, City, Phone boxes
    ELSE show message "No such customer"
    close connection

Listing all customers (the Show All button):

WHEN Show All button is clicked:
    open connection to database
    result = execute:  SELECT CustID, Name, City, Phone FROM Customer
    FOR each row in result: add the row to the grid
    close connection

Step 5 — Test the application. Run it and check each action: add a few customers and confirm they are stored; search for an existing and a non-existing ID; and list all records. Test the validation too — try to add a customer with a blank name and confirm the front-end refuses it. Testing with realistic data is how faults are found before real users meet them.

Step 6 — Deploy and maintain. Once tested, the application is put to use ('deployed'). Over time it will need maintenance — fixing faults, and adding features such as an Update or Delete button (each just another event handler running an UPDATE or DELETE statement) as the shop's needs grow. …