unitdataoperator.pas

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

  CodeSite.SendFmtMsg('SQL[%d]=%s', [Ord(AOperateType), Result]);
      {$endif}  // CodeSite
end;

function TDataOperator.GetPreparedCmd(ASQL: string; KeyNames: array of string;
        KeyValues: array of variant): TBaseCommand;
var
  fParam: TParam;
  fValue: variant;
  i: Integer;
begin
    {$ifdef CodeSite}
  CodeSite.SendMsg(ASQL);
    {$endif}  // CodeSite
  result := CreateTextCommand(ASQL);
  for I := Low(KeyNames) to High(KeyNames) do    // Iterate
  begin
    fParam := result.Params.FindParam(KeyNames[I]);
    if fParam <> nil then
    begin
  //      if (fParam.DataType = ftBoolean) and not (DataController.IsAccessDB) then
  //        fparam.Value := IntToStr(Ord(Boolean(KeyValues[I])))
  //      else
        fValue := KeyValues[I] ;
        fParam.Value := fValue;
        if VarTypeToDataType(VarType(fValue)) = ftString then
        begin
          fParam.Size := Length(VarToStr(fValue)) * 2;
//            fParam.Size := fParam.Size * 2;
        end;

//        fParam.Size := fParam.Size * 2;

    end;
  end;    // for
end;

function TDataOperator.GetPreparedCmd(ASQL: string; AObject: TTableData):
        TBaseCommand;
var
  I: Integer;
  fParamName: string;
  fValue: Variant;
  fParam: TParam;
begin
        //TODO: Prepared the database command and fill the paramlist.
        {$ifdef CodeSite}
  CodeSite.EnterMethod('TDataOperator.GetPreparedCmd');
    //  CodeSite.SendMsg(ASQL);
        {$endif}  // CodeSite
  result := CreateTextCommand(ASQL);
  if AObject <> nil then
  begin
        {$ifdef CodeSite}
    CodeSite.SendObject('AObject', AObject);
        {$endif}  // CodeSite
    for I := 0 to result.Params.Count - 1 do    // Iterate
    begin
      fParam := result.Params[I];
      fParamName := fParam.Name;
      try
        if AObject.FieldList.IndexOf(fParamName) > -1 then
        begin
          fValue := AObject.Values[fParamName];
          fParam.Value := fValue;
          if VarTypeToDataType(VarType(fValue)) = ftString then
          begin
            fParam.Size := Length(VarToStr(fValue)) * 2;
//            fParam.Size := fParam.Size * 2;
          end;
  //          {$ifdef CodeSite}
  //          CodeSite.SendFmtMsg('%s=%s[%d], ParamValue=%s[%d]', [fParamName, VarToStr(fValue),
  //            Ord(VarType(fValue)), VarToStr(result.Parameters[I].Value),
  //            result.Parameters[I].Size]);
  //          {$endif}  // CodeSite
        end;
      except
        on e: Exception do
        begin
          AppLogger.AddLog(Self, 'GetPreparedCmd[ParamName=%s]: %s', [fParamName, E.Message]);
          raise;
        end;
      end;    // try/except
    end;    // for
  end;
        {$ifdef CodeSite}
  CodeSite.ExitMethod('TDataOperator.GetPreparedCmd');
        {$endif}  // CodeSite
end;

class function TDataOperator.GetSQLWhere(ANames: array of string): string;
var
  i: Integer;
begin
  result := EmptyStr;
  for I := Low(ANames) to High(ANames) do    // Iterate
  begin
    result := result + Format(' AND %s = :%s' ,[ANames[I], ANames[I]]);
  end;    // for
  result := Copy(result, 5, Length(result));
  
end;

function TDataOperator.Load(AType: TTableDataClass; KeyNames: array  of string;
        KeyValues: array of variant): TTableData;
var
  fList: TTableDataList;
begin
  result := nil;
  fList := TTableDataList.Create(Self, AType);
  try
    if LoadItems(fList, KeyNames, KeyValues) > 0 then
    begin
      result := fList.Items[0] as TTableData;
  //      result := fList.Extract(fList.Items[0]) as TTableData;
      {$ifdef APP_DEBUG}
      if fList.Count > 1 then
      begin
        AppLogger.AddLog(Self, '%s.Load: [Count=%d]More than one row found!',
          [ClassName, fList.Count]);
      end;
      {$endif}  // APP_DEBUG
    end;
  finally
    fList.Free;
  end;
      //  result := FDataType.Create(Pool);
      //  fSQL := GetDataSQL(result, otSelect);
      //  fCmd := GetPreparedCmd(fSQL, KeyNames, KeyValues);
  
end;

function TDataOperator.Load(AType: TTableDataClass; AValue: Variant):
        TTableData;
begin
  result := Load(AType, [AType.KeyColumnName], [AValue]);
  {$ifdef APP_DEBUG}
    if result = nil then
    begin
      AppLogger.Debug(Self, '%s.Load: Not found [%s=%s]', [AType.ClassName,
        AType.KeyColumnName, VarToStr(AValue)]);
    end;
  {$endif}  // APP_DEBUG
end;

function TDataOperator.LoadAllItems(AList: TTableDataList{; OnlyEnabled:
        Boolean = false}): Integer;
var
  fSQL: string;
  fData: TDataSet;
begin
      //load all items into AList, return the count.
  fSQL := Format('select * from %s' ,[ AList.ItemType.TableName]);
  //  if OnlyEnabled and AList.ItemType.PropertyExists('Enabled') then
  //    fSQL := fSQL + ' where Enabled = ' + STR_BOOL_VALUE[True] ;
  if AList.ItemType.OrderByList <> EmptyStr then
    fSQL := fSQL + ' order by ' + AList.ItemType.OrderByList;
  fData := Open(fSQL, nil);
  try
    result := AddItemsToList(AList, fData);
      {$ifdef CodeSite}
    CodeSite.SendMsg(fSQL);
    CodeSite.SendInteger('Operator.LoadAllItems.Count', result);
      {$endif}  // CodeSite
  finally
    //    FreeAndNil(fData);
  end;
end;

function TDataOperator.LoadItems(AList: TTableDataList; KeyNames: array  of
        string; KeyValues: array of variant): Integer;
var
  fCmd: TBaseCommand;
  fSQL: string;
  fData: TDataSet;
  fWhere: string;
begin
    //load all items into AList, return the count.
  result := 0;
  fWhere := GetSQLWhere(KeyNames);
  fSQL := Format('select * from %s where %s' ,[AList.ItemType.TableName, fWhere]);
  if AList.ItemType.OrderByList <> EmptyStr then
    fSQL := fSQL + ' order by ' + AList.ItemType.OrderByList;

  result := LoadItems(AList, KeyNames, KeyValues, fSQL);
//  
//  try
//    try
//      fCmd := GetPreparedCmd(fSQL, KeyNames, KeyValues);
//      fData := fCmd.Open;
//      result := AddItemsToList(AList, fData);
//      {$ifdef CodeSite}
//      CodeSite.SendInteger('TDataOperator.LoadItems.Count', result);
//      {$endif}  // CodeSite
//    except
//      on e: Exception do
//      begin
//        AppLogger.AddLog(Self, 'LoadItems:%s', [E.Message]);
//        raise;
//      end;
//    end;    // try/except
//  finally
//  //    FreeAndNil(fData);
//    FreeAndNil(fCmd);
//  end;
end;

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

function TDataOperator.Save(AObject: TTableData): Boolean;
var
  fSQL: string;
  fnewTransaction: Boolean;
begin
  if AObject.DeleteFlag then
  begin
    result := Delete(AObject);
    Exit;
  end;
  {$ifdef CodeSite}
  CodeSite.EnterMethod('TDataOperator.Save');
  CodeSite.SendObject('AObject', AObject);
  {$endif}  // Use_UniqueID
  try
    if not DataController.Connected then
      DataController.OpenConnection;
    fnewTransaction := not DataController.InTransaction;
    if fnewTransaction then
      DataController.BeginTrans;
    if Assigned(FBeforeSave) then
      FBeforeSave(Self, AObject);
    AObject.DoBeforeSave(Self);
    if AObject.IsNew then
      fSQL := GetDataSQL(AObject, otInsert)
    else
      fSQL := GetDataSQL(Aobject, otupdate);
    result := Execute(fSQL, AObject);
    AObject.IsNew := False;
    AObject.Modified := False;
    AObject.DeleteFlag := False;
    AObject.DoAfterSave(Self);
    if Assigned(FAfterSave) then
      FAfterSave(Self, AObject);
    if fnewTransaction then
      DataController.CommitTrans;
  except
    on e: Exception do
    begin
      DataController.RollbackTrans;
        {$ifdef CodeSite}
      CodeSite.SendError(E.Message);
      CodeSite.ExitMethod('TDataOperator.Save');
        {$endif}  // CodeSite
      AppLogger.AddLog(Self, '保存错误: %s', [E.Message]);
      raise;
    end;
  end;    // try/except
    {$ifdef CodeSite}
  //  CodeSite.SendMsg(fSQL);
  CodeSite.SendBoolean('Save:', result);
  CodeSite.ExitMethod('TDataOperator.Save');
    {$endif}  // Use_UniqueID
end;

function TDataOperator.SaveList(AList: TTableDataList): Integer;
var
  I: Integer;
begin
  result := 0;
  for I := AList.Count - 1 downto 0 do    // Iterate
  begin
    if AList[I].DeleteFlag then
    begin
      if Delete(AList[I]) then
        AList.Remove(AList[I]);
    end
    else
    if (AList[I].Modified or AList[I].IsNew) and Save(AList[I]) then
      Inc(result);
  end;    // for
  
end;

//function TDataOperator.StrBoolean(AValue: Boolean): string;
//begin
//  result := IntToStr(Ord(AValue));
//  if DataController = nil then Exit;
//
//  if DataController.IsAccessDB then
//    result := STR_BOOL_VALUE1[AValue]
//  else
//    result := STR_BOOL_VALUE2[AValue];
//end;

function TDataOperator.LoadItems(AList: TTableDataList;
  KeyNames: array of string; KeyValues: array of variant; ASQL: string): Integer;
var
  fCmd: TBaseCommand;
  fSQL: string;
  fData: TDataSet;
begin
    //load all items into AList, return the count.
  result := 0;
  try
    try
      fCmd := GetPreparedCmd(ASQL, KeyNames, KeyValues);
      fData := fCmd.Open;
      result := AddItemsToList(AList, fData);
      {$ifdef CodeSite}
      CodeSite.SendInteger('TDataOperator.LoadItems.Count', result);
      {$endif}  // CodeSite
    except
      on e: Exception do
      begin
        AppLogger.AddLog(Self, 'LoadItems:%s', [E.Message]);
        raise;
      end;
    end;    // try/except
  finally
  //    FreeAndNil(fData);
    FreeAndNil(fCmd);
  end;

end;

end.