C# & ADO.NET

DataSet, DataReader, DataAdapter and DataView: what each ADO.NET class does

C# & ADO.NET · By the Postback Notes editorial team · Published 2026-09-24

These four classes are often listed together in interviews as if they were alternatives. They are not: they play different roles in the same pipeline. A DataReader streams rows from an open connection; a DataAdapter uses commands to fill a DataSet and to save its changes; a DataSet (with its DataTables) holds the data in memory; a DataView presents a DataTable filtered and sorted.

Roles at a glance

ClassConnected?Role
SqlDataReaderYes, needs an open connectionRead rows once, forward only, read only, fast
SqlDataAdapterOpens and closes the connection as neededBridge: Fill from the database, Update back to it
DataSet / DataTableNoIn-memory tables, rows, relations, change tracking
DataViewNoA filtered, sorted view over one DataTable, bindable to controls

DataReader: stream and forget

using (var con = new SqlConnection(cs))
using (var cmd = new SqlCommand("SELECT Name, Price FROM dbo.Products", con))
{
    con.Open();
    using (SqlDataReader r = cmd.ExecuteReader())
    {
        while (r.Read())
        {
            Console.WriteLine(r.GetString(0) + ": " + r.GetDecimal(1));
        }
    }
}

Lowest memory use and fastest for display or processing in one pass. You cannot go back, count rows in advance or keep the reader after the connection closes. Examples: ExecuteReader example.

DataAdapter and DataSet: work disconnected

var ds = new DataSet();
using (var con = new SqlConnection(cs))
using (var da = new SqlDataAdapter("SELECT ProductId, Name, Price FROM dbo.Products", con))
{
    da.Fill(ds, "Products");          // opens, reads everything, closes

    DataTable t = ds.Tables["Products"];
    t.Rows[0]["Price"] = 9.99m;       // change tracked in memory (RowState = Modified)

    var builder = new SqlCommandBuilder(da); // generates UPDATE/INSERT/DELETE from the SELECT
    da.Update(t);                     // sends only the changed rows
}

SqlCommandBuilder works for single-table queries that include the primary key. For anything more complex, set da.UpdateCommand, InsertCommand and DeleteCommand yourself with parameters. The difference between a DataSet and a single DataTable is covered in DataSet vs DataTable.

DataView: filter and sort without a new query

DataTable products = ds.Tables["Products"];
var view = new DataView(products)
{
    RowFilter = "Price < 20 AND Name LIKE 'A%'",
    Sort = "Price DESC"
};
gvCheap.DataSource = view;
gvCheap.DataBind();

DataTable copy = view.ToTable();   // materialise the view as a new table

RowFilter uses its own expression syntax, documented under DataColumn.Expression; it is not SQL. If the filter contains user input, escape single quotes by doubling them, or better, filter with LINQ to DataSet (products.AsEnumerable().Where(...)). Every DataTable has a DefaultView, which is what a grid binds to when you pass it the table.

Choosing

Official documentation