What is connection pooling in C# and how to achieve it?

These are used to import namespaces (or create aliases for namespaces or types).

These go at the top of the file, before any declarations.

using System;
using System.IO;
using WinForms = global::System.Windows.Forms;
using WinButton = WinForms::Button;

The using statement ensures that Dispose() is called even if an exception occurs when you are creating objects and calling methods, properties and so on. Dispose() is a method that is present in the IDisposable interface that helps to implement custom Garbage Collection. In other words, if we are doing some database operation (Insert, Update, Delete) but somehow an exception occurs, then here the using statement closes the connection automatically.here's no need to call the connection Close() method explicitly.

Another important factor is that it helps in Connection Pooling. Connection Pooling in .NET helps to eliminate the closing of a database connection multiple times. It sends the connection object to a pool for future use (next database call). The next time a database connection is called from your application the connection pool fetches the objects available in the pool. So it helps to improve the performance of the application. So when we use the using statement the controller sends the object to the connection pool automatically, and there is no need to call the Close() and Dispose() methods explicitly.

using (Stream input = File.OpenRead(filename)) {
   ...
}

Example

string connString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;";
using (SqlConnection conn = new SqlConnection(connString)) {
   SqlCommand cmd = conn.CreateCommand();
   cmd.CommandText = "SELECT CustomerId, CompanyName FROM Customers";
   conn.Open();

   using (SqlDataReader dr = cmd.ExecuteReader()) {
      while (dr.Read())
      Console.WriteLine("{0}\t{1}", dr.GetString(0), dr.GetString(1));
   }
}

In the above code we are not closing any connection, it will close automatically. The using statement will call conn.Close() automatically due to the using statement

(using (SqlConnection conn = new SqlConnection(connString)) and the same for a SqlDataReader object.And also if any exception occurs it will close the connection automatically.

Nizamuddin Siddiqui

Published on 25-Nov-2020 16:26:02