clsuser_operater.pas

用delphi写的界面代码和功能代码分离程序

unit clsUser_Operater;

interface
uses clsUser, ADODB, SysUtils, Forms;

type
  TArrUser = array of TUser;
  TUser_Operater = class
  private
    FADOQuery: TADOQuery;
    function GetMaxID:Integer;
  public
    constructor Create;
    destructor Destroy; override;
    function GetAllUsers: TArrUser;
    function GetUsersByUserName(const UserName:string): TArrUser;
    function GetUserByID(const ID: Integer): TUser;
    procedure SetUser(const AUser: TUser);
    procedure DelUserByID(const ID: Integer);
    function AddUser(const AUser: TUser):Integer;
  end;

implementation


constructor TUser_Operater.Create;
var
  DbName: string;
begin
  FADOQuery := TADOQuery.Create(nil);
  DbName := ExtractFilePath(Application.ExeName) + 'Data.mdb';
  FADOQuery.ConnectionString := Format('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Persist Security Info=False', [DbName]);
end;

destructor TUser_Operater.Destroy;
begin
  inherited;
  FADOQuery.Free;
end;

function TUser_Operater.GetMaxID:Integer;
begin
  Result := 0;
  with FADOQuery do
  begin
    Close;
    SQL.Text := 'SELECT MAX([ID]) FROM [User]';
    Open;
    if RecordCount > 0 then
      Result := Fields[0].AsInteger;
  end;
end;

function TUser_Operater.GetAllUsers: TArrUser;
var
  arrUser: TArrUser;
  AUser:TUser;
begin
  Result := nil;
  with FADOQuery do
  begin
    Close;
    SQL.Text := 'SELECT [ID], [UserName], [Sex], [Age] FROM [User]';
    Open;
    if RecordCount > 0 then
    begin
      while not Eof do
      begin
        SetLength(arrUser, Length(arrUser) + 1);
        AUser := TUser.Create;
        AUser.ID := FieldByName('ID').AsInteger;
        AUser.UserName := FieldByName('UserName').AsString;
        AUser.Sex := FieldByName('Sex').AsString;
        AUser.Age := FieldByName('Age').AsInteger;
        arrUser[Length(arrUser)-1]:=AUser;
        Next;
      end;
      Result := arrUser;
    end;
  end;
end;

function TUser_Operater.GetUsersByUserName(const UserName:string): TArrUser;
var
  arrUser: TArrUser;
  AUser:TUser;
begin
  Result := nil;
  with FADOQuery do
  begin
    Close;
    SQL.Text := Format('SELECT [ID], [UserName], [Sex], [Age] FROM [User] WHERE [UserName] LIKE %s',['"%'+UserName+'%"']);
    Open;
    if RecordCount > 0 then
    begin
      while not Eof do
      begin
        SetLength(arrUser, Length(arrUser) + 1);
        AUser := TUser.Create;
        AUser.ID := FieldByName('ID').AsInteger;
        AUser.UserName := FieldByName('UserName').AsString;
        AUser.Sex := FieldByName('Sex').AsString;
        AUser.Age := FieldByName('Age').AsInteger;
        arrUser[Length(arrUser)-1]:=AUser;
        Next;
      end;
      Result := arrUser;
    end;
  end;
end;

function TUser_Operater.GetUserByID(const ID: Integer): TUser;
var
  AUser: TUser;
begin
  Result := nil;
  with FADOQuery do
  begin
    Close;
    SQL.Text := 'SELECT [ID], [UserName], [Sex], [Age] FROM [User] WHERE [ID]=' + IntToStr(ID);
    Open;
    if RecordCount > 0 then
    begin
      AUser := TUser.Create;
      AUser.ID := FieldByName('ID').AsInteger;
      AUser.UserName := FieldByName('UserName').AsString;
      AUser.Sex := FieldByName('Sex').AsString;
      AUser.Age := FieldByName('Age').AsInteger;
      Result := AUser;
    end;
  end;
end;

procedure TUser_Operater.SetUser(const AUser: TUser);
begin
  with FADOQuery do
  begin
    Close;
    SQL.Text := 'UPDATE [User] SET [UserName]=:UserName, [Sex]=:Sex, [Age]=:Age WHERE [ID]=:ID';
    Parameters.ParamByName('UserName').Value := AUser.UserName;
    Parameters.ParamByName('Sex').Value := AUser.Sex;
    Parameters.ParamByName('Age').Value := AUser.Age;
    Parameters.ParamByName('ID').Value := AUser.ID;
    ExecSQL;
  end;
end;

procedure TUser_Operater.DelUserByID(const ID: Integer);
begin
  with FADOQuery do
  begin
    Close;
    SQL.Text := 'DELETE FROM [User] WHERE [ID]=:ID';
    Parameters.ParamByName('ID').Value := ID;
    ExecSQL;
  end;
end;

function TUser_Operater.AddUser(const AUser: TUser):Integer;
var
  ID:Integer;
begin
  ID:=GetMaxID+1;
  with FADOQuery do
  begin
    Close;
    SQL.Text := 'INSERT INTO [User] ([ID],[UserName],[Sex],[Age]) VALUES (:ID, :UserName, :Sex, :Age)';
    Parameters.ParamByName('ID').Value := ID;
    Parameters.ParamByName('UserName').Value := AUser.UserName;
    Parameters.ParamByName('Sex').Value := AUser.Sex;
    Parameters.ParamByName('Age').Value := AUser.Age;
    ExecSQL;
  end;
  Result := ID;
end;

end.