SQL工厂法

表結構：

exeno  caption  params                             sqlcommand                                                             tablename

201     登錄        @loginname ftsring(20)    select * from user where loginname=:loginname    user

 

//author: cxg

unit MySQL;

interface

uses
  SysUtils, ADODB, uFunction;

type
  TMySQL = class(TObject)
  private
    fExeNo: Integer;
    fCaption: string;
    fParams: string;
    fSqlcommand: string;
    fTablename: string;
    fQry: TADOQuery;
    fValueArr: array[0..49] of Variant;  //最多支持50个参数
    procedure SetExeNo(Value: integer);
  protected

  public
    constructor Create;
    destructor Destroy; override;
    procedure AddParam(const aValue: Variant);
    //说明: 添加参数值
    //参数: const aValue: Variant -- 参数值
    function GetData: _Recordset;  //非事务性查询
    procedure ExeSQL;              //事务性操作
    procedure BeginTrans;          //开始事务
    procedure CommitTrans;         //提交事务
    procedure RollbackTrans;       //回滚事务
    property ExeNo: Integer read fExeNo write SetExeNo;  //SQL执行序号
  end;

implementation

{ TMySQL }

procedure TMySQL.AddParam(const aValue: Variant);
begin
  fValueArr[fQry.Parameters.Count] := aValue;  
  fQry.Parameters.AddParameter;
end;

procedure TMySQL.BeginTrans;
begin
  fQry.Connection.BeginTrans;
end;

procedure TMySQL.CommitTrans;
begin
  fQry.Connection.CommitTrans;
end;

constructor TMySQL.Create;
begin
  inherited;
  fQry := TADOQuery.Create(nil);
  fQry.Connection := uFunction.GetConnection;
end;

destructor TMySQL.Destroy;
begin
  FreeAndNil(fqry);
  inherited;
end;

procedure TMySQL.ExeSQL;
var
  i: Integer;
begin
  with fQry do
  begin
    Close;
    SQL.Clear;
    SQL.Text := fSqlcommand;
    if Parameters.Count > 0 then
      for i := 0 to Parameters.Count - 1 do
        Parameters[i].Value := fValueArr[i];
    ExecSQL;   
  end; 
end;

function TMySQL.GetData: _Recordset;
var
  i: Integer;
begin
  Result := nil;
  with fQry do
  begin
    Close;
    SQL.Clear;
    SQL.Text := fSqlcommand;
    if Parameters.Count > 0 then
      for i := 0 to Parameters.Count - 1 do
        Parameters[i].Value := fValueArr[i];
    Open;
    Result := fQry.Recordset;
  end;
end;

procedure TMySQL.RollbackTrans;
begin
  fQry.Connection.RollbackTrans;
end;

procedure TMySQL.SetExeNo(Value: integer);
var
  aQry: TADOQuery;
begin
  fQry.Parameters.Clear;
  fExeNo := Value;
  aQry := TADOQuery.Create(nil);
  aQry.Connection := uFunction.getconnection;
  with aQry do
  begin
    Close;
    SQL.Clear;
    SQL.Text := 'select * from [sql] where [exeno]=:exeno';
    Parameters.ParamByName('exeno').Value := fExeNo;
    Open;
    if not IsEmpty then
    begin
      fCaption := FieldByName('caption').AsString;
      fParams := FieldByName('params').AsString;
      fSqlcommand := FieldByName('sqlcommand').AsString;
      fTablename := FieldByName('tablename').AsString;
    end;
  end;
  aQry.Free;
end;

end.

 

 

客户端只需要写执行号，和参数

客户端完全没有sql语句 

MySQL := TMySQL.Create;

  MySQL.ExeNo := 201;

  MySQL.AddParam(Trim(Edit_LoginName.Text));

  MySQL.AddParam(Trim(Edit_LoginPW.Text));

  DS.Data := MySQL.GetData;

  

直接写sql就是胖客户

首先安全性差

其次维护不方便

我换个数据库，我的服务端和客户端都不需要更改