MSDN Documentation

Reading Data with ADO.NET

ADO.NET provides several ways to retrieve data from a data source. The primary objects used for this purpose are DbDataReader and DataTable.

Using DbDataReader

The DbDataReader (or its specific provider implementation like SqlDataReader) is the most efficient way to read a forward-only stream of data rows. It is ideal for scenarios where you only need to iterate through the results once.

Example: Reading Data with SqlDataReader

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

public class ReadDataExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Your_Connection_String_Here"; // e.g., "Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;"

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

            try
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            // Access data by column name or ordinal index
                            int customerId = reader.GetInt32(0); // Or reader["CustomerID"]
                            string companyName = reader.GetString(1); // Or reader["CompanyName"]

                            Console.WriteLine($"ID: {customerId}, Name: {companyName}");
                        }
                    }
                    else
                    {
                        Console.WriteLine("No rows found.");
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

Using DataTable

A DataTable represents an in-memory table of data. It can be populated from a data source and then manipulated, filtered, and bound to UI controls. It's suitable when you need to work with the entire dataset multiple times or require advanced data manipulation capabilities.

Example: Populating a DataTable with SqlDataAdapter

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

public class DataTableExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Your_Connection_String_Here";
        string query = "SELECT ProductID, ProductName, UnitPrice FROM Products";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
            DataTable dataTable = new DataTable();

            try
            {
                adapter.Fill(dataTable);

                // Now you can work with the DataTable
                Console.WriteLine($"Total Products: {dataTable.Rows.Count}");

                foreach (DataRow row in dataTable.Rows)
                {
                    Console.WriteLine($"ID: {row["ProductID"]}, Name: {row["ProductName"]}, Price: {row["UnitPrice"]}");
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

Accessing Data from DataRow

When working with a DataTable, you access individual rows using the Rows collection, and then retrieve column values from a DataRow. You can access columns by their ordinal index or by their column name.

// Accessing data from a DataRow
DataRow row = dataTable.Rows[0]; // Get the first row
int productId = (int)row["ProductID"]; // Access by column name, cast to appropriate type
string productName = row[1].ToString(); // Access by ordinal index, convert to string

Remember to handle potential DBNull.Value when accessing data to avoid errors.