一个非常有用的delphi数据库操作类

2016-08-22 10:23:36来源:http://jxyx521.blog.51cto.com/7643111/1293916作者:jyxy521人点击


转自:http://blog.csdn.net/luoyanqing119/article/details/5582771

unit MyADO;interfaceusesSysUtils, Classes,ADODB,DB,Variants,StdCtrls,Dialogs;typeTMyADO = class(TComponent)privateConnection: 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);publicconstructor 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;implementationprocedure Register;beginRegisterComponents('ADO', [TMyADO]);end;constructor TMyADO.Create(ConnectionString:String);begintrySelf.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;begintrySelf.Connection.Close;Self.Query.Close;Self.Table.Close;Self.Query.Free;Self.Table.Free;Self.Connection.Free;exceptfor i:=0 to Self.ComponentCount-1 doif Self.Components[i]<>nil thenSelf.Components[i].Free;ShowMessage('内存可能溢出');end;end;procedure TMyADO.SetConnectionString(ConnectionString: string);beginif Self.Connection.Connected thenSelf.Connection.Connected:=False;Self.Connection.ConnectionString:=ConnectionString;Self.fConnectionString:=ConnectionString;Self.Connection.Connected:=true;end;function TMyADO.GetConnection():TADOConnection;beginResult:=Self.Connection;end;function TMyADO.GetConnection(ConnectionString: String): TADOConnection;var temp:TADOConnection;begintemp:=TADOConnection.Create(nil);temp.ConnectionString:=ConnectionString;temp.LoginPrompt:=False;Result:=temp;end;function TMyADO.GetQuery: TADOQuery;var TempQuery:TADOQuery;beginTempQuery:=TADOQuery.Create(nil);TempQuery.Connection:=Self.Connection;Result:=TempQuery;end;procedure TMyADO.GetQuery(InputQuery: TADOQuery;QueryString: String);beginInitQuery(InputQuery,QueryString);InputQuery.Open;end;procedure TMyADO.GetQuery(InputQuery:TADOQuery; QueryString: String;ParameterList: TStringList);begintryInputQuery.Connection:=Self.Connection;if CheckParaEqual(QueryString,ParameterList) thenbegin InitParameter(InputQuery,QueryString,ParameterList); InputQuery.Open;end;exceptend;end;function TMyADO.GetTable: TADOTable;beginResult:=TADOTable.Create(nil);end;function TMyADO.GetTable(TableName: String): TADOTable;var TempTable:TADOTable;beginTempTable:=TADOTable.Create(nil);TempTable.Connection:=Self.Connection;TempTable.TableName:=TableName;Result:=TempTable;end;function TMyADO.GetTable(TableName: String;Connection: TADOConnection):TADOTable;var TempTable:TADOTable;beginTempTable:=TADOTable.Create(nil);TempTable.Connection:=Connection;TempTable.TableName:=TableName;Result:=TempTable;end;function TMyADO.GetExecuteScalar(QueryString: String): Variant;var TempQuery:TADOQuery;begintryTempQuery:=GetQuery;InitQuery(TempQuery,QueryString);TempQuery.Open;Result:= TempQuery.Fields[0].Value;finallyTempQuery.Free;end;end;function TMyADO.GetExecuteScalar(QueryString: String; ParameterList: TStringList): Variant;var tempQuery:TADOQuery;begintrytempQuery:=GetQuery;if CheckParaEqual(QueryString,ParameterList) thenbeginInitQuery(tempQuery,QueryString);tempQuery.Open;Result:=tempQuery.Fields[0].Value;end;finallytempQuery.Free;end;end;function TMyADO.GetParameteList: TStringList;beginResult:=TStringList.Create;end;function TMyADO.GetParameteList(ParameterString:string;SplitString: String=';'):TStringList;var i:Integer;tempstr:string;tempres:TStringList;begintempres:=TStringList.Create; i:=Pos(SplitString,ParameterString); while i<>0 do begintempstr:=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;begintrytempQuery:=GetQuery;InitQuery(tempQuery,QueryString);tempQuery.Open;tempQuery.First;List.Clear;while not tempQuery.Eof dobeginif trim(VarToStr(tempQuery.Fields[0].Value))<>'' thenList.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;beginTempList:=GetStringList;BindList(QueryString,TempList);AddToComboboxItem(InputComboBox,TempList);TempList.Free;end;function TMyADO.CheckParaEqual(QueryString: String; ParameterList: TStringList):Boolean;beginResult:= (StrNum(':',QueryString)=ParameterList.Count);end;procedure TMyADO.InitQuery(InputQuery: TADOQuery; QueryString: String);beginif InputQuery.Connection=nil thenInputQuery.Connection:=Self.Connection;InputQuery.SQL.Clear;InputQuery.SQL.Add(QueryString);end;procedure TMyADO.GetTableNames(Connection: TADOConnection; List: TStringList);beginend;procedure TMyADO.GetTableNames(List: TStringList);beginSelf.Connection.GetTableNames(List);end;procedure TMyADO.GetFieldNames(TableName:string;List: TStringList);beginSelf.Connection.GetFieldNames(TableName,List);end;procedure TMyADO.GetFieldNames(Connection: TADOConnection;TableName:string; List: TStringList);beginSelf.Connection.GetFieldNames(TableName,List);end;function TMyADO.GetStringList: TStringList;beginResult:=TStringList.Create;end;function TMyADO.GetExecuteNoQuery(ExecuteSQL: String): Boolean;var TempQuery:TADOQuery;begintrytryTempQuery:=GetQuery;InitQuery(TempQuery,ExecuteSQL);TempQuery.ExecSQL;Result:=True;exceptResult:=False;end;finallyTempQuery.Free;end;end;function TMyADO.GetExecuteNoQuery(ExecuteSQL: String;ParameteList: TStringList): Boolean;var TempQuery:TADOQuery;begintryTempQuery:=GetQuery;if CheckParaEqual(ExecuteSQL,ParameteList) thenbegin InitParameter(TempQuery,ExecuteSQL,ParameteList); TempQuery.ExecSQL; Result:=True;end elsebeginResult:=False;ShowMessage('参数个数不一致');end;finallyTempQuery.Free;ParameteList.Free;end;end;procedure TMyADO.InitParameter(InputQuery: TADOQuery; QueryString: string;ParameterList: TStringList);var i:Integer;beginInitQuery(InputQuery,QueryString);for i:=0 to ParameterList.Count-1 doInputQuery.Parameters[i].Value:=ParameterList.Strings[i];end;function TMyADO.StrNum(ShortStr:string;LongString:string):Integer; {测试通过}vari:Integer;begini:=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;beginResult:=Copy(psInput,BeginPlace,CutLeng)end;function TMyADO.StrFind(ShortStr:String;LongStrIng:String):Integer;//在一个字符串中找某个字符的位置varlocality:integer;beginlocality:=Pos(ShortStr,LongStrIng);if locality=0 then Result:=0else Result:=locality;end;procedure TMyADO.AddToComboboxItem(InputComboBox: TComboBox;List: TStringList);var i,j:Integer;beginInputComboBox.Items.Clear;j:=List.Count-1;for i:=0 to j doInputComboBox.Items.Add(List.Strings[i]);end;function TMyADO.getBackupInSertSQL(Connection: TADOConnection;TableName: string;InsertEachTime:Integer=1):TStringList;varMyTable: 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 dofor k:=0 toRecordCount dobeginj:=j+1;TempString2:='';for i:=0 to FieldCount docaseMyTable.Fields[i].DataType offtString,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:beginend;ftAutoInc:beginend; end;//end case;TempString2:=Copy(TempString2,1,Length(TempString2)-1);TempString1:=TempString1+'('+TempString2+'),'+#13#10;if j=InsertEachTime thenbeginTempString1:=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 ifMyTable.Next;end; //end whileif Length( TempString1)>0 thenbegin 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;finallyMyTable.Free;end;end;function TMyADO.getBackupInSertSQL(QueryString: string;InsertEachTime:Integer=1):TStringList;varTempQuery: 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 dofor k:=0 toRecordCount dobeginj:=j+1;TempString2:='';for i:=0 to FieldCount docaseTempQuery.Fields[i].DataType offtString,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:beginend;ftAutoInc:beginend; end;//end case;TempString2:=Copy(TempString2,1,Length(TempString2)-1);TempString1:=TempString1+'('+TempString2+'),'+#13#10;if j=InsertEachTime thenbeginTempString1:=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 ifTempQuery.Next;end; //end whileif Length( TempString1)>0 thenbegin 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;finallyTempQuery.Free;end;end;function TMyADO.replace(Source, Old, New: STRING): string;varp: Integer;beginWHILE POS( Old, Source ) <> 0 DO BEGINp := 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;beginbeginPos:=Pos(BeginString,SourceString);endPos:=Pos(EndString,SourceString);if (endPos=0) and (beginPos=0) thenResult:=''elseif endPos=0 thenResult:=copy(SourceString,beginPos+ Length(BeginString), Length(SourceString)- beginPos- Length(BeginString)+1)else if beginPos=0 thenResult:=Copy(SourceString,0,endPos)elseResult:=copy(SourceString,beginPos+ Length(BeginString), endPos-beginpos- Length(BeginString));Result:=Trim(Result);end;end.


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台