//在长时间的开发工作中,慢慢做了一些类库,下面这个是一个数据库操作类,欢迎大家交流 //联系方式 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; 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; 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; 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; 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; 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; 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 try TempQuery:=GetQuery; InitQuery(TempQuery,ExecuteSQL); TempQuery.ExecSQL; Result:=True; except Result:=False; end; finally TempQuery.Free; end; 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; 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; 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; 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. |
2023-10-27
2022-08-15
2022-08-17
2022-09-23
2022-08-13
请发表评论