Learn How To Define Powerful Dataset Views Using Indices With FireDAC In Delphi Windows Development

The Indices sample shows you how to use the Indexes property of TFDQuery in order to specify a collection of indexes at design time and determine the data view of each index, including a sort order of the records. Moreover, you can sort the records on a specific field using the IndexFieldNames property. Click on a grid column title to sort its records at run time.

Location

You can find the Indices sample project at:

• Start | Programs | Embarcadero RAD Studio Sydney | Samples and then navigate to:
  • Object PascalDatabaseFireDACSamplesComp LayerTFDQueryIndices
• Subversion Repository:
  • You can find Delphi code samples in GitHub Repositories. Search by name into the sample repositories according to your RAD Studio version.

How to Use the Sample

1. Navigate to the location given above and open Indices.dproj.
2. Press F9 or choose Run > Run.
3. Click on the Use Connection Definition combo box and select an option.
4. Interact with the sample:
   • Click on the OrderBy combo box and select an option.
   • Click on any column title.

Files

File in Delphi	Contains
Indices.dproj Indices.dpr	The project itself.
fIndices.pas fIndices.fmx	The main form.

Implementation

Before running the sample, the main components are configured at design time using the Object Inspector as follows:

• A TFDQuery object named qryMain. This component is used to implement a dataset capable of executing SQL queries. The following setup is needed:

• Configure the Connection property to specify the FireDAC connection object that is used to connect to a DBMS.
• Set the SQL property to select * from {id Orders}. It specifies the SQL statement to execute for the query.
• Set the Indexes property to define a collection of indexes with different properties. The sample configures five predefined views of the dataset records:
  • The Indexes[0] implements a sorting of records by the CustormerID field.
  • The Indexes[1] implements a case-insensitive sorting of records by the ShipName field.
  • The Indexes[2] sorts the records by the CustomerID field in descending order.
  • The Indexes[3] applies the filter EmployeeID=8 to the dataset and then sorts the records by the CustormerID field.
  • The Indexes[4] sorts the records using the expression year(orderDate).

• A TDataSource object named DataSource1. This component provides an interface between a dataset component and data-aware controls on a form. In this sample, it is used to provide communication between the dataset and the grid where the dataset is displayed. To this end, the DataSet property of DataSource1 is set to qryMain and the DataSource property of DBGrid1 is set to DataSource1.

When you run the application, click on the Use Connection Definition combo box and select an option in order to define a connection. When you select an item of the combo box, the sample uses the Open method of qryMain in order to execute the SQL statement that retrieves the “Orders” table from the database. Then, the sample displays the “Orders” table using a TDBGrid component. Once the DBGrid1 is filled, you can interact with the sample in the following ways:

1. Choose a predefined index from the OrderBy combo box to set the predefined view of the dataset records.
2. Click on a grid column title to sort its records:
   • One-click sorts the records in ascending order.
   • If the records are sorted in ascending order, and you click again, the sample sorts the records in descending order.
   • If the records are sorted in descending order, click again to undo the sorting of the dataset records.

For more details, check the link to the original post of Embarcadero:

http://docwiki.embarcadero.com/CodeExamples/Sydney/en/FireDAC.TFDQuery.Indices_Sample

Head over and check out the full source code for the TFDQuery Indices FireDAC sample in Delphi.

With the use of Windows IDE, you can Quickly define dataset views using indices with FIREDAC when developing in Delphi or C++ environments. Try your Free Trial here.