Develop Code to Handle Main Events (dbExpress Tutorial)

From RAD Studio
Jump to: navigation, search

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.

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.

ComboBoxConnections.Items[ComboBoxConnections.ItemIndex]
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

Next

Write Code to Handle Minor Events