DELPHI XE 10.1 BERLIN 使用FIREDAC连接数据库(SQL SERVER, MYSQL, SQLITE)

此程序是写的一个Demo, 包含一个主窗体, 一个数据模块.

1. 主窗体设计如下:

提供一个初始化, 一个查询, 一个释放.

初始化用于数据模块实例化, 查询就是执行SQL语句, 释放就是释放数据模块实例. 其它没啥说的.

窗体上方是一个RadioGroup, 丢了四个按钮, 也就是四个数据库类型, 每个按钮控件的TAG分别设置了0123, 用于数据库连接切换方式的切换..

窗体左下方是四个SQL语句, 分别对应了四个数据库.

2. 数据模块设计如下:

首先, 在程序根目录下新建一个记事本, 重命名为Driver.ini, 内容如下:

[MSSQL_Pool]
DriverID=MSSQL
Pooled=True
Server=192.168.2.188
Database=yqsh_SmartTable
User_Name=sa
Password=123
POOL_CleanupTimeout=0
POOL_ExpireTimeout=0
POOL_MaximumItems=50
CharacterSet=UTF8
 
[MySQL_Pool]
DriverID=MySQL
Pooled=True
Database=ungsm
User_Name=root
Password=1234
POOL_CleanupTimeout=0
POOL_ExpireTimeout=0
POOL_MaximumItems=50
Server=192.168.2.188
UseSSL=True
CharacterSet=utf8
 
[SQLite_Pool]
Pooled=True
Database=smarttable.db
POOL_MaximumItems=10
DriverID=SQLite

可以看出, 有三种连接方式, 分别是MSSQL_Pool, MySQL_Pool,SQLite_Pool, 分别对应三种数据库连接, 这个名字是自定义的.

如果不知道这些参数怎么来, 可以在数据模块丢一个TFDConnection控件(最后删除掉), 双击控件, 如下图所示:

然后配置参数, 然后再点击[Info], 如下入所示:

上图选中部分其实就是Driver.ini中的配置参数, 拷贝出来, 自定义一个节点(比如: MSSQL_Pool)就好了.

然后再说下数据模块中的控件:

TFDManager 设置属性ConnectionDefFileName, 这个设置Driver.ini的路径就可以了, 因为Driver.ini就在根目录下, 就直接写Driver.ini就可以了, 设置FetchOptions.Mode为fmAll.

TFDConnection 设置FetchOptions.Mode为fmAll. ConnectionDefName动态指定.

TFDQuery 设置connection属性, 下拉选择TFDConnection即可.

TFDGUIxWaitCursor 丢一个

TFDPhysMSSQLDriverLink 丢一个 连接驱动

TFDPhysMySQLDriverLink 丢一个 连接驱动

TFDPhysSQLiteDriverLink 丢一个 连接驱动

TFDPhysOracleDriverLink 丢一个 连接驱动

3. 主窗代码如下:

unit uFrmMain;
 
interface
 
uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls, Vcl.ExtCtrls;
 
type
  TForm1 = class(TForm)
    mm1: TMemo;
    Button4: TButton;
    Button5: TButton;
    Button6: TButton;
    RadioGroup1: TRadioGroup;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    RadioButton3: TRadioButton;
    RadioButton4: TRadioButton;
    Label1: TLabel;
    Label2: TLabel;
    mm2: TMemo;
    Label3: TLabel;
    mm3: TMemo;
    Label4: TLabel;
    mm4: TMemo;
    procedure Button4Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure RadioButtonClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;
 
var
  Form1: TForm1;
 
implementation
 
uses uDM_DAC;
 
{$R *.dfm}
 
procedure TForm1.Button4Click(Sender: TObject);
begin
  if not Assigned(dm) then
    dm:= TDM.Create(nil);
end;
 
procedure TForm1.Button5Click(Sender: TObject);
begin
  if not Assigned(dm) then
    Exit;
  if not dm.FDConnection.Connected then
    dm.FDConnection.Connected:= True;   //在连接池里获取一个连接
  with dm.FDQuery do
  begin
    Close;
    Case DM.FDBLX of
      0: SQL.Text:= mm1.Lines.Text;
      1: SQL.Text:= mm2.Lines.Text;
      2: SQL.Text:= mm3.Lines.Text;
      3: SQL.Text:= mm4.Lines.Text;
    End;
    Open;
  end;
  ShowMessage(IntToStr(dm.FDQuery.RecordCount));
  DM.FDConnection.Connected:= False;  //归还连接 不是断开
end;
 
procedure TForm1.Button6Click(Sender: TObject);
begin
  if Assigned(dm) then
    FreeAndNil(dm);
end;
 
procedure TForm1.RadioButtonClick(Sender: TObject);
begin
  if not Assigned(dm) then
  begin
    ShowMessage('数据模块未初始化!');
    Exit;
  end;
  if DM.DBChanged(TRadioButton(Sender).Tag) then
    ShowMessage('数据库连接切换成功!')
  else
    ShowMessage('数据库连接切换失败!');
end;
 
end.

4. 数据模块代码如下:

unit uDM_DAC;
 
interface
 
uses
  SysUtils, Classes, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error,
  FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Phys,
  FireDAC.Comp.Client, FireDAC.Phys.MSSQLDef, FireDAC.Phys.ODBCBase,
  FireDAC.Phys.MSSQL, FireDAC.VCLUI.Wait, FireDAC.Comp.UI, FireDAC.Stan.Pool,
  FireDAC.Stan.Async, Data.DB, FireDAC.Stan.Param, FireDAC.DatS,
  FireDAC.DApt.Intf, FireDAC.DApt, FireDAC.Comp.DataSet, FireDAC.Phys.MySQLDef,
  FireDAC.Phys.MySQL, FireDAC.Stan.ExprFuncs, FireDAC.Phys.SQLiteDef,
  FireDAC.Phys.SQLite, FireDAC.Phys.OracleDef, FireDAC.Phys.Oracle;
 
type
  TDM = class(TDataModule)
    FDManager: TFDManager;
    SQLLink: TFDPhysMSSQLDriverLink;
    WaitCursor: TFDGUIxWaitCursor;
    FDConnection: TFDConnection;
    FDQuery: TFDQuery;
    MYSQLLINK: TFDPhysMySQLDriverLink;
    SQLITELINK: TFDPhysSQLiteDriverLink;
    ORACLELINK: TFDPhysOracleDriverLink;
    procedure DataModuleCreate(Sender: TObject);
  private
    { Private declarations }
    function GetCode(iType: Integer; var sCode: string): Boolean;
  public
    { Public declarations }
    //0 Sqlserver 1 Mysql 2 Sqlite 3 Oracle 默认0
    FDBLX: Integer;
    function DBChanged(iType: integer): Boolean;
  end;
 
var
  DM: TDM;
 
implementation
 
{$R *.dfm}
 
procedure TDM.DataModuleCreate(Sender: TObject);
begin
  FDBLX:= 0;
  FDConnection.ConnectionDefName:= 'MSSQL_Pool';
end;
 
function TDM.DBChanged(iType: integer): Boolean;
var
  sCode: string;
begin
  Result:= False;
  if iType= FDBLX then
  begin
    Result:= True;
    Exit;
  end;
  try
    if GetCode(iType, sCode) then
    begin
      FDConnection.ConnectionDefName:= sCode;
      FDBLX:= iType;
      Result:= True;
    end;
  except
    on e: Exception do
      Exit;
  end;
end;
 
function TDM.GetCode(iType: Integer; var sCode: string): Boolean;
begin
  Result:= False;
  case iType of
    0: begin
      sCode:= 'MSSQL_Pool';
      Result:= True;
    end;
    1: begin
      sCode:= 'MySQL_Pool';
      Result:= True;
    end;
    2: begin
      sCode:= 'SQLite_Pool';
      Result:= True;
    end;
    3: begin
      sCode:= 'Oracle_Pool';
      Result:= True;
    end;
  else
    //
  end;
end;
 
end.

需要注意的是, 连接MySQL的时候, 还需要两个dll驱动, 在源代码连接里有.

由于本机没有安装Oracle, 就没有写Oracle的连接了