Database Coding TADOQuery dan TADOTable ADO di Delphi

Teknologi ADO OLEDB dengan komponen TADOConnection, TADOQuery maupun TADOTable dapat digunakan untuk membuat program database menggunakan Microsoft Access, SQL Server dan Oracle. Untuk membuat program database dengan Microsoft Access menggunakan ADO OLEDB dengan kode program ikuti langkah-langkah berikut :

1. Buka Code Gear Delphi. Klik menu File – New VCL Forms Application – Delphi for Win32.

2. Akan ditampilkan Form1 yang masih kosong. Tambahkan komponen TLabel, TEdit dan TButton dari Tab Standard, TADOQuery dari Tab ADO, TDBGrid dari Tab Data Controls, TDataSource dari Tab Data Access.

3. Klik dua kali pada Form1 kemudian pada Private Sub Form1_Load tambahkan kode program berikut ::

procedure TFAccessADOOLEDB.FormCreate(Sender: TObject);

begin

  cn.Connected := False;

  cn.LoginPrompt := False;

  cn.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + ExtractFileDir(Application.ExeName) + '\world.mdb;Persist Security Info=False';

  cn.Connected := True;

  qContacts.Connection := cn;

  qContacts.Close;

  qContacts.SQL.Text := 'SELECT Name, Address, Phone FROM Contacts ORDER BY Name';

  qContacts.Open;

  ds.DataSet := qContacts;

  dg.DataSource := ds;

  dg.Columns.Items[0].Width := 100;

  dg.Columns.Items[1].Width := 100;

  dg.Columns.Items[2].Width := 100;

end;

4. Untuk event-event yang lain, program selengkapnya sebagai berikut :

unit uContacts;

interface

uses

  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

  Dialogs, StdCtrls, Grids, DBGrids, DB, ADODB, RpDefine, RpCon, RpConDS, RpRave,

  RpBase, RpSystem, StrUtils, ShellAPI, OleServer, ExcelXP;

type

  TFAccessADOOLEDB = class(TForm)

    cn: TADOConnection;

    qContacts: TADOQuery;

    ds: TDataSource;

    dg: TDBGrid;

    Label1: TLabel;

    Label2: TLabel;

    Label3: TLabel;

    eName: TEdit;

    eAddress: TEdit;

    ePhone: TEdit;

    Label4: TLabel;

    eFind: TEdit;

    bFirst: TButton;

    bPrev: TButton;

    bNext: TButton;

    bLast: TButton;

    bAdd: TButton;

    bEdit: TButton;

    bSave: TButton;

    bCancel: TButton;

    bRefresh: TButton;

    bDelete: TButton;

    bRave: TButton;

    bExcel: TButton;

    bHTML: TButton;

    bExit: TButton;

    rvds: TRvDataSetConnection;

    rvsys: TRvSystem;

    rvprj: TRvProject;

    mm: TMemo;

    ExApp: TExcelApplication;

    Ex: TExcelWorksheet;

    procedure FormCreate(Sender: TObject);

    procedure qContactsAfterScroll(DataSet: TDataSet);

    procedure bFirstClick(Sender: TObject);

    procedure bPrevClick(Sender: TObject);

    procedure bNextClick(Sender: TObject);

    procedure bLastClick(Sender: TObject);

    procedure bAddClick(Sender: TObject);

    procedure bEditClick(Sender: TObject);

    procedure bCancelClick(Sender: TObject);

    procedure bSaveClick(Sender: TObject);

    procedure bDeleteClick(Sender: TObject);

    procedure bRefreshClick(Sender: TObject);

    procedure bExitClick(Sender: TObject);

    procedure bRaveClick(Sender: TObject);

    procedure bHTMLClick(Sender: TObject);

    procedure bExcelClick(Sender: TObject);

  private

    { Private declarations }

  public

    { Public declarations }

  end;

var

  FAccessADOOLEDB: TFAccessADOOLEDB;

implementation

{$R *.dfm}

procedure TFAccessADOOLEDB.bAddClick(Sender: TObject);

begin

  qContacts.Insert;

end;

procedure TFAccessADOOLEDB.bCancelClick(Sender: TObject);

begin

  qContacts.Cancel;

end;

procedure TFAccessADOOLEDB.bDeleteClick(Sender: TObject);

begin

  qContacts.Delete;

end;

procedure TFAccessADOOLEDB.bEditClick(Sender: TObject);

begin

  qContacts.Edit;

end;

procedure TFAccessADOOLEDB.bExitClick(Sender: TObject);

begin

  Application.Terminate;

end;

procedure TFAccessADOOLEDB.bFirstClick(Sender: TObject);

begin

  qContacts.First;

end;

procedure TFAccessADOOLEDB.bLastClick(Sender: TObject);

begin

  qContacts.Last;

end;

procedure TFAccessADOOLEDB.bNextClick(Sender: TObject);

begin

  qContacts.Next;

end;

procedure TFAccessADOOLEDB.bPrevClick(Sender: TObject);

begin

  qContacts.Prior;

end;

procedure TFAccessADOOLEDB.bRefreshClick(Sender: TObject);

begin

  qContacts.Close;

  qContacts.SQL.Text := 'SELECT Name, Address, Phone FROM Contacts WHERE Name LIKE ''%' + eFind.Text + '%'' ORDER BY Name';

  qContacts.Open;

  dg.Columns.Items[0].Width := 100;

  dg.Columns.Items[1].Width := 100;

  dg.Columns.Items[2].Width := 100; 

end;

procedure TFAccessADOOLEDB.bSaveClick(Sender: TObject);

begin

  qContacts.FieldByName('Name').AsString := eName.Text;

  qContacts.FieldByName('Address').AsString := eAddress.Text;

  qContacts.FieldByName('Phone').AsString := ePhone.Text;

  qContacts.Post;

end;

procedure TFAccessADOOLEDB.FormCreate(Sender: TObject);

begin

  cn.Connected := False;

  cn.LoginPrompt := False;

  cn.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + ExtractFileDir(Application.ExeName) + '\world.mdb;Persist Security Info=False';

  cn.Connected := True;

  qContacts.Connection := cn;

  qContacts.Close;

  qContacts.SQL.Text := 'SELECT Name, Address, Phone FROM Contacts ORDER BY Name';

  qContacts.Open;

  ds.DataSet := qContacts;

  dg.DataSource := ds;

  dg.Columns.Items[0].Width := 100;

  dg.Columns.Items[1].Width := 100;

  dg.Columns.Items[2].Width := 100;

end;

procedure TFAccessADOOLEDB.qContactsAfterScroll(DataSet: TDataSet);

begin

  eName.Text := qContacts.FieldByName('Name').AsString;

  eAddress.Text := qContacts.FieldByName('Address').AsString;

  ePhone.Text := qContacts.FieldByName('Phone').AsString;

end;

end.

5. Klik menu Run - Run (F5) untuk menjalankan program. Setelah program dijalankan hasilnya tampak seperti tampilan berikut :

Database Design TADOQuery dan TADOTable ADO di Delphi

Untuk membuat aplikasi database menggunakan design dengan TADOTable & TADOQuery, ikuti langkah-langkah berikut :

1. Buka Code Gear Delphi. Klik menu File – New VCL Forms Application – Delphi for Win32.

2. Akan ditampilkan Form1 yang masih kosong. Tambahkan komponen TLabel Tab Standard, TADOQuery dari Tab dbGo, TDBEdit, TDBGrid, dan TDBNavigator dari Tab Data Controls, serta TDataSource dari Tab Data Access.

3. Kemudian set properti-propertinya sebagai berikut :

ADOConnection1   :

- ConnectionString  : Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=world.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

- LoginPrompt           : False

- Connected              : True

ADOQuery1  :

- Connection             : ADOConnection1

- SQL                          : SELECT Name, Address, Phone FROM Contacts

- Active                       : True

DataSource1          

- DataSet                   : ADOQuery1

DBGrid1       

- DataSource                        : DataSource1

DBNavigator1        

- DataSource                        : DataSource1

DBEdit1

- DataSource                        : DataSource1

- DataField                : Name

DBEdit2

- DataSource                        : DataSource1

- DataField                : Address

DBEdit3

- DataSource                        : DataSource1

- DataField                : Phone

5. Klik menu Run - Run (F5) untuk menjalankan program.

Database Coding TQuery dan TTable BDE di Delphi

Teknologi BDE dengan komponen TQuery maupun TTable dapat digunakan untuk membuat program database menggunakan Paradox, FoxPro, Dbase maupun Database yang mendukung ODBC antara lain : Microsoft Access, SQL Server, Interbase/Firebird, MySQL, PostgreSQL dan Oracle. Untuk membuat program database dengan Paradox menggunakan BDE dengan kode program ikuti langkah-langkah berikut :

1. Buka Code Gear Delphi. Klik menu File – New VCL Forms Application – Delphi for Win32.

2. Akan ditampilkan Form1 yang masih kosong. Tambahkan komponen TLabel, TEdit dan TButton dari Tab Standard, TQuery dari Tab BDE, TDBGrid dari Tab Data Controls, TDataSource dari Tab Data Access.

3. Klik dua kali pada Form1 kemudian pada Private Sub Form1_Load tambahkan kode program berikut ::

procedure TFParadoxBDE.FormCreate(Sender: TObject);

begin

  qContacts.Close;

  qContacts.DatabaseName := ExtractFilePath(Application.ExeName);

  qContacts.RequestLive := True;

  qContacts.SQL.Text := 'SELECT Name, Address, Phone FROM Contacts ORDER BY Name';

  qContacts.Open;

  ds.DataSet := qContacts;

  dg.DataSource := ds;

  dg.Columns[0].Width := 100;

  dg.Columns[1].Width := 100;

  dg.Columns[2].Width := 100;

end;

4. Untuk event-event yang lain, program selengkapnya sebagai berikut :

unit uContacts;

interface

uses

  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

  Dialogs, StdCtrls, Grids, DBGrids, DB, DBTables;

type

  TFParadoxBDE = class(TForm)

    qContacts: TQuery;

    ds: TDataSource;

    dg: TDBGrid;

    Label1: TLabel;

    eName: TEdit;

    Label2: TLabel;

    eAddress: TEdit;

    Label3: TLabel;

    ePhone: TEdit;

    bFirst: TButton;

    bPrev: TButton;

    bNext: TButton;

    bLast: TButton;

    bAdd: TButton;

    bEdit: TButton;

    bSave: TButton;

    bCancel: TButton;

    bRefresh: TButton;

    bDelete: TButton;

    eFind: TEdit;

    procedure FormCreate(Sender: TObject);

    procedure qContactsAfterScroll(DataSet: TDataSet);

    procedure bFirstClick(Sender: TObject);

    procedure bPrevClick(Sender: TObject);

    procedure bNextClick(Sender: TObject);

    procedure bLastClick(Sender: TObject);

    procedure bAddClick(Sender: TObject);

    procedure bEditClick(Sender: TObject);

    procedure bCancelClick(Sender: TObject);

    procedure bSaveClick(Sender: TObject);

    procedure bRefreshClick(Sender: TObject);

  private

    { Private declarations }

  public

    { Public declarations }

  end;

var

  FParadoxBDE: TFParadoxBDE;

implementation

{$R *.dfm}

procedure TFParadoxBDE.bAddClick(Sender: TObject);

begin

  qContacts.Insert;

end;

procedure TFParadoxBDE.bCancelClick(Sender: TObject);

begin

  qContacts.Cancel;

end;

procedure TFParadoxBDE.bEditClick(Sender: TObject);

begin

  qContacts.Edit;

end;

procedure TFParadoxBDE.bFirstClick(Sender: TObject);

begin

  qContacts.First;

end;

procedure TFParadoxBDE.bLastClick(Sender: TObject);

begin

  qContacts.Last;

end;

procedure TFParadoxBDE.bNextClick(Sender: TObject);

begin

  qContacts.Next;

end;

procedure TFParadoxBDE.bPrevClick(Sender: TObject);

begin

  qContacts.Prior;

end;

procedure TFParadoxBDE.bRefreshClick(Sender: TObject);

begin

  qContacts.Close;

  qContacts.SQL.Text := 'SELECT Name, Address, Phone FROM Contacts WHERE Name LIKE ''%' + eFind.Text + '%''';

  qContacts.Open;

  dg.Columns[0].Width := 100;

  dg.Columns[1].Width := 100;

  dg.Columns[2].Width := 100;

end;

procedure TFParadoxBDE.bSaveClick(Sender: TObject);

begin

  qContacts.FieldByName('Name').AsString := eName.Text;

  qContacts.FieldByName('Address').AsString := eAddress.Text;

  qContacts.FieldByName('Phone').AsString := ePhone.Text;

  qContacts.Post;

end;

procedure TFParadoxBDE.FormCreate(Sender: TObject);

begin

  qContacts.Close;

  qContacts.DatabaseName := ExtractFilePath(Application.ExeName);

  qContacts.RequestLive := True;

  qContacts.SQL.Text := 'SELECT Name, Address, Phone FROM Contacts ORDER BY Name';

  qContacts.Open;

  ds.DataSet := qContacts;

Database Design TQuery dan TTable BDE di Delphi

Untuk membuat aplikasi database menggunakan design dengan TTable & TQuery, ikuti langkah-langkah berikut :

1. Buka Code Gear Delphi. Klik menu File – New VCL Forms Application – Delphi for Win32.

2. Akan ditampilkan Form1 yang masih kosong. Tambahkan komponen TLabel Tab Standard, TQuery dari Tab BDE, TDBEdit, TDBGrid, dan TDBNavigator dari Tab Data Controls, serta TDataSource dari Tab Data Acces.

3. Kemudian set properti-propertinya sebagai berikut :

Query1          :

- DatabaseName      : <path lokasi database> atau kosongkan jika sama dengan project

- RequestLive           : True

- SQL                          : SELECT Name, Address, Phone FROM Contacts

- Active                       : True

DataSource1          

- DataSet                   : Query1

DBGrid1       

- DataSource                        : DataSource1

DBNavigator1        

- DataSource                        : DataSource1

DBEdit1

- DataSource                        : DataSource1

- DataField                : Name

DBEdit2

- DataSource                        : DataSource1

- DataField                : Address

DBEdit3

- DataSource                        : DataSource1

- DataField                : Phone

5. Klik menu Run - Run (F5) untuk menjalankan program.