ADO.NET Concepts

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, but it is also available for .NET Core and .NET 5+.

Introduction to ADO.NET

ADO.NET provides a rich set of components for building data-driven applications. It enables you to connect to a data source, execute commands, retrieve data, and process the results. ADO.NET is designed to be flexible and performant, supporting a wide range of data sources including relational databases, XML files, and other data sources.

Key Components of ADO.NET

ADO.NET is built around a set of core objects that work together to facilitate data access. The primary components are:

Connecting to a Data Source

The first step in accessing data is establishing a connection. This is done using a Connection object specific to your data provider. You'll typically need a connection string that contains the necessary details to locate and authenticate with the data source.


using System.Data.SqlClient;

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Operations with the connection...
    Console.WriteLine("Connection opened successfully!");
}

Executing Commands

Once connected, you can execute SQL commands using Command objects. You can execute queries that return data, or commands that perform data modification (INSERT, UPDATE, DELETE).


using System.Data.SqlClient;

// ... (connection established)

string sql = "SELECT CustomerID, CompanyName FROM Customers;";

using (SqlCommand command = new SqlCommand(sql, connection))
{
    // Execute reader for SELECT statements
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
        }
    }

    // Execute non-query for INSERT, UPDATE, DELETE
    // int rowsAffected = command.ExecuteNonQuery();
}

Working with DataReaders

DataReader objects offer an efficient way to retrieve data row by row. They are ideal when you don't need to manipulate the data or when dealing with very large result sets, as they don't load the entire dataset into memory.

Tip: Always use using statements with Connection, Command, and DataReader objects to ensure resources are properly disposed.

Disconnected Data Access with DataSet and DataTable

For scenarios where you need to work with data offline or perform complex data manipulation, the DataSet and DataTable objects are invaluable. A DataSet acts as an in-memory database, holding one or more DataTable objects.


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

// ... (connection established)

using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Products", connection))
{
    DataTable productsTable = new DataTable();
    adapter.Fill(productsTable);

    // Now you can work with productsTable in memory
    foreach (DataRow row in productsTable.Rows)
    {
        Console.WriteLine($"{row["ProductName"]} - ${row["UnitPrice"]}");
    }

    // To update the database, you would typically manage changes
    // in the DataTable and then use the DataAdapter's Update method.
}

Key Benefits of ADO.NET

Note: While ADO.NET is powerful, for many modern .NET applications, ORM (Object-Relational Mapper) frameworks like Entity Framework Core offer higher-level abstractions that simplify data access significantly.

Further Reading