一个简单的,适应多种数据库之间存取操作的数据库访问层
//大家在进行数据库访问的时候,经常会将自己常用的一些写成函数的形式, 但如果是不同类型的数据库,或者想转换数据库类型,就比较麻烦了,此处提供了三个对象.TDataAccess为基类.TServerAccess为ADO类型的,TClientAccess为BDE类型的, 这几个都有点不太舒服的地方,就是没有提供初始化委托函数,而是用一个INITALL来进行初始化,大家可以自己改进,这只是我们应用的一个特例.
unit DataAccess_U;
interface
uses
Classes, ADODB, DB, SysUtils, Dbtables, Variants, Forms;
type
TDataAccess = class
public
function GetSQLValue(ASQLStr: string; var AValue: Variant): Boolean; overload; virtual;
function GetSQLValue(ASQLStr: string; var AValue: string): Boolean; overload; virtual;
procedure ExecSQL(ASQLStr: string); virtual; abstract;
function OpenSQL(ASQLStr: string; var ADataSet: TDataSet): Boolean; virtual; abstract;
end;
TServerAccess = class(TDataAccess)
private
function GetCommQuery: TADOQuery;
procedure FreeCommQuery(AQuery: TADOQuery);
public
ADOConnection: TADOConnection;
ConnectString: string;
constructor Create;
destructor Destroy; override;
procedure ExecSQL(ASQLStr: string); override;
function OpenSQL(ASQLStr: string; var ADataSet: TDataSet): Boolean; override;
procedure InitAll; virtual;
end;
TClientAccess = class(TDataAccess)
private
DataBaseName: string; //{ DONE : 等待赋值 }
function GetCommQuery: TQuery;
procedure FreeCommQuery(AQuery: TQuery);
public
constructor Create;
procedure ExecSQL(ASQLStr: string); override;
function OpenSQL(ASQLStr: string; var ADataSet: TDataSet): Boolean; override;
class procedure CreateAlias;
procedure InitAll;
end;
implementation
constructor TServerAccess.Create;
begin
ADOConnection := TADOConnection.Create(nil);
end;
destructor TServerAccess.Destroy;
begin
ADOConnection.Free;
inherited;
end;
procedure TServerAccess.ExecSQL(ASQLStr: string);
var
tmpQuery: TADOQuery;
begin
tmpQuery := GetCommQuery;
try
tmpQuery.Close;
tmpQuery.SQL.Text := ASQLStr;
tmpQuery.ExecSQL;
finally
freeCommQuery(tmpQuery);
end;
end;
procedure TServerAccess.FreeCommQuery(AQuery: TADOQuery);
begin
FreeAndNil(AQuery);
end;
function TServerAccess.GetCommQuery: TADOQuery;
begin
Result := TADOQuery.Create(nil);
Result.Connection := ADOConnection;
end;
procedure TServerAccess.InitAll;
begin
end;
function TServerAccess.OpenSQL(ASQLStr: string;
var ADataSet: TDataSet): Boolean;
var
tmpADOQuery: TADOQuery;
begin
tmpADOQuery := GetCommQuery();
tmpADOQuery.SQL.Text := ASQLStr;
tmpADOQuery.Open;
Result := tmpADOQuery.RecordCount > 0;
ADataSet := tmpADOQuery;
end;
constructor TClientAccess.Create;
begin
DataBaseName := 'selfold';
end;
class procedure TClientAccess.CreateAlias;
var
tmpStrList: TStringList;
begin
Session.DeleteAlias('selfold');
Session.SaveConfigFile;
if not Session.IsAlias('selfold') then
begin
tmpStrList := TStringList.Create;
try
tmpStrList.Add('path =' + ExtractFilePath(Application.ExeName) + 'HS_DATA');
Session.AddAlias('selfold', 'STANDARD', tmpStrList);
Session.SaveConfigFile;
finally
tmpStrList.Free;
end;
end;
end;
procedure TClientAccess.ExecSQL(ASQLStr: string);
var
tmpQuery: TQuery;
begin
tmpQuery := GetCommQuery;
tmpQuery.SQL.Text := ASQLStr;
tmpQuery.ExecSQL;
FreeCommQuery(tmpQuery);
end;
procedure TClientAccess.FreeCommQuery(AQuery: TQuery);
begin
AQuery.Free;
end;
function TClientAccess.GetCommQuery: TQuery;
begin
Result := TQuery.Create(nil);
Result.DatabaseName := DataBaseName;
end;
procedure TClientAccess.InitAll;
begin
end;
function TClientAccess.OpenSQL(ASQLStr: string;
var ADataSet: TDataSet): Boolean;
var
tmpQuery: TQuery;
begin
tmpQuery := GetCommQuery;
tmpQuery.RequestLive := True;
tmpQuery.SQL.Text := ASQLStr;
tmpQuery.Open;
ADataSet := tmpQuery;
Result := tmpQuery.RecordCount > 0;
end;
{ TDataAccess }
function TDataAccess.GetSQLValue(ASQLStr: string;
var AValue: Variant): Boolean;
var
tmpDataSet: TDataSet;
i: Integer;
begin
Result := OpenSQL(ASQLStr, tmpDataSet);
try
if Result then
begin
AValue := VarArrayCreate([0, tmpDataSet.FieldCount], varVariant);
for I := 0 to tmpDataSet.FieldCount - 1 do // Iterate
begin
AValue[I] := tmpDataSet.Fields[I].AsString;
end; // for
end;
finally
tmpDataSet.Close;
tmpDataSet.Free;
end;
end;
function TDataAccess.GetSQLValue(ASQLStr: string;
var AValue: string): Boolean;
var
tmpDataSet: TDataSet;
begin
Result := OpenSQL(ASQLStr, tmpDataSet);
try
if Result then
AValue := tmpDataSet.Fields[0].AsString;
finally
tmpDataSet.Close;
tmpDataSet.Free;
end;
end;