Fill Datatable and Bind GridView Using Data Reader in ASP.Net C#.Net VB.Net

In this article i am going to explain about how to fill the datatable using datareader and bind that datatable to gridview.

Datareader is read only forward only way of reading data. Compare to dataset datareader is fast in terms of reading data. To fill the datatable using datareader DataTable.Load() methaod is used in that datareader object is passed as the argument.

Create a new asp.net application and add a grid view in to display the details from the students table. Below is the html code used.

Fill Datatable Using Data Reader

    

For explanation purpose i have created a table called student and inserted some sample records. Below is the script used to create table and insert record into table.

CREATE TABLE Students
(
Id INT IDENTITY PRIMARY KEY,
Name VARCHAR(100),
Class TINYINT,
Section CHAR(1),
JoinedDate DATE
)
GO

INSERT INTO Students(Name,Class,Section,JoinedDate)
VALUES('Student-1',10,'A','2009-06-01')

INSERT INTO Students(Name,Class,Section,JoinedDate)
VALUES('Student-2',9,'B','2014-06-01')

INSERT INTO Students(Name,Class,Section,JoinedDate)
VALUES('Student-3',11,'C','2011-06-01')

INSERT INTO Students(Name,Class,Section,JoinedDate)
VALUES('Student-4',12,'D','2012-06-01')

INSERT INTO Students(Name,Class,Section,JoinedDate)
VALUES('Student-5',8,'E','2005-06-01')
GO

Before moving ahead include the connection string element in the web.config file.

And now include the below namespaces to access the sqlconnection, datatable and configuration objects

C#.Net:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

On pageload event i am fetching the data from the students table using datareader and filling it to the datatable using DataTable.Load() method and then binding the same datatable to gridview. Below is the code.

C#.Net:

protected void Page_Load(object sender, EventArgs e)
{
try
{
string connectionString = ConfigurationManager.ConnectionStrings["mydsn"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "Select * from students";
        cmd.CommandType = CommandType.Text;
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        SqlDataReader dReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        DataTable dTable = new DataTable("Students");
        dTable.Load(dReader);
        grdReasults.DataSource = dTable;
        grdReasults.DataBind();
        cmd.Dispose();
        conn.Close();
    }
}
}
catch (Exception ex)
{
// handle error
}
}

VB.Net:

Protected Sub Page_Load(sender As Object, e As EventArgs)
 Try
  Dim connectionString As String = ConfigurationManager.ConnectionStrings("mydsn").ConnectionString
  Using conn As New SqlConnection(connectionString)
   Using cmd As New SqlCommand()
    cmd.Connection = conn
    cmd.CommandText = "Select * from students"
    cmd.CommandType = CommandType.Text
    If conn.State = ConnectionState.Closed Then
     conn.Open()
    End If
    Dim dReader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    Dim dTable As New DataTable("Students")
    dTable.Load(dReader)
    grdReasults.DataSource = dTable
    grdReasults.DataBind()
    cmd.Dispose()
    conn.Close()
   End Using
  End Using
   ' handle error
 Catch ex As Exception
 End Try
End Sub

The output will look like

Download Source