Koneksi Database Dengan ADO

Halaman : 2
RETRIEVE ALL DATABASE TABLES WITH ADO

// ******************************************************************
// RETRIEVE ALL DATABASE TABLES WITH ADO
// Category : ADO
// ******************************************************************

//How to retrieve all database tables with ADO

unit dbTables;

interface

uses ADODb;

type
TTableType = (ttTable, ttView, ttSynonym, ttSystemTable, ttAccessTable);

type
TTableTypes = set of TTableType;

type
TTableItem = record
ItemName: string;
ItemType: string;
end;

type
TTableItems = array of TTableItem;

function addFilter(string1, string2: string): string;
function ADODbTables(ADOConnection: TADOConnection; types: TTableTypes): TTableItems;

implementation

function addFilter(string1, string2: string): string;
begin
if string1 <> ” then
Result := string1 + ‘ or ‘ + string2
else
Result := string2;
end;

function ADODbTables(ADOConnection: TADOConnection; types: TTableTypes): TTableItems;
var
ADODataSet: TADODataSet;
i: integer;
begin
ADODataSet := TADODataSet.Create(nil);
ADODataSet.Connection := ADOConnection;
ADOConnection.OpenSchema(siTables, EmptyParam, EmptyParam, ADODataSet);

if (ttTable in types) then
ADODataSet.Filter := addFilter(ADODataSet.Filter, ‘(TABLE_TYPE = ”TABLE”)’);

if (ttView in types) then
ADODataSet.Filter := addFilter(ADODataSet.Filter, ‘(TABLE_TYPE = ”VIEW”)’);

if (ttSynonym in types) then
ADODataSet.Filter := addFilter(ADODataSet.Filter, ‘(TABLE_TYPE = ”SYNONYM”)’);

if (ttSystemTable in types) then
ADODataSet.Filter := addFilter(ADODataSet.Filter, ‘(TABLE_TYPE = ”SYSTEM TABLE”)’);

if (ttAccessTable in types) then
ADODataSet.Filter := addFilter(ADODataSet.Filter, ‘(TABLE_TYPE = ”ACCESS TABLE”)’);

ADODataSet.Filtered := True;

SetLength(Result, ADODataSet.RecordCount);

i := 0;
with ADODataSet do
begin
First;
while not EOF do
begin
with Result[i] do
begin
ItemName := FieldByName(’TABLE_NAME’).AsString;
ItemType := FieldByName(’TABLE_TYPE’).AsString;
end;
Inc(i);
Next;
end;
end;

ADODataSet.Free;
end;

end.

{
Example: create a new project and add a TADOConnection (ADOConnection1),
a TButton (Button1) and a TMemo (Memo1); assign a ConnectionString to the
TADOConnection component and set “ADOConnection1.Active := True”
}

procedure TForm1.Button1Click(Sender: TObject);
var
output: ttableitems;
i: integer;
begin
output := ADODbTables(ADOConnection1, [ttTable, ttView, ttSynonym]);
// output := ADODbTables(ADOConnection1, [ttSystemTable, ttAccessTable]);
for i := Low(output) to High(output) do
begin
Memo1.Lines.Add(output[i].ItemName + ‘—’ + output[i].ItemType);
end;
output := nil;
end;

READ THE INSTALLED ADO VERSION

// ******************************************************************
// READ THE INSTALLED ADO VERSION
// Category : ADO
// ******************************************************************

{
With different versions of MDAC available it is sometimes good to check whether
the latest version is installed to make sure everything works fine.
The following function returns the installed ADO version.
}

uses
ComObj;
function GetADOVersion: Double;
var
ADO: OLEVariant;
begin
try
ADO := CreateOLEObject(’adodb.connection’);
Result := StrToFloat(ADO.Version);
ADO := null;
except
Result := 0.0;
end;
end;

READ AN ACCESS DB USING ADO
// ******************************************************************
// READ AN ACCESS DB USING ADO
// Category : ADO
// ******************************************************************


// Read an MS-ACCESS Database (any versions) using ADO
// Verify if it is an ACCESS MDB
// Components Needed on the Application Form are:
// TADOtable,TDataSource,TOpenDialog,TDBGrid,TBitBtn.


unit uMain;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Db, DBTables, ADODB, Grids, DBGrids, ExtCtrls, DBCtrls, StdCtrls, Buttons;

type
TfrmMain = class(TForm)
DSUsers: TDataSource;
DBGridUsers: TDBGrid;
BitBtn1: TBitBtn;
OpenDialog1: TOpenDialog;
TUsers: TADOTable;
procedure FormCreate(Sender: TObject);
procedure ValidateAccessDB;
function CheckIfAccessDB(lDBPathName: string): boolean;
private
{ Private declarations }
public
{ Public declarations }
end;

var
frmMain: TfrmMain;
const
DBNAME = ‘ADODemo.MDB’;
DBPASSWORD = ‘123′; // Access DB Password Protected

implementation

{$R *.DFM}

procedure TfrmMain.FormCreate(Sender: TObject);
begin
validateAccessDB;
end;

procedure TfrmMain.ValidateAccessDB;
var
lDBpathName : String;
lDBcheck : boolean;
begin
if FileExists(ExtractFileDir(Application.ExeName) + ‘\’ + DBNAME) then
lDBPathName := ExtractFileDir(Application.ExeName) + ‘\’ + DBNAME
else if OpenDialog1.Execute then
// Set the OpenDialog Filter for ADOdemo.mdb only
lDBPathName := OpenDialog1.FileName;

lDBCheck := False;
if Trim(lDBPathName) <> ” then
lDBCheck := CheckIfAccessDB(lDBPathName);

if lDBCheck = True then
begin
// ADO Connection String to the MS-ACCESS DB
TUsers.ConnectionString :=
‘Provider=Microsoft.Jet.OLEDB.4.0;’ +
‘Data Source=’ + lDBPathName + ‘;’ +
‘Persist Security Info=False;’ +
‘Jet OLEDB:Database Password=’ + DBPASSWORD;
TUsers.TableName := ‘Users’;
TUsers.Active := True;
end
else
frmMain.Free;
end;

// Check if it is a valid ACCESS DB File Before opening it.

function TfrmMain.CheckIfAccessDB(lDBPathName: string): Boolean;
var
UnTypedFile: file of byte;
Buffer: array[0..19] of byte;
NumRecsRead: Integer;
i: Integer;
MyString: string;
begin
AssignFile(UnTypedFile, lDBPathName);
reset(UnTypedFile);
BlockRead(UnTypedFile, Buffer, High(Buffer), NumRecsRead);
CloseFile(UnTypedFile);
for i := 1 to High(Buffer) do
MyString := MyString + Trim(Chr(Ord(Buffer[i])));
Result := False;
if Mystring = ‘StandardJetDB’ then
Result := True;
if Result = False then
MessageDlg(’Invalid Access Database’, mtInformation, [mbOK], 0);
end;
end.

<- Halaman sebelumnya

Halaman selanjutnya ->