delphi 数据库技术(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 及其属性设置:
组件 |
类型 |
属性 |
属性值 |
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 及其属性设置:
组件 |
类型 |
属性 |
属性值 |
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 :
组件设计代码:
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