sampledbgw.pas

数据库封装原代码 1. 该代码可以免费使用, 该代码的名字暂时定为"哲别" 2. 如果你需要使用该代码, 请注明该代码的原来作者: Jacky Zhou 3. 如果你发现该代码有bug,可以自己修改

unit SampleDBGW;

interface

uses
  SysUtils, Classes, DBGW, DB, DBTables, Entity, EtyList;

const
  //sp_GetSequence
  ID_FROM_AGE = 'nFromAge';
  ID_TO_AGE = 'nToAge';
  SP_GET_SUM_AGE = 'sp_GetSumAge';

type
  TdmSampleDBGW = class(TdmDBGW)
    procedure DataModuleCreate(Sender: TObject);
    procedure m_dbBeforeConnect(Sender: TObject);
  private
    m_spGetSumAge: TStoredProc;
    function InitSPGetSumAge() : Boolean;
    function SP_GetSumAge(pety : PIEntity; const listCondition : TEtyList = nil) : Boolean;
  public
    function LoadEntityByStoredProc(pety : PIEntity; const listCondition : TEtyList = nil) : Boolean; override;
  end;

var
  dmSampleDBGW: TdmSampleDBGW;

implementation

uses Variants, EtyCondition;

//-----------------------------------------------------------------------------
//                              DataModuleCreate
//-----------------------------------------------------------------------------
procedure TdmSampleDBGW.DataModuleCreate(Sender: TObject);
begin
  try
    inherited;

    m_db.Close;

    //init m_db
    m_db.AliasName := 'Sample_SQLServer';
    m_db.DatabaseName := 'DB_Sample';
    m_db.LoginPrompt := false;

    m_db.Open;

    //init stored procedures
    InitSPGetSumAge;
  except

  end;
end;

//-----------------------------------------------------------------------------
//                              m_dbBeforeConnect
//-----------------------------------------------------------------------------
procedure TdmSampleDBGW.m_dbBeforeConnect(Sender: TObject);
begin
  inherited;

  if m_db.LoginPrompt = false then
  begin
    m_db.Params.Values['USER NAME'] := 'sa';
    m_db.Params.Values['PASSWORD'] := '';
  end;
end;

//-----------------------------------------------------------------------------
//                              LoadEntityByStoredProc
//-----------------------------------------------------------------------------
function TdmSampleDBGW.LoadEntityByStoredProc(pety : PIEntity; const listCondition : TEtyList = nil) : Boolean;
var
  strEntityName : String;
begin
  try
    strEntityName := LowerCase(pety.EntityName);

    if strEntityName = LowerCase('EtySumAge') then
    begin
      m_spGetSumAge.Destroy;
      m_spGetSumAge := TStoredProc.Create(self);
      m_spGetSumAge.DatabaseName := m_db.DatabaseName;
      m_spGetSumAge.StoredProcName := SP_GET_SUM_AGE;

      TParam.Create(m_spGetSumAge.Params, ptInput);
      TParam.Create(m_spGetSumAge.Params, ptInput);
      m_spGetSumAge.Params[0].Name := ID_FROM_AGE;
      m_spGetSumAge.Params[1].Name := ID_TO_AGE;

      Result := SP_GetSumAge(pety, listCondition);
    end
    else
      Result := false;
  except
    Result := false;
  end;
end;

//-----------------------------------------------------------------------------
//                              InitSPGetSumAge
//-----------------------------------------------------------------------------
function TdmSampleDBGW.InitSPGetSumAge() : Boolean;
begin
  try
    m_spGetSumAge := TStoredProc.Create(self);
    m_spGetSumAge.DatabaseName := m_db.DatabaseName;
    m_spGetSumAge.StoredProcName := SP_GET_SUM_AGE;

    TParam.Create(m_spGetSumAge.Params, ptInput);
    TParam.Create(m_spGetSumAge.Params, ptInput);
    m_spGetSumAge.Params[0].Name := ID_FROM_AGE;
    m_spGetSumAge.Params[1].Name := ID_TO_AGE;

    Result := true;
  except
    Result := false;
  end;
end;

//-----------------------------------------------------------------------------
//                              SP_GetSumAge
//-----------------------------------------------------------------------------
function TdmSampleDBGW.SP_GetSumAge(pety : PIEntity; const listCondition : TEtyList = nil) : Boolean;
var
  nFromAge, nToAge : integer;
  etyCondition : IEntity;
  
  i : Integer;
  nColumnCounts : Integer;
  strTableName : String;
  strFieldType : String;
  strFieldName : String;
  varFieldValue : Variant;
begin
  try
    if m_spGetSumAge.ParamCount <> 2 then
    begin
      Result := false;
      exit;
    end;

    //get nFromAge and nToAge
    if listCondition.GetEntityCount = 0 then
    begin
      m_spGetSumAge.ParamByName(ID_FROM_AGE).AsInteger := 0;
      m_spGetSumAge.ParamByName(ID_TO_AGE).AsInteger := 0;
    end
    else if listCondition.GetEntityCount = 1 then
    begin
      etyCondition := listCondition.GetEntity(0);
      nFromAge := etyCondition.GetAttributeValue(FD_VALUE);
      m_spGetSumAge.ParamByName(ID_FROM_AGE).AsInteger := nFromAge;
      m_spGetSumAge.ParamByName(ID_TO_AGE).AsInteger := 0;
    end
    else if listCondition.GetEntityCount = 2 then
    begin
      etyCondition := listCondition.GetEntity(0);
      nFromAge := etyCondition.GetAttributeValue(FD_VALUE);
      m_spGetSumAge.ParamByName(ID_FROM_AGE).AsInteger := nFromAge;

      etyCondition := listCondition.GetEntity(1);
      nToAge := etyCondition.GetAttributeValue(FD_VALUE);
      m_spGetSumAge.ParamByName(ID_TO_AGE).AsInteger := nToAge;
    end;

    //run storedproc
    m_spGetSumAge.Close;
    m_spGetSumAge.Prepare;
    m_spGetSumAge.Open;

    //the result must only be one record
    if m_spGetSumAge.RecordCount <> 1 then
    begin
      Result := false;
      exit;
    end;

    //get table name
    strTableName := GetTableName(pety^.GetEntityName);

    //save data to pety
    nColumnCounts := m_spGetSumAge.FieldCount;
    if nColumnCounts = 0 then
    begin
      Result := false;
      exit
    end;

    for i := 0 to nColumnCounts - 1 do
    begin
      strFieldName := m_spGetSumAge.Fields[i].FieldName;
      strFieldType := GetTableFieldType(strTableName, strFieldName);
      varFieldValue := FormatValueDBToApp(m_spGetSumAge.Fields[i].AsVariant, strFieldType);
      if not VarIsNull(varFieldValue) then
        pety^.SetAttributeValue(strFieldName, varFieldValue);
    end;

    Result := true;
  except
    Result := false;
  end;
end;

end.