ADO.NET Documentation

Introduction to ADO.NET

ADO.NET is a set of .NET Framework classes that expose data access services to the .NET programmer. ADO.NET provides a rich set of components for creating distributed, data-sharing applications. It is an essential part of the .NET Framework for building applications that interact with data sources such as relational databases, XML, and other data sources.

ADO.NET allows you to connect to data sources, execute commands, and retrieve data into .NET objects. It also provides the ability to manipulate that data and persist it back to the data source.

Key Components of ADO.NET

The core of ADO.NET consists of a set of providers, each of which is designed to access a specific data source. The most commonly used providers include:

System.Data.SqlClient: For connecting to Microsoft SQL Server.
System.Data.OleDb: For accessing data sources through OLE DB, such as Microsoft Access.
System.Data.Odbc: For accessing data sources through ODBC.
System.Data.OracleClient: For connecting to Oracle databases (note: this provider is deprecated in .NET Core and .NET 5+).

Managed Providers

Each managed provider includes a set of classes that implement a consistent, language-independent, and COM+ independent interface. The primary classes used in ADO.NET are:

Connection: Establishes a connection to a data source.
Command: Represents a Transact-SQL statement or stored procedure to execute against a data source.
DataReader: Provides a way to read a forward-only stream of rows from a data source.
DataAdapter: Fills a DataSet and resolves changes to the data in the DataSet back to the data source.
DataSet: An in-memory representation of a database. It can hold multiple tables, relationships, and constraints.

Working with DataSets

The DataSet object is a cornerstone of ADO.NET's disconnected data access model. It is a memory-resident representation of data that can hold multiple tables, relationships between them, and constraints.

Advantages of DataSets:

Allows manipulation of data independently from the data source.
Can contain data from multiple, heterogeneous sources.
Supports caching of changes, enabling optimistic concurrency.

Example: Populating a DataSet


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

// Assuming you have a connection string
string connectionString = "Your_Connection_String_Here";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string sql = "SELECT CustomerID, CompanyName FROM Customers";
    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
    DataSet dataSet = new DataSet();
    adapter.Fill(dataSet, "Customers"); // Fill the DataSet with data, naming the table

    // Now you can access the data
    foreach (DataRow row in dataSet.Tables["Customers"].Rows)
    {
        Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}");
    }
}

Executing Commands and Data Readers

For scenarios where you need to read data efficiently and sequentially, the DataReader is the preferred choice. It provides a direct, fast, and memory-efficient way to retrieve data.

Example: Using a DataReader


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

string connectionString = "Your_Connection_String_Here";
string sql = "SELECT ProductID, ProductName, UnitPrice FROM Products WHERE CategoryID = @CategoryID";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        // Add parameter to prevent SQL injection
        command.Parameters.AddWithValue("@CategoryID", 1);

        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine($"Product ID: {reader["ProductID"]}, Name: {reader["ProductName"]}, Price: {reader["UnitPrice"]}");
                }
            }
            else
            {
                Console.WriteLine("No products found.");
            }
        }
    }
}

Connecting to Data Sources

Establishing a connection is the first step in any data access operation. The Connection object manages the lifecycle of the connection to the database.


// Example using SqlConnection
string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
SqlConnection connection = null;
try
{
    connection = new SqlConnection(connectionString);
    connection.Open();
    Console.WriteLine("Connection opened successfully.");
}
catch (SqlException ex)
{
    Console.WriteLine($"Error connecting to database: {ex.Message}");
}
finally
{
    if (connection != null && connection.State == ConnectionState.Open)
    {
        connection.Close();
        Console.WriteLine("Connection closed.");
    }
}

Always ensure connections are closed or disposed of properly, preferably using the using statement to guarantee resource cleanup.