17.5.2 设计一个数据库查询器 例17.2:在数据库查询器中,用户可以选择要查询的数据库,查询数据库中的那一个表、根据数据库表中那一个字段进行查询,并且可以方便地指定查询条件,指定查询条件主要包括指定逻辑运算符(=、>、<、<=、>=、like、in、NOT like、NOT in)和字段值。 例子全部的程序清单如下: unit main; interface uses SysUtils, Windows, Messages, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls, DB, DBTables, Buttons, ComCtrls, Tabnotbk; type TQueryForm = class(TForm) BitBtn1: TBitBtn; DataSource1: TDataSource; Table1: TTable; GroupBox1: TGroupBox; CheckBox1: TCheckBox; CheckBox2: TCheckBox; PageControl1: TPageControl; TabSheet1: TTabSheet; Label5: TLabel; Label1: TLabel; Label2: TLabel; Label3: TLabel; Label4: TLabel; ListBox1: TListBox; ListBox2: TListBox; ListBox3: TListBox; Edit1: TEdit; ComboBox1: TComboBox; BitBtn2: TBitBtn; TabSheet2: TTabSheet; Memo1: TMemo; procedure FormCreate(Sender: TObject); procedure ListBox1Click(Sender: TObject); procedure ListBox2Click(Sender: TObject); procedure BitBtn2Click(Sender: TObject); end; var QueryForm: TQueryForm; implementation {$R *.DFM} uses RSLTFORM; procedure TQueryForm.FormCreate(Sender: TObject); begin Screen.Cursor := crHourglass; { Populate the alias list } with ListBox1 do begin Items.Clear; Session.GetAliasNames(Items); end; { Make sure there are aliases defined } Screen.Cursor := crDefault; if ListBox1.Items.Count < 1 then MessageDlg( 'There are no database aliases currently defined. You need at least one alias to use this demonstration.', mtError, [mbOK], 0 ); { Default the drop-down list to the first value in the list } ComboBox1.ItemIndex := 0; end; procedure TQueryForm.ListBox1Click(Sender: TObject); var strValue: string; { Holds the alias selected by the user } bIsLocal: Boolean; { Indicates whether or not an alias is local } slParams: TStringList; { Holds the parameters of the selected alias } iCounter: Integer; { An integer counter variable for loops} begin { Determine the alias name selected by the user } with ListBox1 do strValue := Items.Strings[ItemIndex]; { Get the names of the tables in the alias and put them in the appropriate list box, making sure the user's choices are reflected in the list. } ListBox2.Items.Clear; Session.GetTableNames(strValue, { alias to enumerate } '', { pattern to match } CheckBox1.Checked, { show extensions flag } CheckBox2.Checked, { show system tables flag } ListBox2.Items); { target for table list } { Make sure there are tables defined in the alias. If not, show an error; otherwise, clear the list box. } Screen.Cursor := crDefault; if ListBox2.Items.Count < 1 then MessageDlg('There are no tables in the alias you selected. Please choose another', mtError, [mbOK], 0 ); ListBox3.Items.Clear; end; procedure TQueryForm.ListBox2Click(Sender: TObject); begin Screen.Cursor := crHourglass; try { First, disable the TTable object. } if Table1.Active then Table1.Close; { Open the selected table } with ListBox1 do Table1.DatabaseName := Items.Strings[ItemIndex]; with ListBox2 do Table1.TableName := Items.Strings[ItemIndex]; { Open the table and put a list of the field names in the Fields list box. } Table1.Open; if Table1.Active then Table1.GetFieldNames(ListBox3.Items); finally Screen.Cursor := crDefault; end; end; procedure TQueryForm.BitBtn2Click(Sender: TObject); var strAlias, { Alias name selected by the user } strTable, { Table name selected by the user } strField, { Field name selected by the user } strValue, { Field Value entered by the user } strWhere, { WHERE clause for the user's query } strQuote, { Holds quotes is the query field is text } strQuery: string; { String used to construct the query } frmQuery: TResultForm; { The Results form } type { The following type is used with the Type drop-down list. The text values corresponding with each item is described in comments, along with the relevant SQL operators. } etSQLOps = (soNoCondition, { not field conditions: no WHERE clause } soEqual, { equals: = } soNotEqual, { is not equal to: <> } soLessThan, { is less than: < } soLessEqual, { is less than or equal to: <= } soMoreThan, { is greater than: > } soMoreEqual, { is greater than or equal to: >= } soStartsWith, { starts with: LIKE xx% } soNoStartsWith, { doesn't start with: NOT LIKE xx% } soEndsWith, { ends with: LIKE %xx } soNoEndsWith, { doesn't end with: NOT LIKE %xx } soContains, { contains: LIKE %xx% } soNoContains, { doesn't contain: NOT LIKE %xx% } soBlank, { is blank: } soNotBlank, { is not blank: } soInside, { contains only: IN ( xx, yy, zz ) } soOutside); { doesn't contain: NOT IN (xx, yy, zz) } begin { Initialize the variables needed to run the query } with ListBox1 do if ItemIndex = -1 then raise Exception.Create('Can''t Run Query: No Alias Selected') else strAlias := Items.Strings[ItemIndex]; with ListBox2 do if ItemIndex = -1 then raise Exception.Create('Can''t Run Query: No Table Selected') else strTable := Items.Strings[ItemIndex]; with ListBox3 do if ItemIndex = -1 then begin if ComboBox1.ItemIndex > Ord(soNocondition) then raise Exception.Create('Can''t Run Query: No Field Selected') else strField := ''; end else strField := Items.Strings[ItemIndex]; if (Edit1.Text = '') and (ComboBox1.ItemIndex > Ord(soNoCondition)) and (ComboBox1.ItemIndex < Ord(soBlank)) then raise Exception.create('Can''t Run Query: No Search Value Entered') else strValue := Edit1.Text; { See if the field being search is a string field. If so, then pad the quote string with quotation marks; otherwise, set it to a null value. } if strField <> '' then with Table1.FieldByName(strField) do if (DataType = ftString) or (DataType = ftMemo) then strQuote := '"' else strQuote := ''; { Construct the WHERE clause of the query based on the user's choice in Type. } case etSQLOps(ComboBox1.ItemIndex) of soNoCondition: strWhere := ''; soEqual: strWhere := strField + ' = ' + strQuote + strValue+ strQuote; soNotEqual: strWhere := strField + ' <> ' + strQuote + strValue + strQuote; soLessThan: strWhere := strField + ' < ' + strQuote + strValue + strQuote; soLessEqual: strWhere := strField + ' <= ' + strQuote + strValue + strQuote; soMoreThan: strWhere := strField + ' > ' + strQuote + strValue + strQuote; soMoreEqual: strWhere := strField + ' >= ' + strQuote + strValue + strQuote; soStartsWith: strWhere := strField + ' LIKE ' + strQuote + strValue + '%' + strQuote; soNoStartsWith: strWhere := strField + ' NOT LIKE ' + strQuote + strValue + '%' + strQuote; soEndsWith: strWhere := strField + ' LIKE ' + strQuote + '%' + strValue + strQuote; soNoEndsWith: strWhere := strField + ' NOT LIKE ' + strQuote + '%' + strValue + strQuote; soContains: strWhere := strField + ' LIKE '+ strQuote+'%'+ strValue + '%' + strQuote; soNoContains: strWhere := strField + ' NOT LIKE ' + strQuote + '%' + strValue + '%' + strQuote; soBlank: strWhere := strField + ' IS NULL'; soNotBlank: strWhere := strField + ' IS NOT NULL'; end; if ComboBox1.ItemIndex = Ord(soNoCondition) then strQuery := 'SELECT * FROM "' + strTable + '"' else if Table1.FieldByName(strField).DataType = ftString then strQuery := 'SELECT * FROM "' + strTable + '" t WHERE t.' + strWhere else strQuery := 'SELECT * FROM "' + strTable + '" t WHERE t.' + strWhere; { Create an instance of the browser form. } frmQuery := TResultForm.Create(Application); { Use a resource protection block in case an exception is raised. This ensures that the memory allocated for the Results form is released. } try with frmQuery do begin Screen.Cursor := crHourglass; if Query1.Active then Query1.Close; Query1.DatabaseName := strAlias; {set the alias the query poitns to} Query1.SQL.clear; { empty existing SQL in the query } Query1.SQL.Add(strQuery); { add query string to query object } Query1.Active := True; { try to run the query } Screen.Cursor := crDefault; if Query1.Active then begin { If the query didn't return any records, there's no point in displaying the form. In that event, raise an exception. } if Query1.RecordCount < 1 then raise Exception.create('No records matched your criteria. Please try again.' ); { write a message to the browse form's status line } if strField = '' then Panel3.Caption := 'Now showing all records from ' + strTable + '...' else Panel3.Caption := 'Now showing '+ strTable +' where '+ strField +' contains values equal to '+ strValue + '...'; { show the form } ShowModal; end; end; finally frmQuery.Free; end; end; end. unit RSLTFORM; interface uses SysUtils, Windows, Messages, Classes, Graphics, Controls, StdCtrls, DB, Forms, DBCtrls, DBGrids, DBTables, Buttons, Grids, ExtCtrls, Dialogs; type TResultForm = class(TForm) DBGrid1: TDBGrid; DBNavigator: TDBNavigator; Panel1: TPanel; DataSource1: TDataSource; Panel2: TPanel; Panel3: TPanel; Query1: TQuery; SpeedButton2: TSpeedButton; Panel4: TPanel; SpeedButton1: TSpeedButton; procedure SpeedButton1Click(Sender: TObject); procedure SpeedButton2Click(Sender: TObject); end; var ResultForm: TResultForm; implementation {$R *.DFM} procedure TResultForm.SpeedButton1Click(Sender: TObject); begin Close; end; procedure TResultForm.SpeedButton2Click(Sender: TObject); var strText: string; { Variable to hold display text } iCounter: Integer; { Loop counter variable } begin { Build a string containing the query } strText := ''; for iCounter := 0 to Query1.SQL.Count - 1 do strText := strText + Query1.SQL[iCounter]; { Display the query text } MessageDlg('The underlying query is: ' + #10 + #10 + strText, mtInformation, [mbOK], 0 ); end; end.  
2/2 首页 上一页 1 2 |