Ado.net Connect Pool by ConnectionString Test

Environment:
DB : SQL Server 2008
client framework : .Net Framework 3.5

Test Code:
Case1 : use connection pool, 3000 times select table

SqlConnection sqlconn = new SqlConnection();
sqlconn.ConnectionString = "Network Library=DBMSSOCN;Data Source=serverip,port;
Initial Catalog=wgs;User ID=your id; Password=your password;"+
"Min Pool Size=0; Max Pool Size=8;
Connection Timeout=3;Connection Lifetime=10";
//"Pooling=false; Connect Timeout=3";
string queryString = "SELECT * FROM dbo.yourtable;";
//Console.WriteLine("State: {0}", sqlconn.ConnectionString);
SqlCommand command = new SqlCommand(queryString, sqlconn);

DateTime beging = DateTime.Now;
int n = 0;
while (n < 3000) {
sqlconn.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
//Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1]));
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
n=n+1;
sqlconn.Close();
//Console.WriteLine("State: {0}", sqlconn.State);
}
DateTime end = DateTime.Now;
Console.WriteLine("Cost Time: {0}", end.Subtract(beging));

Case2 :don't use connection pool, 3000 times select table
SqlConnection sqlconn = new SqlConnection();
sqlconn.ConnectionString = "Network Library=DBMSSOCN;Data Source=serverip,port;
Initial Catalog=wgs;User ID=your id; Password=your password;"+
"Pooling=false; Connect Timeout=3";
string queryString = "SELECT * FROM dbo.yourtable;";
//Console.WriteLine("State: {0}", sqlconn.ConnectionString);
SqlCommand command = new SqlCommand(queryString, sqlconn);

DateTime beging = DateTime.Now;
int n = 0;
while (n < 3000) {
sqlconn.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
//Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1]));
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
n=n+1;
sqlconn.Close();
//Console.WriteLine("State: {0}", sqlconn.State);
}
DateTime end = DateTime.Now;
Console.WriteLine("Cost Time: {0}", end.Subtract(beging));

Conclusion:
Case1: cost 4.1~.4.3 seconds.
Case2: cost up 25 seconds.
Using Connection Pool is a good solution.

.Net Framework Provider for Oacle

最近有需求在.net(C#) + oracle 環境 加上Connection Pool,
在網路上用google大神,仔細比較了幾種方式:
1.Oracle 提供的 Interface or API
2.Vender 寫的Client呼叫 Oracle的


為了有 connection pool 機制,又可以用OCI,所選擇的連線方式:

使用 Oracle 的 .NET Framework 資料提供者

Oracle 的 .NET Framework 資料提供者利用由 Oracle 用戶端軟體所提供的「Oracle 呼叫介面 (OCI)」，來存取 Oracle 資料庫。

Oracle 的 .NET Framework 資料提供者系統需求
Oracle 的 .NET Framework 資料提供者需要 Microsoft Data Access Components (MDAC) 2.6 (含) 以後版本。建議使用 MDAC 2.8 SP1。
您還必須安裝 Oracle 8i 3 版 (8.1.7) 用戶端 (含) 以後版本。
Oracle 9i 版之前的 Oracle 用戶端軟體無法存取 UTF16 資料庫，因為 UTF16 是 Oracle 9i 的新功能。若要使用此功能，您必須將用戶端軟體升級至 Oracle 9i (含) 以後版本。

Connection Pooling for the .NET Framework Data Provider for Oracle
Enlisting in Distributed Transactions
OracleConnection.ConnectionString Property

Oracle Connection Pool integrate with Microsoft .Net

List all methods of .net connection to oralce , then choice one which has connection pool.


Oracle Database on Windows and .NET FAQ
Oracle offers a variety of data access methods for COM-based, .NET-based, and Win32/64-based programming languages. Oracle developers can use any one of the following data access drivers based on the Windows programming language employed:

.NET: Oracle Data Provider for .NET, OLE DB.NET via Oracle Provider for OLE DB, ODBC.NET via Oracle ODBC Driver
COM: Oracle Objects for OLE (OO4O), Oracle Provider for OLE DB via Microsoft's ActiveX Data Objects (ADO)
PL/SQL and Java (server-side programming) to COM Automation servers (e.g. Microsoft Office): COM Automation Feature
Win32/64: ODBC Driver, Oracle Call Interface (OCI), Oracle Provider for OLE DB


Pooling=>
Solution 1
Oracle Data Provider.NET (ODP) and built a C# window service.
Getting Started with Oracle Data Provider for .NET (C# Version)
Solution 2
Connection Pooling in ADO .NET
reference from Connection Pooling in ADO .NET

ODBC V.S. OLEDB V.S. ODP Performance
reference from here
Oracle odbc 插入一萬筆要51秒，而OleDB只要14秒，用Oracle donet client 只要八秒


MDAC Architecture