Title: Simple guide to ADO
Question: After two years of working with ADO and with numerous applications, running day after day in a multiuser, administrative environnement , I am more content with ADO than ever. I work mainly with MS SQL Server 2000, MS Access and sometimes with good old Foxpro. I don't implement big applications (100 or more users with zillions of records) and on my scale ADO works really perfect.
A year ago I wrote some articles about ADO. Those articles are outdated by now, but are still read. That is why I wrote this article to replace some of the previous articles. I will guide you through the making of a complete basic ADO application. Let us start.
Answer:
If you want to use ADO you will mainly work with these 3 components:
1. TADOConnection : handling the connection to a database,
2. TADOQuery : to extract a dataset,
3. TADOCommand : to manipulate the database directly.
Direct use of the connectionstring property of the ADO Connect component is against everything ADO stands for: a flexible and high performance connection to a range of databases. The way to handle the connection data is via a ini-file (*.INI) or a MS Universal Data Link file (*.UDL). An important aspect of a good application is flexibility, so the best strategy is to cater for both.
Flexibility is also the reason for implementing a priority in looking for the connectiondata for a user requesting the connection. If user JOHND starts application SHOWDATA the application SHOWDATA should look first for the ini-file JOHND.INI and after that for SHOWDATA.INI. Without an ini-file my applications should terminate.
The second step is the connection file. SHOWDATA should look first for JOHND.UDL, after that for SHOWDATA.UDL and after that assume that the ini file contains the connection information.
The beauty of an ini-file is that you can change the (connection)information even with Notepad, but you have to enter them manually and ... connection information should be without typos. The UDL file is easier in that respect becuase you will use a wizard.
The UDL file can be created one way or another, but one way 'allways' works. If you want to create myapp.udl, create that file - as an empty file - with Wordpad/Notepad. Open it with Windows Explorer and you are in the UDL-wizard.
Starting with an empty mainunit.mainform and an empty dataunit.dataform.
Drop on the dataform:
a TADOconnection (called ADO_conn),
a TADOcommand (called ADO_comm),
a TADOquery (called AQ_test) and
a TDataSource (called DSAQ_test).
Enter:
'ADO_conn' in the connection property of ADO_comm and AQ_test.
'AQ_test' in the dataset property of in DSAQ_test.
'Select * from mytable' in the SQL property of AQ_test
(mytable is your test table in your test database)
----------------------------------------------------------------------
Job 1: complete mainunit.
----------------------------------------------------------------------
MAKE SURE THAT THE USES LIST OF THE INTERFACE SECTION COMPRISES:
... db, ADOdb, inifiles;
CREATE THE FOLLOWING VARIABLES:
inifilename, connectionfilenam, currentusername: string;
appinifle: tInifile;
trnsproc: boolean;
procedure mainformcreate_part2;
IN USES - LIST IN THE IMPLEMENTATION SECTION:
uses dataunit;
WRITE FORMCREATE EVENT FOR THE MAINFORM:
procedure Tmainform.FormCreate(Sender: TObject);
begin
mainunit.currentusername := mainunit.GetCurrentUserName;
mainunit.trnsproc := false;
mainunit.inifilename := mainunit.ExtractInifilename;
mainunit.connectionfilename := mainunit.ExtractConnectionfilename;
mainunit.appinifile := TIniFile.Create(mainunit.inifilename);
mainunit.mainformcreate_part2;
end;
procedure mainformcreate_part2;
begin
{
the code that changes per application
}
end;
SUPPORTING FUNCTIONS IN THE MAINFORM (Observe the priority issue)
function ExtractInifilename: string;
begin
{Priority 1. USERNAME.INI
2. APPNAME.INI
3. TERMINATE APPLICATION}
result := ExtractFilePath(ParamStr(0)) +
mainunit.currentusername + '.ini';
if not FileExists(result) then
begin
result := ChangeFileExt(Paramstr(0), '.ini');
if not FileExists(result) then
begin
showmessage('Fatal: No INI-file found');
application.Terminate;
end;
end;
end;
function ExtractConnectionfilename: string;
begin
{Priority 1. APPNAME.UDL
2. APPNAME.INI
3. USERNAME.INI}
result := ChangeFileExt(Paramstr(0), '.udl');
if not FileExists(result) then
begin
result := ChangeFileExt(Paramstr(0), '.ini');
if not FileExists(result) then result := inifilename
end;
end;
function GetCurrentUserName: string;
var
Len: Cardinal;
begin
Len := 255; { includes one char for null terminator }
SetLength(Result, Len - 1);
if GetUserName(PChar(Result), Len)
then SetLength(Result, Len - 1)
else result := 'LocalUser';
end;
----------------------------------------------------------------------
Job 2: complete dataunit - connection
----------------------------------------------------------------------
unit dataunit;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, FileCtrl,
Forms, Dialogs,Db, ADODB, inifiles, clipbrd, DBTables;
type
TDataform = class(TDataModule)
ADO_conn: TADOConnection;
ADO_comm: TADOCommand;
AQ_TEST: TADOQuery;
DSAQ_TEST: TDataSource;
procedure DataModuleCreate(Sender: TObject);
{
etc...
}
private
{ Private declarations }
public
{ Public declarations }
end;
var
Dataform: TDataform;
procedure datamodulecreate_part2;
procedure transproc(job: char);
function exec_command(commandstring: string;
do_setcommstring: boolean = true; do_parsesql: boolean = true;
do_promptparms: boolean = true; do_localtp: boolean = true;
do_exec: boolean = true; testmode: boolean = false): integer;
implementation
uses mainunit;
{$R *.DFM}
procedure TDataform.datamoduleCreate(Sender: TObject);
var
connlist: tstringlist;
connstr, errmess, temp: string;
countvar: integer;
begin
try
{we want the decision to invoke login dialog in the personal ini-
file. Gives greater flexibility per user}
temp := mainunit.appinifile.Readstring
('STARTAPPLICATION', 'LoginPrompt1', 'True');
ado_conn.LoginPrompt := (uppercase(temp) = 'TRUE');
if ExtractFileExt(mainunit.Connectionfilename) = '.UDL' then
begin
errmess := 'UDL-file problem - Connection 1';
connstr := Format('File Name=%s',[mainunit.Connectionfilename]);
end
else
begin
errmess := 'INI-file problem - Connection 1';
connlist := tstringlist.create;
appinifile.ReadSectionValues('CONNECTSTRING1', connlist);
for countvar := 0 to connlist.count - 1
do connstr := connstr + connlist.strings[countvar];
Freeandnil(connlist);
end;
dataform.ADO_conn.connected := false;
dataform.ADO_conn.connectionstring := connstr;
dataform.ADO_conn.connected := true;
temp := appinifile.Readstring(
'STARTAPPLICATION', 'Transactiemachanism', 'None');
mainunit.trnsproc := (uppercase(temp) = 'AUTOMATIC');
except
Freeandnil(connlist);
showmessage('Fatal: '+ errmess) ;
application.Terminate;
end;
datamodulecreate_part2;
end;
procedure datamodulecreate_part2;
var
errmess: string;
begin
try
{do here all other things you have to do ....}
with dataform do
begin
{open here all datasets }
errmess := 'Cannot open dataset AQ_TEST ';
aq_test.open;
end;
except
showmessage('Fatal: '+ errmess) ;
application.Terminate;
end;
end;
Remarks:
I have split the datamodulecreate (and the mainformcreate). The first part is the same for all apps while the second part is variable. I replaced my message system in this article with showmessage (in reality unacceptable!)
You could even go a step further. In my article 'Let User Create an UDL-file from within your App.' I describe how - within your application - to interface the user with the UDL-file wizard and write the UDL file after the user hits the OK button. Implement this in the except part of the datamodulecreate procedure and the whole stuff is automated.
If you only want data-aware components on you form, then don't read further because this all you have to do! Put some data aware components on the mainform and compile.
If you want to know a bit more about the connection string, go to the end of this article.
----------------------------------------------------------------------
Job 3: complete dataunit - transaction processing
----------------------------------------------------------------------
As I never use batch update via the dataset - but always with SQL statements via the command object - I do not need the transaction processing mechanism applied on single-record-user-operations via the dataset. Makes life much easier.
When updating the database directly, that is directly via the TADOcommand object or via my Comm_Exec function, the use of the transaction processing mechanism is a must.
The procedure transproc(job: char) interfaces to the transaction processing mechanism. The working of the procedure is clear, I suppose.
procedure transproc(job: char);
begin
with dataform.ado_conn do
case job of
'B':
begin
dataunit.transproc('T');
begintrans;
end;
'C':
if intransaction then committrans
else showmessage('Nothing to commit - Connection 1');
'R':
if intransaction then rollbacktrans
else showmessage('Nothing to rollback - Connection 1');
'T':
if intransaction then
begin
rollbacktrans;
showmessage('Had to rollbach active transaction - Connection 1');
end;
else showmessage('Wrong request for connection 1');
end;
end;
The exec_command function is also not difficult to understand. An errornumber is negative when the dataset was empty. The purpose of the 'do_localtp' parameter is that you can bring two or more commands in a single transaction.
Example:
transproc('B');
if exec_command(... False....) = 0 then
begin
if exec_command(... False ...)= 0
then transproc('C') else transproc('R');
end
else transproc('R') ;
function exec_command(commandstring: string;
do_setcommstring: boolean = true; do_parsesql: boolean = true;
do_promptparms: boolean = true; do_localtp: boolean = true;
do_exec: boolean = true; testmode: boolean = false): integer;
var
excomm_error, excomm_empty, teller: integer;
parm: string;
begin
if length(trim(commandstring)) = 0
then excomm_empty := -1
else excomm_empty := 1;
with dataform.ado_comm do
begin
excomm_error := 0;
result := 0;
try
excomm_error := 1;
if do_localtp then transproc('T');
excomm_error := 2;
if testmode then clipboard.settextbuf(pchar(commandstring));
excomm_error := 3;
if do_setcommstring then commandtext := commandstring;
excomm_error := 4;
if do_parsesql then parameters.parsesql(commandstring, true);
excomm_error := 5;
if (not do_promptparms) and (not do_exec) then exit;
if do_promptparms then
begin
excomm_error := 6;
if parameters.count 0
then begin
for teller := 0 to parameters.count - 1 do
begin
parm := '';
if not inputquery(
'Enter parameter',parameters.items[teller].name, parm) then
begin
Showmessage('Aborted by user - No parameters');
excomm_error := 7;
result := excomm_error * excomm_empty;
exit;
end;
parameters[teller].value := parm;
end;
end;
end;
if do_exec then
begin
excomm_error := 8;
if (mainunit.trnsproc) and (do_localtp) then transproc('B');
excomm_error := 9;
execute;
if (mainunit.trnsproc) and (do_localtp) then transproc('C');
end;
excomm_error := 0 ; {task completed/no error}
except
if (do_localtp) then transproc('R');
end;
end;
result := excomm_error * excomm_empty;
end;
end.
----------------------------------------------------------------------
Example of the use of the command object
----------------------------------------------------------------------
General advice: don't try to make a command string with variables and/or fieldvalues by 'knitting' everything into a single string. In simple examples it looks fine, but in real life there is not much that simple. Use always parameters instead.
Procedure
Z1: We work directly on the ADO command object.
The values are provided in the program (one way or another).
We have to arrange the transaction processing mechanism.
Z2: We work with exec_comm function (see above).
The values are provided in the program.
This is the practical way of working.
Z3: We work with exec_comm function (see above).
The values are prompted.
Z1 and Z2 are equivalent. Z3 is different.
procedure Z1;
var
number,title,author,commtext: string;
begin
number := '1-55622-758-2' ;
title := 'Developers Guide to ADO' ;
author := 'Fedorov and Elmanove';
commtext := 'insert into table1 (ISBN,TITLE,AUTHOR) ' +
'values (:isbnnumber,:booktitle,:author)' ;
dataunit.ado_comm.commandtext := commtext ;
dataunit.ado_comm.parameters.parsesql(commtext,true);
dataunit.ado_comm.parameters[0].value := number ;
dataunit.ado_comm.parameters[1].value := title ;
dataunit.ado_comm.parameters[2].value := author ;
try
transproc('B');
dataunit.ado_comm.execute;
transproc('C');
except
showmessage('Transaction aborted');
transproc('R');
end;
end;
procedure Z2;
var
number,title,author,commtext: string;
begin
number := '1-55622-758-2' ;
title := 'Developers Guide to ADO' ;
author := 'Fedorov and Elmanove';
commtext := 'insert into table1 (ISBN,TITLE,AUTHOR) ' +
'values (:isbnnumber,:booktitle,:author)' ;
if dataunit.exec_comm(commtext,true,true,false,false,false,false)= 0 then
{adding the command string and parsing}
begin
dataunit.ado_comm.parameters[0].value := number ;
dataunit.ado_comm.parameters[1].value := title ;
dataunit.ado_comm.parameters[2].value := author ;
dataunit.exec_comm('',false,false,false,true,true,false);
{execute the command with transaction procesing}
end;
end;
procedure Z3;
var
commtext: string;
begin
commtext := 'insert into table1 (ISBN,TITLE,AUTHOR) ' +
'values (:isbnnumber,:booktitle,:author)' ;
dataunit.exec_comm(commtex)
{ the whole buch incl. prompting the parameters}
end;
----------------------------------------------------------------------
The connectionstring
----------------------------------------------------------------------
The connection part of the ini-file and the udl-file are in principle the same. The most obvious difference is, that the udl-file inserts a space between to udl-file inserts
Example of a ini-file to an Access database NOPDATA.MDB, residing in the folder F:\TEST\NOP\
[CONNECTSTRING]
Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=F:\TEST\NOP\NOPDATA.MDB;
Mode=Read|Write|Share Deny None;
Persist Security Info=False
The same connection in a udl-file is:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=F:\TEST\NOP\NOPDATA.MDB;
Mode=Read|Write|Share Deny None;
Persist Security Info=False
A udl-file to a SQL server 2000 database is:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;
Persist Security Info=False;
User ID=sa;
Initial Catalog=DEMOSQLBUILDER;
Data Source=hanspiet
It is easy to learn what should be in to connection string for your favourite database by doing the following:
Wordpad: make empty TEST.TXT and rename to TEST.UDL
Explorer: open TEST.UDL
Build an udl-file to your database. Use Help if needed.
Test connection and save.
Wordpad: Rename TEST.UDL in TEST.TXT and open with Wordpad.
You could eventually cut and paste the connectionstring
into an ini-file.