一、FDConnection 连接池
http://docs.embarcadero.com/products/rad_studio/firedac/frames.html?frmname=topic&frmfile=Defining_Connection.html
http://docwiki.embarcadero.com/RADStudio/XE8/en/Defining_Connection_%28FireDAC%29
http://docwiki.embarcadero.com/RADStudio/Berlin/en/Multithreading_(FireDAC)
FDManager()->ConnectionDefs->ConnectionDefByName("")
FDManager.ConnectionDefs.ConnectionDefByName(cbDB.Text).Params.Pooled := True
oConn.ConnectionDefName;
FDConnection1->Params->Pooled;
$(FDHOME)\FDConnectionDefs.ini
D:\Users\Public\Documents\Embarcadero\Studio\FireDAC\FDConnectionDefs.ini
D:\Users\Public\Documents\Embarcadero\Studio\16.0\Samples\Object Pascal\Database\FireDAC\Samples\Comp Layer\TFDConnection\Pooling
从这个官方例子看出使用连接池比不使用连接池的效率提高4倍。
http://www.cnblogs.com/zhenfei/p/4105515.html
1)FDConnectionDefs.ini文件
[MSSQL_Demo]
DriverID=MSSQL
Server=127.0.0.1
Database=Northwind
User_Name=sa
Password=
MetaDefSchema=dbo
MetaDefCatalog=Northwind
ExtendedMetadata=True
以上代码存为FDConnectionDefs.ini文件放到当前exe目录下,程序会自己找到。小节名称与ConnectionDefByName参数一致。
this->FDManager1->ConnectionDefFileName = "FDConnectionDefs.ini";
this->FDManager1->ConnectionDefs->ConnectionDefByName("MSSQL_Demo")->Params->Pooled = true;
考虑到安全性,密码需要加密,所以建议用方法2或3。
2)代码动态创建IFDStanConnectionDef
uses
FireDAC.Comp.Client, FireDAC.Stan.Intf;
var
oDef: IFDStanConnectionDef;
begin
oDef := FDManager.ConnectionDefs.AddConnectionDef;
oDef.Name := 'MSSQL_Connection';
oDef.DriverID := 'MSSQL';
oDef.Server := '127.0.0.1';
oDef.Database := 'Northwind';
oDef.OSAuthent := True;
oDef.MarkPersistent;
oDef.Apply;
.....................
FDConnection1.ConnectionDefName := 'MSSQL_Connection';
FDConnection1.Connected := True;
3)连接参数
var
oParams: TStrings;
begin
oParams := TStringList.Create;
oParams.Add('Server=127.0.0.1');
oParams.Add('Database=Northwind');
oParams.Add('OSAuthent=Yes');
FDManager.AddConnectionDef('MSSQL_Connection', 'MSSQL', oParams);
.....................
FDConnection1.ConnectionDefName := 'MSSQL_Connection';
FDConnection1.Connected := True;
c++ FDManager()->AddConnectionDef("", "", para);
其他
FDManager.ConnectionDefFileName := ExtractFilePath(Application.ExeName) + 'myconndef.ini';
FDManager.ConnectionDefFileAutoLoad := True;
oConn := TFDConnection.Create(nil);
oConn.ConnectionDefName := 'myconn';
oConn.Connected := True;
Create Connection def
void __fastcall TForm35::Button5Click(TObject *Sender) { TStrings *para; para = new TStringList(); para->Add("Server=192.168.1.1"); para->Add("Database=db"); para->Add("User_Name=sa"); para->Add("Password=123"); FDManager()->AddConnectionDef("sqlPTT", "MSSQL", para); FDConnection1->ConnectionDefName = "sqlPTT"; Label1->Caption = this->FDConnection1->ResultConnectionDef->BuildString(); delete para; int startTime = GetTickCount(); for (int i = 0; i < 10; i++) { this->Button1->Click(); } Caption = (GetTickCount() - startTime) / 1000.0; }
use pool
void __fastcall TForm35::Button1Click(TObject *Sender) { TFDConnection *con; con = new TFDConnection(NULL); con->ConnectionDefName = FDConnection1->ConnectionDefName; TFDQuery *qry; qry = new TFDQuery(NULL); qry->Connection = con; qry->SQL->Text = "select count(*) from tt"; qry->Open(); con->Close(); delete con; delete qry; }
连接池比普通连接块4倍!
连接字符串ConnectString
mssql带端口号
Server={127.0.0.1,9433};User_Name=sa;;Password=123;Database=dbname;DriverID=MSSQL
Name=Unnamed;Database=mydb;User_Name=sa;Password=123;Server=127.0.0.1;DriverID=MSSQL
FDConnection1.ConnectionString := 'DriverID=MSSQL;Server=127.0.0.1;Database=Northwind;User_name=sa;Password=123;';
Database=car;User_Name=sa;Password=;Server=127.0.0.1,9433;DriverID=MSSQL
mysql
Database=testdata;User_Name=root;Password=root;Server=127.0.0.1;DriverID=MySQL;CharacterSet=utf8
sqlite:
Database=D:\msdb.db;DriverID=SQLite
执行十条sql语句性能比较。
1、一个连接打开,不关闭,耗时0.184ms
2、使用连接池,耗时0.265ms
3、不使用连接池,0.71ms
oracle连接字符串ip地址输入10.0.0.2:1521/orcl
FDConnection1.Params.DriverID:='SQLite';
FDConnection1.Params.Database := 'Demo.db';
以前ADO连接
ADOConstr = PromptDataSource(0, ADOConnection1->ConnectionString);
DBF连接串
Provider = Microsoft.Jet.OLEDB.4.0 ;Data Source ={0};Extended Properties=dBASE IV
SQLite连接字符串
Name=Unnamed;Database=E:\Bin\posdata.sqlite;DriverID=SQLite
MYSQL连接串
Name=Unnamed;Database=tData;User_Name=root;Password=root;Server=127.0.0.1;DriverID=MySQL;CharacterSet=utf8;Port=3306
ADO连接字符串
Provider=SQLOLEDB.1;Password=123;Persist Security Info=True;User ID=sa;Initial Catalog=mydb;Data Source=127.0.0.1
ADOExcel
ADOConnectionExcel.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+
EXLfilename+';Extended Properties=excel 8.0;'+
'Persist Security Info=false;'
;
--ORACLE
DriverID=Ora
Database=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl)))
User_Name=DI_EHR
Password=neusoft
AuthMode=Normal
CharacterSet=UTF8
DB2
http://docwiki.embarcadero.com/RADStudio/Rio/en/Connect_to_IBM_DB2_Server_(FireDAC)
Use Cases
- Connect to DB2 using the existing database alias:
DriverID=DB2 Alias=addemo User_Name=db2admin Password=mypwd MetaDefSchema=db2admin
- Connect to DB using full connection information:
DriverID=DB2 Server=127.0.0.1 Database=addemo Port=50000 Protocol=TCPIP User_Name=db2admin Password=mypwd MetaDefSchema=db2admin
FireDAC EXCEL
FDConnection.Params.Clear;
FDConnection.Params.Add('Database=<myfile>');
FDConnection.Params.Add('ODBCDriver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}');
FDConnection.Params.Add('DriverID=ODBC');
FDConnection.Params.Add('ODBCAdvanced=HDR=No');
FDConnection.Open;
或者字符串
'ODBCDriver={Driver do Microsoft Excel(*.xls)};Database=E:\2001aa.xls;DataSource=Excel Files;DriverID=ODBC'
ADO MSSQL
Provider=SQLOLEDB.1;Password=123;Persist Security Info=True;User ID=sa;Initial Catalog=MYDB;Data Source=127.0.0.1
C++
ADOConstr = "Provider=SQLOLEDB.1;Password=%S;" "Persist Security Info=True;User ID=%S;" "Initial Catalog=%S;" "Data Source=%S";
ADOConstr = Format(ADOConstr,
ARRAYOFCONST(((String)Password, (String)UserID, (String)DBName, (String)HostName)));