数据库访问封装 DBTool.pas

 

unit DBTool;

interface

uses
    SysUtils, Classes, DBClient, DataModule, DB, DBConsts, Dialogs, Variants;

type
    TNameRecord = class
    private
        FFieldNames: TStrings;
        FValues: Variant;
        procedure AppendField(Field: TField);
    public
        constructor Create(FieldNames: TStrings);
        destructor Destroy; override;
        function FieldByName(FieldName: string): Integer;
        
        function GetFieldValueI(Field: Integer): Integer; overload;
        function GetFieldValueA(Field: Integer): string; overload;
        function GetFieldValueI(FieldName: string): Integer; overload;
        function GetFieldValueA(FieldName: string): string; overload;
    end;

    TNameRecords = array of TNameRecord;

    function GetNameRecords(Sql: string): TNameRecords;

    function ExecuteSQL(sql: string): TClientDataSet; overload;
    procedure ExecuteSQL(cdsTemp: TClientDataSet; sql: string); overload;

    //sql statements

    function SqlSet(Field, Value: string): string; overload;
    function SqlSet(Field: string; Value: Integer): string; overload;
    function SqlIns(Value: string): string; overload;
    function SqlIns(Value: Integer): string; overload;

var
    g_cds: TClientDataSet;

implementation

function GetNameRecords(Sql: string): TNameRecords;
var
    fieldNames: TStringList;
    I, recordIndex: Integer;
begin
    with g_cds do begin
        DataRequest(Sql);
        Open;
        First;
        SetLength(Result, RecordCount);
        
        fieldNames := TStringList.Create;
        GetFieldNames(fieldNames);
        recordIndex := 0;

        while not EOF do begin
            Result[recordIndex] := TNameRecord.Create(fieldNames);
            // Result[I].CaseSensitive := True;

            
            for I := 0 to fieldNames.Count-1 do begin
                Result[recordIndex].AppendField(Fields[I]);
            end;
            Inc(recordIndex);
            Next;
        end;
        Close;
    end;
end;

function ExecuteSQL(sql: string): TClientDataSet;
begin
    Result := g_cds;
    try
        with Result do begin
            Close;
            DataRequest(sql);
        end;
    except
        Result := nil;
    end;
end;

procedure ExecuteSQL(cdsTemp: TClientDataSet; sql: string);
begin
    try
        with cdsTemp do begin
            Close;
            DataRequest(sql);
        end;
    except
    end;
end;

{ Functions for sql statements }
function SqlSet(Field, Value: string): string;
begin
    Result := Field + ' = ''' + Value + '''';
end;

function SqlSet(Field: string; Value: Integer): string;
begin
    Result := Field + ' = ' + IntToStr(Value);
end;

function SqlIns(Value: string): string; overload;
begin
    Result := '''' + Value + '''';
end;

function SqlIns(Value: Integer): string; overload;
begin
    Result := IntToStr(Value);
end;

{ TNameRecords }

function TNameRecord.FieldByName(FieldName: string): Integer;
begin
    Result := FFieldNames.IndexOf(FieldName);
    if Result < 0 then
        DatabaseErrorFmt(SFieldNotFound, [FieldName]);
end;

function TNameRecord.GetFieldValueI(Field: Integer): Integer;
begin
    Result := TVarData(FValues[Field]).VInteger;
end;

function TNameRecord.GetFieldValueI(FieldName: string): Integer;
begin
    Result := GetFieldValueI(FieldByName(FieldName));
end;

function TNameRecord.GetFieldValueA(Field: Integer): string;
begin
    Result := VarToStr(FValues[Field]);
end;

function TNameRecord.GetFieldValueA(FieldName: string): string;
begin
    Result := GetFieldValueA(FieldByName(FieldName));
end;

procedure TNameRecord.AppendField(Field: TField);
begin
    FValues[FieldByName(Field.FieldName)] := Field.Value;
end;

constructor TNameRecord.Create(FieldNames: TStrings);
begin
    FFieldNames := FieldNames;
    FValues := VarArrayCreate([0, FieldNames.Count-1], varVariant);
end;

destructor TNameRecord.Destroy;
begin
    FFieldNames.Free;
    inherited;
end;

end.

 

 

{ Example code }
function GetCardTypeList: TNameRecords;
begin
    Result := GetNameRecords('select CARD_ID, CARD_NAME, CARD_TYPE from ' + MAINT_CARD_TYPE_TABLE);
end;

procedure AssignList(NameRecords: TNameRecords; AList: TStrings);
var
    I: Integer;
begin
    AList.Clear;
    for I := Low(NameRecords) to High(NameRecords) do with NameRecords[I] do begin
        AList.Add(GetFieldValueA(1)); // assure field 0 to be ID, and field 1 to be description

    end;
end;

procedure TTestForm.FormShow(Sender: TObject);
var
    cardType: string;
begin
    m_cardTypeList := GetCardTypeList;
    AssignList(m_cardTypeList, cbCardType.Items);

    cardType := m_cardTypeList[0].GetFieldValueA('CARD_TYPE');
    ShowMessage(cardType);
end;