Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
-Delphi/Pascal
-CBuilder/C++
-C#Builder/C#
-JBuilder/Java
-Kylix
Member Area
-Home
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Login/Logout
-Become a Member
-Why sign up!
-Newsletter
-Chat Online!
-Indexes NEW!!
Employment
-Build your resume
-Find a job
-Post a job
-Resume Search
Contacts
-Contacts
-Feedbacks
-Link to us
-Privacy/Disclaimer
Embarcadero
Visit Embarcadero
Embarcadero Community
JEDI
Links
How to write a simple query builder using ADO Components. Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
31-Oct-03
Category
DB-General
Language
Delphi 5.x
Views
175
User Rating
No Votes
# Votes
0
Replies
0
Publisher:
DSP, Administrator
Reference URL:
DKB
			Author: S S B Magesh Puvananthiran

Writing a simple query builder using ADO Components.

Answer:

This article is intended to demonstrate how can we use the ADO components available 
in Delphi. 

I have written a simple application using ADO components to retrieve the Data 
Source Names, Table Names, Field Names, Procedure Names and an option to write 
query and execute it and display the result in a grid. 

The function of the application: 

When you run the application, itíll fetch all the ODBC Data Source Names from the 
current system and list in a list box. If you select a Data Source Name, you will 
be asked to enter the user name and password. Once you enter the right user name 
and password, the tables and procedures available in the data source. And if you 
click on a table name, all the fields in the table will be listed. 

And in the memo field, you can enter SQL query and click on the Execute button, 
itíll execute the query and display the result in the grid below. 

Also you can save the query to a text file if you click on the Save button. 

And in the Data Source Names list box, if you right click, there will be a Refresh 
menu and itíll refresh the ODBC Data Source Names. 

This is really a simple version of Query Builder and we can add as many features as 
possible and just wanted to share you people. 

Following is the complete code for the application: 

Project file: ADODemo.dpr

1   program ADODemo;
2   uses
3     Forms,
4     UADODemo1 in 'UADODemo1.pas' {frmADODemo},
5     ULogin in 'ULogin.pas' {frmLogin};
6   
7   {$R *.res}
8   
9   begin
10    Application.Initialize;
11    Application.Title := 'ADO Demo';
12    Application.CreateForm(TfrmADODemo, frmADODemo);
13    Application.CreateForm(TfrmLogin, frmLogin);
14    Application.Run;
15  end.
16  
17  unit file 1: UADODemo1.pas 
18  
19  unit UADODemo1;
20  
21  interface
22  
23  uses
24    Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
25    Dialogs, StdCtrls, DB, DBTables, ADODB, ExtCtrls, Buttons, Grids, DBGrids,
26    ComCtrls, Inifiles, Menus;
27  
28  const
29    WinNTOr2000 = 'C:\WinNT\';
30    Win95Or98 = 'C:\Windows\';
31    ODBCDataSources = 'ODBC 32 bit Data Sources';
32  type
33    TfrmADODemo = class(TForm)
34      pnlClientPanel: TPanel;
35      lblDataSources: TLabel;
36      lbxDataSources: TListBox;
37      lblTables: TLabel;
38      lbxTables: TListBox;
39      lblFields: TLabel;
40      lbxFields: TListBox;
41      lblProcedures: TLabel;
42      lbxProcedures: TListBox;
43      memQueryText: TMemo;
44      lblQueryText: TLabel;
45      bitExecute: TBitBtn;
46      bitClose: TBitBtn;
47      bitSaveQuery: TBitBtn;
48      dbgResultData: TDBGrid;
49      lblQueryResult: TLabel;
50      ADOConnection: TADOConnection;
51      sbrStatusBar: TStatusBar;
52      popRefresh: TPopupMenu;
53      mitRefresh: TMenuItem;
54      DlgSaveDialog: TSaveDialog;
55      ADOQuery1: TADOQuery;
56      procedure FormCreate(Sender: TObject);
57      procedure lbxDataSourcesClick(Sender: TObject);
58      procedure bitSaveQueryClick(Sender: TObject);
59      procedure lbxTablesClick(Sender: TObject);
60      procedure bitExecuteClick(Sender: TObject);
61      procedure FormDestroy(Sender: TObject);
62    private
63      function ODBCPath: string;
64      { Private declarations }
65    public
66      { Public declarations }
67    end;
68  
69  var
70    frmADODemo: TfrmADODemo;
71    DSNSelectedIndex: Integer;
72  
73  implementation
74  
75  uses ULogin;
76  
77  {$R *.dfm}
78  
79  procedure TfrmADODemo.FormCreate(Sender: TObject);
80  //Loading the Data source names
81  var
82    DataSources: TStringList;
83    ODBCIniFile: TIniFile;
84  begin
85    DSNSelectedIndex := 0;
86    DataSources := TStringList.Create;
87    ODBCIniFile := TIniFile.Create(ODBCPath + 'ODBC.INI');
88    ODBCIniFile.ReadSection(ODBCDataSources, DataSources);
89    lbxDataSources.Items.Assign(DataSources);
90  end;
91  
92  function TfrmADODemo.ODBCPath;
93  //Finding the location of ODBC.INI file
94  var
95    OSVersionInfo: TOSVersionInfo;
96  begin
97    OSVersionInfo.dwOSVersionInfoSize := SizeOf(OSVersionInfo);
98    if GetVersionEx(OSVersionInfo) then
99    begin
100     if ((OSVersionInfo.dwMajorVersion = 5) or (OSVersionInfo.dwMajorVersion = 4)) 
101 and
102       (OSVersionInfo.dwMinorVersion = 0) and
103       (OSVersionInfo.dwPlatformId = VER_PLATFORM_WIN32_NT) then
104       ODBCPath := WinNTOr2000
105     else if (OSVersionInfo.dwPlatformId = VER_PLATFORM_WIN32_NT) then
106       ODBCPath := WinNTOr2000
107     else if (OSVersionInfo.dwPlatformId = 3) and (OSVersionInfo.dwMinorVersion = 51)
108       and (OSVersionInfo.dwPlatformId = VER_PLATFORM_WIN32_NT) then
109       ODBCPath := WinNTOr2000
110     else
111       ODBCPath := Win95Or98;
112   end;
113 end;
114 
115 procedure TfrmADODemo.lbxDataSourcesClick(Sender: TObject);
116 begin
117   //If any connection is open, then close it first
118   if ADOConnection.Connected then
119     ADOConnection.Close;
120   //Showing the Database Login Dialog box
121   frmLogin.edtName.Clear;
122   frmLogin.edtPassword.Clear;
123   if frmLogin.ShowModal = mrOk then
124   begin
125     try
126       Screen.Cursor := crHourGlass;
127       ADOConnection.ConnectionString := 'User ID=' + frmLogin.edtName.Text +
128         ';Password=' + frmLogin.edtPassword.Text + ';Data Source=' +
129         lbxDataSources.Items[lbxDataSources.ItemIndex];
130       ADOConnection.Connected := True;
131       DSNSelectedIndex := lbxDataSources.ItemIndex;
132       lbxTables.Clear;
133       lbxProcedures.Clear;
134       lbxFields.Clear;
135       ADOConnection.GetTableNames(lbxTables.Items);
136       ADOConnection.GetProcedureNames(lbxProcedures.Items);
137       Screen.Cursor := crDefault;
138     except
139       Screen.Cursor := crDefault;
140       lbxTables.Clear;
141       lbxProcedures.Clear;
142       lbxFields.Clear;
143       MessageDlg('Unable to Connect to  ' +
144         lbxDataSources.Items[lbxDataSources.ItemIndex], mtInformation, [mbOk], 0);
145     end;
146   end
147   else
148   begin
149     lbxDataSources.Selected[DSNSelectedIndex] := True;
150   end;
151 end;
152 
153 procedure TfrmADODemo.bitSaveQueryClick(Sender: TObject);
154 //Saving the typed query into a text file
155 begin
156   if DlgSaveDialog.Execute then
157     memQueryText.Lines.SaveToFile(DlgSaveDialog.FileName);
158 end;
159 
160 procedure TfrmADODemo.lbxTablesClick(Sender: TObject);
161 //Getting the Field names while clicking the table names
162 begin
163   lbxFields.Clear;
164   ADOConnection.GetFieldNames(lbxTables.Items[lbxTables.ItemIndex], 
165 lbxFields.Items);
166 end;
167 
168 procedure TfrmADODemo.bitExecuteClick(Sender: TObject);
169 //Executing the query
170 begin
171   try
172     if (ADOConnection.Connected) and (Trim(memQueryText.Lines.Text) <> '') then
173     begin
174       ADOQuery1.Connection := ADOConnection;
175       ADOQuery1.SQL.AddStrings(memQueryText.Lines);
176       ADOQuery1.ExecSQL;
177       dbgResultData.DataSource.DataSet := ADOQuery1.DataSource.DataSet;
178     end;
179   except
180     MessageDlg('Error Showing Data', mtInformation, [mbOk], 0);
181   end;
182 end;
183 
184 procedure TfrmADODemo.FormDestroy(Sender: TObject);
185 //Closing the ADO Connection if it is connected
186 begin
187   if ADOConnection.Connected then
188     ADOConnection.Close;
189 end;
190 
191 end.


Whenever we select a Data Source Name from the list box, a database login dialog 
will come up asking us to enter the user name and password for that DSN and once we 
enter the correct user name and password, we will be logged in and the 
tables,procedures will be listed. 

Unit File 2: Ulogin.pas 

192 unit ULogin;
193 
194 interface
195 
196 uses
197   Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
198   Dialogs, ExtCtrls, ComCtrls, StdCtrls, Buttons;
199 
200 type
201   TfrmLogin = class(TForm)
202     pnlClient: TPanel;
203     lblName: TLabel;
204     lblPassword: TLabel;
205     edtName: TEdit;
206     edtPassword: TEdit;
207     sbrStatusBar: TStatusBar;
208     bitOK: TBitBtn;
209     bitClose: TBitBtn;
210     procedure FormShow(Sender: TObject);
211   private
212     { Private declarations }
213   public
214     { Public declarations }
215   end;
216 
217 var
218   frmLogin: TfrmLogin;
219 
220 implementation
221 
222 {$R *.dfm}
223 
224 procedure TfrmLogin.FormShow(Sender: TObject);
225 begin
226   edtName.SetFocus;
227 end;
228 
229 end.


I have not included the .dfm files with this; but hope you can easily find out the 
components I have used using the .pas files. 

Even though there are so many query builders available, I just wanted to try with ADO components from Delphi and going to expand this by adding more features. I am very glad to welcome your ideas on this. 

			
Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10

 

Advertisement
Share this page
Advertisement
Download from Google

Copyright © Mendozi Enterprises LLC