Connecting a VCL Application to SQL Server Using FireDAC in RAD Studio

Desired Outcome

Create a VCL Windows application in RAD Studio that connects to a Microsoft SQL Server database using FireDAC, runs a parameterized query, and displays the results in a grid.

Prerequisites

• RAD Studio (Delphi or C++Builder) with FireDAC installed.
• Access to a SQL Server instance; know the server name, database name, user name and password.
• The appropriate FireDAC MSSQL driver (ODBC driver or native client) available in the system PATH or output directory.
• A VCL form with a TEdit for input, a TButton to execute, a TDataSource and a TDBGrid to show data.

Procedure

1. Drop a TFDConnection component onto a data module or form. Set its DriverId to MSSQL. Fill Server, Database, User_Name and Password with the SQL Server connection details.
2. Add a TFDQuery component. Set its Connection property to the TFDConnection you just configured.
3. In the TFDQuery's SQL.Text property enter a parameterized statement, for example:

SELECT * FROM Customers WHERE City = :City

1. Place a TEdit named EditCity on the form, a TButton named BtnExecute, a TDataSource and a TDBGrid linked to the data source.
2. In the button's OnClick event write:

try
  FDConnection1.Connected := True;
  FDQuery1.Close;
  FDQuery1.ParamByName('City').AsString := EditCity.Text;
  FDQuery1.Open;
except
  on E: Exception do
    ShowMessage('Error: ' + E.Message);
finally
  FDQuery1.Close;
  FDConnection1.Connected := False;
end;

1. After the query is no longer needed, release resources:

FDQuery1.Close;
FDConnection1.Connected := False;

Expected Checks

• Verify that FDConnection1.Connected is true after the Connected := True line.
• Check that the grid displays rows matching the city entered.
• Look at the IDE's Event Log or output window for any FireDAC connection messages; a successful attempt shows no error.
• Optionally use SQL Server Profiler to confirm the query arrives with the supplied parameter value.

Recovery Options (Rollback)

If an exception occurs, the except block shows the message and leaves the connection in its previous state. To ensure no open connection remains, you can explicitly set FDConnection1.Connected := False in the finally section:

try
  FDConnection1.Connected := True;
  FDQuery1.Close;
  FDQuery1.ParamByName('City').AsString := EditCity.Text;
  FDQuery1.Open;
except
  on E: Exception do
    ShowMessage('Error: ' + E.Message);
finally
  FDQuery1.Close;
  FDConnection1.Connected := False;
end;

Limitations

• The example uses the MSSQL driver; if you rely on the ODBC driver, ensure the ODBC data source name (DSN) is correctly configured.
• FireDAC runtime packages must be deployed with the application on machines without RAD Studio.
• For large result sets consider using TFDQuery.FetchOptions.Mode to limit memory usage.