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

Updated on 25-Nov-2020 11:56:02

• Related Questions & Answers
• What was your dream and how did you achieve it?
• What is loose coupling how do we achieve it using Java?
• Is it possible to achieve a slice chain in MongoDB?
• What is Binary Serialization and Deserialization in C# and how to achieve Binary Serialization in C#?
• What is overthinking and how to overcome it?
• What is SignalR and how to use it?
• What is Bluesnarfing and how to prevent it?
• What is TCP Connection Release?
• What is Connection less Service?
• What is ECMAScript and how it is related to JavaScript?
• What is CIDR and how it works?
• What is Anxiety Neurosis and how to overcome it?
• What is computer illiteracy and How to treat it
• What is CryptoLocker Ransomware and How to Remove it?
• What is Proprietary Information and How to Protect It?