Connection Pooling

Question

Hello friend's How to use Connection Pooling in C#?

Krishna Garad · Answer

You can specify the min and max pool size in connection string like Min Pool Size=5;Max Pool Size=10

Satyapriya Nayak · Answer

Hi Ganesh Connection Pooling in ADO.NET Introduction Performance is an important consideration in modern software systems. Developers look for each and every opportunity to fine tune their applications for performance. To that end ADO.NET connection pooling can significantly help you to write data access code that is performance oriented. Although enabled by default one must know how to take control and fine tune the connection pooling to make the most of the feature. What Is Connection Pooling? Data-driven applications frequently access a database to query data. There are two broad ways to access the data: Open a connection with the database as your application starts and keep it open throughout the life of your application. Fire all the queries through this open connection. Open a connection just before executing a query and close it immediately once the query execution is over. As you might have guessed the former way is better as far as performance is concerned. However it suffers from a serious limitation. Because one connection is held open for a large window in terms of scalability it is extremely poor. In multiuser scenarios such as web sites this will restrict the total number of users who can access your application. The second way is safe and follows the philosophy—Open the connection as late as possible and close the connection as early as possible. Although this approach is good in terms of scalability and multiuser scenarios it has its own disadvantage. The frequent creation opening closing and destroying of the database connections results in performance penalty. This is precisely where database connection pooling come handy. A database connection pool is a set of database connections that are held open with the database and are kept ready to serve. This way a set of connections is created once the pool comes into existence. When a user requests a connection an already created connection is served from the pool. Similarly when a user closes a connection it is returned to the pool instead of being destroyed. This can significantly improve the performance of data access. Connection Pooling in ADO.NET In ADO.NET database connection pooling is enabled by default. You can configure various aspects of connection pooling via a connection string. The following table lists some of the important parameters related to connection pooling: Connection String Attribute Description Default Value Pooling Decides whether connection pooling will be enabled. Possible values are true or false. True MinPoolSize Governs the minimum number of connections that are maintained in the pool. 0 MaxPoolSize Governs the maximum number of connections that a pool can contain. 100 Connection Timeout Indicates a timeout value in seconds for which the data provider tries to connect with the database. Once this time is elapsed an exception is thrown. 15 seconds A connection pool gets created when the first connection is opened with the database. Some Rules To get the most out of database connection pooling it is important to remember the following rules: If you want to pool a set of connections all of them must have the same connection string. All the connections must have the same security context. If you are using Windows integrated security the Windows user responsible for starting the application process is used to determine the security context. All the connections must belong to the same process. Testing Connection Pooling Behavior Now that you have some idea about what connection pooling is you can develop a simple application to verify the learned facts. Begin by creating a new Windows Application and design its default form as shown below: Figure 1: The default form The form contains a checkbox control that governs whether or not connection pooling is enabled. If enabled you also can specify the minimum pool size and maximum pool size. Clicking on the Open a connection button opens a database connection so that a connection pool is created. To test the behavior of MaxPoolSize attribute you open multiple connections with the database at a time. The number of connections to open can be specified in the relevant textbox. Now go in the code window of the form class and add a helper method named GetConnectionString() as shown below: private string GetConnectionString () { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder (); builder . DataSource = localhost ; builder . InitialCatalog = northwind ; if ( checkBox1 . Checked ) { builder . Pooling = true ; builder . MinPoolSize = int . Parse ( textBox2 . Text ); builder . MaxPoolSize = int . Parse ( textBox3 . Text ); } else { builder . Pooling = false ; } builder . UserID = sa ; builder . Password = pass@word ; return builder . ConnectionString ; } The GetConnectionString() method returns a string representing the database connection string. Inside it uses the SqlConnectionStringBuilder built-in class to build the connection string. It is assumed that you have Northwind database installed on your SQL server. The code simply sets properties of SqlConnectionStringBuilder class. Notice the Pooling MinPoolSize and MaxPoolSize properties. These properties will add the appropriate connection string attributes. Please refer the below links http://www.c-sharpcorner.com/UploadFile/dsdaf/ConnPooling07262006093645AM/ConnPooling.aspx http://www.c-sharpcorner.com/uploadfile/mahesh/understanding-connection-pooling-in-ado-net/default.aspx http://www.c-sharpcorner.com/UploadFile/munnamax/connectionpooling08112007062332AM/connectionpooling.aspx http://www.codeproject.com/Articles/17768/ADO-NET-Connection-Pooling-at-a-Glance http://www.codeguru.com/csharp/csharp/cs_network/database/article.php/c14885/Using-Connection-Pooling-in-ADONET.htm Thanks