Browse DevX
Sign up for e-mail newsletters from DevX


ADO.NET Best Practices

ADO.NET is a powerful toolbox but it's not a software magic wand. Learn about common best practices for using three key element of any data access strategy: connections, security, and transactions.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

o get the most out of ADO.NET classes, developers must fully understand the model and study a few best practices. Based on years of real-world experience with ADO, ADO.NET provides a richer set of more powerful tools. But, ADO.NET is not designed to be an out-of-the-box tool that reduces any programming work to just point-and-click. Database connections, data access security, pooling, and transactions are all key elements of any real-world enterprise distributed application. No matter which technology you use for each tier of your .NET application—Windows Forms, ASP.NET, or Web services—data access is concentrated in the back end of the system and is implemented using a common programming interface—the ADO.NET object model.

ADO.NET comes with a bunch of objects that represent the various logical entities of a data access model—connection and transaction in particular. To create a connection, that is to establish a physical link with the backend database, you instantiate the connection object on a particular .NET data provider. To create a transaction, you can either instantiate a transaction object over an existing connection or explicitly execute a BEGIN TRAN SQL statement. Around a connection and a transaction, though, a number of choices lie; they are all important and critical to the health and effectiveness of the application. How do you store the connection string and how do you protect the sensitive data (such as the password) it may contain? How do you design your overall data access strategy to take into account security (that is, authentication and authorization) without hitting too much performance and scalability? If you need a transactional behavior, what would be an effective way of implementing and controlling transactions? Should you go for automatic or manual transactions? These questions are only a few examples of the choices you face when devising and building the data access layer of a .NET application. ADO.NET helps provide the tools. Smart developers take advantage of those tools in light of accepted best practices.

You must close the connection object to make it return to the pool. You should never rely on the garbage collector to silently free connection objects.
Database Connections
Database connections represent a critical, expensive, and limited resource. Optimizing how you use them is therefore fundamental for any real application; not just for .NET Framework applications, but in particular for multi-tier Web applications. The bottom line for database connections can be summarized in the following two points:
  • Store connection strings securely
  • Open connections late and close them early
A connection string is the key to connect to the database and access its content. The connection string contains the credentials of the user as well as any information needed to identify the data to retrieve. When it comes to performing a database operation, the user credentials are the most important element and the key that enable data access and determines the rights on the data. Developers should think in terms of user roles, not user accounts, choose Windows authentication, and exploit connection pooling. Let's examine these points in more detail.

Thanks for your registration, follow us on our social networks to keep up-to-date