Hi!
@georges: I'm working (in the last year) in a application to manage loans of books in a library.
To simplify all SQlite3 (runtime) code refering to: open/close/query, etc... I use the Modularization.
Create a unit named unitdatabases.pas (not a form, a unit)
unit UnitDatabases;
interface
uses
Sqlite3DS,
SQLite3Conn,
sqldb,
DBCtrls,
DB,
Classes, SysUtils, ExtCtrls;
type
{TDatabases}
TDatabases = class
public
class procedure DatabaseOpen(Sqlite3Dataset: TSqlite3Dataset; DataSource: TDataSource; DBNavigator: TDBNavigator; sDBFileName: string; sDBTableName: string; sDBPrimaryKey: string); overload;
class procedure DatabaseClose(Sqlite3Dataset: TSqlite3Dataset); overload;
class procedure DatabaseOpen(SQLite3Connection: TSQLite3Connection; SQLTransaction: TSQLTransaction; DataSource: TDataSource; SQLQuery: TSQLQuery; sDBFileName: string); overload;
class procedure DatabaseClose(SQLite3Connection: TSQLite3Connection; SQLTransaction: TSQLTransaction; DataSource: TDataSource; SQLQuery: TSQLQuery); overload;
class procedure DatabaseOpenQuery(Sqlite3Dataset: TSqlite3Dataset; DataSource: TDataSource; DBNavigator: TDBNavigator; sDBFileName: string; sDBTableName: string; sDBPrimaryKey: string; sQuery: string);
end;
implementation
class procedure TDatabases.DatabaseOpen(Sqlite3Dataset: TSqlite3Dataset; DataSource: TDataSource; DBNavigator: TDBNavigator; sDBFileName: string; sDBTableName: string; sDBPrimaryKey: string); overload;
begin
TDatabases.DatabaseClose(Sqlite3Dataset);
Sqlite3Dataset.FileName := sDBFileName;
Sqlite3Dataset.TableName := sDBTableName;
Sqlite3Dataset.PrimaryKey := sDBPrimaryKey;
DataSource.DataSet := Sqlite3Dataset;
if not (DBNavigator = nil) then DBNavigator.DataSource := DataSource;
Sqlite3Dataset.Open;
Sqlite3Dataset.Active := True;
Sqlite3Dataset.SaveOnClose := True;
Sqlite3Dataset.SaveOnRefetch := True;
end;
class procedure TDatabases.DatabaseClose(Sqlite3Dataset: TSqlite3Dataset); overload;
begin
Sqlite3Dataset.Active := False;
Sqlite3Dataset.Close;
end;
class procedure TDatabases.DatabaseOpen(SQLite3Connection: TSQLite3Connection; SQLTransaction: TSQLTransaction; DataSource: TDataSource; SQLQuery: TSQLQuery; sDBFileName: string); overload;
begin
SQLite3Connection.DatabaseName := sDBFileName;
SQLite3Connection.Connected := True;
SQLTransaction.Database := SQLite3Connection;
SQLQuery.Database := SQLite3Connection;
DataSource.DataSet := SQLQuery;
end;
class procedure TDatabases.DatabaseClose(SQLite3Connection: TSQLite3Connection; SQLTransaction: TSQLTransaction; DataSource: TDataSource; SQLQuery: TSQLQuery); overload;
begin
SQLQuery.Close;
SQLTransaction.CloseDataSets;
SQLTransaction.Active := False;
SQLite3Connection.CloseDataSets;
SQLite3Connection.CloseTransactions;
SQLite3Connection.Close;
end;
class procedure TDatabases.DatabaseOpenQuery(Sqlite3Dataset: TSqlite3Dataset; DataSource: TDataSource; DBNavigator: TDBNavigator; sDBFileName: string; sDBTableName: string; sDBPrimaryKey: string; sQuery: string);
begin
TDatabases.DatabaseClose(Sqlite3Dataset);
Sqlite3Dataset.FileName := sDBFileName;
Sqlite3Dataset.TableName := sDBTableName;
Sqlite3Dataset.PrimaryKey := sDBPrimaryKey;
DataSource.DataSet := Sqlite3Dataset;
Sqlite3Dataset.SQL := sQuery;
// DBGrid1.DataSource := DataSource;
if not (DBNavigator = nil) then DBNavigator.DataSource := DataSource;
Sqlite3Dataset.ExecSQL;
Sqlite3Dataset.Open;
Sqlite3Dataset.Active := True;
Sqlite3Dataset.SaveOnClose := True;
Sqlite3Dataset.SaveOnRefetch := True;
end;
end.
After in your Form, create the visual components, but no edit any properties... The code does it for you...
The followed code has default names of visual controls, you can change if you need.
unit Unit1;
{$mode objfpc}{$H+}
interface
uses
UnitDatabases,
Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs;
type
TForm1 = class(TForm)
Button1: TButton;
Button2: TButton;
Datasource1: TDatasource;
DBEdit1: TDBEdit;
DBEdit2: TDBEdit;
DBEdit3: TDBEdit;
DBNavigator1: TDBNavigator;
Sqlite3Dataset1: TSqlite3Dataset;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ private declarations }
public
{ public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.lfm}
procedure TfrmMaterialesActualizacion.FormCreate(Sender: TObject);
begin
DBEdit1.DataField := 'ID_BOOK';
DBEdit2.DataField := 'NAME';
DBEdit3.DataField := 'AUTHOR';
Edit1.MaxLength := 10;
Edit2.MaxLength := 80;
Edit3.MaxLength := 80
end;
procedure TForm1.Button1Click(Sender: TObject);
var
sDBPath, sTableName, sTableIdx, sQuery : String;
begin
sDBPath := 'Path\To\Your\Database.db';
sTableName := 'books';
sTableIdx := 'id';
sQuery := 'SELECT * FROM ' + sTableName + ' WHERE ACTIVE="1" ';
TDatabases.DatabaseOpenQuery(Sqlite3Dataset1, DataSource1, DBNavigator1, sDBPath , sTableName, sTableIdx, sQuery);
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
TDatabases.DatabaseClose(Sqlite3Dataset1);
end;
end.
No really functional Sample (need to add the visual controls), but use your imagination.
Now you can joke with the code.
if you do not understand something, just ask.
Regards!
P/d: Maybe tomorrow I can complete a functional example to compile, for now it's all...
-- edit ------------------------------------
If you create the Conection Controls manually, you need all units required by that Controls added into the form in Uses clause. Some controls need add Components to the project, so first add the visual control, then delete it from the visual interfase of the Form and finally, manage as you need the runtime creation of the Conection Controls.
When you know exactly needed code, modularize all.