ADO.NET Concepts

Introduction to ADO.NET

ADO.NET is a set of classes, accessible from the System.Data namespace, that exposes data access services to the .NET Framework programmer. ADO.NET is comprised of two primary components: the DataSet and the .NET Framework data providers. ADO.NET components allow you to create applications that access data from a variety of data sources, both relational and non-relational. You can use ADO.NET to retrieve data from data sources and then display or process that data. ADO.NET can also synchronize data from multiple data sources or remote data services with the data in your application.

ADO.NET data providers are the fundamental building blocks for accessing data with ADO.NET. Each .NET Framework data provider is a set of classes that bridge the .NET Framework with a particular data source. ADO.NET includes built-in data providers for the following data sources:

  • Microsoft SQL Server (System.Data.SqlClient)
  • ODBC data sources (System.Data.Odbc)
  • Oracle databases (System.Data.OracleClient)
  • OLE DB data sources (System.Data.OleDb)

You can also use the ADO.NET provider model to extend ADO.NET to support other data sources.

Core Components

ADO.NET provides a rich set of objects for working with data. The most fundamental 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 retrieve a forward-only, read-only stream of rows from a data source.
  • DataAdapter: Bridges a DataSet and a data source for retrieving and saving data.
  • DataSet: An in-memory representation of data, consisting of one or more DataTable objects.

Disconnected Data Access

A key feature of ADO.NET is its support for disconnected data access. This means that you can retrieve data from a data source, close the connection, and then work with the data in a DataSet. This is highly efficient for applications that do not require a continuous connection to the data source, such as web applications. The DataAdapter is the primary object used to populate a DataSet from a data source and to reconcile changes made to the DataSet back to the data source.

Consider this simplified C# example of retrieving data:

// Example using System.Data.SqlClient
using System.Data;
using System.Data.SqlClient;

// ...

using (SqlConnection connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connection);
    DataSet dataSet = new DataSet();
    adapter.Fill(dataSet);

    // Now you can work with dataSet.Tables[0] without an open connection
    foreach (DataRow row in dataSet.Tables[0].Rows)
    {
        Console.WriteLine(row["CustomerID"]);
    }
}

Managed Providers

ADO.NET data providers are built on a concept of managed providers. Each managed provider is a set of classes that encapsulates the data access logic for a specific data source. This abstraction allows ADO.NET applications to work with different data sources with minimal changes to the application code. For example, you can often replace an SqlClient with an OleDb provider by changing the connection string and the specific provider classes used, while the overall data access pattern remains similar.

← Previous Topic Next Topic →