Talk to a MSAccess database thru Dot Net

A working Delphi for Dot Net example that talks to an MSAccess
database and displays one result. This code should properly
demonstrate the use of Dot Net Components to retreive Data from
MS Access. Other examples I’ve seen are Internet based and do not
function correctly.

Any Comments to : Andychap@hotmail.com
Please put DOT NET in the subject so I can
Junk filter.

//——————————————————————–
// Main program refers to the unit below. Make sure of the
// program paths!!!!!
//——————————————————————–

//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// Simple Access Database demonstration
//
// Written by Andy Chapman (AndyChap@hotmail.com)
// Copyright (C) ACMIS Software , 2003
//
// For all you Delphi lovers here’s a complete example of reading
// an MSAccess database useing Dot Net notation. Most of the examples
// found on the net refered to internet and IIS applications but this
// one runs as a true dot net implementation. With thanks to all the
// articles posted on the net… you know who you are.
//
// Program DBTest uses Newform2 as its inclusion. This form is where
// all the work is done. Please pass comment to the EMail address above
// and mark in the Subject Line DOT NET or my mail filter will kill it
//
// Regards : Andrew Chapman

 

program DBTest;

uses
System.Windows.Forms,
Newform2 in 'c:\tempnet\Newform2.pas' {Form1};

begin
Mainform := TForm1.Create;
Application.Run(Mainform);
end.

//--------------------------------------------------------------------
// Unit refered to by the main program. Make sure your database
// path is set correctly
//--------------------------------------------------------------------

unit NewForm2;

interface

uses
{ Just like the old uses clause in a delphi program. The SYSTEM namespace refers to windows
type controls and not specifically the delphi ones. To draw a delphi button call on the
BORLAND.VCL name spaces... see below }

System.Reflection,

System.Drawing,
System.Drawing.Text,
System.ComponentModel,
System.Windows.Forms,
System.Data.OleDB,
System.Data,
System.Data.Common,
System.Runtime.InteropServices;

type
TForm1 = class(Form)
private
buttonload: system.windows.forms.button; // a button
Components: system.componentmodel.container; // a component store
datagrid1: system.windows.forms.datagrid; // not used in this implementation
public
constructor Create; // which I will inherite and amend
procedure InitializeComponents; // easy way to centralise component creation
procedure Button1_Click(Sender: TObject; E: EventArgs); // on click event
end;

var
MainForm: TForm1; // as ever a main delphi form

implementation

constructor TForm1.Create;
begin
inherited Create; // normal create stuff then set up all the required components
InitializeComponents; // sets up components
end;

procedure TForm1.InitializeComponents;
var
MyControls: array[0..2] of control; // container class for main form
begin
Self.ClientSize := system.Drawing.Size.Create(600,413); // client window on screen
Self.Components := System.ComponentModel.Container.Create();
// container class for the other bits
Self.buttonload := system.windows.forms.button.Create(); // make a button
Self.buttonload.add_click(button1_click); // set its on click event
Self.buttonload.Size := system.drawing.Size.Create(112,32); // size up the button
Self.buttonload.location := system.drawing.point.Create(480,352); // where on screen ?
Self.buttonload.Text := 'Read the database';
// text on the button - 'caption' in real delphi

Self.datagrid1 := system.windows.forms.datagrid.Create(); // draw a datagrid - not used
Self.datagrid1.Size := system.drawing.Size.Create(584,336);
Self.datagrid1.location := system.drawing.point.Create(8,8);

MyControls[0] := Self.buttonload; // add button to container class
MyControls[1] := Self.datagrid1; // add grid to container class
Self.Controls.AddRange(MyControls);
// basically add them to the form, form is now parent
end;

procedure TForm1.Button1_Click(Sender: TObject; E: EventArgs);
var
dbConnection: oleDBConnection;
dbCommand: OleDBCommand;
dbReader: OleDBDataReader;
dbDataAdapter: OleDBDataAdapter;
dbDataset: Dataset;
temp, temp1: string;
int1: Integer;
begin
temp := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb'; // connection string
temp1 := 'Select * from Shawbury'; // SQL code to fire

(* Fount that I had to use an explicit string to make the connection , The exapmple code
was'nt very clear on this - it works so hopefully it's the right solution *)
dbConnection := System.Data.OleDB.OleDbConnection.Create(temp); // make a DB Connection
dbConnection.Open(); // open a DB Conection
dbCommand := System.Data.OleDB.OleDbCommand.Create(temp1, dbConnection);
// execute the SQL
dbReader := dbCommand.ExecuteReader(); // and store in a datareader

int1 := dbReader.GetOrdinal('subcol1');
// I have a coloum in the Database called subcol1

while dbReader.read() do // keep reading all records
begin
// gives you a warm feeling to see the last record on the button
// - now I'm sure its read the file
buttonload.Text := dbreader.GetValue(int1).tostring;
end;
end;

end.