ต้องการอ่านฐานข้อมูล SQLite ที่เตรียมไว้มาแสดงบน data-aware ต่างๆ เช่น DBCombobox, DBEdit แล้วทำการ Edit ข้อมูลเช่นเพิ่ม record ลบ record ด้วยการเขียนโค๊ดคุมเป็นหลัก ต่อไปผมจะ post โค๊ดทั้งหมดที่อยู่บนยูนิต main (main.pas)
unit main;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, FileUtil, LResources, Forms, Controls, Graphics, Dialogs,
StdCtrls, ExtCtrls, sqldb, DbCtrls, db, SQLite3Conn;
type
{ TfrmSetEllipsoid }
TfrmSetEllipsoid = class(TForm)
cmdDelete: TButton;
cmdNew: TButton;
cmdApply: TButton;
cmdOK: TButton;
cmdCancel: TButton;
dbtxtFlattening: TDBEdit;
dbtxtSemiMajorAxis: TDBEdit;
dbtxtEllipsoidCode: TDBEdit;
DBNavigator1: TDBNavigator;
dbcboEllipsoidName: TDBComboBox;
GroupBox1: TGroupBox;
Image1: TImage;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Label4: TLabel;
procedure cmdApplyClick(Sender: TObject);
procedure cmdCancelClick(Sender: TObject);
procedure cmdDeleteClick(Sender: TObject);
procedure cmdNewClick(Sender: TObject);
procedure cmdOKClick(Sender: TObject);
procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
procedure FormCreate(Sender: TObject);
procedure FormDestroy(Sender: TObject);
private
{ private declarations }
FDBCon : TSQLite3Connection;
FTrans : TSQLTransaction;
FSql : TSQLQuery;
FDatasource : TDatasource;
FDatabaseFile : string;
FNewRecordState : boolean;
public
{ public declarations }
end;
var
frmSetEllipsoid: TfrmSetEllipsoid;
implementation
{ TfrmSetEllipsoid }
procedure TfrmSetEllipsoid.cmdOKClick(Sender: TObject);
begin
FSql.Close;
FDBCon.Close;
FTrans.EndTransaction;
Close;
end;
procedure TfrmSetEllipsoid.FormClose(Sender: TObject;
var CloseAction: TCloseAction);
begin
FTrans.EndTransaction;
FSql.Close;
FDBCon.Close;
end;
procedure TfrmSetEllipsoid.FormCreate(Sender: TObject);
begin
//เปลี่ยน path ที่อยู่ของฐานข้อมูลให้ตรงกับโฟลเดอร์ที่เก็บ
FDatabaseFile := 'e:\sourcecodes\lazarus\sqliteproj2\datums.s3db';
//FDatabaseFile := '/media/PJ320GMISC/SourceCodes/Lazarus/SQLiteProj2/datums.s3db';
FDBCon := TSQLite3Connection.Create(NIL);
FTrans := TSQLTransaction.Create(NIL);
FSql := TSQLQuery.Create(NIL);
FDatasource := TDatasource.Create(NIL);
FDBCon.DatabaseName := FDatabaseFile;
FDBCon.Transaction := FTrans;
FTrans.DataBase := FDBCon;
FSql.DataBase := FDBCon;
FSql.Transaction := FTrans;
FDBCon.Connected := true;
FTrans.Active := true;
FDatasource.Dataset := FSql;
FDatasource.AutoEdit := true;
FSql.Close;
FSql.SQL.Clear;
FSql.SQL.Text := 'SELECT * FROM ELLIPSOIDS';
FSql.Open;
//FTrans.CommitRetaining; {This line does not work in win32. But OK in linux???}
dbcboEllipsoidName.DataSource := FDatasource;
dbcboEllipsoidName.DataField := 'NAME';
dbtxtEllipsoidCode.DataSource := FDatasource;
dbtxtEllipsoidCode.DataField := 'CODE';
dbtxtSemiMajorAxis.DataSource := FDatasource;
dbtxtSemiMajorAxis.DataField := 'A';
dbtxtFlattening.DataSource := FDatasource;
dbtxtFlattening.DataField := 'INV_F';
DBNavigator1.DataSource := FDatasource;
FNewRecordState := false;
end;
procedure TfrmSetEllipsoid.FormDestroy(Sender: TObject);
begin
FTrans.Free;
FSql.Free;
FDatasource.Free;
//Make sure TSQLite3Connection is the last.
FDBCon.Free;
end;
procedure TfrmSetEllipsoid.cmdCancelClick(Sender: TObject);
begin
Close;
end;
procedure TfrmSetEllipsoid.cmdDeleteClick(Sender: TObject);
var
szECode : string;
sql : TSQLQuery;
begin
try
sql := TSQLQuery.Create(nil);
sql.Database := FDBCon;
sql.Transaction := FTrans;
szECode := dbtxtEllipsoidCode.Text;
if (MessageDlg ('คำเตือน','ยืนยันว่าต้องการลบทรงรี', mtConfirmation,
[mbOK, mbCancel],0) = mrOK) then
begin
sql.Close;
sql.SQL.Text := 'DELETE FROM ELLIPSOIDS WHERE CODE = :CODE';
sql.Params.ParamByName('CODE').AsString := szECode;
sql.ExecSQL;
FSql.ApplyUpdates;
FTrans.CommitRetaining;
FSql.Close;
FSql.SQL.Clear;
FSql.SQL.Text := 'SELECT * FROM ELLIPSOIDS';
FSql.Open;
FTrans.CommitRetaining;
end;
finally
sql.Free;
end;
end;
procedure TfrmSetEllipsoid.cmdNewClick(Sender: TObject);
begin
//Make its to ready for editable.
dbcboEllipsoidName.ReadOnly := false;
dbtxtSemiMajorAxis.ReadOnly := false;
dbtxtflattening.ReadOnly := false;
dbtxtEllipsoidCode.ReadOnly := false;
dbcboEllipsoidName.Text := '';
dbtxtSemiMajorAxis.Text := '';
dbtxtFlattening.Text := '';
dbtxtEllipsoidCode.Text := '';
cmdApply.Enabled := true;
cmdNew.Enabled := false;
DBNavigator1.BtnClick(nbInsert);
FNewRecordState := true;
end;
//The big problem that DBNavigator1.Post method doesn't work.
//using query to solve the problem with ExeSql method.
procedure TfrmSetEllipsoid.cmdApplyClick(Sender: TObject);
var
szEName, szECode : string;
dA, dB, dF : extended;
f1, f2 : boolean;
sql : TSQLQuery;
begin
try
sql := TSQLQuery.Create(nil);
sql.Database := FDBCon;
sql.Transaction := FTrans;
szEName := trim(dbcboEllipsoidName.Text);
szECode := trim(dbtxtEllipsoidCode.Text);
if (szEName <> '') and (szECode <> '') then
begin
szECode := Trim(dbtxtEllipsoidCode.Text);
f1 := trystrtofloat(dbtxtSemiMajorAxis.Text, dA);
f2 := trystrtofloat(dbtxtFlattening.Text, dF);
dB := dA - dA * 1 / dF;
if (FNewRecordState) then
begin
if (f1 and (dA > 6377000)) and (f2 and (df > 293)) then
begin
sql.Close;
sql.SQL.Clear;
sql.SQL.Text := 'INSERT INTO ELLIPSOIDS (NAME,CODE,A,B,INV_F)'
+ ' VALUES (:NAME,:CODE,:A,:B,:INV_F)';
sql.Params.ParamByName('NAME').AsString := szEName;
sql.Params.ParamByName('CODE').AsString := szECode;
sql.Params.ParamByName('A').AsFloat := dA;
sql.Params.ParamByName('B').AsFloat := dB;
sql.Params.ParamByName('INV_F').AsFloat := dF;
sql.ExecSQL;
cmdApply.Enabled := False;
cmdNew.Enabled := true;
FTrans.CommitRetaining;
FNewRecordState := false;
end
else if not(f1 and (dA > 6377000)) then
begin
MessageDlg ('Warning', 'Semi-major axis must be the number and its value must > 6,377,000 m.?', mtConfirmation,
[mbOK],0);
dbtxtSemiMajorAxis.SetFocus;
end
else if not (f2 and (df > 297)) then
begin
MessageDlg ('Warning', 'Flattening must be the number and its value must > 297 m.?', mtConfirmation,
[mbOK],0);
dbtxtFlattening.SetFocus;
end;
end
else
begin
sql.Close;
sql.SQL.Clear;
sql.SQL.Text := 'UPDATE ELLIPSOIDS SET NAME=:NAME,'
+ 'A=:A,B=:B,INV_F=:INV_F WHERE CODE=:CODE';
sql.Params.ParamByName('NAME').AsString := szEName;
sql.Params.ParamByName('CODE').AsString := szECode;
sql.Params.ParamByName('A').AsFloat := dA;
sql.Params.ParamByName('B').AsFloat := dB;
sql.Params.ParamByName('INV_F').AsFloat := dF;
sql.ExecSQL;
cmdApply.Enabled := False;
FTrans.CommitRetaining;
end;
end;
finally
sql.Free;
end;
end;
initialization
{$I main.lrs}
end.
ติดตามตอนหน้าผมจะอธิบายโค๊ดที่สำคัญ เราจะทดลองเพิ่มและลบ record ที่อยู่ในฐานข้อมูลดู
