unitdataoperator.pas

简单封装数据库表的类的一个简单的例子： http://www.delphifans.com/SoftView/SoftView_1476.html

unit UnitDataOperator;

//{$I MyUtils.inc}

interface

uses
  SysUtils, Windows, Messages, Classes, DB, ADODB, Variants,
  {$ifdef CodeSite}
  CSIntf,
  {$endif}  // CodeSite
  UnitDataInterface, UnitDataController, UnitAppLogger,
  UnitBaseTable;

type
  TOperateType = (otSelect, otInsert, otUpdate, otDelete);
  TDataOperator = class;

  TOperateEvent = procedure(Sender: TDataOperator; AObject: TTableData);

  TDataOperator = class(TComponent)
  private
    FAfterDelete: TOperateEvent;
    FAfterSave: TOperateEvent;
    FBeforeDelete: TOperateEvent;
    FBeforeSave: TOperateEvent;
    FDataController: TBaseController;
    function AddItemsToList(AList: TTableDatalist; AData: TDataSet): Integer;
    function CreateTextCommand(ASQL: string): TBaseCommand;
    function Execute(ASQL: string; AObject: TTableData): Boolean;
    function GetDataSQL(AObject: TTableData; AOperateType: TOperateType):
            string;
    function GetPreparedCmd(ASQL: string; KeyNames: array of string; KeyValues:
            array of variant): TBaseCommand; overload;
    function GetPreparedCmd(ASQL: string; AObject: TTableData): TBaseCommand;
            overload;
    function Open(ASQL: string; AObject: TTableData): TDataSet;
  public
    constructor Create(AOwner: TComponent); reintroduce;
    destructor Destroy; override;
    function CheckObjectExists(AType: TTableDataClass; KeyValue: Variant):
            Boolean;
    function Delete(Aobject: TTableData): Boolean; overload;
    function Delete(AType: TTableDataClass; KeyNames: array  of string;
            KeyValues: array of variant): Boolean; overload;
    function DeleteAll(TObjectType: TTableDataClass): Boolean;
    function FindByKeyValue(AType: TTableDataClass; KeyValue: Variant):
            TTableData;
    class function GetSQLWhere(ANames: array of string): string;
    function Load(AType: TTableDataClass; KeyNames: array  of string;
            KeyValues: array of variant): TTableData; overload;
    function Load(AType: TTableDataClass; AValue: Variant): TTableData;
            overload;
    function LoadAllItems(AList: TTableDataList{; OnlyEnabled: Boolean =
            false}): Integer;
    function LoadItems(AList: TTableDataList; KeyNames: array of string;
            KeyValues: array of variant): Integer; overload;
    function LoadItems(AList: TTableDataList; KeyNames: array  of string;
            KeyValues: array of variant; ASQL: string): Integer; overload;
    function Save(AObject: TTableData): Boolean;
    function SaveList(AList: TTableDataList): Integer;
    property AfterDelete: TOperateEvent read FAfterDelete write FAfterDelete;
    property AfterSave: TOperateEvent read FAfterSave write FAfterSave;
    property BeforeDelete: TOperateEvent read FBeforeDelete write FBeforeDelete;
    property BeforeSave: TOperateEvent read FBeforeSave write FBeforeSave;
    property DataController: TBaseController read FDataController write
            FDataController;
  end;
  
implementation


const
//  {$IFDEF DB_ACCESS}
  STR_BOOL_VALUE1                : array[Boolean] of string = ('false', 'true');
//  {$ELSE}
  STR_BOOL_VALUE2                : array[Boolean] of string = ('0', '1');
//  {$ENDIF}


{
******************************** TDataOperator *********************************
}
constructor TDataOperator.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  //  FDataType := ATableDataType;
    //  FValueItems := TTableDataList.Create(AOwner, FDataType);
end;

destructor TDataOperator.Destroy;
begin
  //  FValueItems.Free;
  {$ifdef CodeSite}
  CodeSite.SendInteger('DataOperator.ComponentCount:', Self.ComponentCount);
  {$endif}  // CodeSite
  inherited;
  {$ifdef CodeSite}
  CodeSite.SendMsg('TDataOperator.Destroy');
  {$endif}  // CodeSite
end;

function TDataOperator.AddItemsToList(AList: TTableDatalist; AData: TDataSet):
        Integer;
var
  fItem: TTableData;
begin
  with AData do
  begin
    first;
    while not Eof do
    begin
      fitem := AList.ItemType.Create(AList.Owner, AData);
  //      fDataType.Create(Pool, AData);
      fitem.IsNew := False;
      AList.Add(fItem);
      Next;
    end;    // while
  end;    // with
  result := AData.RecordCount;
end;

function TDataOperator.CheckObjectExists(AType: TTableDataClass; KeyValue:
        Variant): Boolean;
var
  fObj: TTableData;
begin
  fObj := FindByKeyValue(AType, KeyValue);
  result := fObj <> nil;
  //  if result then
  //    fObj.Free;
end;

function TDataOperator.CreateTextCommand(ASQL: string): TBaseCommand;
begin
  if FDataController = nil then
     raise Exception.CreateFmt('%s.CreateTextCommand:No DataController specified.',
       [Classname]);
  result := FDataController.CreateCommand(ASQL, cmdText);
//  result.CommandText := ASQL;
//  result.CommandType := cmdText;
end;

function TDataOperator.Delete(Aobject: TTableData): Boolean;
var
  fSQL: string;
  fnewTransaction: Boolean;
begin
  if Aobject.IsNew then
  begin
    result := True;
    Exit;
  end;
    {$ifdef CodeSite}
  CodeSite.EnterMethod('TDataOperator.Delete');
  CodeSite.SendObject('TDataOperator.Delete, AObject:', Aobject);
    {$endif}  // CodeSite
  //  if not DataController.Connection.InTransaction then
  fnewTransaction := not DataController.InTransaction;
  if fnewTransaction then
    DataController.BeginTrans;
  try
    if Assigned(FBeforeDelete) then
      FBeforeDelete(Self, AObject);
    AObject.DoBeforeDelete(Self);
    fSQL := GetDataSQL(AObject, otDelete);
    result := Execute(fSQL, AObject);
    AObject.DoAfterDelete(Self);
    if Assigned(FAfterDelete) then
      FAfterDelete(Self, Aobject);
    if fnewTransaction then
      DataController.CommitTrans;
  except
    on e: Exception do
    begin
      DataController.RollbackTrans;
      AppLogger.AddLog(Self, '%s.Delete: %s', [ClassName, E.Message]);
      raise;
    end;
  end;    // try/except
    {$ifdef CodeSite}
  CodeSite.SendBoolean('Deleted:', result);
  CodeSite.ExitMethod('TDataOperator.Delete');
    {$endif}  // CodeSite
end;

function TDataOperator.Delete(AType: TTableDataClass; KeyNames: array  of
        string; KeyValues: array of variant): Boolean;
var
  I: Integer;
  fList: TTableDataList;
  
  //  fCmd: TBaseCommand;
  
begin
    {$ifdef CodeSite}
  CodeSite.EnterMethod('TDataOperator.Delete');
    {$endif}  // CodeSite
  result := False;
  fList := TTableDataList.Create(Self, AType);
  try
    if LoadItems(fList, KeyNames, KeyValues) > 0 then
    begin
        {$ifdef CodeSite}
      CodeSite.SendInteger('Delete Count:', fList.Count);
        {$endif}  // CodeSite
      for I := 0 to fList.Count - 1 do    // Iterate
      begin
        Delete(fList[I]);
      end;    // for
    end;
  finally
      // free resources
    fList.Free;
  end;  // try/finally
  //  fSQL := Format('delete from %s where %s', [TableName, GetSQLWhere(KeyNames)]);
  //  fCmd := GetPreparedCmd(fSQL, KeyNames, KeyValues);
  //  result := fCmd.Execute;
    {$ifdef CodeSite}
  CodeSite.SendBoolean('Delete:', result);
  CodeSite.ExitMethod('TDataOperator.Delete');
    {$endif}  // CodeSite
end;

function TDataOperator.DeleteAll(TObjectType: TTableDataClass): Boolean;
var
  fSQL: string;
//  fCmd: TBaseCommand;
begin
  fSQL := 'DELETE FROM ' + TObjectType.TableName;
//  fCmd := CreateTextCommand(fSQL);
//  try
    try
      result := DataController.Execute(fSQL);
//      result := fCmd.Execute;
    except
      on e: Exception do
      begin
        AppLogger.AddLog(Self, E.Message);
        result := False;
//        {$ifdef CodeSite}
//        CodeSite.SendObject('DeleteAll: Command', fCmd);
//        {$endif}  // CodeSite
      end;
    end;  // try/except
//  finally
//    fCmd.Free;
//  end;
end;

function TDataOperator.Execute(ASQL: string; AObject: TTableData): Boolean;
var
  fCmd: TBaseCommand;
begin
  result := False;
  fCmd := Self.GetPreparedCmd(ASQL, AObject);
  try
    try
      result := fCmd.Execute;
    except
      on e: Exception do
      begin
        AppLogger.AddLog(Self, 'Execute:%s', [E.Message]);
        raise;
      end;
    end;    // try/except
  finally
    FreeAndNil(fCmd);
  end;
end;

function TDataOperator.FindByKeyValue(AType: TTableDataClass; KeyValue:
        Variant): TTableData;
begin
  //  result := FValueItems.FindByKeyValue(KeyValue);
  //  if result = nil then
  result := Load(Atype, [AType.KeyColumnName], [KeyValue]);
  {$ifdef CodeSite}
  CodeSite.SendFmtMsg('FindBy:%s=[%s]', [AType.KeyColumnName, VarToStr(KeyValue)]);
  CodeSite.SendObject('FindByKeyValue.result', result);
  {$endif}  // CodeSite
end;

function TDataOperator.GetDataSQL(AObject: TTableData; AOperateType:
        TOperateType): string;
  
  const
    sSQLType: array[TOperateType] of string = (
    'select *', 'insert into %s(%s) values(%s)',
    'update %s set %s where %s = %s%s', 'delete');
  
    sFieldSplitter = ',';
    sParamPrefix = ':';
  var
    I: Integer;
    fNames, fValues: string;
    fname: string;
  
begin
  result := EmptyStr;
  case AOperateType of    //
    otSelect, otDelete:
      begin
        result := Format('%s from %s where %s = %s%s',
          [sSQLType[AOperateType], AObject.TableName,
           AObject.KeyColumnName, sParamPrefix, AObject.KeyColumnName]) ;
        if (AOperateType = otSelect) and (AObject.OrderByList <> EmptyStr) then
          result := result + ' order by ' + AObject.OrderByList;
      end;
    otInsert:
      begin
        for I := 0 to AObject.FieldList.Count - 1 do    // Iterate
        begin
          fName := AObject.FieldList[I];
          if not AObject.UseUniqueID and SameText(fName, COL_UNIQUEID) then Continue;
  //            {$endif}  // Use_UniqueID
          if SameText(AObject.KeyColumnName, fName) and AObject.AutoKeyValue then
            Continue;
          fNames := fNames + sFieldSplitter + fName ;
        end;    // for
        fValues := StringReplace(fNames, sFieldSplitter, sFieldSplitter + sParamPrefix, [rfReplaceAll]);
        fNames := Copy(fNames, 2, Length(fNames));
        fValues := Copy(fValues, 2, Length(fValues));
        result := Format(sSQLType[AOperateType], [AObject.TableName, fNames, fValues]);
      end;
    otUpdate:
      begin
        for I := 0 to AObject.FieldList.Count - 1 do    // Iterate
        begin
          fName := AObject.FieldList[I];
          if SameText(COL_UNIQUEID, fName) or
             SameText(AObject.KeyColumnName, fName) then
            Continue;
          fNames := fNames + sFieldSplitter + fName + ' = ' + sParamPrefix + fName;
        end;    // for
        fNames := Copy(fNames, 2, Length(fNames));
        result := Format(sSQLType[AOperateType], [AObject.TableName, fNames, AObject.KeyColumnName,
          sParamPrefix, AObject.KeyColumnName]);
      end;
  end;    // case
      {$ifdef CodeSite}