ADO.NET Connection Pooling

 ADO.NET Connection Pooling 

 In this post, we learn about ado.net Connection Pooling. When we database connection open & close by using ado.net is a time-consuming process. For this reason, ADO.NET automatically stores database connections in a pool. Connection pooling offers a great performance improvement because we don't have to wait for a brand new connection to the database to be established when there's a suitable connection already available. When we close a connection then that connection isn't actually closed; instead, this connection is marked as unused and stored in the pool, ready to be used again.
 If we supply the same details in the connection string (same database, username, password, and so on), then the connection from the pool is retrieved and returned to we. we then use that same connection to access the database.
 When using a OracleConnection object, we can indicate the maximum number of connections allowed in the pool by specifying max pool size in our connection string (the default is 100). We can also indicate the minimum number of connections in the pool by specifying min pool size (the default is 0).

 To Known more about ado.net click here.

 Now here we look sample code OracleConnection specifies a max pool size of 10 and a min pool size of 5:.

  Code: Program.cs  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;

namespace CsApp_Connection
{
    class  Program
    {
        public static void Main()
        {
            string connectionString = "data source=LearnAsp;user
                id=LearnAsp;"
            + "password=LearnAsp; Pooling=true; Max pool size=10; Min pool size=5;";

            Console.WriteLine("\tConnection Pooling Example."");
            OracleConnection myConn = new OracleConnection(connectionString);

                 for ( int count = 1; count <= 10; count++)
                {
                    Console.WriteLine("\tcount = " + count);
                    DateTime start = DateTime.Now;
                    myConn.Open();
                    TimeSpan timeTaken = DateTime.Now - start;
                    Console.WriteLine("\t\tMilliseconds = " + timeTaken.Milliseconds);
                    Console.WriteLine("\t\tmySqlConnection.State = " + myConn.State);
                    myConn.Close();
                }
            
        }
    }
} 

  Output  

 As we can see, the time to open the first connection is relatively long compared with the subsequent ones. This is because the first connection makes the actual connection to the database. When it is closed, it's stored in the connection pool. When the connection is then opened again, it's retrieved from the pool, and this retrieval is very fast.