Programming Microsoft ASP.NET 2.0 Core Reference

The functionalities supplied by a .NET data provider fall into a couple of categories: the capability of populating container classes, and the capability of setting up a connection and executing commands.

The .NET Framework comes with data providers for SQL Server, Oracle, and all OLE DB and ODBC data sources.

A data provider is faster and more appropriate than any other database technology for data-access tasks in .NET. Especially effective with SQL Server, a managed data provider hooks up at the wire level and removes any sort of abstraction layer.

The data provider supplies an object to establish and manage the connection to a data source. This object implements connection pooling.

In .NET applications, connection strings can be stored in a special section of the configuration file and encrypted if required.

The data provider supplies an object to execute commands on an open connection and optionally within a transaction. The command object lists various execute methods to account for query and nonquery commands. Commands can execute either synchronously or asynchronously.

Data returned by a query command are cached in a data reader object, which is a kind of optimized read-only, forward-only cursor.

Local and distributed transactions can be managed through the TransactionScope class introduced with ADO.NET 2.0.