Basic Database Access

A key strength of Delphi is its robust and efficient ability to connect to and manipulate data from various database systems. The standard, modern solution for this is the FireDAC component library.

1. FireDAC Architecture

FireDAC is a universal data access layer that allows a Delphi application to connect to dozens of database systems (e.g., SQLite, MySQL, SQL Server, Oracle) using a consistent set of components.

Database access in Delphi involves three main categories of components:

1. Connection: Establishes the link to the physical database file or server.
2. Dataset: Holds the data retrieved from the database (e.g., the result of a SQL query).
3. Visual Control: Displays or manipulates the data from the dataset (e.g., a data grid).

2. Connection Component: TFDConnection

The TFDConnection component manages the physical link to the database.

• Placement: Placed once on a form or a dedicated data module.
• Configuration: Configuration is typically done at design time via the Connection Editor.

Key Properties for TFDConnection:

// Example: Connecting to a SQLite database programmatically
procedure TForm1.ConnectToDB;
begin
  FDConnection1.Close; // Close any existing connection first
  FDConnection1.DriverName := 'SQLite';
  
  // Clear any old parameters and add the required path
  FDConnection1.Params.Clear;
  FDConnection1.Params.Add('Database=C:\data\customers.sqlite');
  
  // Attempt to open the connection
  FDConnection1.Connected := True;
end;

3. Dataset Component: TFDQuery

The Dataset component represents a set of data (a table, a view, or the result of an arbitrary SQL query). TFDQuery is the most flexible dataset component.

• Placement: Placed on the form and linked to the connection.
• Linking: Set the Connection property of the TFDQuery to the name of the TFDConnection component (e.g., FDConnection1).

Key Properties for TFDQuery:

procedure TForm1.LoadCustomerData;
begin
  // Ensure the connection is open
  if not FDConnection1.Connected then ConnectToDB; 

  FDQuery1.Close; // Close previous dataset if open
  
  // Define the SQL command
  FDQuery1.SQL.Text := 'SELECT CustomerID, Name, City FROM Customers ORDER BY Name';
  
  FDQuery1.Open; // Execute the query and load the data
end;

4. Data Linking Component: TDataSource

The TDataSource component acts as a non-visual bridge between a dataset (like TFDQuery1) and a visual, data-aware control (like a data grid).

• Purpose: It provides a common interface for visual controls to read the current record, navigate, and receive notifications when the underlying dataset changes.
• Linking: Set the DataSet property of the TDataSource to the name of your dataset component (e.g., TFDQuery1).

5. Visual Component: TDBGrid

Data-aware controls are components designed specifically to display data from a dataset. The TDBGrid is the most common component for displaying tabular data.

• Linking: Set the DataSource property of the TDBGrid to the name of your TDataSource component (e.g., DataSource1).

Workflow Summary:

1. TFDConnection connects to the database.
2. TFDQuery (or TFDTable) executes the SQL and fetches the records.
3. TDataSource links the query result to the visual control.
4. TDBGrid displays the data.

6. Navigating and Editing

When a dataset is open:

• Field Access: You can access the data in the current record’s fields programmatically:Delphi// Get the name from the current record var CurrentName := FDQuery1.FieldByName('Name').AsString;
• Navigation: Methods like FDQuery1.Next, FDQuery1.Prior, FDQuery1.First, and FDQuery1.Last move the internal pointer between records.
• Editing: To modify data, you must call FDQuery1.Edit, change the field values, and then call FDQuery1.Post to save changes back to the database.