How to bind GridView with DataReader in ASP.Net using C# and VB.Net

Database

I’ll make use of Customers Table of Microsoft’s Northwind Database which you can easily download using the link provided below

HTML Markup

For this article I am using ASP.Net GridView control so that I can display the data fetched from the table using SQLCommand ExecuteReader function.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" RowStyle-BackColor="#A1DCF2"

    HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White">

    <Columns>

        <asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="CustomerID" />

        <asp:BoundField ItemStyle-Width="150px" DataField="ContactName" HeaderText="Contact Name" />

        <asp:BoundField ItemStyle-Width="150px" DataField="City" HeaderText="City" />

    </Columns>

</asp:GridView>

Namespaces

You will need to import the following namespaces

C#

using System.Data;

using System.Configuration;

using System.Data.SqlClient;

VB.Net

Imports System.Data

Imports System.Configuration

Imports System.Data.SqlClient

Connection String

The connection string has been placed inside the connectionStrings section of the Web.Config file.

<connectionStrings>

 <addname="constr"connectionString="Data Source=.\sql2005; Initial Catalog=Northwind; user ID=sa; password=pass@123"providerName="System.Data.SqlClient"/>

</connectionStrings>

Fetching Data using SQLCommand ExecuteReader function in ASP.Net

C#

protected void Page_Load(object sender, EventArgs e)

{

    if (!IsPostBack)

    {

        this.BindGrid();

    }

}

private void BindGrid()

{

    string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

    using (SqlConnection con = new SqlConnection(strConnString))

    {

        using (SqlCommand cmd = new SqlCommand())

        {

            cmd.CommandText = "select top 10 * from Customers";

            cmd.Connection = con;

            con.Open();

            GridView1.DataSource = cmd.ExecuteReader();

            GridView1.DataBind();

            con.Close();

        }

    }

}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

    If Not IsPostBack Then

        Me.BindGrid()

    End If

End Sub

Private Sub BindGrid()

    Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

    Using con As New SqlConnection(strConnString)

        Using cmd As New SqlCommand()

            cmd.CommandText = "select top 10 * from Customers"

            cmd.Connection = con

            con.Open()

            GridView1.DataSource = cmd.ExecuteReader()

            GridView1.DataBind()

            con.Close()

        End Using

    End Using

End Sub