Develop Code to Handle Main Events (dbExpress Tutorial)
Go Up to Tutorial: Using dbExpress to View and Update Databases in an Application
This section contains code to handle the main events associated with the form's components.
Contents
Combo Box Entry Selection
Selecting the database connection from the TComboBox triggers this event, which also closes the TComboBox. Select the TComboBox component. On the Event tab in the Object Inspector, double-click the OnCloseUp
event to generate the stub for this event handler. Then fill in the following code:
Delphi
procedure TForm2.ComboBoxConnectionsCloseUp(Sender: TObject);
begin
// Was a selection made?
if ComboBoxConnections.ItemIndex = -1 then
Exit;
// Initialize some utility objects.
if not Assigned(AllTables) then
AllTables := TStringList.Create;
if Assigned(FMetaDataProvider) then
FreeAndNil(FMetaDataProvider);
// If there was an open connection, close it. Indicate the connection is not active.
if SQLConnection1.Connected then
SQLConnection1.Close;
CheckBoxActive.Checked := False;
// Open the selected database connection.
SQLConnection1.ConnectionName := ComboBoxConnections.Items[ComboBoxConnections.ItemIndex];
SQLConnection1.LoadParamsOnConnect := True;
SQLConnection1.LoginPrompt := False;
SQLConnection1.Open;
SQLConnection1.GetTableNames(AllTables, False); //get the database's list of tables
// Get metadata for selected connection.
FMetaDataProvider := TDBXDataExpressMetaDataProvider.Create;
FMetaDataProvider.Connection := sqlconnection1.DBXConnection; //set to our open connection
FMetaDataProvider.Open;
// Populate the list box with the tables in this database connection.
PopulateListBox;
// Initialize some dialog items' state.
SQLDataSet1.CommandText := '';
EditSQL.Text := '';
CheckBoxActive.Enabled := False;
ButtonApply.Enabled := False;
end;
C++
void __fastcall TForm1::ComboBoxConnectionsCloseUp(TObject *Sender)
{
// Was a selection made?
if (ComboBoxConnections->ItemIndex == -1)
return;
// Initialize some utility objects.
if (!AllTables)
AllTables = new TStringList();
if (FMetaDataProvider) {
delete FMetaDataProvider;
FMetaDataProvider = NULL;
}
// If there was an open connection, close it. Indicate the connection is not active.
if (SQLConnection1->Connected)
SQLConnection1->Close();
CheckBoxActive->Checked = false;
// Open the selected database connection.
SQLConnection1->ConnectionName = ComboBoxConnections->Items->Strings[ComboBoxConnections->ItemIndex];
SQLConnection1->LoadParamsOnConnect = true;
SQLConnection1->LoginPrompt = false;
SQLConnection1->Open();
SQLConnection1->GetTableNames(AllTables, false); //get the database's list of tables
// Get metadata for selected connection.
FMetaDataProvider = new TDBXDataExpressMetaDataProvider();
FMetaDataProvider->Connection = SQLConnection1->DBXConnection; //set to our open connection
FMetaDataProvider->Open();
// Populate the list box with the tables in this database connection.
PopulateListBox();
// Initialize some dialog items' state.
SQLDataSet1->CommandText = "";
EditSQL->Text = "";
CheckBoxActive->Enabled = false;
ButtonApply->Enabled = false;
}
Commentary on Entry Selection Handling
The code checks if there's an active connection and closes it, since we have selected another one. Although a database has been selected, it is not yet active.
We open the connection using the item in the TComboBox you selected—the chosen database connection.
- In Delphi code, this TComboBox selection is
ComboBoxConnections.Items[ComboBoxConnections.ItemIndex]
- In C++, the TComboBox selection is
ComboBoxConnections->Items->Strings[ComboBoxConnections->ItemIndex]
After the connection is opened, we can get metadata—the database's table names—using the GetTableNames procedure. This is saved in the AllTables
TStringList variable.
We also get metadata using TDBXDataExpressMetaDataProvider
, which is derived from TDBXMetaDataProvider
.
PopulateListBox
is a utility function we will see later. This function simply adds the table names we just obtained to the list box for later access.
Although we have opened the database connection, we have not yet read any data. This is why the code disables various controls that are only used when there is data.
Selecting the Table
We select the table by double-clicking a table name in the list box, which was populated by selecting the database connection. To write a handler for this event, select the list box component. On the Event tab in the Object Inspector, double-click the OnDblClick
event to generate the event handler's stub. Here is the code for the handler:
Delphi
procedure TForm2.ListBox1DblClick(Sender: TObject);
var
FieldNamesList: TStringList;
SQLStmt: String;
I: Integer;
begin
// Activate appropriate controls now that table is selected.
CheckBoxActive.Enabled := True;
ButtonApply.Enabled := True;
TableName := ListBox1.Items[ListBox1.ItemIndex]; //get table name
// Get field (column) names for this table.
FieldNamesList := TStringList.Create;
FieldNamesList.Clear;
SQLConnection1.GetFieldNames(TableName, FieldNamesList);
// Construct SQL Select statement for all fields.
if FieldNamesList.Count > 0 then
begin
SQLStmt := 'select ';
for I := 0 to FieldNamesList.Count - 1 do
begin
SQLStmt := SQLStmt + FieldNamesList[I] + ',';
end;
SQLStmt[Length(SQLStmt)] := ' ';
SQLStmt := SQLStmt + 'from '+TableName;
end
else
// Can't find field names, so use '*'
SQLStmt := 'select * from '+TableName;
FreeAndNil(FieldNamesList);
// Set up SQL statement to get all table's data.
EditSQL.Text := SQLStmt;
// Set CommandText to SQL statement.
SQLDataSet1.CommandText := EditSQL.Text;
if ClientDataSet1.Active then
ClientDataSet1.Close;
ClientDataSet1.Open;
CheckBoxActive.Checked := True;
end;
C++
void __fastcall TForm1::ListBox1DblClick(TObject *Sender)
{
TStringList *FieldNamesList;
String SQLStmt;
int I;
// Activate appropriate controls now that table is selected.
CheckBoxActive->Enabled = true;
ButtonApply->Enabled = true;
TableName = ListBox1->Items->Strings[ListBox1->ItemIndex]; //get table name
// Get field (column) names for this table.
FieldNamesList = new TStringList();
FieldNamesList->Clear();
SQLConnection1->GetFieldNames(TableName, FieldNamesList);
// Construct SQL Select statement for all fields.
if (FieldNamesList->Count > 0)
{
SQLStmt = "select ";
for (I = 0; I < FieldNamesList->Count; I++)
{
if (I == 0)
SQLStmt = SQLStmt + FieldNamesList->Strings[I];
else
SQLStmt = SQLStmt + "," + FieldNamesList->Strings[I];
}
SQLStmt = SQLStmt + " from " + TableName;
}
else
// Can't find field names, so use '*'
SQLStmt = "select * from " + TableName;
delete FieldNamesList;
FieldNamesList = NULL;
// Set up SQL statement to get all table's data.
EditSQL->Text = SQLStmt;
// Set CommandText to SQL statement.
SQLDataSet1->CommandText = EditSQL->Text;
if (ClientDataSet1->Active)
ClientDataSet1->Close();
ClientDataSet1->Open();
CheckBoxActive->Checked = true;
}
Comments on Handling Table Selection
Now that we have selected a database table, we can activate data handling controls.
We create a list of all the field (also known as column) names of the table. We use these to construct a SQL SELECT
statement to get data from all the fields in the table.
After the SQL statement is formatted, we place the statement in the CommandText property of the TSQLConnection component. We had already set DbxCommandType to "Dbx.SQL" at design time to indicate that the TSQLConnection uses SQL commands.
Execute the formatted SELECT
statement by opening the TClientDataSet, closing it if already open. This results in displaying the table's data in the TDBGrid. The TDBNavigator is now active also.
Clicking on Table Cell
The application allows you to view field data by clicking in a TDBGrid cell. The cell's value displays in a TDBMemo in a separate form, which we will create in Add Cell Viewing Form.
Double-click the OnCellClick
event of the TDBGrid component to generate the stub for this event handler. Then add the following code:
Delphi
procedure TForm2.DBGrid1CellClick(Column: TColumn); //Column = table column clicked
begin
if ClientDataSet1.Active then
begin
// If the form isn't displayed, create it.
if not Assigned(MemoForm) then
begin
MemoForm := TFormCurrentField.Create(Self);
MemoForm.DBMemoCurrentField.DataSource := DataSource1; //set data source
end;
// Position form just below main dialog.
MemoForm.Top := Self.Top+Self.Height;
MemoForm.Left := Self.Left;
MemoForm.Visible := False;
MemoForm.Visible := True;
MemoForm.DBMemoCurrentField.DataField := Column.FieldName;
// Format caption with information on the field.
MemoForm.Caption := Format('Field Name: %s / Record Num: %d',[Column.FieldName, DBGrid1.DataSource.DataSet.RecNo]);
MemoForm.Width := Self.Width;
end;
end;
C++
void __fastcall TForm1::DBGrid1CellClick(TColumn *Column)
{
if (ClientDataSet1->Active)
{
// If the form isn't displayed, create it.
if (!MemoForm)
{
MemoForm = new TFormCurrentField(this);
MemoForm->DBMemoCurrentField->DataSource = DataSource1; //set data source
}
// Position form just below main dialog.
MemoForm->Top = this->Top + this->Height;
MemoForm->Left = this->Left;
MemoForm->Visible = false;
MemoForm->Visible = true;
MemoForm->DBMemoCurrentField->DataField = Column->FieldName;
// Format caption with information on the field.
//MemoForm->Caption = Format("Field Name: %s / Record Num: %d",[Column->FieldName, DBGrid1->DataSource->DataSet->RecNo]);
MemoForm->Caption.sprintf(L"Field Name: %s / Record Num: %d",Column->FieldName, DBGrid1->DataSource->DataSet->RecNo);
MemoForm->Width = this->Width;
}
}
Commentary on Table Cell Clicking
This event is handled primarily by setting the properties of the form, MemoForm
, which is a TFormCurrentField
object. We will define TFormCurrentField
in Add Cell Viewing Form. In particular, the handler sets properties of the TDBMemo component on the form. The DataField property contains the column name. Setting the DataSource to the data source allows the TDBMemo to display the actual field data.
Previous
Write Code to Initialize the Application