In this blog we will go through a set  of best practices for working with ADO.NET:

1. Database Connection

 From performance perspective, it is important that While working with a database that we should open a connection with the database as late as possible and should close it as soon as possible. Also we should always use connection pooling with databases. This ensures that a connection  created is returned back to the connection pool for further usage by the system for another database call. The SQL Server managed provider supports connection pooling by default and the developer needs to put in minimum effort. We just need to modify the connection string to override default settings for connection pooling parameters.

2. Connection String

It is a good practice to store a connection string securely as it's compromise can lead to serious security breach. Hence storing a connection string as clear text is considered a bad practice. We can keep an encrypted connection string in the web.config file. For encryption/decryption of connection string in web.config,there are providers available in .NET framework.

3. DataReader & DataAdapter

Both are  widely used in Asp.Net applications to retrieve  data from the database. However we need to judiciously select either of them for  specific situations.  We should use the DataReader's connection oriented forward-only  data access for read only purpose. On the other hand DataAdapter is best used for disconnected data access architecture. DataAdapter object manages connection between DataSource and DataSet , reads the data from database and further binds the read data to DataSet.

4. Use stored procedures instead of embedded T-SQL

  It is a standard approach to compile the Data Manipulation Language statements into stored procedures. Stored procedures execute  faster than T-SQL statements because they are pre-compiled on the server and are reusable. We can use ADO.NET to execute embedded T-SQL statements directly however we should avoid this approach.

5. Use complex stored procedures instead of multiple retrievals

  We should use complex stored procedures rather that return multiple result sets by  making multiple calls to database. ADO.NET simplifies the process of working with multiple result sets. For example, we can make use of SqlDataReader object's NextResult method to iterate through the multiple result sets.

6. Use centralized data access functions

 
 We should always use a data access class that implements wrapper functions for basic data access operations. These wrapper functions expose a set of data access methods that are used to interact with the database. Any time a business object needs to access the database  it routes it's call through this data access class. In absence of this data access class this code becomes repetitive across the project.

7. Use of helper technologies

  ASP.NET provides complementary technologies that can be used in combination with  ADO.NET to provide optimized data access. One such technology is caching which can be used to reduce the number of queries being run against the database.

8. Use exception handling appropriately

 Exception handling , especially throwing errors is very expensive. Hence we should  implement structured error handling in the applications which by design prevent the code to fall into exception handlers. We should use the various types of error and type checking functions to detect errors before the compiler is forced to raise a runtime error.