Microsoft Docs

Introduction to ADO.NET

ADO.NET is a set of .NET Framework classes that expose data access services to the .NET programmer. ADO.NET is part of the .NET Framework, and it is used to access data sources like relational databases and XML. It provides a consistent programming model regardless of the data source being accessed.

ADO.NET allows you to build components that interact with data sources. Applications that use ADO.NET can retrieve data from a data source, process that data, and update data stores. ADO.NET is designed to work with multiple data sources, from simple files to complex relational databases.

Key Components of ADO.NET

The core components of ADO.NET are:

• DataSet: A DataSet is an in-memory representation of data. It can hold multiple tables, relationships between tables, and constraints. It's a disconnected data structure, meaning it's not directly connected to the data source once populated.
• DataTable: Represents a single table of data in memory. It contains a collection of DataRow objects and a DataColumn collection.
• DataRow: Represents a single row of data within a DataTable.
• DataColumn: Represents a column in a DataTable.
• DataAdapter: A DataAdapter acts as a bridge between a DataSet and a data source. It is used to retrieve data from a data source and to save data from the DataSet back to the data source.
• DbConnection: Represents a unique session to a data source. It is used to establish a connection to the database.
• DbCommand: Represents an SQL statement or stored procedure to be executed against a data source.
• DbDataReader: Provides a way to retrieve a forward-only stream of rows from a data source. It is a connected object, meaning the connection remains open while reading.
• DbParameter: Represents a parameter for a DbCommand, allowing for parameterized queries to prevent SQL injection.

Disconnected Data Access

One of ADO.NET's most significant features is its support for disconnected data access. This means that your application can retrieve data, close the connection to the data source, manipulate the data in memory (using DataSet objects), and then later reconnect to update the data source.

This disconnected model offers several benefits:

• Scalability: Applications can handle more users because connections to the data source are held for shorter periods.
• Flexibility: Data can be manipulated without an active connection, allowing for operations like caching and offline processing.
• Performance: Reduces network traffic and server load.

Connected vs. Disconnected Scenarios

ADO.NET supports both connected and disconnected access patterns:

• Connected Access: Uses objects like DbConnection and DbDataReader. The connection to the data source is open for the duration of the data retrieval or manipulation. This is generally faster for read-only scenarios where data is processed immediately.
• Disconnected Access: Primarily uses DataSet and DataAdapter. Data is fetched into a DataSet, the connection is closed, and modifications are made to the DataSet. The DataAdapter is then used to synchronize these changes back to the data source.

Example: Retrieving Data with DbDataReader

Here's a simple example of how to retrieve data using a DbDataReader:

using System;
using System.Data;
using System.Data.SqlClient; // Or other provider

public class DataReaderExample
{
    public static void ReadProducts(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT ProductID, ProductName, UnitPrice FROM Products;";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"ID: {reader["ProductID"]}, Name: {reader["ProductName"]}, Price: {reader["UnitPrice"]}");
                        }
                    }
                    else
                    {
                        Console.WriteLine("No products found.");
                    }
                }
            }
        }
    }
}
                

Example: Populating a DataSet with DataAdapter

And an example demonstrating disconnected access with DataSet and DataAdapter:

using System;
using System.Data;
using System.Data.SqlClient; // Or other provider

public class DataSetExample
{
    public static void GetData(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers;";
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
            DataSet dataSet = new DataSet();

            adapter.Fill(dataSet, "Customers"); // Populate the DataSet

            // Now you can work with dataSet.Tables["Customers"] disconnected
            foreach (DataRow row in dataSet.Tables["Customers"].Rows)
            {
                Console.WriteLine($"ID: {row["CustomerID"]}, Company: {row["CompanyName"]}, Contact: {row["ContactName"]}");
            }

            // To update the database, you would typically use CommandBuilders or explicit UPDATE/INSERT/DELETE commands
        }
    }
}
                

Conclusion

ADO.NET provides a powerful and flexible framework for accessing and managing data in .NET applications. Understanding its core components and the principles of connected vs. disconnected data access is crucial for building robust and efficient data-driven applications.