FireDAC.MSSQL Sample

This sample demonstrates how to use FireDAC to work with Microsoft SQL server.

Location

You can find the MSSQL sample project at:

• Start | Programs | Embarcadero RAD Studio Rio | Samples and then navigate to:
  • Object Pascal\Database\FireDAC\Samples\Getting Started\MSSQL
• Subversion Repository:
  • You can find Delphi code samples in GitHub Repositories. Search by name into the samples repositories according to your RAD Studio version.

Description

The MSSQL getting started sample shows how to use FireDAC with MS SQL Server database. This sample allows the user to create temporary connection definition at run-time and master-details relationship between datasets using TFDQuery to work with MSSQL databases.

How to Use the Sample

1. Navigate to the location given above and open GettingStarted.dproj.
2. Press F9 or choose Run > Run.

Files

File in Delphi	Contains
GettingStarted.dproj GettingStarted.dpr	The project itself.
fGettingStarted.pas fGettingStarted.fmx	The main form.

Implementation

The sample implements the following features.

Creating temporary connection definition

The simplest way to configure connection to MS SQL database at run-time is to build a temporary connection definition:

with dbMain do begin
  Close;
  // create temporary connection definition
  with Params do begin
    Clear;
    Add('DriverID=MSSQL');
    Add('Server=(local)');
    Add('Database=FDDemo');
  end;
  Open;
end;

In the sample, the temporary definition is created when clicking the Connect button.

Master Details

In the demo database, the Categories and Products tables have one-to-many relation by CategoryID field. The qryCategories.SQL property is set up as follows:

select * from Categories

In addition, the qryProducts.SQL property is set up as follows:

select * from Products
where CategoryID = :CategoryID

Finally, the qryProducts.MasterSource property is set to dsCategories, while the MasterFields property is set to CategoryID. This creates a master-details relationship between the datasets.

Executing queries

Simple queries execution is demonstrated via ExecSQL method of TFDConnection.

Inserting a record providing parameters of various types:

dbMain.ExecSQL('insert into Categories(CategoryName, Description, Picture) ' +
               'values(:N, :D, :P)', ['New category', 'New descr', $0334]);

Updating records:

 dbMain.ExecSQL('update Products set UnitPrice = UnitPrice * :P1 + :P2 ' +
                'where ProductID < 3', [Random(5), Random(3)]);

Deleting a record:

dbMain.ExecSQL('delete from Categories where CategoryName like :N', ['New category']);

Getting a scalar value from the database:

iID := dbMain.ExecSQLScalar('select MAX(CategoryID) from Categories');

Uses

• TFDQuery
• TFDConnection

See Also

• Defining Connection (FireDAC)
• Connect to Microsoft SQL Server (FireDAC)
• Master-Detail Relationship (M/D)
• MS SQL Server Questions (FireDAC)

Samples

• FireDAC InterBase sample
• FireDAC MSAccess sample
• FireDAC MySQL sample
• FireDAC SQLite sample
• FireDAC Firebird sample