What's the difference between a DataTable and a DataReader?

Difference Between DataTable and DataReader in C#

Both DataTable and DataReader are used to work with data from a database in ADO.NET, but they have key differences:

Feature	DataTable	DataReader
Type	In-memory table representation	Forward-only, read-only stream of data
Data Storage	Stores the entire data in memory	Does not store data; reads directly from database
Access Type	Random access (you can access any row/column)	Forward-only (you can only read sequentially)
Performance	Slower for large datasets due to in-memory storage	Faster and efficient for reading large data
Read/Write	Supports read and write	Read-only
Connection	Can be disconnected after filling the DataTable	Requires open connection while reading
Use Case	When you need manipulation, sorting, filtering, or binding to UI	When you just need to read data quickly
Example Method	SqlDataAdapter.Fill(DataTable)	SqlCommand.ExecuteReader()

Example of DataReader

using(SqlConnection con = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("SELECT * FROM Students", con);
    con.Open();
    SqlDataReader reader = cmd.ExecuteReader();

    while(reader.Read())
    {
        Console.WriteLine(reader["Name"] + " - " + reader["Age"]);
    }
    reader.Close();
}

• Forward-only, faster, requires open connection.

Example of DataTable

using(SqlConnection con = new SqlConnection(connectionString))
{
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Students", con);
    DataTable dt = new DataTable();
    da.Fill(dt);

    foreach(DataRow row in dt.Rows)
    {
        Console.WriteLine(row["Name"] + " - " + row["Age"]);
    }
}

• Random access, can manipulate data, disconnected from DB after fill.

Summary

• Use DataReader for fast, read-only, forward-only access.

• Use DataTable when you need disconnected, manipulable, or bindable data.

A DataReader provides a fast, read-only, and forward-only stream of data from a database. It's connected to the data source and processes one row at a time, making it highly efficient and low on memory usage.

In contrast, a DataTable is a disconnected, in-memory representation of a single database table. It holds all the data at once, allowing for powerful features like searching, filtering, sorting, and navigating data in any direction. However, this comes with a greater memory footprint.

DataTable → Stores the whole table in memory, you can use it later even without a database connection.

DataReader → Reads data one row at a time, very fast, but needs the database connection to stay open.