C++Builder - Database tutorial, part 1;

Terminology

A database is an organized collection of information.

A database is organized in tables.

• A relational database is a collection of tables, views, reports and possibly other elements that belong together.
• A flat database on the other hand, consists of only one table.

A table contains records, and each record contains fields. In other words, each column in the table is a field, and each row is a record.

A query is a command that retrieves data from the database.

Text-database

In this tutorial, we will build a flat text database for maintaining the stock of articles of a furniture shop.

Compilation and test

Preparations

1. Create a new folder \CppProjects on your harddisk.
2. Create folder Articles1 "under" \CppProjects.
3. Download Articles1.zip (source code for part 1) and unzip it to folder Articles1.

Compilation

1. Start C++ Builder and open articles1.cbproj: menu File / Open Project...
2. Run the project: menu Run / Run (or press function key F9).
   
3. The program articles1.exe is launched.
   
   
4. Click the Add button and enter some data.
5. Click the OK button. Now, the "status" of the operation should appear near the top of the window. 
6. Add a second record, for example: 0002, table, 50, 20 and click OK.
   
7. Enter 0001 in the box below the Find button and click the Find button in order to find the article.
8. Stop the program via its menu and Exit, via its sytem menu, or via its Close-button (top right of the window).
9. Stop C++ Builder. If you get the question "Save changes to...?", say no. Because maybe you changed something in the source code and for the moment we don't want to save these changes.

You'll note that the program is not very user friendly yet. We'll change that in the next parts of our tutorial.

Organisation of the database and setup

For each record, we create an object of the type TArticle. When you look at the source code in Unit1.cpp, at the beginning you see the declaration of our class TArticle:

class TArticle : public TObject {
  private:
    String FName;
    float FPrice;
    int FStock;
  public:
    __property String Name = { read = FName, write = FName };
    __property float Price = { read = FPrice, write = FPrice };
    __property int Stock = { read = FStock, write = FStock };
};

We shall store the data in a TStringList, named Articles.
Besides a list of strings, a stringlist also can contain a list of objects. For each article, we'll store its ID in a string of the stringlist, and we'll store a TArticle object with the contents of the other fields in the associated object.
For example, the ID of the first article is stored in Articles->Strings[0] and the rest of its data is stored in Articles->Objects[0].

We declared stringlist Articles as a private member of the form, as can be seen in Unit1.h:

private:    // User declarations
  TStringList *Articles;

Articles is created at the beginning of the application, see Unit1.cpp:

void __fastcall TForm1::FormCreate(TObject *Sender)
{
  Articles = new TStringList;
  Articles->CaseSensitive = false;
  Articles->Sorted = true;
  // automatically free the stringlist's objects when it is destroyed 
  Articles->OwnsObjects = true;
  // Some housekeeping:
  edID->Text = "";
  edName->Text = "";
  edPrice->Text = "";
  edStock->Text = "";
  edFind->Text = "";
  stStatus->Caption = "";  
}

Articles is destroyed at the end of the application, see Unit1.cpp:

void __fastcall TForm1::FormDestroy(TObject *Sender)
{
  delete Articles;
}

User interface

A click on btnAdd prepares for adding an article:

void __fastcall TForm1::btnAddClick(TObject *Sender)
{
  stStatus->Caption = " Adding Article ";
  edID->Text = "";
  edName->Text = "";
  edPrice->Text = "";
  edStock->Text = "";
  edID->SetFocus(); // position the cursor in edit box edID
}

Clicking btnOK stores the data, if an article with the same ID does not already exist. Else, we inform the user that an Article with this ID already exists:

void __fastcall TForm1::btnOKClick(TObject *Sender)
{
  int index;
  String ID;
  ID = edID->Text;
  TArticle *Art = new TArticle; // create Art, a TArticle object
  if (Articles->Find(ID, index))
    stStatus->Caption = " " + ID + " already exists ";
  else {
    Art->Name = edName->Text;
    Art->Price = StrToFloat(edPrice->Text);
    Art->Stock = StrToInt(edStock->Text);
    Articles->AddObject(ID, Art);
    stStatus->Caption = " Article was added ";
  }
}

A preliminary check for doubles is done with:
if (Articles->Find(ID, index))

Storing the data is done in this line:
Articles->AddObject(ID, Art);

The function AddObject() combines two tasks: a string is added containing ID, and next an object is added to this string.

Clicking btnCancel simply informs the user that nothing was added:

void __fastcall TForm1::btnCancelClick(TObject *Sender)
{
  stStatus->Caption = " No article was added ";
}

Finally, btnFind is used for finding an article, given its ID:

void __fastcall TForm1::btnFindClick(TObject *Sender)
{
  int index;
  String ID;
  ID = edFind->Text; // input the ID from edit box edFind
  if (Articles->Find(ID, index)) {    // if found, the position is returned in variable index
    stStatus->Caption = " Article was found ";
    TArticle *Art = static_cast<tarticle*>(Articles->Objects[index]);
    edID->Text = ID;
    edName->Text = Art->Name;
    edPrice->Text = FormatFloat("0.00", Art->Price);
    edStock->Text = IntToStr(Art->Stock);
  }
  else
    stStatus->Caption = " Article " + ID + " not found ";
}

Important note: directly using Objects[index] will not work!
So, we create Art, an object of the class TArticle:

   TArticle *Art = ...

Now we tell Objects[i] to act "as if it were" a TArticle. This is called typecasting.
Amazingly, all this hocus pocus is done with this compact statement:

   TArticle *Art = static_cast<tarticle*>(Articles->Objects[index]);