MSDN Documentation

Key Components and Their Roles

Connections

The Connection object (e.g., SqlConnection, OracleConnection) establishes a connection to a data source. It manages the communication session between the application and the database.

// Example: Establishing a SQL Server connection
using System.Data.SqlClient;

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    Console.WriteLine("Connection opened successfully.");
    // Perform database operations here
}
                

Commands

The Command object (e.g., SqlCommand, OracleCommand) is used to execute SQL statements or stored procedures against a data source.

// Example: Executing a SELECT query
using (SqlCommand command = new SqlCommand("SELECT CustomerID, CompanyName FROM Customers", connection))
{
    // Execute the command
}
                

Data Readers

The DataReader object (e.g., SqlDataReader, OracleDataReader) provides a forward-only, read-only stream of data from the data source. It's highly efficient for retrieving large amounts of data.

using (SqlCommand command = new SqlCommand("SELECT ProductName, UnitPrice FROM Products", connection))
{
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"Product: {reader["ProductName"]}, Price: {reader["UnitPrice"]}");
        }
    }
}
                

Data Adapters

The DataAdapter object (e.g., SqlDataAdapter, OracleDataAdapter) acts as a bridge between a DataSet and a data source. It is used to retrieve data from the data source into a DataSet and to resolve changes made in the DataSet back to the data source.

// Example: Using a DataAdapter to fill a DataSet
DataTable customersTable = new DataTable("Customers");
using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", connection))
{
    adapter.Fill(customersTable);
}
// Now customersTable contains data from the Customers table
                

Connected vs. Disconnected Data Access

ADO.NET supports two primary data access models:

• Connected Data Access: This model maintains an active connection to the data source while retrieving and manipulating data. The DataReader is a prime example of this. It's efficient for read-only scenarios or when immediate updates are required.
• Disconnected Data Access: In this model, the application retrieves data into a DataSet or DataTable, closes the connection, and then manipulates the data in memory. Changes are then sent back to the data source using a DataAdapter. This is useful for applications that need to work with data offline or when dealing with distributed applications.

Common Scenarios and Best Practices

• Always use using statements for objects that implement IDisposable (like Connection, Command, DataReader) to ensure resources are properly released.
• Parameterize your SQL queries to prevent SQL injection vulnerabilities.
• Close connections as soon as they are no longer needed.
• Choose the appropriate data access method (DataReader vs. DataSet) based on your application's needs.
• Handle exceptions gracefully to manage potential database errors.