MySQL数据库操作类(转)

于 2008-08-22 16:38:00 发布
阅读量55 收藏
点赞数
原文链接：http://www.cnblogs.com/ghd2004/archive/2008/08/22/1274231.html
版权

//
//在长时间的开发工作中,慢慢做了一些类库,下面这个是一个数据库操作类,欢迎大家交流
//联系方式    QQ:413133880         Email: QQ413133880@gmail.com
unit MySQLData;

interface

uses
SysUtils, Classes,DB,Variants,StdCtrls,Dialogs,myaccess;

type
TMySQL = class(TComponent)
    private
    Connection: TMyConnection;
    Query: TMyQuery;
    Table: TMyTable;
    FConnectionString:string;
    procedure InitQuery(InputQuery: TMyQuery; QueryString: String);
    function CheckParaEqual(QueryString: String; ParameterList: TStringList):Boolean;
    procedure InitParameter(InputQuery:TMyQuery;QueryString:string;ParameterList:TStringList);
public
    constructor Create(ConnectionString: String);overload;
    constructor Create(Server:string='localhost';Port:Integer=3306;LoginName:string='root';Password:string='';Database:string='';CharSet:string='latin1');overload;
    destructor Destory;virtual;
    procedure SetConnectionString(ConnectionString: string);
    function GetConnection():TMyConnection;overload;virtual;
    function GetConnection(ConnectionString: String): TMyConnection;overload;virtual;
    function GetQuery: TMyQuery;overload;virtual;
    procedure GetQuery(InputQuery: TMyQuery; QueryString: String);overload;virtual;
    procedure GetQuery(InputQuery:TMyQuery; QueryString: String; ParameterList: TStringList);overload;virtual;
    function GetTable: TMyTable;overload;virtual;
    function GetTable(TableName: String): TMyTable;overload;virtual;
    function GetTable(TableName: String; Connection: TMyConnection):TMyTable;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: TMyConnection; List: TStringList);overload;virtual;
    procedure GetTableNames(List: TStringList);overload;virtual;
    procedure GetFieldNames(TableName:string;List: TStringList);overload;virtual;
    procedure GetFieldNames(Connection: TMyConnection;TableName:string; List: TStringList);overload;virtual;
    function GetStringList: TStringList;virtual;
    procedure AddToComboboxItem(InputComboBox:TComboBox;List:TStringList);virtual;
    function getBackupInSertSQL(Connection: TMyConnection;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', [TMySQL]);
end;
constructor TMySQL.Create(ConnectionString:String);
begin
try

 

 

 

 

except
    
     ShowMessage('创建ADO对象失败');
     Exit;
end;
end;
destructor TMySQL.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 TMySQL.SetConnectionString(ConnectionString: string);
begin
if Self.Connection.Connected then

 


end;
function TMySQL.GetConnection():TMyConnection;
begin
Result:=Self.Connection;
end;
function TMySQL.GetConnection(ConnectionString: String): TMyConnection;
var temp:TMyConnection;
begin

 


end;
function TMySQL.GetQuery: TMyQuery;
var TempQuery:TMyQuery;
begin
TempQuery:=TMyQuery.Create(nil);
TempQuery.Connection:=Self.Connection;
Result:=TempQuery;
end;

procedure TMySQL.GetQuery(InputQuery: TMyQuery;QueryString: String);
begin
InitQuery(InputQuery,QueryString);
InputQuery.Open;
end;
procedure TMySQL.GetQuery(InputQuery:TMyQuery; 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 TMySQL.GetTable: TMyTable;
begin
Result:=TMyTable.Create(nil);
end;
function TMySQL.GetTable(TableName: String): TMyTable;
var TempTable:TMyTable;
begin
    TempTable:=TMyTable.Create(nil);
    TempTable.Connection:=Self.Connection;
    TempTable.TableName:=TableName;
    Result:=TempTable;
end;
function TMySQL.GetTable(TableName: String;Connection: TMyConnection):TMyTable;
var TempTable:TMyTable;
begin
    TempTable:=TMyTable.Create(nil);
    TempTable.Connection:=Connection;
    TempTable.TableName:=TableName;
    Result:=TempTable;
end;
function TMySQL.GetExecuteScalar(QueryString: String): Variant;
var TempQuery:TMyQuery;
begin
try
TempQuery:=GetQuery;
InitQuery(TempQuery,QueryString);
TempQuery.Open;
Result:= TempQuery.Fields[0].Value;
finally
   TempQuery.Free;
end;
end;
function TMySQL.GetExecuteScalar(QueryString: String; ParameterList: TStringList): Variant;
var tempQuery:TMyQuery;
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 TMySQL.GetParameteList: TStringList;
begin
Result:=TStringList.Create;
end;
function TMySQL.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 TMySQL.BindList(QueryString: String;List:TStringList):TStringList;
var tempQuery:TMyQuery;
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 TMySQL.BindComboBox(InputComboBox: TComboBox; QueryString: String);
var TempList:TStringList;
begin
   TempList:=GetStringList;
   BindList(QueryString,TempList);
   AddToComboboxItem(InputComboBox,TempList);

   TempList.Free;
end;
function TMySQL.CheckParaEqual(QueryString: String; ParameterList: TStringList):Boolean;
begin
   Result:= (StrNum(':',QueryString)=ParameterList.Count);
end;
procedure TMySQL.InitQuery(InputQuery: TMyQuery; QueryString: String);
begin
if InputQuery.Connection=nil then
InputQuery.Connection:=Self.Connection;
InputQuery.SQL.Clear;
InputQuery.SQL.Add(QueryString);
end;
procedure TMySQL.GetTableNames(Connection: TMyConnection; List: TStringList);
begin
end;
procedure TMySQL.GetTableNames(List: TStringList);
begin
Self.Connection.GetTableNames(List);
end;
procedure TMySQL.GetFieldNames(TableName:string;List: TStringList);
begin


end;
procedure TMySQL.GetFieldNames(Connection: TMyConnection;TableName:string; List: TStringList);
begin

end;
function TMySQL.GetStringList: TStringList;
begin
Result:=TStringList.Create;
end;
function TMySQL.GetExecuteNoQuery(ExecuteSQL: String): Boolean;
var TempQuery:TMyQuery;
begin
try
    try
    TempQuery:=GetQuery;
    InitQuery(TempQuery,ExecuteSQL);
    TempQuery.Execute;
    Result:=True;
    except
       Result:=False;
    end;
finally
    TempQuery.Free;
end;
end;
function TMySQL.GetExecuteNoQuery(ExecuteSQL: String;
ParameteList: TStringList): Boolean;
var TempQuery:TMyQuery;
begin
try
TempQuery:=GetQuery;
if CheckParaEqual(ExecuteSQL,ParameteList) then
begin
     InitParameter(TempQuery,ExecuteSQL,ParameteList);
     TempQuery.Execute;
     Result:=True;
end else
begin
    Result:=False;
    ShowMessage('参数个数不一致');
end;
finally
   TempQuery.Free;
   ParameteList.Free;
end;
end;
procedure TMySQL.InitParameter(InputQuery: TMyQuery; QueryString: string;
ParameterList: TStringList);
var i:Integer;
begin
InitQuery(InputQuery,QueryString);
for i:=0 to ParameterList.Count-1 do
InputQuery.Params[i].Value:=ParameterList.Strings[i];
end;

function TMySQL.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 TMySQL.StrSub(psInput:String; BeginPlace,CutLeng:Integer):String;
begin
    Result:=Copy(psInput,BeginPlace,CutLeng)
end;

function TMySQL.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 TMySQL.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 TMySQL.getBackupInSertSQL(Connection: TMyConnection;TableName: string;
        InsertEachTime:Integer=1):TStringList;
var
MyTable: TMyTable;
TempString1, TempString2: string;
RecordCount, FieldCount: Integer;
i, j, k: Integer;
ResultStringList: TStringList;
tempstr: string;
begin
     try
     ResultStringList:=TStringList.Create;
     MyTable:=TMyTable.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:='';
    
      
   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;
        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'+ TempString1+';';
        ResultStringList.Add(TempString1);
        j:=0;
        TempString1:='';
        end;

        MyTable.Next;

    end;

    if Length( TempString1)>0 then
    begin
      TempString1:=Copy(TempString1,1,Length(TempString1)-3);
  
      TempString1:='INSERT INTO '+ TableName+ ' VALUES'+ TempString1+';';
      ResultStringList.Add(TempString1);
      j:=0;
      TempString1:='';
    end;

    Result:=ResultStringList;
    finally
        MyTable.Free;
    end;
end;

function TMySQL.getBackupInSertSQL(QueryString: string;
        InsertEachTime:Integer=1):TStringList;
var
TempQuery: TMyQuery;
TempString1, TempString2: string;
RecordCount, FieldCount: Integer;
i, j, k: Integer;
ResultStringList: TStringList;
tempstr: string;
fieldnames:string;
begin
     try
     ResultStringList:=TStringList.Create;
     TempQuery:=TMyQuery.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:='';
    
      
   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;
        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 '+ StrCut(QueryString,'from','where')+'('+ fieldnames+')' + ' VALUES'+ TempString1+';';
        ResultStringList.Add(TempString1);
        j:=0;
        TempString1:='';
        end;

        TempQuery.Next;

    end;

    if Length( TempString1)>0 then
    begin
      TempString1:=Copy(TempString1,1,Length(TempString1)-3);
  
      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 TMySQL.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 );
END;
Result := Source;
end;


function TMySQL.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;

 

constructor TMySQL.Create(Server: string; Port: Integer; LoginName,
Password, Database: string;CharSet:string);
begin
try
Self.Connection:=TMyConnection.Create(Self);
Self.Connection.Server:=Server;
Self.Connection.Port:=Port;
Self.Connection.LoginPrompt:=False;
Self.Connection.Username:=LoginName;
Self.Connection.Password:=Password;
Self.Connection.Database:=Database;
Self.Connection.Options.Charset:=CharSet;
Self.Connection.Connected:=True;


Table:=TMyTable.Create(Self);
Query:=TMyQuery.Create(Self);
Table.Connection:=Self.Connection;
Query.Connection:=Self.Connection;

except

end;

end;

end.

转载于:https://www.cnblogs.com/ghd2004/archive/2008/08/22/1274231.html

a7129691
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值