MSDN Documentation

Retrieving Data with ADO.NET

This document explains how to retrieve data from a data source using ADO.NET. ADO.NET provides a rich set of components for data access, enabling you to connect to various data sources and manipulate data effectively.

Using DataReaders

DataReader objects provide a forward-only, read-only stream of data. They are the most efficient way to retrieve a large result set when you need to process each record sequentially.

Example: Using SqlDataReader

The following example demonstrates how to use SqlDataReader to retrieve data from a SQL Server database.

using System;
using System.Data.SqlClient;

public class DataRetrievalExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);

            try
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"ID: {reader["CustomerID"]}, Company: {reader["CompanyName"]}, Contact: {reader["ContactName"]}");
                        }
                    }
                    else
                    {
                        Console.WriteLine("No rows found.");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

Using DataSets and DataAdapters

For scenarios requiring disconnected data retrieval, manipulation, or synchronization between a client and server, DataSet and DataAdapter objects are invaluable. A DataAdapter acts as a bridge between a DataSet and a data source, filling the DataSet with data and reconciling changes made in the DataSet back to the data source.

Example: Using SqlDataAdapter and DataSet

This example shows how to populate a DataSet using SqlDataAdapter.

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

public class DataSetExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        string query = "SELECT ProductID, ProductName, UnitPrice FROM Products;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
            DataSet dataSet = new DataSet();

            try
            {
                adapter.Fill(dataSet, "Products"); // Fill the DataSet with data

                // Accessing data from the DataSet
                foreach (DataRow row in dataSet.Tables["Products"].Rows)
                {
                    Console.WriteLine($"ID: {row["ProductID"]}, Product: {row["ProductName"]}, Price: {row["UnitPrice"]}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

Key Components for Data Retrieval

• Connection Objects: Establish a connection to the data source (e.g., SqlConnection, OleDbConnection).
• Command Objects: Represent SQL statements or stored procedures to be executed against the data source (e.g., SqlCommand, OleDbCommand).
• DataReader Objects: Provide a forward-only, read-only stream of data.
• DataAdapter Objects: Fill DataSet objects and resolve changes back to the data source.
• DataSet Objects: Represent an in-memory cache of data that can contain multiple tables.