Summary

You can use the ConnectionStringBuilder object to accurately create connection strings because it provides validation of all of the available connection string settings. The ConnectionStringBuilder also plays an important role in the creation of database platform-independent code. You use the connection string to turn on advanced connectivity options such as connection pooling, clustered connections, and asynchronous access.

Connection pooling improves application responsiveness by allowing users to reuse existing active connections instead of creating new connections when a request is made to the database. A client-side connection manager maintains a list, or pool, of available connections that can be reused. Use the using statement to ensure that the Dispose method is called on the connection and command objects to avoid connection leaks.

Implementing a failover partner for clustered connections is as easy as setting the Failover Partner connection string setting. When a failover occurs, the server specified by the Failover Partner setting is used as the data source.

Asynchronous access to data can greatly improve the performance or perceived performance (responsiveness) of your application. To code asynchronous access in your application, use the Asynchronous Processing setting in the connection string and call one of the command object's Begin methods. And finally, the SqlConnection object provides statistical information that may be retrieved if the statistics have been enabled.