Title: Simple Query Builder using ADO Components
Question: 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, itll 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, itll 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 itll 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
program ADODemo;
uses
Forms,
UADODemo1 in 'UADODemo1.pas' {frmADODemo},
ULogin in 'ULogin.pas' {frmLogin};
{$R *.res}
begin
Application.Initialize;
Application.Title := 'ADO Demo';
Application.CreateForm(TfrmADODemo, frmADODemo);
Application.CreateForm(TfrmLogin, frmLogin);
Application.Run;
end.
Unit File 1: UADODemo1.pas
unit UADODemo1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, DBTables, ADODB, ExtCtrls, Buttons, Grids, DBGrids,
ComCtrls,Inifiles, Menus;
Const
WinNTOr2000 = 'C:\WinNT\';
Win95Or98 = 'C:\Windows\';
ODBCDataSources = 'ODBC 32 bit Data Sources';
type
TfrmADODemo = class(TForm)
pnlClientPanel: TPanel;
lblDataSources: TLabel;
lbxDataSources: TListBox;
lblTables: TLabel;
lbxTables: TListBox;
lblFields: TLabel;
lbxFields: TListBox;
lblProcedures: TLabel;
lbxProcedures: TListBox;
memQueryText: TMemo;
lblQueryText: TLabel;
bitExecute: TBitBtn;
bitClose: TBitBtn;
bitSaveQuery: TBitBtn;
dbgResultData: TDBGrid;
lblQueryResult: TLabel;
ADOConnection: TADOConnection;
sbrStatusBar: TStatusBar;
popRefresh: TPopupMenu;
mitRefresh: TMenuItem;
DlgSaveDialog: TSaveDialog;
ADOQuery1: TADOQuery;
procedure FormCreate(Sender: TObject);
procedure lbxDataSourcesClick(Sender: TObject);
procedure bitSaveQueryClick(Sender: TObject);
procedure lbxTablesClick(Sender: TObject);
procedure bitExecuteClick(Sender: TObject);
procedure FormDestroy(Sender: TObject);
private
function ODBCPath : String;
{ Private declarations }
public
{ Public declarations }
end;
var
frmADODemo: TfrmADODemo;
DSNSelectedIndex : Integer;
implementation
uses ULogin;
{$R *.dfm}
procedure TfrmADODemo.FormCreate(Sender: TObject);
//Loading the Data source names
var
DataSources : TStringList;
ODBCIniFile : TIniFile;
begin
DSNSelectedIndex := 0;
DataSources := TStringList.Create;
ODBCIniFile := TIniFile.Create(ODBCPath + 'ODBC.INI');
ODBCIniFile.ReadSection(ODBCDataSources,DataSources);
lbxDataSources.Items.Assign(DataSources);
end;
function TfrmADODemo.ODBCPath;
//Finding the location of ODBC.INI file
var
OSVersionInfo : TOSVersionInfo;
begin
OSVersionInfo.dwOSVersionInfoSize := SizeOf(OSVersionInfo);
if GetVersionEx(OSVersionInfo) then
begin
if ((OSVersionInfo.dwMajorVersion = 5) or (OSVersionInfo.dwMajorVersion = 4)) and
(OSVersionInfo.dwMinorVersion = 0) and
(OSVersionInfo.dwPlatformId = VER_PLATFORM_WIN32_NT) then
ODBCPath := WinNTOr2000
else if (OSVersionInfo.dwPlatformId = VER_PLATFORM_WIN32_NT) then
ODBCPath := WinNTOr2000
else if (OSVersionInfo.dwPlatformId = 3) and (OSVersionInfo.dwMinorVersion = 51)
and (OSVersionInfo.dwPlatformId = VER_PLATFORM_WIN32_NT) then
ODBCPath := WinNTOr2000
else
ODBCPath := Win95Or98;
end;
end;
procedure TfrmADODemo.lbxDataSourcesClick(Sender: TObject);
begin
//If any connection is open, then close it first
if ADOConnection.Connected then
ADOConnection.Close;
//Showing the Database Login Dialog box
frmLogin.edtName.Clear;
frmLogin.edtPassword.Clear;
if frmLogin.ShowModal = mrOk then
begin
try
Screen.Cursor := crHourGlass;
ADOConnection.ConnectionString := 'User ID=' + frmLogin.edtName.Text + ';Password=' + frmLogin.edtPassword.Text + ';Data Source=' + lbxDataSources.Items[lbxDataSources.ItemIndex];
ADOConnection.Connected := True;
DSNSelectedIndex := lbxDataSources.ItemIndex;
lbxTables.Clear;
lbxProcedures.Clear;
lbxFields.Clear;
ADOConnection.GetTableNames(lbxTables.Items);
ADOConnection.GetProcedureNames(lbxProcedures.Items);
Screen.Cursor := crDefault;
except
Screen.Cursor := crDefault;
lbxTables.Clear;
lbxProcedures.Clear;
lbxFields.Clear;
MessageDlg('Unable to Connect to ' + lbxDataSources.Items[lbxDataSources.ItemIndex],mtInformation,[mbOk],0);
end;
end
else
begin
lbxDataSources.Selected[DSNSelectedIndex] := True;
end;
end;
procedure TfrmADODemo.bitSaveQueryClick(Sender: TObject);
//Saving the typed query into a text file
begin
if DlgSaveDialog.Execute then
memQueryText.Lines.SaveToFile(DlgSaveDialog.FileName);
end;
procedure TfrmADODemo.lbxTablesClick(Sender: TObject);
//Getting the Field names while clicking the table names
begin
lbxFields.Clear;
ADOConnection.GetFieldNames(lbxTables.Items[lbxTables.ItemIndex],lbxFields.Items);
end;
procedure TfrmADODemo.bitExecuteClick(Sender: TObject);
//Executing the query
begin
try
if (ADOConnection.Connected) and (Trim(memQueryText.Lines.Text) '') then
begin
ADOQuery1.Connection := ADOConnection;
ADOQuery1.SQL.AddStrings(memQueryText.Lines);
ADOQuery1.ExecSQL;
dbgResultData.DataSource.DataSet := ADOQuery1.DataSource.DataSet;
end;
except
MessageDlg('Error Showing Data',mtInformation,[mbOk],0);
end;
end;
procedure TfrmADODemo.FormDestroy(Sender: TObject);
//Closing the ADO Connection if it is connected
begin
if ADOConnection.Connected then
ADOConnection.Close;
end;
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
unit ULogin;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ExtCtrls, ComCtrls, StdCtrls, Buttons;
type
TfrmLogin = class(TForm)
pnlClient: TPanel;
lblName: TLabel;
lblPassword: TLabel;
edtName: TEdit;
edtPassword: TEdit;
sbrStatusBar: TStatusBar;
bitOK: TBitBtn;
bitClose: TBitBtn;
procedure FormShow(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
frmLogin: TfrmLogin;
implementation
{$R *.dfm}
procedure TfrmLogin.FormShow(Sender: TObject);
begin
edtName.SetFocus;
end;
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.
Thanks.
Magesh.