ADO.NET Overview

Core Objects

ADO.NET is built around a set of core objects that provide access to data. These include:

• DataSet: An in-memory representation of data. It can hold multiple tables of data, along with their relationships and constraints. It is disconnected from the data source, meaning you can work with it without an active connection to the database.
• DataTable: Represents a single table of data in memory, similar to a database table. It contains columns (DataColumn) and rows (DataRow).
• DataConnection: Represents an open connection to a data source.
• DataCommand: Represents a command to execute against a data source.
• DataReader: Provides a forward-only, read-only stream of data from a data source. It is highly efficient for reading large amounts of data.
• DataAdapter: A bridge between a DataSet and a data source. It is used to fill a DataSet with data from a data source and to propagate changes made to the DataSet back to the data source.

Connected vs. Disconnected Data Access

ADO.NET supports both connected and disconnected data access models:

Connected Data Access

This model uses a DataConnection and typically a DataReader to retrieve data. The connection remains open while data is being read. This is generally more efficient for read-only scenarios where you need to process data as it's retrieved.

using System.Data.SqlClient;

// ...

using (SqlConnection connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    string query = "SELECT CustomerID, CompanyName FROM Customers";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"{reader["CustomerID"]} - {reader["CompanyName"]}");
            }
        }
    }
}
                

Disconnected Data Access

This model involves using a DataSet and a DataAdapter. The DataAdapter fetches data into the DataSet, and then the connection to the data source can be closed. Modifications can be made to the DataSet, and then the DataAdapter can be used again to update the data source.

using System.Data.SqlClient;
using System.Data;

// ...

string connectionString = "YourConnectionString";
string query = "SELECT OrderID, OrderDate FROM Orders";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
    DataSet dataSet = new DataSet();

    adapter.Fill(dataSet); // Fills the DataSet with data

    // Work with the DataSet (e.g., display data, modify rows)
    foreach (DataRow row in dataSet.Tables[0].Rows)
    {
        Console.WriteLine($"Order ID: {row["OrderID"]}, Date: {row["OrderDate"]}");
    }

    // To update the database, you would typically configure the SqlCommandBuilder
    // and then call adapter.Update(dataSet);
}
                

Key Benefits

• Performance: Optimized for common data access scenarios.
• Scalability: Efficiently handles large datasets and concurrent access.
• Flexibility: Supports various data sources and access patterns.
• Rich Functionality: Provides powerful features for data manipulation, caching, and synchronization.