Simple Query Builder using ADO Components

Problem/Question/Abstract:

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

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.