Simple Query Builder using ADO Components?

Writing a simple query builder using ADO Components.

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.



Thanks.

Magesh.

 

Share this article!

Follow us!

Find more helpful articles: