表結構：
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就是胖客戶
首先安全性差
其次維護不方便
我換個數據庫，我的服務端和客戶端都不需要更改
?
)
