ASP.NET

Bind a GridView with ExecuteReader in ASP.NET (C#)

ASP.NET · By the Postback Notes editorial team · Published 2026-10-10

A GridView can bind to anything enumerable, including the SqlDataReader returned by ExecuteReader. It is the shortest path from a query to a table on the page and uses little memory. It has two constraints worth knowing: the connection must stay open during binding, and the grid cannot page over a reader.

The page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Products.aspx.cs" Inherits="Demo.Products" %>
<!DOCTYPE html>
<html>
<head runat="server"><title>Products</title></head>
<body>
<form id="form1" runat="server">
    <asp:GridView ID="gvProducts" runat="server" AutoGenerateColumns="false"
        EmptyDataText="No products found.">
        <Columns>
            <asp:BoundField DataField="ProductId" HeaderText="#" />
            <asp:BoundField DataField="Name" HeaderText="Product" />
            <asp:BoundField DataField="UnitPrice" HeaderText="Price" DataFormatString="{0:N2}"
                ItemStyle-HorizontalAlign="Right" />
            <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" />
        </Columns>
    </asp:GridView>
</form>
</body>
</html>

The code-behind

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;

namespace Demo
{
    public partial class Products : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindProducts();
            }
        }

        private void BindProducts()
        {
            string cs = ConfigurationManager.ConnectionStrings["Db"].ConnectionString;
            const string sql = "SELECT ProductId, Name, UnitPrice, Discontinued FROM dbo.Products ORDER BY Name";

            using (var con = new SqlConnection(cs))
            using (var cmd = new SqlCommand(sql, con))
            {
                con.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    gvProducts.DataSource = reader;
                    gvProducts.DataBind();   // rows are read here, while the connection is open
                }
            }
        }
    }
}

The order inside the using blocks is the whole technique. DataBind() enumerates the reader, which pulls rows from SQL Server as it goes. If you closed the connection before binding, you would get "Invalid attempt to call Read when reader is closed".

DataFormatString="{0:N2}" formats the price with the server's culture. Set <globalization culture="…" uiCulture="…" /> in web.config, or the page's Culture attribute, if the server's regional settings are not the ones your users expect.

Filtering with a parameter

private void BindProducts(int? categoryId)
{
    string cs = ConfigurationManager.ConnectionStrings["Db"].ConnectionString;
    string sql = "SELECT ProductId, Name, UnitPrice, Discontinued FROM dbo.Products"
               + (categoryId.HasValue ? " WHERE CategoryId = @cat" : "")
               + " ORDER BY Name";

    using (var con = new SqlConnection(cs))
    using (var cmd = new SqlCommand(sql, con))
    {
        if (categoryId.HasValue)
            cmd.Parameters.Add("@cat", SqlDbType.Int).Value = categoryId.Value;
        con.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            gvProducts.DataSource = reader;
            gvProducts.DataBind();
        }
    }
}

The optional clause is chosen from fixed strings written in code; the value itself is always a parameter.

Paging and sorting: use a DataTable

With AllowPaging="true", the GridView needs to know the total number of rows and to jump to an arbitrary page. A reader can only move forward once, so binding a reader to a paging grid fails with "The data source does not support server-side data paging". Load the reader into a DataTable instead; the connection can then close before binding:

var table = new DataTable();
using (var con = new SqlConnection(cs))
using (var cmd = new SqlCommand(sql, con))
{
    con.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        table.Load(reader);   // copies all rows into memory
    }
}
gvProducts.DataSource = table;
gvProducts.DataBind();

DataTable.Load is covered in Convert a SqlDataReader to a DataTable. For large tables, page in SQL instead (OFFSET … FETCH) and bind only the current page, using AllowCustomPaging and VirtualItemCount to show the pager.

Binding to objects

Many projects map rows to a class and bind a List<T>. The grid's DataField names then refer to property names:

var list = new List<Product>();
using (SqlDataReader r = cmd.ExecuteReader())
{
    while (r.Read())
    {
        list.Add(new Product
        {
            ProductId = r.GetInt32(0),
            Name = r.GetString(1),
            UnitPrice = r.IsDBNull(2) ? (decimal?)null : r.GetDecimal(2),
            Discontinued = r.GetBoolean(3)
        });
    }
}
gvProducts.DataSource = list;
gvProducts.DataBind();

This decouples the page from the database schema and makes the data easy to reuse, for example for an export. Since .NET 4.5, setting ItemType="Demo.Product" on the grid enables strongly typed templates with Item.Name instead of Eval("Name").

Performance notes

• Select only the columns the grid displays; a reader transfers every column in the query.
• View state stores the grid's rendered data so it can be rebuilt on postback. For read-only grids, EnableViewState="false" shrinks the page, but then you must rebind on every request, including postbacks.
• Open the connection as late as possible and dispose it immediately; connection pooling makes this cheap.

Compare the execution methods in ExecuteReader vs ExecuteNonQuery vs ExecuteScalar, and see a fuller reader example in the ExecuteReader example.

Troubleshooting

When several grids on one page read from the database, open one connection per binding method rather than sharing an open connection across the page: pooling keeps it cheap and each method stays self-contained.

Official documentation

• Microsoft Learn: BaseDataBoundControl.DataSource, GridView.AllowCustomPaging
• Microsoft Learn: SqlDataReader class, DataTable.Load
• Microsoft Learn: SQL Server connection pooling