一个方便有用的Delphi数据库操作类，转

2023-11-05 05:50•python•阅读 3779

//在长时间的开发工作中,慢慢做了一些类库,下面这个是一个数据库操作类,欢迎大家交流

//联系方式 QQ:413133880 Email: QQ413133880@gmail.com

unit MyADO;

interface

uses

SysUtils, Classes,ADODB,DB,Variants,StdCtrls,Dialogs;

type

TMyADO = class(TComponent)

private

Connection: TADOConnection;

Query: TADOQuery;

Table: TADOTable;

FConnectionString:string;

procedure InitQuery(InputQuery: TADOQuery; QueryString: String);

function CheckParaEqual(QueryString: String; ParameterList: TStringList):Boolean;

procedure InitParameter(InputQuery:TADOQuery;QueryString:string;ParameterList:TStringList);

public

constructor Create(ConnectionString: String);virtual;

destructor Destory;virtual;

procedure SetConnectionString(ConnectionString: string);

function GetConnection():TADOConnection;overload;virtual;

function GetConnection(ConnectionString: String): TADOConnection;overload;virtual;

function GetQuery: TADOQuery;overload;virtual;

procedure GetQuery(InputQuery: TADOQuery; QueryString: String);overload;virtual;

procedure GetQuery(InputQuery:TADOQuery; QueryString: String; ParameterList: TStringList);overload;virtual;

function GetTable: TADOTable;overload;virtual;

function GetTable(TableName: String): TADOTable;overload;virtual;

function GetTable(TableName: String; Connection: TADOConnection):TADOTable;overload;virtual;

function GetExecuteScalar(QueryString: String): Variant;overload;virtual;

function GetExecuteScalar(QueryString: String; ParameterList: TStringList): Variant;overload;virtual;

function GetExecuteNoQuery(ExecuteSQL:String):Boolean;overload;virtual;

function GetExecuteNoQuery(ExecuteSQL: String; ParameteList: TStringList):Boolean;overload;virtual;

function GetParameteList: TStringList;overload;virtual;

function GetParameteList(ParameterString:string;SplitString: String=';'):TStringList;overload;virtual;

function BindList(QueryString: String;List:TStringList):TStringList;overload;virtual;

procedure BindComboBox(InputComboBox: TComboBox; QueryString: String);virtual;

procedure GetTableNames(Connection: TADOConnection; List: TStringList);overload;virtual;

procedure GetTableNames(List: TStringList);overload;virtual;

procedure GetFieldNames(TableName:string;List: TStringList);overload;virtual;

procedure GetFieldNames(Connection: TADOConnection;TableName:string; List: TStringList);overload;virtual;

function GetStringList: TStringList;virtual;

procedure AddToComboboxItem(InputComboBox:TComboBox;List:TStringList);virtual;

function getBackupInSertSQL(Connection: TADOConnection;TableName: string;

InsertEachTime:Integer=1):TStringList;overload;virtual;

function getBackupInSertSQL(QueryString: string;

InsertEachTime:Integer=1):TStringList;overload;virtual;

function StrNum(ShortStr, LongString: string): Integer;virtual;

function StrSub(psInput: String; BeginPlace, CutLeng: Integer): String;virtual;

function StrFind(ShortStr, LongStrIng: String): Integer;virtual;

function replace(Source, Old, New: STRING): string;virtual;

function StrCut(SourceString:WideString;BeginString:WideString;EndString:WideString):WideString ;virtual;

published

property ConnectionString:string write setConnectionString;

end;

procedure Register;

implementation

procedure Register;

begin

RegisterComponents('ADO', [TMyADO]);

end;

constructor TMyADO.Create(ConnectionString:String);

begin

try

Self.Connection:=TADOConnection.Create(nil);

Self.Connection.LoginPrompt:=False;

Self.Connection.ConnectionString:=ConnectionString;

Self.FConnectionString:=ConnectionString;

Self.Connection.Connected:=true;

Self.Query:=TADOQuery.Create(nil);

Self.Table:=TADOTable.Create(nil);

Self.Query.Connection:=Self.Connection;

Self.Table.Connection:=Self.Connection;

except

//Destory;

ShowMessage('创建ADO对象失败');

Exit;

end;

destructor TMyADO.Destory;

var i:Integer;

begin

try

Self.Connection.Close;

Self.Query.Close;

Self.Table.Close;

Self.Query.Free;

Self.Table.Free;

Self.Connection.Free;

except

for i:=0 to Self.ComponentCount-1 do

if Self.Components[i]<>nil then

Self.Components[i].Free;

ShowMessage('内存可能溢出');

end;

procedure TMyADO.SetConnectionString(ConnectionString: string);

begin

if Self.Connection.Connected then

Self.Connection.Connected:=False;

Self.Connection.ConnectionString:=ConnectionString;

Self.fConnectionString:=ConnectionString;

Self.Connection.Connected:=true;

end;

function TMyADO.GetConnection():TADOConnection;

begin

Result:=Self.Connection;

end;

function TMyADO.GetConnection(ConnectionString: String): TADOConnection;

var temp:TADOConnection;

begin

temp:=TADOConnection.Create(nil);

temp.ConnectionString:=ConnectionString;

temp.LoginPrompt:=False;

Result:=temp;

end;

function TMyADO.GetQuery: TADOQuery;

var TempQuery:TADOQuery;

begin

TempQuery:=TADOQuery.Create(nil);

TempQuery.Connection:=Self.Connection;

Result:=TempQuery;

end;

procedure TMyADO.GetQuery(InputQuery: TADOQuery;QueryString: String);

begin

InitQuery(InputQuery,QueryString);

InputQuery.Open;

end;

procedure TMyADO.GetQuery(InputQuery:TADOQuery; QueryString: String;ParameterList: TStringList);

begin

try

InputQuery.Connection:=Self.Connection;

if CheckParaEqual(QueryString,ParameterList) then

begin

InitParameter(InputQuery,QueryString,ParameterList);

InputQuery.Open;

end;

except

end;

function TMyADO.GetTable: TADOTable;

begin

Result:=TADOTable.Create(nil);

end;

function TMyADO.GetTable(TableName: String): TADOTable;

var TempTable:TADOTable;

begin

TempTable:=TADOTable.Create(nil);

TempTable.Connection:=Self.Connection;

TempTable.TableName:=TableName;

Result:=TempTable;

end;

function TMyADO.GetTable(TableName: String;Connection: TADOConnection):TADOTable;

var TempTable:TADOTable;

begin

TempTable:=TADOTable.Create(nil);

TempTable.Connection:=Connection;

TempTable.TableName:=TableName;

Result:=TempTable;

end;

function TMyADO.GetExecuteScalar(QueryString: String): Variant;

var TempQuery:TADOQuery;

begin

try

TempQuery:=GetQuery;

InitQuery(TempQuery,QueryString);

TempQuery.Open;

Result:= TempQuery.Fields[0].Value;

finally

TempQuery.Free;

end;

function TMyADO.GetExecuteScalar(QueryString: String; ParameterList: TStringList): Variant;

var tempQuery:TADOQuery;

begin

try

tempQuery:=GetQuery;

if CheckParaEqual(QueryString,ParameterList) then

begin

InitQuery(tempQuery,QueryString);

tempQuery.Open;

Result:=tempQuery.Fields[0].Value;

end;

finally

tempQuery.Free;

end;

function TMyADO.GetParameteList: TStringList;

begin

Result:=TStringList.Create;

end;

function TMyADO.GetParameteList(ParameterString:string;SplitString: String=';'):TStringList;

var i:Integer;

tempstr:string;

tempres:TStringList;

begin

tempres:=TStringList.Create;

i:=Pos(SplitString,ParameterString);

while i<>0 do

begin

tempstr:=Copy(ParameterString,0,(i-1));

tempres.Add(tempstr);

Delete(ParameterString,1,i+length(SplitString)-1);

i:=Pos(SplitString,ParameterString);

end;

tempres.Add(ParameterString);

Result:=tempres;

end;

function TMyADO.BindList(QueryString: String;List:TStringList):TStringList;

var tempQuery:TADOQuery;

begin

try

tempQuery:=GetQuery;

InitQuery(tempQuery,QueryString);

tempQuery.Open;

tempQuery.First;

List.Clear;

while not tempQuery.Eof do

begin

if trim(VarToStr(tempQuery.Fields[0].Value))<>'' then

List.Add(tempQuery.Fields[0].Value);

tempQuery.Next;

end;

Result:=List;

finally

tempQuery.Free;

end;

procedure TMyADO.BindComboBox(InputComboBox: TComboBox; QueryString: String);

var TempList:TStringList;

begin

TempList:=GetStringList;

BindList(QueryString,TempList);

AddToComboboxItem(InputComboBox,TempList);

TempList.Free;

end;

function TMyADO.CheckParaEqual(QueryString: String; ParameterList: TStringList):Boolean;

begin

Result:= (StrNum(':',QueryString)=ParameterList.Count);

end;

procedure TMyADO.InitQuery(InputQuery: TADOQuery; QueryString: String);

begin

if InputQuery.Connection=nil then

InputQuery.Connection:=Self.Connection;

InputQuery.SQL.Clear;

InputQuery.SQL.Add(QueryString);

end;

procedure TMyADO.GetTableNames(Connection: TADOConnection; List: TStringList);

begin

end;

procedure TMyADO.GetTableNames(List: TStringList);

begin

Self.Connection.GetTableNames(List);

end;

procedure TMyADO.GetFieldNames(TableName:string;List: TStringList);

begin

Self.Connection.GetFieldNames(TableName,List);

end;

procedure TMyADO.GetFieldNames(Connection: TADOConnection;TableName:string; List: TStringList);

begin

Self.Connection.GetFieldNames(TableName,List);

end;

function TMyADO.GetStringList: TStringList;

begin

Result:=TStringList.Create;

end;

function TMyADO.GetExecuteNoQuery(ExecuteSQL: String): Boolean;

var TempQuery:TADOQuery;

begin

try

TempQuery:=GetQuery;

InitQuery(TempQuery,ExecuteSQL);

TempQuery.ExecSQL;

Result:=True;

except

Result:=False;

end;

finally

TempQuery.Free;

end;

function TMyADO.GetExecuteNoQuery(ExecuteSQL: String;

ParameteList: TStringList): Boolean;

var TempQuery:TADOQuery;

begin

try

TempQuery:=GetQuery;

if CheckParaEqual(ExecuteSQL,ParameteList) then

begin

InitParameter(TempQuery,ExecuteSQL,ParameteList);

TempQuery.ExecSQL;

Result:=True;

end else

begin

Result:=False;

ShowMessage('参数个数不一致');

end;

finally

TempQuery.Free;

ParameteList.Free;

end;

procedure TMyADO.InitParameter(InputQuery: TADOQuery; QueryString: string;

ParameterList: TStringList);

var i:Integer;

begin

InitQuery(InputQuery,QueryString);

for i:=0 to ParameterList.Count-1 do

InputQuery.Parameters[i].Value:=ParameterList.Strings[i];

end;

function TMyADO.StrNum(ShortStr:string;LongString:string):Integer; {测试通过}

var

i:Integer;

begin

i:=0;

while pos(ShortStr,LongString)>0 do

begin

i:=i+1;

LongString:=StrSub(LongString,(StrFind(ShortStr,LongString))+1,Length(LongString)-StrFind(ShortStr,LongString))

end;

Result:=i;

end;

function TMyADO.StrSub(psInput:String; BeginPlace,CutLeng:Integer):String;

begin

Result:=Copy(psInput,BeginPlace,CutLeng)

end;

function TMyADO.StrFind(ShortStr:String;LongStrIng:String):Integer;//在一个字符串中找某个字符的位置

var

locality:integer;

begin

locality:=Pos(ShortStr,LongStrIng);

if locality=0 then

Result:=0

else

Result:=locality;

end;

procedure TMyADO.AddToComboboxItem(InputComboBox: TComboBox;

List: TStringList);

var i,j:Integer;

begin

InputComboBox.Items.Clear;

j:=List.Count-1;

for i:=0 to j do

InputComboBox.Items.Add(List.Strings[i]);

end;

function TMyADO.getBackupInSertSQL(Connection: TADOConnection;TableName: string;

InsertEachTime:Integer=1):TStringList;

var

MyTable: TADOTable;

TempString1, TempString2: string;

RecordCount, FieldCount: Integer;

i, j, k: Integer;

ResultStringList: TStringList;

tempstr: string;

begin

try

ResultStringList:=TStringList.Create;

MyTable:=TADOTable.Create(nil);

Connection.LoginPrompt:=False;

MyTable.Connection:=Connection;

MyTable.TableName:=TableName;

MyTable.Open;

RecordCount:=MyTable.RecordCount -1;

FieldCount:=MyTable.FieldCount-1;

MyTable.First;

j:=0;

//另种快速做法

TempString1:='';

// MyTable.MoveBy(170);

// while not MyTable.Eof do

for k:=0 to RecordCount do

begin

j:=j+1;

TempString2:='';

for i:=0 to FieldCount do

case MyTable.Fields[i].DataType of

ftString,ftWideString,ftMemo,ftFmtMemo :

begin

tempstr:= ( Mytable.Fields[i].AsString);

tempstr:=replace(tempstr,#13#10,'');

tempstr:=replace( tempstr,'''','');

TempString2:=TempString2+''''+ tempstr+''''+',';

end;

ftSmallint,ftInteger,ftWord,ftLargeint:

begin

TempString2:=TempString2+inttostr(MyTable.Fields[i].AsInteger)+',';

end;

ftBoolean:

begin

TempString2:=TempString2+ BoolToStr(MyTable.Fields[i].AsBoolean)+',';

end;

ftCurrency,ftBCD:

begin

TempString2:=TempString2+ CurrToStr(MyTable.Fields[i].AsCurrency)+',';

end;

ftFloat:

begin

TempString2:=TempString2+ FloatToStr(MyTable.Fields[i].AsFloat)+',';

end;

ftDate,ftDateTime:

begin

TempString2:=TempString2+ DateToStr(MyTable.Fields[i].AsDateTime)+',';

end;

ftUnknown:

begin

end;

ftAutoInc:

begin

end;

end;//end case;

TempString2:=Copy(TempString2,1,Length(TempString2)-1);

TempString1:=TempString1+'('+TempString2+'),'+#13#10;

if j=InsertEachTime then

begin

TempString1:=Copy(TempString1,1,Length(TempString1)-3);

//TempString1:='INSERT INTO '+ TableName+ ' VALUES'+#13#10+ TempString1+';';

TempString1:='INSERT INTO '+ TableName+ ' VALUES'+ TempString1+';';

ResultStringList.Add(TempString1);

j:=0;

TempString1:='';

end;//end if

MyTable.Next;

end; //end while

if Length( TempString1)>0 then

begin

TempString1:=Copy(TempString1,1,Length(TempString1)-3);

// TempString1:='INSERT INTO '+ TableName+ ' VALUES'+#13#10+ TempString1+';';

TempString1:='INSERT INTO '+ TableName+ ' VALUES'+ TempString1+';';

ResultStringList.Add(TempString1);

j:=0;

TempString1:='';

end; //以上是第二种方法

Result:=ResultStringList;

finally

MyTable.Free;

end;

function TMyADO.getBackupInSertSQL(QueryString: string;

InsertEachTime:Integer=1):TStringList;

var

TempQuery: TADOQuery;

TempString1, TempString2: string;

RecordCount, FieldCount: Integer;

i, j, k: Integer;

ResultStringList: TStringList;

tempstr: string;

fieldnames:string;

begin

try

ResultStringList:=TStringList.Create;

TempQuery:=TADOQuery.Create(nil);

Self.Connection.LoginPrompt:=False;

TempQuery.Connection:=Self.Connection;

TempQuery.SQL.Clear;

TempQuery.SQL.Add(QueryString);

TempQuery.Open;

for j:=0 to TempQuery.FieldCount-1 do

fieldnames:=fieldnames+ TempQuery.Fields[j].FieldName+',';

fieldnames:=Copy(fieldnames,0,Length(fieldnames)-1);

RecordCount:=TempQuery.RecordCount -1;

FieldCount:=TempQuery.FieldCount-1;

TempQuery.First;

j:=0;

TempString1:='';

// TempQuery.MoveBy(170);

// while not TempQuery.Eof do

for k:=0 to RecordCount do

begin

j:=j+1;

TempString2:='';

for i:=0 to FieldCount do

case TempQuery.Fields[i].DataType of

ftString,ftWideString,ftMemo,ftFmtMemo :

begin

tempstr:= ( TempQuery.Fields[i].AsString);

tempstr:=replace(tempstr,#13#10,'');

tempstr:=replace( tempstr,'''','');

TempString2:=TempString2+''''+ tempstr+''''+',';

end;

ftSmallint,ftInteger,ftWord,ftLargeint:

begin

TempString2:=TempString2+inttostr(TempQuery.Fields[i].AsInteger)+',';

end;

ftBoolean:

begin

TempString2:=TempString2+ BoolToStr(TempQuery.Fields[i].AsBoolean)+',';

end;

ftCurrency,ftBCD:

begin

TempString2:=TempString2+ CurrToStr(TempQuery.Fields[i].AsCurrency)+',';

end;

ftFloat:

begin

TempString2:=TempString2+ FloatToStr(TempQuery.Fields[i].AsFloat)+',';

end;

ftDate,ftDateTime:

begin

TempString2:=TempString2+ DateToStr(TempQuery.Fields[i].AsDateTime)+',';

end;

ftUnknown:

begin

end;

ftAutoInc:

begin

end;

end;//end case;

TempString2:=Copy(TempString2,1,Length(TempString2)-1);

TempString1:=TempString1+'('+TempString2+'),'+#13#10;

if j=InsertEachTime then

begin

TempString1:=Copy(TempString1,1,Length(TempString1)-3);

//TempString1:='INSERT INTO '+ TableName+ ' VALUES'+#13#10+ TempString1+';';

TempString1:='INSERT INTO '+ StrCut(QueryString,'from','where')+'('+ fieldnames+')' + ' VALUES'+ TempString1+';';

ResultStringList.Add(TempString1);

j:=0;

TempString1:='';

end;//end if

TempQuery.Next;

end; //end while

if Length( TempString1)>0 then

begin

TempString1:=Copy(TempString1,1,Length(TempString1)-3);

// TempString1:='INSERT INTO '+ TableName+ ' VALUES'+#13#10+ TempString1+';';

TempString1:='INSERT INTO '+ StrCut(QueryString,'from','where')+'('+ fieldnames+')' + ' VALUES'+ TempString1+';';

ResultStringList.Add(TempString1);

j:=0;

TempString1:='';

end; //以上是第二种方法

Result:=ResultStringList;

finally

TempQuery.Free;

end;

function TMyADO.replace(Source, Old, New: STRING): string;

var

p: Integer;

begin

WHILE POS( Old, Source ) <> 0 DO BEGIN

p := POS( Old, Source );

DELETE( Source, p, LENGTH( Old ) );

INSERT( New, Source, p );

{W}END;

Result := Source;

end;

function TMyADO.StrCut(SourceString, BeginString,

EndString: WideString): WideString;

var beginPos,endPos:Integer;

begin

beginPos:=Pos(BeginString,SourceString);

endPos:=Pos(EndString,SourceString);

if (endPos=0) and (beginPos=0) then

Result:=''

else

if endPos=0 then

Result:=copy(SourceString,beginPos+ Length(BeginString), Length(SourceString)- beginPos- Length(BeginString)+1)

else if beginPos=0 then

Result:=Copy(SourceString,0,endPos)

else

Result:=copy(SourceString,beginPos+ Length(BeginString), endPos-beginpos- Length(BeginString));

Result:=Trim(Result);

end;

end.

上一篇 »PHP中PDO数据库操作类
下一篇 »java直接通过jdbc连接操作数据库模板

一个方便有用的Delphi数据库操作类，转

相关推荐

php数据库操作类，转

php链式操作mysql数据库，封装类带使用示例

php单例模式封装数据库操作类增删改查

php MySQLi数据库操作 封装类

MySQL数据库操作类，PHP实现，支持连贯操作

PHP数据库类

php+MySQL实战案例【二】php数据库辅助类

php操作redis，转

php MySQLi数据库操作封装类