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 applicationWindows Forms, ASP.NET, or Web servicesdata access is concentrated in the back end of the system and is implemented using a common programming interfacethe ADO.NET object model.
ADO.NET comes with a bunch of objects that represent the various logical entities of a data access modelconnection 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 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