Simple Query Builder using ADO Components (Views: 2141)
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. |