ADO.NET APIs

This section provides comprehensive documentation on the ADO.NET APIs, which are essential for accessing and manipulating data in your .NET applications. ADO.NET offers a rich set of components for connecting to data sources, executing commands, retrieving data, and managing results.

Core Components of ADO.NET

ADO.NET is built around a set of fundamental classes that provide a consistent, object-oriented interface to data sources. The primary components include:

Connections

Connection objects represent a unique session with a data source. They are used to establish a connection and are typically managed using connection strings.

Commands

Command objects are used to execute commands against a data source. This can include executing SQL statements, stored procedures, or other data manipulation language (DML) statements.

Example of executing a command:


using (SqlConnection connection = new SqlConnection("your_connection_string"))
{
    SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM Employees", connection);
    connection.Open();
    int count = (int)command.ExecuteScalar();
    Console.WriteLine($"Total employees: {count}");
}

DataReaders

DataReader objects provide a forward-only, read-only stream of data from a data source. They are highly efficient for retrieving large result sets that do not need to be buffered in memory.

Example of using a DataReader:


using (SqlConnection connection = new SqlConnection("your_connection_string"))
{
    SqlCommand command = new SqlCommand("SELECT EmployeeID, FirstName, LastName FROM Employees", connection);
    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"{reader["FirstName"]} {reader["LastName"]}");
        }
    }
}

DataSets and DataTables

DataSet objects represent an in-memory cache of data that can be retrieved from a data source. A DataSet can contain multiple DataTable objects, each representing a table of data. These are useful for disconnected scenarios where you need to work with data without an active connection.

Note: While DataSet is powerful, for modern applications, consider using lightweight alternatives like DataTable or custom models with data binding, especially when performance is critical.

Providers and Data Sources

ADO.NET supports a wide range of data sources through different data providers:

Key Concepts

Tip: For new development, consider using the Entity Framework Core (EF Core), a modern object-relational mapper (ORM) that simplifies data access and provides a higher level of abstraction over ADO.NET.

Related Topics