在Delphi中使用连接池连接MSSQL数据库和不使用连接池连接数据库的有什么区别

以下是完整的Delphi数据库连接池实现代码，包含初始化、连接获取和使用示例：

 

1. 连接池初始化单元 (uDBPool.pas)

 

unit uDBPool;


interface


uses
  FireDAC.Comp.Client, FireDAC.Stan.Def;


procedure InitConnectionPool;
function GetConnectionFromPool: TFDConnection;
procedure ReleaseConnection(var AConnection: TFDConnection);


implementation


uses
  FireDAC.Phys.MSSQL;


procedure InitConnectionPool;
begin
  FDManager.Close;
  FDManager.ConnectionDefs.Clear;


  FDManager.AddConnectionDef('MSSQL_Pool', 'MSSQL',
    'DriverID=MSSQL;' +
    'Server=192.168.1.100,1433;' +
    'Database=MyDB;' +
    'User_Name=sa;' +
    'Password=123456;' +
    'Pooled=True;' +
    'POOL_MaxSize=20;' +
    'POOL_ExpireTimeout=30000;' +
    'LoginTimeout=3;' +
    'Mars=Yes');


  FDManager.Active := True;
end;


function GetConnectionFromPool: TFDConnection;
begin
  Result := TFDConnection.Create(nil);
  try
    Result.ConnectionDefName := 'MSSQL_Pool';
    Result.LoginPrompt := False;
    Result.ResourceOptions.AutoReconnect := True;
    Result.FetchOptions.Mode := fmAutomatic;
    Result.FetchOptions.AutoClose := True;
    Result.Open;
  except
    FreeAndNil(Result);
    raise;
  end;
end;


procedure ReleaseConnection(var AConnection: TFDConnection);
begin
  if Assigned(AConnection) then
  begin
    if AConnection.Connected then
      AConnection.Close;
    FreeAndNil(AConnection);
  end;
end;


end.

 

2. 使用示例单元 (uMainForm.pas)

 

unit uMainForm;


interface


uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, 
  System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs,
  FireDAC.Comp.Client, Vcl.StdCtrls;


type
  TfrmMain = class(TForm)
    btnQuery: TButton;
    procedure FormCreate(Sender: TObject);
    procedure btnQueryClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;


var
  frmMain: TfrmMain;


implementation


{$R *.dfm}


uses uDBPool;


procedure TfrmMain.FormCreate(Sender: TObject);
begin
  InitConnectionPool;
end;


procedure TfrmMain.btnQueryClick(Sender: TObject);
var
  Conn: TFDConnection;
  Query: TFDQuery;
begin
  Conn := GetConnectionFromPool;
  try
    Query := TFDQuery.Create(nil);
    try
      Query.Connection := Conn;
      Query.SQL.Text := 'SELECT TOP 10 * FROM Customers';
      Query.Open;
      
      while not Query.Eof do
      begin
        ShowMessage(Query.FieldByName('CompanyName').AsString);
        Query.Next;
      end;
    finally
      Query.Free;
    end;
  finally
    ReleaseConnection(Conn);
  end;
end;


procedure TfrmMain.FormDestroy(Sender: TObject);
begin
  FDManager.Close;
end;


end.

 

3. 直接连接版本 (非池化)

 

unit uDirectDB;


interface


function GetDirectConnection: TFDConnection;
procedure ReleaseDirectConnection(var AConnection: TFDConnection);


implementation


function GetDirectConnection: TFDConnection;
begin
  Result := TFDConnection.Create(nil);
  try
    with Result.Params do
    begin
      Add('DriverID=MSSQL');
      Add('Server=192.168.1.100,1433');
      Add('Database=MyDB');
      Add('User_Name=sa');
      Add('Password=123456');
      Add('Pooled=False');
      Add('LoginTimeout=3');
    end;
    Result.LoginPrompt := False;
    Result.Open;
  except
    FreeAndNil(Result);
    raise;
  end;
end;


procedure ReleaseDirectConnection(var AConnection: TFDConnection);
begin
  if Assigned(AConnection) then
  begin
    if AConnection.Connected then
      AConnection.Close;
    FreeAndNil(AConnection);
  end;
end;


end.

 

4. 项目文件 (Project1.dpr)

 

program Project1;


uses
  Vcl.Forms,
  uMainForm in 'uMainForm.pas' {frmMain},
  uDBPool in 'uDBPool.pas',
  uDirectDB in 'uDirectDB.pas';


{$R *.res}


begin
  Application.Initialize;
  Application.MainFormOnTaskbar := True;
  Application.CreateForm(TfrmMain, frmMain);
  Application.Run;
end.

 

关键功能说明

 

1. 连接池管理：
   • 自动维护连接生命周期
   • 支持最大20个连接
   • 30秒空闲超时
2. 异常安全：
   • 所有资源都有try-finally保护
   • 连接失败时自动释放资源
3. 使用建议：
   • 主窗体创建时初始化连接池
   • 每次查询获取新连接
   • 操作完成后立即释放
4. 性能优化：
   • 启用MARS(多活动结果集)
   • 自动重连机制
   • 智能数据获取模式

 

这套实现可以直接集成到现有Delphi项目中，只需修改连接字符串参数即可使用。