Retrieving Data with ADO.NET

ADO.NET provides a rich set of classes for accessing data sources, including relational databases. This section focuses on the fundamental techniques for retrieving data.

Core Components for Data Retrieval

The primary classes involved in retrieving data are:

Using DbDataReader (Connected Scenario)

The DbDataReader is the most efficient way to retrieve data when you need to process it row by row without holding the entire dataset in memory. This is known as a connected scenario.

Steps:

  1. Establish a connection using a DbConnection object.
  2. Create a DbCommand object with your SQL query.
  3. Associate the command with the connection.
  4. Execute the command using ExecuteReader(), which returns a DbDataReader.
  5. Iterate through the rows using Read().
  6. Access column data by ordinal index or column name.
  7. Close the reader and the connection when finished.

Example (SQL Server):


using System;
using System.Data.SqlClient;

public class DataRetriever
{
    public void RetrieveCustomerNames(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT CustomerID, CompanyName FROM Customers";
            SqlCommand command = new SqlCommand(query, connection);

            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        // Access data by ordinal index
                        int customerId = reader.GetInt32(0);
                        string companyName = reader.GetString(1);

                        Console.WriteLine($"ID: {customerId}, Name: {companyName}");

                        // Alternatively, access data by column name
                        // Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
                    }
                }
                else
                {
                    Console.WriteLine("No rows found.");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}
Note: Always wrap database operations in try-catch blocks to handle potential exceptions. Using statement ensures that disposable resources like connections and readers are properly closed and disposed of.

Using DataTable and DataSet (Disconnected Scenario)

In a disconnected scenario, you load data into memory using a DataTable or DataSet and then process it. This is useful for applications that need to operate on data without maintaining an active connection to the database, such as during UI interactions or when dealing with large datasets.

DataTable

A DataTable represents a single table of data in memory. You can populate it using a DbDataAdapter.

DataSet

A DataSet is a collection of DataTable objects, along with relationships and constraints between them. It represents a complete in-memory cache of relational data.

Using DataAdapter and DataTable

The DbDataAdapter acts as a bridge between a DataSet (or DataTable) and a data source. It can fill a DataSet with data and resolve changes made to the DataSet back to the data source.

Steps:

  1. Create a DbConnection.
  2. Create a DbCommand.
  3. Create a DbDataAdapter (e.g., SqlDataAdapter).
  4. Create a DataTable or DataSet.
  5. Use the Fill() method of the adapter to populate the DataTable/DataSet.
  6. Process the data in the DataTable/DataSet.
  7. Close the connection.

Example (SQL Server):


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

public class DataRetriever
{
    public void LoadProductsIntoDataTable(string connectionString)
    {
        DataTable productsTable = new DataTable("Products");

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT ProductID, ProductName, UnitPrice FROM Products";
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);

            try
            {
                // The adapter automatically opens and closes the connection when Fill is called
                adapter.Fill(productsTable);

                // Process the data in the DataTable
                foreach (DataRow row in productsTable.Rows)
                {
                    Console.WriteLine($"ID: {row["ProductID"]}, Name: {row["ProductName"]}, Price: {row["UnitPrice"]}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}
Tip: For more complex scenarios involving multiple related tables, consider using a DataSet and defining relations between its DataTable objects.

Choosing the Right Approach

Further Reading