Database Connection

Whilst using a database, we should open a connection with the database as delayed as possible and should close it as early as possible.

It will always be preferable to use connection pooling with databases. The benefit of connection pooling, is that connections placed in the pool are used over again in order that a whole new connection does not have to be established, reducing amount of time needed to establish connections. The overhead for introduction of connections will likely be bypassed by using connections from the pool. The connection pool size can be defined within the connection string, 100 is standard. For the very same connection string, ADO.NET utilizes the same connection pool.

It is recommended to call the Close method on connection objects. Calling the Close method returns the connection towards the connection pool. This connection from the connection pool can then be re-utilized. You should not rely on the garbage collector to destroy connection objects.

It is best to utilize the “using” block of C# for connections. This automatically disposes a connection as connection objects implement the IDisposable interface.

Transaction

Most company distributed programs use transactions. There are 2 kinds of transactions – manual and automatic. In ADO.NET, we could use a transaction by calling the BeginTransaction method on the connection object. The use of automatic transaction could be an overhead on overall performance.

DataReader & DataSet

The DataReader should not be accessed through layers. It is made for connected data access and it needs to be used after reading data.

Datareader is faster then Dataset because Dataset is used to hold 1 or more tables with data while DataReader hold only single table data at one time and Only one DataReader can be opened on a single associated connection.