ADO.NET

ADO.NET is a set of .NET classes that expose data access services to the .NET programmer. ADO.NET provides consistent access to data sources such as SQL Server and XML, as well as data sources that can be accessed through OLE DB and ODBC. It enables you to write managed code that creates and manages data manipulation and data access.

Key Components

ADO.NET provides a rich set of components for working with data. The core objects include:

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. It is the most efficient way to retrieve data when you need to read all rows in a result set.
DataAdapter: Bridges the gap between a DataSet and a data source for retrieving and saving data. It uses Command objects to execute SQL statements against a data source.
DataSet: An in-memory representation of data that can hold multiple tables, relationships, and constraints. It is disconnected from the data source, allowing you to work with data offline.
DataTable: Represents a single table of data in memory.
DataRow: Represents a single row within a DataTable.
DataColumn: Represents a column within a DataTable.

Core Concepts

Understanding these concepts is crucial for effective use of ADO.NET:

Connections and Commands

Establishing a connection is the first step to interacting with a data source. Once connected, you can execute commands to retrieve or manipulate data.

Example: Executing a Simple Query

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

public class Sample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string sql = "SELECT COUNT(*) FROM MyTable";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                int count = (int)command.ExecuteScalar();
                Console.WriteLine($"Number of rows: {count}");
            }
        }
    }
}

Data Readers

DataReader is ideal for scenarios where you need to iterate through a large result set quickly without loading it all into memory.

Example: Using DataReader

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

public class Sample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string sql = "SELECT CustomerID, CompanyName FROM Customers";
            using (SqlCommand command = new SqlCommand(sql, connection))
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
                }
            }
        }
    }
}

DataSets

DataSet is a powerful tool for working with data offline or when you need to represent complex data structures.

Example: Populating a DataSet

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

public class Sample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;";
        DataSet dataSet = new DataSet();

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string sql = "SELECT ProductID, ProductName, UnitPrice FROM Products";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
            adapter.Fill(dataSet, "Products");
        }

        Console.WriteLine($"Table '{dataSet.Tables[0].TableName}' contains {dataSet.Tables[0].Rows.Count} rows.");
        foreach (DataRow row in dataSet.Tables["Products"].Rows)
        {
            Console.WriteLine($"Product: {row["ProductName"]}, Price: {row["UnitPrice"]}");
        }
    }
}

Data Providers

ADO.NET supports various data providers, each with its own set of classes:

SQL Server: System.Data.SqlClient (or Microsoft.Data.SqlClient for newer .NET versions)
OLE DB: System.Data.OleDb
ODBC: System.Data.Odbc
Oracle: System.Data.OracleClient (Note: This provider is largely deprecated.)

Microsoft Learn