MSDN Documentation

ADO.NET Introduction

Welcome to the introduction to ADO.NET, the foundational data access technology within the .NET Framework. ADO.NET provides a rich set of classes for accessing data from various data sources, most commonly relational databases.

What is ADO.NET?

ADO.NET is a set of .NET Framework classes that expose data access services to .NET programmers. It is an integral part of the .NET Framework, enabling developers to connect to databases, execute SQL commands, and retrieve result sets. ADO.NET allows you to build applications that interact with data efficiently.

Key components of ADO.NET include:

• Connection Objects: Establish a connection to a data source. Examples include SqlConnection for SQL Server, OleDbConnection for OLE DB providers, and OracleConnection for Oracle.
• Command Objects: Represent SQL statements or stored procedures to be executed against a data source. Examples include SqlCommand and OleDbCommand.
• DataReader Objects: Provide a forward-only, read-only stream of data from a data source. They are highly efficient for reading large amounts of data. Examples include SqlDataReader and OleDbDataReader.
• DataSet Objects: Represent an in-memory cache of data retrieved from a data source. DataSet objects are disconnected from the data source, allowing you to work with data offline and then update the source later.
• DataAdapter Objects: Bridge the gap between a DataSet and a data source, facilitating the transfer of data. Examples include SqlDataAdapter and OleDbDataAdapter.

Core Concepts

ADO.NET operates on two primary models:

Connected Data Access

This model involves establishing a continuous connection to the data source while data is being accessed. The most common way to achieve this is by using a DataReader. This approach is highly efficient when you only need to read data sequentially and don't require complex manipulation or offline capabilities.

A typical connected data access flow:

1. Open a connection to the data source.
2. Create a command object to execute a query.
3. Execute the command and obtain a DataReader.
4. Iterate through the DataReader to access the data row by row.
5. Close the DataReader and the connection.

Disconnected Data Access

This model allows you to retrieve data, close the connection to the data source, and then work with the data in memory using a DataSet. This is useful for applications that need to perform operations on data without maintaining an open connection, such as mobile applications or web services.

A typical disconnected data access flow:

1. Open a connection.
2. Create a Command object and a DataAdapter.
3. Use the DataAdapter to fill a DataSet.
4. Close the connection.
5. Manipulate the data within the DataSet.
6. Re-open a connection.
7. Use the DataAdapter to update the data source from the DataSet.
8. Close the connection.

Example Snippet (Conceptual)

Here's a simplified illustration of opening a connection and executing a command:

using System.Data.SqlClient;

// ...

string connectionString = "Server=myServer;Database=myDatabase;Integrated Security=True;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    Console.WriteLine("Connection opened successfully.");

    string sql = "SELECT COUNT(*) FROM Customers";
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        object result = command.ExecuteScalar();
        Console.WriteLine($"Number of customers: {result}");
    }
}
            

This introduction covers the fundamental concepts of ADO.NET. In subsequent tutorials, we will delve deeper into each component and explore practical examples of their usage.

"Data is the new oil. It's valuable, but if unrefined, it cannot be used." — Clive Humby