Overview

ADO.NET allows sourcing from various databases, but does not provide a single set of classes to perform the task. Different sources require different protocols; for example, older sources may employ ODBC protocol while newer use OleDb protocol. Some sources allow direct communication through .NET class libraries.

ADO.NET provides different sets of libraries for interaction with different sources. These libraries, known as data providers, follow:

Name	API Prefix	Description
ODBC Data Provider	Odbc	It supports ODBC interface data sources, typically older databases.
OleDb Data Provider	OleDb	It supports OleDb interface data sources such as Access or Excel.
Oracle Data Provider	Oracle	It supports Oracle databases.
SQL Data Provider	Sql	It supports SQL Server.
Borland Data Provider	Bdp	It supports many databases, e.g., Interbase, IBM DB2, and more.

The API prefix applies to data operation objects; for example, a connection object, used to connect to the source, applies OleDb for an appropriate source with a connection object named “OleDbConnection.” All objects in this guide use the Sql prefix to operate on data with SQL Server.

Objects execute data operations. ADO.NET employs many objects and each provides a window into how to work with data in various ways. The most important objects follow:

1. The SqlConnection Object – Before any interaction happens, the application must connect to the data source. The connection identifies the database server, name, user name, credentials, and parameters required for a connection. Command objects use connection objects to route commands to the correct source.
2. The SqlCommand Object – Database interaction requires specification of actions. The command object specifies those actions by sending SQL statements to the database through the connection object. The command object alone can execute commands directly, or a reference assigned to a command object and to an SqlDataAdapter can perform them.
3. The SqlDataReader Object – Many simple operations only require retrieving a data stream for reading. The data reader object gathers the results of a SELECT statement from the command object. It performs in the interest of performance, delivering fast, forward-only streams. It only allows sequential retrieval of data from the stream, which offers accelerated performance, however, the dataset object proves more appropriate for data manipulation.
4. The DataSet Object – Dataset objects are in-memory data representations containing multiple datatable objects. These tables hold columns and rows like any other table, and allow for the definition of table relationships, particularly parent-child relationships. The dataset specifically targets management of data in memory and support for disconnected data operations. It requires no prefix because all providers use dataset.
5. The SqlDataAdapter Object – Some data, read-only data, requires few changes to the data source. Some operations require data caching to minimize database calls for unchanging data.
6. The SqlConnection Object – Before any interaction happens, the application must connect to the data source. The connection identifies the database server, name, user name, credentials, and parameters required for a connection. Command objects use connection objects to route commands to the correct source. • The SqlCommand Object – Database interaction requires specification of actions. The command object specifies those actions by sending SQL statements to the database through the connection object. The command object alone can execute commands directly, or a reference assigned to a command object and to an SqlDataAdapter can perform them.
7. The SqlDataReader Object – Many simple operations only require retrieving a data stream for reading. The data reader object gathers the results of a SELECT statement from the command object. It performs in the interest of performance, delivering fast, forward-only streams. It only allows sequential retrieval of data from the stream, which offers accelerated performance, however, the dataset object proves more appropriate for data manipulation.
8. The DataSet Object – Dataset objects are in-memory data representations containing multiple datatable objects. These tables hold columns and rows like any other table, and allow for the definition of table relationships, particularly parent-child relationships. The dataset specifically targets management of data in memory and support for disconnected data operations. It requires no prefix because all providers use dataset.
9. The SqlDataAdapter Object – Some data, read-only data, requires few changes to the data source. Some operations require data caching to minimize database calls for unchanging data. The data adapter provides a means for performing such tasks by aiding in disconnected mode data management. A data adapter holds a reference to the connection object, and automatically opens and closes connections during read/write operations. It also holds command object references for SELECT, INSERT, UPDATE, and DELETE operations. A data adapter defined for a particular table within a dataset manages all database communication, only requiring the user to instruct the adapter when to load or write.

Though ADO.NET provides accommodation for using specific resources and their native language, it proves much more tedious, error-prone, and counterproductive to utilize standard ADO. It also leads to bloated code impacting readability, portability, and maintenance. ASP.NET was designed for use with C# or Visual Basic, and the LINQ database querying language. LINQ fully integrates with C# and VB code offering native code database operations, and simplifying operations even outside of database manipulation. LINQ of course utilizes ADO.NET for operating on various data sources, but this occurs in a more productive and less cluttered way than standard ADO.NET development. A best practice would be to utilize C#-LINQ or VB-LINQ when possible rather than employing ADO.NET standard objects.