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.
|
