delphi 数据库技术(16.ADO数据库编程示例)

16.ADO 数据库编程示例

在 Delphi 的 ADO 数据库编程中组件之间的配合并不是很友好的,有时候会出现莫名其妙的错误,所以,在实际开发中,我们一般不会全部使用数据库开发组件去完成一个项目,同时受 Web 项目开发的影响,我们对一些数据库表的增删改查总是分别去设计和开发,而不是希望在一个组件中完成。鉴于此种情况,我们在 Delphi 中进行 ADO 数据库编程时,常用的组件一般包括:

  • TADOConnection
  • TADODataSet
  • TADOQuery
  • TADOCommand

以上组件一般设计在数据模块中,像 TADOQuery 和 TADOCommand 组件一般在程序代码中直接使用。

在两层数据库开发时,Data Access 和 Data Controls 组件一般会使用:

  • TDataSource
  • TDBNavigator
  • TDBGrid

其他数据控制组件一般不考虑使用。而对于 TDBGrid 组件也是设置为只读,仅用于展现数据。

在接下来的内容中,我们按照上面的思路实现一个简单的应用程序。

16.1 综合示例

示例:通过两个窗体实现对一个数据表的增删改查操作,其中,主窗体以只读网格显示数据,表单窗体负责对数据的新增和修改,实现一个简单的人事信息管理功能。

数据准备:

create table c_institution ( institution_id varchar(64) primary key, institution_name varchar(16) ); insert into c_institution (institution_id, institution_name) values (gen_random_uuid(), '研发部'); insert into c_institution (institution_id, institution_name) values (gen_random_uuid(), '销售部'); insert into c_institution (institution_id, institution_name) values (gen_random_uuid(), '技术支持部'); insert into c_institution (institution_id, institution_name) values (gen_random_uuid(), '财务部'); insert into c_institution (institution_id, institution_name) values (gen_random_uuid(), '行政部'); create table d_worker ( worker_id varchar(64) primary key, institution_id varchar(64), name varchar(32), sex boolean, birthday date, school varchar(64), speciality varchar(32), native_place varchar(256), resume text ); INSERT INTO public.d_worker (worker_id, institution_id, "name", sex, birthday, school, speciality, native_place, resume) VALUES(gen_random_uuid(), 'a98a28e8-e9a1-4b41-95ec-ebfa303b9a96', '张敏', false, '1981-9-10', '内蒙古大学', '经济管理', '内蒙古乌兰察布市集宁区', '无'); INSERT INTO public.d_worker (worker_id, institution_id, "name", sex, birthday, school, speciality, native_place, resume) VALUES(gen_random_uuid(), 'a98a28e8-e9a1-4b41-95ec-ebfa303b9a96', '刘静', false, '1978-7-11', '内蒙古大学', '计算机科学与技术', '内蒙古鄂尔多斯市东胜区', '无'); INSERT INTO public.d_worker (worker_id, institution_id, "name", sex, birthday, school, speciality, native_place, resume) VALUES(gen_random_uuid(), 'a98a28e8-e9a1-4b41-95ec-ebfa303b9a96', '弓慧敏', false, '1983-3-10', '内蒙古科技大学', '计算机科学与技术', '内蒙古呼和浩特市托克托县', '无');

数据模块组件 MainDM 及其属性设置:

delphi 数据库技术(16.ADO数据库编程示例)(1)

组件

类型

属性

属性值

MainADOConnection

TADOConnection

ConnectionString

Provider=MSDASQL.1;Persist Security Info=False;Data Source=demodb;

LoginPrompt

False

Connected

True

WorkerADODataSet

TADODataSet

Connection

HRADOConnection

CommandType

cmdText

CommandText

SELECT worker_id, w.institution_id, name, sex, birthday, school, speciality, native_place, resume, i.institution_name FROM public.d_worker w left join c_institution i on w.institution_id = i.institution_id

Active

True

主窗体组件 Form1 及其属性设置:

delphi 数据库技术(16.ADO数据库编程示例)(2)

组件

类型

属性

属性值

WorkerDataSource

TDataSource

DataSet

MainDM.WorkerADODataSet

ButtonPanel

TPanel

Caption

''

Align

alTop

WorkerDBNavigator

TDBNavigator

Align

alRight

VisibleButtons

[nbFirst,nbPrior,nbNext,nbLast]

DataSource

DataModule2.WorkerDataSource

WorkerDBGrid

TDBGrid

Align

alClient

DataSource

WorkerDataSource

ReadOnly

True

AddButton

TButton

Caption

'新增'

EditButton

TButton

Caption

'修改'

RemoveButton

TButton

Caption

'删除'

表单窗体组件 WorkerForm :

delphi 数据库技术(16.ADO数据库编程示例)(3)

组件设计代码:

Label1: TLabel; Label2: TLabel; Label3: TLabel; Label4: TLabel; Label5: TLabel; Label6: TLabel; Label7: TLabel; Label8: TLabel; NameEdit: TEdit; GroupBox1: TGroupBox; BoyRadioButton: TRadioButton; GirlRadioButton: TRadioButton; InstitutionComboBox: TComboBox; BirthdayDateTimePicker: TDateTimePicker; SchoolEdit: TEdit; SpecialityEdit: TEdit; NativePlaceEdit: TEdit; ResumeMemo: TMemo; CancelButton: TButton; OkButton: TButton;

主窗口代码:

procedure TForm1.DBGrid1DblClick(Sender: TObject); begin // 双击 Self.EditButtonClick(Sender); end; procedure TForm1.EditButtonClick(Sender: TObject); begin // 修改 if MainDM.WorkerADODataSet.Eof or MainDM.WorkerADODataSet.Bof then begin ShowMessage('请选择记录!'); Exit; end; WorkerForm.isNew := False; if WorkerForm.ShowModal = mrOK then MainDM.WorkerADODataSet.Requery(); end; procedure TForm1.RefreshButtonClick(Sender: TObject); begin // 刷新 MainDM.WorkerADODataSet.Requery(); end; procedure TForm1.RemoveButtonClick(Sender: TObject); var command: TADOCommand; sql: String; begin // 删除 if MainDM.WorkerADODataSet.Eof or MainDM.WorkerADODataSet.Bof then begin ShowMessage('请选择记录!'); Exit; end; sql := 'delete from d_worker where worker_id = :workerId'; command := TADOCommand.Create(Self); command.Connection := MainDM.MainADOConnection; command.CommandType := cmdText; command.CommandText := sql; command.Parameters.ParamByName('workerId').Value := MainDm.WorkerADODataSetworker_id.Value; command.Execute; MainDM.WorkerADODataSet.Requery(); end;

在主窗体中,实现增删改和刷新操作,刷新非常简单,直接调用数据集组件的 Requery 方法即可;新增和修改只是负责打开表单窗体对话框,并在单击“确定”按钮返回后进行数据的刷新;删除操作在代码中直接使用 TADOCommand 对象执行 delete SQL 语句进行删除操作。

表单窗体代码:

unit UnitWorker; interface uses Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.ExtCtrls, Data.DB, Data.Win.ADODB, Vcl.ComCtrls; type TWorkerForm = class(TForm) Label1: TLabel; Label2: TLabel; Label3: TLabel; Label4: TLabel; Label5: TLabel; Label6: TLabel; Label7: TLabel; Label8: TLabel; NameEdit: TEdit; GroupBox1: TGroupBox; BoyRadioButton: TRadioButton; GirlRadioButton: TRadioButton; InstitutionComboBox: TComboBox; BirthdayDateTimePicker: TDateTimePicker; SchoolEdit: TEdit; SpecialityEdit: TEdit; NativePlaceEdit: TEdit; ResumeMemo: TMemo; CancelButton: TButton; OkButton: TButton; procedure CancelButtonClick(Sender: TObject); procedure FormActivate(Sender: TObject); procedure OkButtonClick(Sender: TObject); private { Private declarations } public { Public declarations } isNew: boolean; end; var WorkerForm: TWorkerForm; implementation {$R *.dfm} uses UnitMainDM; procedure TWorkerForm.CancelButtonClick(Sender: TObject); begin // 取消 ModalResult := mrCancel; end; procedure TWorkerForm.FormActivate(Sender: TObject); var query: TADOQuery; index: Integer; begin // 读取部门选项 query := TADOQuery.Create(Self); query.Connection := MainDM.MainADOConnection; query.SQL.Clear; query.SQL.Add('SELECT institution_id, institution_name FROM c_institution'); query.Prepared := True; query.Active := True; InstitutionComboBox.Items.Clear; while not query.Eof do begin InstitutionComboBox.AddItem( query.FieldByName('institution_name').AsString, TObject(NewStr(query.FieldByName('institution_id').AsString))); query.Next; end; query.Close; if not isNew then begin NameEdit.Text := MainDM.WorkerADODataSetname.Value; if MainDM.WorkerADODataSetsex.Value = '1' then BoyRadioButton.Checked := True else GirlRadioButton.Checked := True; index := InstitutionComboBox.Items.IndexOf(MainDM.WorkerADODataSetinstitution_name.Value); InstitutionComboBox.ItemIndex := index; BirthdayDateTimePicker.Date := MainDM.WorkerADODataSetbirthday.Value; SchoolEdit.Text := MainDM.WorkerADODataSetschool.Value; SpecialityEdit.Text := MainDM.WorkerADODataSetspeciality.Value; NativePlaceEdit.Text := MainDM.WorkerADODataSetnative_place.Value; ResumeMemo.Text := MainDM.WorkerADODataSetresume.Value; end else begin NameEdit.Text := ''; BoyRadioButton.Checked := True; SchoolEdit.Text := ''; SpecialityEdit.Text := ''; NativePlaceEdit.Text := ''; ResumeMemo.Text := ''; end; end; procedure TWorkerForm.OkButtonClick(Sender: TObject); var command: TADOCommand; sql: String; institutionId: String; begin // 确定 if length(NameEdit.Text) <= 0 then begin ShowMessage('请输入姓名!'); Exit; end; if InstitutionComboBox.ItemIndex < 0 then begin ShowMessage('请选择所在部门!'); Exit; end; institutionId := PAnsiString(InstitutionComboBox.Items.Objects[InstitutionComboBox.ItemIndex])^; // ShowMessage(institutionId); if isNew then begin sql := 'INSERT INTO d_worker (worker_id, institution_id, name, sex, birthday, school, speciality, native_place, resume) values (gen_random_uuid(), :institutionId, :name, :sex, :birthday, :school, :speciality, :nativePlace, :resume)'; command := TADOCommand.Create(Self); command.Connection := MainDM.MainADOConnection; command.CommandType := cmdText; command.CommandText := sql; command.Parameters.ParamByName('institutionId').Value := institutionId; command.Parameters.ParamByName('name').Value := NameEdit.Text; if BoyRadioButton.Checked then command.Parameters.ParamByName('sex').Value := True else command.Parameters.ParamByName('sex').Value := False; command.Parameters.ParamByName('birthday').Value := FormatDateTime('yyyy-MM-dd', BirthdayDateTimePicker.Date); command.Parameters.ParamByName('school').Value := SchoolEdit.Text; command.Parameters.ParamByName('speciality').Value := SpecialityEdit.Text; command.Parameters.ParamByName('nativePlace').Value := NativePlaceEdit.Text; command.Parameters.ParamByName('resume').Value := ResumeMemo.Text; command.Execute; end else begin sql := 'UPDATE d_worker SET institution_id=:institutionId, name=:name, sex=:sex, birthday=:birthday, school=:school, speciality=:speciality, native_place=:nativePlace, resume=:resume WHERE worker_id=:workerId'; command := TADOCommand.Create(Self); command.Connection := MainDM.MainADOConnection; command.CommandType := cmdText; command.CommandText := sql; command.Parameters.ParamByName('institutionId').Value := institutionId; command.Parameters.ParamByName('name').Value := NameEdit.Text; if BoyRadioButton.Checked then command.Parameters.ParamByName('sex').Value := True else command.Parameters.ParamByName('sex').Value := False; command.Parameters.ParamByName('birthday').Value := FormatDateTime('yyyy-MM-dd', BirthdayDateTimePicker.Date); command.Parameters.ParamByName('school').Value := SchoolEdit.Text; command.Parameters.ParamByName('speciality').Value := SpecialityEdit.Text; command.Parameters.ParamByName('nativePlace').Value := NativePlaceEdit.Text; command.Parameters.ParamByName('resume').Value := ResumeMemo.Text; command.Parameters.ParamByName('workerId').Value := MainDM.WorkerADODataSetworker_id.Value; command.Execute; end; ModalResult := mrOk; end; end.

通过代码,我们可以看到,在表单对话框窗体中,我们没有使用数据控制组件,而是使用普通的组件来实现表单的填写,在窗体中,增加了一个变量:

isNew: boolean;

该变量用于标识窗体中的数据是用于修改还是新增。

在修改或新增数据时,通过 FormActivate 事件对窗体进行初始化,在确定按钮的单击事件中,实现数据的新增和更新,新增和更新数据时,均采用 TADOCommand 组件直接执行 SQL 语句来实现。

在上面的示例中,我们没有使用 Delphi 的大部分数据控制组件,只使用其 TDBGrid ,且仅仅用于显示数据,而新增、修改、删除操作均通过硬编码的方法来实现,虽然看起来有点笨拙,但不容易出错,出现错误也很容易纠正。

16.2 TComboBox 键值对选项的使用方法

在 TComboBox 组件中,一般我们使用其 Items 属性来设置选项,其类型为字符串数组,但在数据库编程中,往往需要键值作为选项,如上面的示例中,选择部门时,选项是部门名称,但得到的值应该是部门 ID,这样的情况如何处理呢?

在上面的代码中,通过调用组件的 AddItem 方法在添加选项时同时将部门 ID 作为 TObject 对象添加到选项中,如:

InstitutionComboBox.AddItem( query.FieldByName('institution_name').AsString, TObject(NewStr(query.FieldByName('institution_id').AsString)));

在上面的代码中,将从数据表上获取的数据的 institution_name 字段值作为选项,同时将 institution_id 字段值封装为 TObject 对象一并添加到选项中。

那么,接下来如何获取用户选择的数据呢?

institutionId := PAnsiString(InstitutionComboBox.Items.Objects[InstitutionComboBox.ItemIndex])^;

当用户修改数据时,打开对话框窗体时,如何初始化组件的值呢?

index := InstitutionComboBox.Items.IndexOf(MainDM.WorkerADODataSetinstitution_name.Value); InstitutionComboBox.ItemIndex := index;

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页