C# & ADO.NET

Convert a SqlDataReader to a DataTable with DataTable.Load (C#)

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

You have a SqlDataReader but need a DataTable: to bind a paging GridView, to keep the data after the connection closes, or to pass it to a method that expects a table. DataTable.Load does the conversion in one call.

Basic usage

public static DataTable Query(string cs, string sql, params SqlParameter[] parameters)
{
    var table = new DataTable();
    using (var con = new SqlConnection(cs))
    using (var cmd = new SqlCommand(sql, con))
    {
        cmd.Parameters.AddRange(parameters);
        con.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            table.Load(reader);  // builds the columns from the reader's schema, copies all rows
        }
    }
    return table;               // usable after the connection is closed
}

DataTable orders = Query(cs,
    "SELECT OrderId, Customer, Total FROM dbo.Orders WHERE OrderDate >= @from",
    new SqlParameter("@from", SqlDbType.Date) { Value = DateTime.Today.AddDays(-30) });

Load reads the reader to the end of the current result set. If the reader has more result sets, it moves to the next one; if not, it closes the reader. That is why loading several tables in a row works:

using (SqlDataReader r = cmd.ExecuteReader())  // "SELECT ... ; SELECT ... ;"
{
    var customers = new DataTable("Customers");
    var orders = new DataTable("Orders");
    customers.Load(r);   // first result set, then advances
    orders.Load(r);      // second result set
}

A DataSet has a matching method: ds.Load(reader, LoadOption.OverwriteChanges, "Customers", "Orders").

Columns, keys and constraint errors

Load creates columns with the reader's names and .NET types. It also reads key information from the schema, so a query that returns duplicate values in a primary-key column (for example through a join) can fail with "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." Fixes:

• Correct the query so keys are unique.
• Or relax constraints during the load: create a DataSet with EnforceConstraints = false, add the table to it, then load.
• To see which row is wrong, inspect table.GetErrors() and each row's RowError in the debugger.

LoadOption

When the table already contains rows with the same primary key, the LoadOption argument decides what happens: PreserveChanges (the default) keeps pending edits, OverwriteChanges replaces current and original values, Upsert updates current values only. For a fresh table, the option has no visible effect.

Load or SqlDataAdapter.Fill?

Both end with the same kind of table; pick the one that fits the code you already have. The roles of these classes are compared in DataSet, DataReader, DataAdapter and DataView, and binding the result in Bind a GridView with ExecuteReader.

Official documentation

• Microsoft Learn: DataTable.Load, LoadOption, DataSet.Load