ADO.NET isn't just a fancy new wrapper for the COM-based version of ADO. Microsoft built ADO.NET from the ground up for developing Web-based n-tier database applications.
Related: ADO.NET Don'ts
Here are my favorite tips to help you write better ADO.NET applications.
7. Use the System.Data.SqlClient Namespace
ADO.NET provides three .NET Data Providers that connect to SQL Server. Built on the SQL Server Tabular Data Stream (TDS) protocol, the System.Data.SqlClient namespace gives you better performance than the other providers.
6. Use the SqlTypes Namespace
Using the SqlTypes namespace for SQL Server data helps prevent type-conversion errors. If you use the native .NET data types, the ADO.NET Framework will automatically convert to and from SqlTypes. By using the SqlTypes data types, you can control the conversion process and avoid overhead.
5. Incorporate Structured Error Handling
The .NET Framework provides a common set of structured error-handling capabilities through an Exception object and a Try-Catch-Finally structure. These capabilities make your ADO.NET applications more robust by enabling them to trap and programmatically respond to error conditions.
4. Take Advantage of Built-In Connection Pooling
The .NET Data Provider for SQL Server automatically creates a connection pool, based on values you specify in the ConnectionString property of the SqlConnection object, when SQL Server opens a connection. If the values in the ConnectionString match an existing connection, SQL Server adds the new connection to the existing pool. Otherwise, SQL Server creates a new pool.
3. Take Advantage of Stored Procedures
SQL Server adds a compiled data-access plan to the database when you create a stored procedure, resulting in faster execution of queries and other data-manipulation actions. You can also use stored procedures to implement stronger database security, restricting end-user access to just the data that the stored procedures return.
2. Cache Nonvolatile Web Application Data
Built on a disconnected model, the ADO.NET DataSet provides the ideal basis for caching Web application data. This technique isn't suited for data that frequently changes, but for data that's primarily static, it lets the application quickly access the in-memory data cached in the DataSet.
1. Explicitly Close Your Connections
In ADO.NET applications, as opposed to ADO applications, the connection object isn't necessarily destroyed when it goes out of scope. You must execute the Close method to make sure you release all connection resources. You can call the Close method multiple times, even when the connection is already closed, without raising an error.
