轉自：http://liucanwei509.blog.163.com/blog/static/171261132011112104242689/
unit DbHelper;
interface
uses Classes,DB, ADODB,SysUtils,UProcParameter;
type
TDbHelper=class
class function GetANewCon(conString:string):TADOConnection;
private
FAdoCon:TADOConnection;
FConString:string;
function GetAdoCon:TADOConnection;
public
property AdoCon:TADOConnection read GetAdoCon;
function OpenSql(Sql: String): TDataSet;
procedure ExecSql(Sql: String);
procedure ExecSqlTran(SqlList: TStringList);
function IsExistsRec(Sql: String): Boolean;
function GetFirstFieldAsInt(Sql: String): Integer;
function GetFirstFieldAsStr(Sql: String): string;
constructor Create(conString:string);reintroduce;
destructor Destroy;override;
function OpenProc(procName:string;procParams:TProcParameters):TDataSet;
procedure ExecProc(procName: string; procParams: TProcParameters);
end;
implementation
{ TDbHelper }
constructor TDbHelper.Create(conString:string);
begin
inherited Create;
FConString:=conString;
end;
destructor TDbHelper.Destroy;
inherited Destroy;
if Assigned(FAdoCon) then FreeAndNil(FAdoCon);
function TDbHelper.GetAdoCon: TADOConnection;
Result:=FAdoCon;
if not Assigned(FAdoCon) then
FAdoCon:=TAdoConnection.Create(nil);
if not FAdoCon.Connected then
begin
FAdoCon.ConnectionString:=FConString;
FAdoCon.KeepConnection:=True;
FAdoCon.LoginPrompt:=False;
FAdoCon.Open;
class function TDbHelper.GetANewCon(conString: string): TADOConnection;
Result:=TAdoConnection.Create(nil);
Result.ConnectionString:=conString;
Result.KeepConnection:=True;
Result.LoginPrompt:=False;
Result.Open;
function TDbHelper.OpenSql(Sql: String): TDataSet;
var
AQuery:TAdoQuery;
Result:=nil;
AQuery:=TAdoQuery.Create(nil);
AQuery.Connection:=GetAdoCon;
AQuery.SQL.Text:=Sql;
AQuery.Open;
Result:=AQuery;
procedure TDbHelper.ExecSql(Sql: String);
try
AQuery.Connection:=GetAdoCon;
AQuery.SQL.Text:=Sql;
AQuery.ExecSQL;
finally
AQuery.Close;
FreeAndNil(AQuery);
procedure TDbHelper.ExecSqlTran(SqlList: TStringList);
Sql:String;
i:Integer;
AQuery.Connection.BeginTrans;
try
for i:=0 to SqlList.Count-1 do
begin
Sql:=SqlList.Strings[i];
AQuery.SQL.Text:=Sql;
AQuery.ExecSQL;
AQuery.Close;
end;
AQuery.Connection.CommitTrans;
except
AQuery.Connection.RollbackTrans;
end;
function TDbHelper.IsExistsRec(Sql: String): Boolean;
AResSet:TDataSet;
Result:=False;
AResSet:=OpenSql(Sql);
if AResSet<>nil then
Result:=AResSet.RecordCount>0;
AResSet.Close;
FreeAndNil(AResSet);
function TDbHelper.GetFirstFieldAsInt(Sql:String): Integer;
Result:=-1;
AQuery:=TAdoQuery.Create(nil);
AQuery.Open;
if AQuery.RecordCount>0 then
begin
Result:=AQuery.Fields.Fields[0].AsInteger;
if Assigned(AQuery) then FreeAndNil(AQuery);
function TDbHelper.GetFirstFieldAsStr(Sql: String): String;
Result:='';
Result:=AQuery.Fields.Fields[0].AsString;
function TDbHelper.OpenProc(procName:string;procParams:TProcParameters):TDataSet;
aProc:TADOStoredProc;
aParameter:TProcParameter;
aProc:=TADOStoredProc.Create(nil);
aProc.Connection:=GetAdoCon;
aProc.ProcedureName:=procName;
for i:=0 to procParams.Count-1 do
aParameter:=procParams.GetParameter(i);
with aProc.Parameters.AddParameter do
Name:=aParameter.Name;
DataType:=aParameter.DataType;
Direction:=aParameter.Direction;
if aParameter.Size<>0 then Size:=aParameter.Size;
Value:=aParameter.Value;
aProc.Open;
Result:=aProc;