delphi mysql类_DELPHI XE MYSQL数据库操作类 MYSQLHELPER

注: 无需odbc配置{*

* MySQL Helper v1.0

* 2015.6.19

* 说明:

* 这是一个操作MySQL的类,该类必须和libmysql.dll,dbxmys.dll两个文件一起使用.

* 安装:

* 将dll拷贝到C:\Windows\System32下和项目目录下,发行的时候放到exe目录下即可.

* 使用:

* //使用insert,update,delete语句时请使用:TMySQLHelper.ExecSQL();返回受影响行数Integer;

* //使用select语句时请使用TMySQLHelper.Query();返回数据集TSQLQuery;

* 测试:

* WIN7 SP1 X86 , MySQL 5.6.17 , Delphi XE 测试通过.

* ==========================================

* var

* MySQLHelper : TMySQLHelper;

* begin

* MySQLHelper := TMySQLHelper.Create;

* MySQLHelper.User_name := 'root';

* MySQLHelper.Password := 'root';

* MySQLHelper.Database := 'Test';

* ShowMessage('影响行数:'+IntToStr(MySQLHelper.ExecSQL('INSERT INTO test(name)values(''FangJun'')')));

* MySQLHelper.Free;

* end;

* ==========================================

* var

* MySQLHelper : TMySQLHelper;

* SQLQuery : TSQLQuery;

* begin

* MySQLHelper := TMySQLHelper.Create;

* MySQLHelper.User_name := 'root';

* MySQLHelper.Password := 'root';

* MySQLHelper.Database := 'Test';

* SQLQuery := TSQLQuery.Create(nil);

* SQLQuery := MySQLHelper.Query('select * from test');

* while not SQLQuery.Eof do

* begin

* ShowMessage('姓名:'+VarToStr(SQLQuery.FieldValues['name']);

* SQLQuery.Next;

* end;

* MySQLHelper.MySQLClose;

* MySQLHelper.Free;

* end;

* ==========================================}

unitMySQLHelper;interface

usesSysUtils,StdCtrls,Classes,Variants,DB,SqlExpr,DBXMySQL;typeTMySQLHelper= class(TObject)private_PORT : Integer;

_HOST :string;

_DATABASE :string;

_USER_NAME :string;

_PASSWORD :string;

_SERVERCHARSET :string;

_SQLQuery : TSQLQuery;

_SQLConnection : TSQLConnection;procedure Set_PORT(constValue: Integer);procedure Set_HOST(const Value: string);procedure Set_DATABASE (const Value: string);procedure Set_USER_NAME(const Value: string);procedure Set_PASSWORD (const Value: string);procedure Set_SERVERCHARSET(const Value: string);functionMySQLConnection:TSQLConnection;public

constructor Create; overload;property Post:Integer writeSet_PORT;property Host:string writeSet_HOST;property Database:string writeSet_DATABASE;property User_name:string writeSet_USER_NAME;property Password:string writeSet_PASSWORD;property ServerCharSet:string writeSet_SERVERCHARSET;function ExecSQL(const SQL:string):Integer;function Query(const SQL:string):TSQLQuery;procedureMySQLClose;end;implementation

//初始化

constructor TMySQLHelper.Create;begin_HOST := '127.0.0.1';

_PORT := 3306;

_SERVERCHARSET := 'utf8';end;//执行 SQL 语句 INSERT , UPDATE , DELETE 返回影响行数

function TMySQLHelper.ExecSQL(const SQL:string):Integer;begin

if not Assigned(_SQLQuery) then_SQLQuery := TSQLQuery.Create(nil);with _SQLQuery do

beginClose;

SQL.Clear;

SQLConnection :=MySQLConnection;end;try_SQLQuery.SQL.Add(SQL);

result :=_SQLQuery.ExecSQL;except on E: Exception do

raise Exception.Create('SQL语句执行失败 ：'+E.Message);end;

MySQLClose;end;//执行 SQL 语句 Select 返回 数据集

function TMySQLHelper.Query(const SQL:string):TSQLQuery;begin

if not Assigned(_SQLQuery) then_SQLQuery := TSQLQuery.Create(nil);with _SQLQuery do

beginClose;

SQL.Clear;

SQLConnection :=MySQLConnection;end;try_SQLQuery.SQL.Add(SQL);

_SQLQuery.Open;

_SQLQuery.Active :=true;

result :=_SQLQuery;except on E: Exception do

raise Exception.Create('SQL语句查询失败 ：'+E.Message);end;end;//关闭连接

procedureTMySQLHelper.MySQLClose;begin_SQLQuery.Close;

_SQLConnection.Close;end;//连接MySQL 返回 TSQLConnection

functionTMySQLHelper.MySQLConnection:TSQLConnection;begin

if not Assigned(_SQLConnection) then_SQLConnection := TSQLConnection.Create(nil);with _SQLConnection do

beginClose;

GetDriverFunc := 'getSQLDriverMYSQL';

LibraryName := 'dbxmys.dll';

VendorLib := 'LIBMYSQL.dll';

DriverName:= 'MySQL';

Params.Values['drivername']:= 'MySQL';

Params.Values['port'] :=IntToStr(_PORT);

Params.Values['hostname'] :=_HOST;

Params.Values['database'] :=_DATABASE;

Params.Values['user_name'] :=_USER_NAME;

Params.Values['password'] :=_PASSWORD;

Params.Values['ServerCharSet'] :=_SERVERCHARSET;end;try_SQLConnection.Open;

_SQLConnection.Connected :=true;

result :=_SQLConnection;except on E: Exception do

raise Exception.Create('数据库连接错误：'+E.Message);end;end;procedure TMySQLHelper.Set_PORT(constValue: Integer);begin

if Value<>0 then_PORT :=Valueend;procedure TMySQLHelper.Set_HOST (const Value: string);begin

if Value<>'' then_HOST :=Valueend;procedure TMySQLHelper.Set_DATABASE (const Value: string);begin_DATABASE :=Valueend;procedure TMySQLHelper.Set_USER_NAME (const Value: string);begin_USER_NAME :=Value;end;procedure TMySQLHelper.Set_PASSWORD (const Value: string);begin_PASSWORD :=Value;end;procedure TMySQLHelper.Set_SERVERCHARSET (const Value: string);begin

if Value<>'' then_SERVERCHARSET :=Valueend;end.