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

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.