Introduction to ADO.NET

Welcome to the ADO.NET documentation. This section provides a comprehensive overview of ADO.NET, a powerful set of .NET Framework components for accessing data from a data source, such as a relational database.

What is ADO.NET?

ADO.NET is a data access technology in the Microsoft .NET Framework. It provides a way to connect to data sources, execute commands, and retrieve results in a disconnected or connected manner. ADO.NET is designed to be a lightweight, extensible framework that can be used with various data sources, including SQL Server, Oracle, and other relational databases, as well as XML files and other data stores.

Key Concepts

ADO.NET consists of a set of .NET Framework classes that expose data access functionality to the .NET programmer. The primary goal of ADO.NET is to provide developers with a simplified, yet powerful, way to interact with data. Key components include:

The DataSet Object

The DataSet object represents a collection of DataTable objects, allowing you to hold and manipulate data in a disconnected manner. It's ideal for scenarios where you retrieve data from a source, work with it independently, and then update the source later.

A DataSet can contain multiple DataTable objects.
Each DataTable contains rows and columns of data.
It supports relationships between tables and constraints.

Data Providers

Data providers are a set of classes that provide access to a specific data source. For example, the System.Data.SqlClient namespace provides classes for accessing Microsoft SQL Server. Other providers exist for Oracle, OleDb, and Odbc.

Common data provider objects include:

Connection: Establishes a connection to the data source.
Command: Represents a Transact-SQL statement or stored procedure to execute against the data source.
DataReader: Provides a forward-only, read-only stream of data from the data source.
DataAdapter: Facilitates the retrieval and saving of data between a DataSet and a data source.

Connected vs. Disconnected Scenarios

ADO.NET supports two primary data access models:

Connected Data Access: In this model, you maintain an open connection to the data source while you interact with the data. This is efficient for reading data, especially when you need to process data row by row. The DataReader object is typically used here.
Disconnected Data Access: In this model, you retrieve data into a DataSet, close the connection, and then work with the data locally. Updates can be batched and sent back to the data source later using a DataAdapter. This model is useful for applications that need to display data in a UI, allow user modifications, and then persist those changes.

Getting Started

To begin using ADO.NET, you'll typically need to:

Add a reference to the necessary ADO.NET assemblies in your project.
Instantiate a Connection object, specifying the connection string for your data source.
Open the connection.
Create a Command object, setting its CommandText property to your SQL query or stored procedure name, and associating it with the connection.
Execute the command. For queries that return data, you might use a DataReader or a DataAdapter to populate a DataSet.
Process the retrieved data.
Close the connection when you are finished.

Example: Retrieving Data with SqlDataReader


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))
        {
            string query = "SELECT CustomerID, CompanyName FROM dbo.Customers;";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                try
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"CustomerID: {reader["CustomerID"]}, CompanyName: {reader["CompanyName"]}");
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"An error occurred: {ex.Message}");
                }
            }
        }
    }
}

Next Steps

Explore the following topics to deepen your understanding of ADO.NET:

Next: Connecting to Data Sources →