第十七章 SQL编程(二)

2012-11-12网络收集佚名

17.5 SQL编程实例 

我们在学习了SQL程序的编写方法之后,我们便可以着手创建自己的应用程序了, 通过创建应用程序我们对Delphi的强大功能就会有更深刻的印象,同时会进一步全面掌握有关SQL编程的知识,在本节中我们主要介绍两个例子,前一个例子主要是用静态的SQL语句编程,后一个例子是用动态SQL语句编程。 

17.5.1 设计简单的SQL程序编辑器 

例17.1:在这个例子中,我们设计一个交互式的SQL程序编辑器, 在这个编辑器中,我们可以根据SQL语言的语法规则,编写常用的SQL命令,并通过单击编辑器中的有关的按钮,直接执行编写好的SQL命令,SQL命令的执行结果也会及时地通过一个TDBGrid 部件显示出来。 

表17.3 SQL编辑器中个主要部件的属性

━━━━━━━━━━━━━━━━━━━━

部 件 属 性 值

────────────────────

Form1 Caption=SQL程序编辑器

DBGrid1 DataSource=DataSource1

Button1 Caption=执行(&E)

Button2 Caption=清除(&C)

Button3 Caption=退出(&X)

Button3 kind=bkClose

Memo1

DataSource1 DataSet=Query1

Query1 DatabaseName=DEMOS

━━━━━━━━━━━━━━━━━━━━

 

因为我们在设置Query1的DatabaseName属性时将其设置为DEMOS, 所以我们设计的这个SQL程序编辑器只能对DEOMS中的数据库表进行操作。

单击按钮Button1的事件处理过程代码为:

 

程序清单17.1

 

procedure TForm1.Button1Click(Sender:TObject);

begin

Query1.close;

Query1.SQL.clear;

Query1.SQL.Add(Memo1.text);

Query1.Open;

end;

 

单击按钮Button2的事件处理过程为:

 

程序清单17.2

 

procedure TForm1.Button2Click(Sender:TObject);

begin

Query1.close;

Query1.SQL.clear;

Query1.ExceSQL;

end;

 

下面我们对程序清单17.1和程序清单17.2中的程序代码进行简要的分析:

程序清单17.1中的程序代码是用来执行查询的。

 

Query1.close;

 

这一行程序是用来关闭Query1的,我们在前面的章节中介绍过,只有在调用close 方法将TQuery部件关闭之后,才能修改其SQL属性值,执行close命令关闭查询是很安全的,如果查询已经被关闭了,调用该方法不会产生任何影响。

 

Query1.SQL.clear;

 

因为TQuery部件的SQL属性只能包含一条SQL语句,调用Clear 方法的目的是为了清除SQL属性原来的属性值即原来的SQL命令语句,如果不调用clear方法清除原来的SQL命令语句,当在后面的程序中调用Add方法为SQL属性设置新的SQL命令语句时,Delphi 会将新的SQL命令语句加在原来的SQL命令语句,这样使得SQL属性中包含两条独立的SQL语句,这是不允许的。

 

Query1.SQL.Add(Memo.text);

 

该条命令是将SQL编辑器的编辑区内的内容(TMemo部件Memo1)设置成Query1的SQL属性值。

 

Query1.open;

 

该语句用来执行Query1中的SQL命令语句, 如果执行查询从数据库中获得查询结果,查询结果会在数据网格DBGrid1中显示出来。

程序清单2是用来清除查询的, 其前两行语句跟程序清单1中的代码是一样的。Query1.ExecSQL有一些特别,调用ExecSQL方法也是打开Query1,ExecSQL方法与open方法不一样的,请参看前面的章节,当Query1中SQL属性值为空时,即没有SQL语句时,只能调用ExecSQL方法来打开Query1,如果调用 open 方法会返回一个错误。 在执行完 Query1.ExecSQL语句之后,应用程序将会清除数据网格DBGrid1中的所有内容。 

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.

 


本文来源:网络收集 作者:佚名

声明
声明:本站所发表的文章、评论及图片仅代表作者本人观点,与本站立场无关。若文章侵犯了您的相关权益,请及时与我们联系,我们会及时处理,感谢您对本站的支持!联系Email:support@txwb.com,系统开号,技术支持,服务联系QQ:1175525021本站所有有注明来源为天下网吧或天下网吧论坛的原创作品,各位转载时请注明来源链接!
天下网吧·网吧天下
  • 本周热门
  • 本月热门
  • 阅读排行