ADO连接池和TADOStoreProc动态参数调用

一个使用ADO连接池的示例,演示了TADOStoredProc动态参数的使用，带重连机制
===================
unit UnitDemo;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls;

type
TForm2 = class(TForm)
Button1: TButton;
procedure Button1Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure FormDestroy(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form2: TForm2;

//数据库服务器
gDBServer: String = '127.0.0.1';
//数据库名称
gDBName: String = 'master';
//数据库用户名
gDBUser: String = 'sa';
//密码
gDBPass: String = '2001';

implementation

{$R *.dfm}
uses
ADODB,
UnitADOConnectionPool;

const
CreateSQL = 'create procedure TestMyPool (@type sysname) '#13#10+
'as'#13#10+
'select * from sysobjects where xtype=@type'#13#10+
'return @@rowcount';
DeleteSQL = 'if Exists(select 1 from sysobjects where xtype=N''P'' and name=N''TestMyPool'')'#13#10+
' drop procedure TestMyPool';

var
gPoolMan: TADOConnPoolMan = Nil;

procedure TForm2.Button1Click(Sender: TObject);

var
ADOObject:TADOConnPoolObject;

ADOStoredProc:TADOStoredProc;
Running :Integer;

I: Integer;

begin
//取得一个存储过程资源（含一数据库有效连接）
ADOObject := gPoolMan.CreateSP('TestMyPool');
if ADOObject = Nil then
//取得资源失败
Exit;
try
ADOStoredProc := ADOObject.ExecObject as TADOStoredProc;

Running := 2;//允许重试(两次)操作，以便在操作失败之后达到重连
while Running>0 do
begin
Dec(Running);
if ADOObject.NeedRefresh then
begin//判断是否有重连标志（比如数据库断开等，可能需要进行重连）
if Not ADOObject.Reconnect then
Exit;
ADOObject.NeedRefresh := Not ADOStoredProc.Parameters.Refresh;
if ADOObject.NeedRefresh then
Exit;
end;

for I := 1(*Zero is the *Result* Parameter*) to ADOStoredProc.Parameters.Count - 1 do
begin
//=========================
//传递参数
ADOStoredProc.Parameters.Items[I].Value := 'U';
//=========================
end;
if Running <> 0 then
try
//执行存储过程
ADOStoredProc.Open;

//执行存储过程成功，退出循环进入后续的数据处理
break;
except
On E:Exception do
begin
//执行失败非程序级的异常通常有两种可能：
//1.数据库连接断开
//2.自适合的参数传递当中可能存储过程已更新，参与不一致
//设置重连标志
ADOObject.NeedRefresh := True;
//===================
//这里记录数据库操作失败日志

//===================
end;
end;
Exit;
end;

//==========================
//从ADOStoredProc当中读取记录
ShowMessage(IntToStr(ADOStoredProc.Parameters.ParamByName('Result').Value));
//==========================

//关闭存储对象的资源
ADOStoredProc.Close;

finally
//调用结束，释放资源
ADOObject.Free;
end;

end;

procedure TForm2.FormCreate(Sender: TObject);
var
ADOConn:TADOConnection;
begin
(****************BEGIN*******************)
(*注：仅为测试准备 *)
//初始化测试环境

ADOConn := Nil;
if Not TADOConnPoolMan.ConnectADO( gDBServer,gDBUser,gDBPass,gDBName,true,ADOConn) then
Exit;
try
ADOConn.Execute(DeleteSQL);
ADOConn.Execute(CreateSQL);
finally
try
ADOConn.Close;
except
end;
ADOConn.Free;
end;
(*****************END********************)


//初始化连接池
gPoolMan := TADOConnPoolMan.Create(gDBServer,gDBUser,gDBPass,gDBName,true);
end;

procedure TForm2.FormDestroy(Sender: TObject);
var
ADOConn:TADOConnection;
begin
//释放连接池
if Assigned(gPoolMan) then
gPoolMan.Free;

(****************BEGIN*******************)
(*注：仅为测试准备 *)
//清理测试环境
ADOConn := Nil;
if Not TADOConnPoolMan.ConnectADO( gDBServer,gDBUser,gDBPass,gDBName,true,ADOConn) then
Exit;
try
ADOConn.Execute(DeleteSQL);
finally
try
ADOConn.Close;
except
end;
ADOConn.Free;
end;
(*****************END********************)
end;

end.