Show sqlite database in Delphi XE6 using firedac programmatically

Question

I am new in database using Delphi. Actually I need to display all the tables and their data in a gridview using firedac. Several videos on youtube and docwiki teaches how to access database through visual components i.e. throwing FDConnetion, FDTable, DataSource, etc. on the form.

But I would like to do the same thing programmatically so that I can focus more on coding and separate form from the business logic.

Here is what I have done in C# while accessing MSAccess data:

public void LoadUsersInfo()
        {

            try {
                OleDbConnection Connection = new OleDbConnection();        
                Connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;DataSource=dbCyberNet.accdb;Persist Security Info=False;";        
                Connection.Open();
                OleDbCommand command = new OleDbCommand();
                command.Connection = Connection;
                String query = "SELECT * FROM tblUsersInfo";
                command.CommandText = query;

                OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                dbGrid.DataSource = dt;
            }
            catch (Exception ex) {
                MessageBox.Show("Error occured while loading users information . " + ex.Message.ToString());
            }
            finally { Connection.Close(); }
        }

I would like to do similar in delphi for sqlite database. Here is what I have tried (edited).

procedure TForm1.Button1Click(Sender: TObject);
var
  con: TFDConnection;
  table: TFDTable;
  ds: TDataSource;
begin
  con := TFDConnection.Create(nil);
  con.LoginPrompt := False;
  table := TFDTable.Create(nil);
  table.TableName := 'Students';
  ds := TDataSource.Create(nil);
  ds.DataSet := table;

  try
    con.Open('DriverID=SQLite;Database=‪studentsDB.sqlite;');
    table.Connection := con;
    table.Active := True;
    con.Connected := True;
    DBGrid1.DataSource := ds;
  finally
    con.Close;
    con.Free;
    table.Free;
    ds.Free;
  end;
end;

Whenever I run this code, error dialog appears showing: First chance exception at $759AF097. Exception class ESQLiteNativeException with message '[FireDAC][Phys][SQLite] ERROR: no such table: STUDENTS'. Process Project1.exe (3836)

whereas the same table name exists inside the table and using visual firedac components, I can see the data grid fills easily.

Answer 1

You can do the same as with C#: assign your TFDQuery instance as DataSource of the VCL grid!

Add a TDataSource to the form, set its DataSet property to your TFDQuery, and assign it to your grid.

See the official Delphi documentation and other SO questions.

Answer 2

You can execute your Select query by calling the query's Open method so your code will look like this :-

procedure TStudents.executeQuery;
var
  con: TFDConnection;
  query: TFDQuery;
begin
  con := TFDConnection.Create(nil);
  query := TFDQuery.Create(con);
  con.LoginPrompt := False;
  con.Open('DriverID=SQLite;Database=studentsDB.sqlite;');
  query.Connection := con;
  query.SQL.Text := 'SELECT * FROM Students';
  query.Open;
  query.First;
  While Not Query.EOF Do
  Begin
    // Populate your object or do whatever processing you like here.
    Query.Next;
  End;
end;

You can then programatically create a TDataSource and a Grid and hook those up in code in a similar fashion. You'll need to move the Connection and Query objects out of your object's executeQuery method as they are local variables and will not be accessible to anything outside that method.

Note that Delphi does not garbage collect like C# does, so as it stand, the code leaks memory.

As an aside, your code appears to be an object method and it looks like you're attempting to create a set of business objects that know how to load and save themselves. Rather than roll your own, you might be better served by looking at one of the existing frameworks that exist (tiOPF, InstantObjects, mORMot or TMS Aurelius) to achieve this. These will allow you to concentrate on solving the problem at hand and not worry about the minutae of creating, loading and saving of objects.