ADO.NET Checklist

ADO.NET, the middleware that enables database access, is at the core of every .NET-based database application. Therefore, the proper use of ADO.NET is the most crucial factor in writing efficient, high-performing database applications. Checking the following eight points will help you ensure that your ADO.NET applications are performing their best.

Use the .NET Data Provider for SQL Server. ADO.NET offers three data providers that you can use to connect to SQL Server, but these providers are not all created equal. If your application is connecting to SQL Server, be sure to use the .NET Data Provider for SQL Server that you can find in the System.Data.SqlClient namespace. Unlike the other data providers, the .NET Data Provider for SQL Server connects to SQL Server by using SQL Server's native Tabular Data Stream (TDS) protocol, which provides far better performance for connecting to SQL Server databases than ODBC or OLE DB.

Use Windows authentication. Windows authentication gives your applications a more secure connection to SQL Server than mixed authentication does. Applications that use mixed SQL Server authentication must pass the database login over the network, where it's susceptible to interception by malicious users or intruders. In addition to providing a more secure login, Windows authentication can also make your management easier by giving you only one set of network credentials to maintain.

Take advantage of built-in connection pooling. One great feature that the .NET Data Provider for SQL Server gives to Web applications is the ability to automatically perform connection pooling. The .NET Data Provider for SQL Server creates connection pools according to the values in the SqlConnection object's ConnectionString property. If the values in the ConnectionString match an existing connection, ADO.NET automatically pools the connection. If the values don't match, it creates a new connection.

Avoid dynamic SQL. Although dynamic SQL can be useful for creating ad-hoc queries, it can also be the source of SQL injection attacks. In most cases, you can replace dynamic SQL by using either stored procedures or parameterized SQL. Stored procedures and parameterized SQL queries not only are more secure, but also perform better because SQL Server precompiles the query plan.

Take advantage of the SqlDataReader object. Bar none, using the SqlDataReader is the fastest way to retrieve data from a SQL Server database. In many cases, if your application just needs to retrieve a read-only copy of the data, you can avoid the overhead of a DataSet by substituting the SqlDataReader object for the DataSet.

Take advantage of the DataView object. Though often overlooked, the DataView object gives your ADO.NET applications a couple of important capabilities. First, using the DataView's Find method is the fastest way of locating data in a DataSet. Second, the DataView can effectively enhance your application's flexibility by letting you subset the data, re-sort the data, or create calculated columns. And third, the DataView is a fully bindable object, which enables you to bind its contents to Windows or Web Forms.

Retrieve only the data that you need. When constructing your T-SQL SELECT statements, make sure that they select only the columns that your application is going to use and that they're using the appropriate WHERE clauses to limit the data returned to just what your application needs. Careless SQL results in excessive work for the database, the network, and your application—consuming valuable system resources and hindering scalability and performance.

Explicitly close all your connections. The ADO.NET Connection object isn't necessarily destroyed when it goes out of scope. Using the Close method is important to ensure that the resources the Connection object allocates are released when your application no longer needs them. Make sure that every Connection-object Open call is always followed by a Close. Your application can call the Close method multiple times—even when the connection is already closed—without raising an error.

—Michael Otey