unitmain.pas

SQL2000跟课件学习的源代码 分享共同学习

unit UnitMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, DB, DBTables, StdCtrls, ExtCtrls, ComCtrls,
  Buttons, ToolWin, DBCtrls, Mask;

type
  TForm1 = class(TForm)
    QueryAdressList: TQuery;
    DataSource1: TDataSource;
    Panel1: TPanel;
    Label2: TLabel;
    DateTimePicker1: TDateTimePicker;
    SpeedButton1: TSpeedButton;
    SpeedButton4: TSpeedButton;
    SpeedButton5: TSpeedButton;
    CBoxSex: TDBComboBox;
    EditName: TDBEdit;
    EditBusiness: TDBEdit;
    EditTelePhone: TDBEdit;
    SpeedButton6: TSpeedButton;
    SpeedButton2: TSpeedButton;
    CBClass: TDBComboBox;
    Label1: TLabel;
    TextCreditHour: TDBText;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    procedure SpeedButton1Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure QueryAdressListAfterScroll(DataSet: TDataSet);
    procedure ToolBtnAppendClick(Sender: TObject);
    procedure ToolBtnSaveClick(Sender: TObject);
    procedure ToolBtnCancelClick(Sender: TObject);
    procedure ToolBtnDeleteClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

uses UnitSearch, jpeg;

var
  ConStr:       String;   // 存放查询的条件
  InsertStatus: Boolean;  // 修改还是插入状态
  
{$R *.dfm}

procedure TForm1.FormShow(Sender: TObject);
begin
  // UserList是Database1的DatabaseName属性值
  QueryAdressList.DatabaseName :='UserList';
  QueryAdressList.RequestLive := True;        // 指定返回一个可更新的数据集。
  QueryAdressList.CachedUpdates := True;      // 指定对数据集进行缓冲更新。
  // 指定对只读数据集进行更新的更新对象
  QueryAdressList.UpdateObject := UpdateSQL1;
  // 设置数据源对应的数据集为QueryAdressList
  DataSource1.DataSet := QueryAdressList;
  // DBGrid1中的数据从DataSource1数据源中取
  DBGrid1.DataSource := DataSource1;
  with QueryAdressList.SQL do
  begin
    Clear;
    Add('SELECT *');
    Add('FROM AddressList');
  end;
  QueryAdressList.Active := True;
  ConStr := '';   // 查询条件清空
end;

procedure TForm1.SpeedButton1Click(Sender: TObject);
var
  SearchHint:   String;
begin
  Case (Sender As TSpeedButton).Tag of
    1:  SearchHint := '姓名';
    2:  SearchHint := '单位';
    3:  SearchHint := '地址';
    4:  SearchHint := '电话';
    5:  SearchHint := '住址';
  end;
  FormSearch.Caption := '请输入要查询的'+SearchHint;
  FormSearch.EditSearch.Text := '';
  // 显示输入查询内容对话框
  if FormSearch.ShowModal = mrNo then Exit;
  // 判断是否连接查询，即按多个字段设置查询条件
  if Not FormSearch.CheckBox1.Checked then
     ConStr := '';
  Case (Sender As TSpeedButton).Tag of
    1: if ConStr = '' then     // 为姓名设置查询条件
         ConStr := 'Name Like ''%'+FormSearch.EditSearch.Text+'%'''
       else
         ConStr := ConStr + 'and Name Like ''%'+FormSearch.EditSearch.Text+'%''';
    2: if ConStr = '' then    // 为单位设置查询条件
         ConStr := 'Units Like ''%'+FormSearch.EditSearch.Text+'%'''
       else
         ConStr := ConStr + 'and Units Like ''%'+FormSearch.EditSearch.Text+'%''';
    3: if ConStr = '' then    // 为单位地址设置查询条件
         ConStr := 'UnitsAddr Like ''%'+FormSearch.EditSearch.Text+'%'''
       else
         ConStr := ConStr + 'and UnitsAddr Like ''%'+FormSearch.EditSearch.Text+'%''';
    4: if ConStr = '' then   // 为办公室电话设置查询条件
         ConStr := 'OfficeTel1 Like ''%'+FormSearch.EditSearch.Text+'%'''
       else
         ConStr := ConStr + 'and OfficeTel1 Like ''%'+FormSearch.EditSearch.Text+'%''';
    5: if ConStr = '' then  // 为住址设置查询条件
         ConStr := 'HomeAddr Like ''%'+FormSearch.EditSearch.Text+'%'''
       else
         ConStr := ConStr + 'and HomeAddr Like ''%'+FormSearch.EditSearch.Text+'%''';
  end;
  QueryAdressList.DisableControls;
  try
    QueryAdressList.Active := False;
    QueryAdressList.SQL.Clear;          // 动态生成SQL语句
    QueryAdressList.SQL.Add('SELECT *');
    QueryAdressList.SQL.Add('FROM AddressList');
    QueryAdressList.SQL.Add('where '+ConStr);
    QueryAdressList.Active := True;
  finally
    QueryAdressList.EnableControls;
  end;
  QueryAdressList.First;
end;

procedure TForm1.QueryAdressListAfterScroll(DataSet: TDataSet);
var
  ImageFile:    String;
begin
  with QueryAdressList do
  begin
    EditName.Text := FieldByName('Name').AsString;
    EditBusiness.Text := FieldByName('Business').AsString;
    CBoxSex.Text := FieldByName('Sex').AsString;
    DateTimePicker1.DateTime := FieldByName('Birthday').AsDateTime;
    EditUnits.Text := FieldByName('Units').AsString;
    // 取照片对应的文件，照片的文件名为该记录的id号，存放在Photo目录中
    ImageFile := ExtractFilePath(Application.ExeName)+'Photo\'
                 + FieldByName('Id').AsString+'.jpg';
    if FileExists(ImageFile) then
      Image1.Picture.LoadFromFile(ImageFile)
    else
      Image1.Picture := Nil;
    EditOPostalcode.Text := FieldByName('OPostalcode').AsString;
    EditUnitsAddr.Text := FieldByName('UnitsAddr').AsString;
    EditQH.Text := FieldByName('QH').AsString;
    EditOfficeTel1.Text := FieldByName('OfficeTel1').AsString;
    EditOfficeTel2.Text := FieldByName('OfficeTel2').AsString;
    EditHPostalcode.Text := FieldByName('HPostalcode').AsString;
    EditHomeAddr.Text := FieldByName('HomeAddr').AsString;
    EditHomeTel1.Text := FieldByName('HomeTel1').AsString;
    EditHomeTel2.Text := FieldByName('HomeTel2').AsString;
    EditCombinedSet.Text := FieldByName('CombinedSet').AsString;
    EditBP.Text := FieldByName('BP').AsString;
    EditFax.Text := FieldByName('Fax').AsString;
    EditEmail.Text := FieldByName('Email').AsString;
  end;
  InsertStatus := False;                // 修改状态标记
  ToolBtnAppend.Enabled := True;        // 可以添加记录
{  ToolBtnSave.Enabled := False;
  ToolBtnCancel.Enabled := False;
}
end;

procedure TForm1.ToolBtnAppendClick(Sender: TObject);
begin
  EditName.Text := '';
  EditBusiness.Text := '';
  CBoxSex.Text := '男';
  DateTimePicker1.DateTime := Date;
  EditUnits.Text := '';
  EditOPostalcode.Text := '';
  EditUnitsAddr.Text := '';
  EditQH.Text := '';
  EditOfficeTel1.Text := '';
  EditOfficeTel2.Text := '';
  EditHPostalcode.Text := '';
  EditHomeAddr.Text := '';
  EditHomeTel1.Text := '';;
  EditHomeTel2.Text := '';
  EditCombinedSet.Text := '';
  EditBP.Text := '';
  EditFax.Text := '';
  EditEmail.Text := '';
  EditName.SetFocus;
  InsertStatus := True;        // 添加状态标记
  DBGrid1.Enabled := False;
  ToolBtnAppend.Enabled := False;    // 处在添加记录状态
end;

procedure TForm1.ToolBtnSaveClick(Sender: TObject);
begin
  Database1.StartTransaction;
  // 暂时关闭QueryAdressList的AfterScroll事件
  QueryAdressList.AfterScroll := Nil;
  try
   try
    if InsertStatus then // 插入状态
      QueryAdressList.Append
    else
      QueryAdressList.Edit;
    with QueryAdressList do
    begin
      FieldByName('Name').AsString := EditName.Text;
      FieldByName('Business').AsString := EditBusiness.Text;
      FieldByName('Sex').AsString := CBoxSex.Text;
      FieldByName('Birthday').AsDateTime := DateTimePicker1.DateTime;
      FieldByName('Units').AsString := EditUnits.Text;
      FieldByName('OPostalcode').AsString := EditOPostalcode.Text;
      FieldByName('UnitsAddr').AsString := EditUnitsAddr.Text;
      FieldByName('QH').AsString := EditQH.Text;
      FieldByName('OfficeTel1').AsString := EditOfficeTel1.Text;
      FieldByName('OfficeTel2').AsString := EditOfficeTel2.Text;
      FieldByName('HPostalcode').AsString := EditHPostalcode.Text;
      FieldByName('HomeAddr').AsString := EditHomeAddr.Text;
      FieldByName('HomeTel1').AsString := EditHomeTel1.Text;
      FieldByName('HomeTel2').AsString := EditHomeTel2.Text;
      FieldByName('CombinedSet').AsString := EditCombinedSet.Text;
      FieldByName('BP').AsString := EditBP.Text;
      FieldByName('Fax').AsString := EditFax.Text;
      FieldByName('Email').AsString := EditEmail.Text;
      Post;
    end;
    QueryAdressList.ApplyUpdates; // 把更新提交到数据库;
    Database1.Commit; // 若完成, 则提交修改;
   except
    Database1.Rollback; // 若失败, 则清除修改
    raise; // 重新触发一个异常，防止执行CommitUpdates方法
   end;
   QueryAdressList.CommitUpdates; // 若成功, 则清除cache
   InsertStatus := False;        // 修改状态标记
   DBGrid1.Enabled := True;
  finally
  // 恢复QueryAdressList的AfterScroll事件
    QueryAdressList.AfterScroll := QueryAdressListAfterScroll;
    ToolBtnAppend.Enabled := True;    
  end;
end;

procedure TForm1.ToolBtnCancelClick(Sender: TObject);
begin
  QueryAdressListAfterScroll(QueryAdressList);
end;

procedure TForm1.ToolBtnDeleteClick(Sender: TObject);
begin
  if Application.MessageBox('删除该记录吗？', '删除记录提示', MB_OKCANCEL) = IDOK then
  begin
    Database1.StartTransaction;
    try
      QueryAdressList.Delete;   // 删除记录
      QueryAdressList.ApplyUpdates; {try to write the updates to the database};
      Database1.Commit; {on success, commit the changes};
    except
      Database1.Rollback; // 若失败, 则清除修改
      raise; // 重新触发一个异常，防止执行CommitUpdates方法
    end;
    QueryAdressList.CommitUpdates; // 若成功, 则清除cache
  end;
end;

end.