Page 105 - DCAP312_WEB_TECHNOLOGIES_II

Unit 5: Database Access

Usually we are using two types of DataReader in ADO.NET. They are SqlDataReader and the Notes
OleDbDataReader. The System.Data.SqlClient and System.Data.OleDb are containing these
DataReaders respectively.
SQL was initially developed at IBM by Donald D. Chamberlin and Raymond
F. Boyce in the early 1970s.

5.7 Connection Pooling

Data-driven applications frequently access a database to query data. There are two broad ways
to access the data:
1. 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.
2. 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.
5.7.1 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 5.1 lists some of the
important parameters related to connection pooling:

Table 5.1: Parameters Related to Connection Pooling

Connection Default
String Description
Attributes Value
Pooling Decides whether connection pooling will be True
enabled. Possible values are true or false.
Min Pool Size Governs the minimum number of connections 0
that are maintained in the pool.
Max Pool Size Governs the maximum number of connections 100
that a pool can contain.
Connection Indicates a timeout value in seconds for which 15 seconds
Timeout the data provider tries to connect with the
database. Once this time is elapsed, an exception
is thrown.

LOVELY PROFESSIONAL UNIVERSITY 99