In this article, we are going to learn how to Load Datatable or DataSet from SqlDataReader in C# ASP.Net. DataTable has Load() method which fills DataTable with values from a data source using the supplied IDataReader.

Step 1: Create Datasource

First we need to create a table named as tblEmployee and insert some records. Below is script to create a table and insert some records.

Create table tblEmployee
(
 EmpId Int primary key identity,
 Name varchar(20),
 Salary int
)

insert into tblEmployee values('Rahul',20000)
insert into tblEmployee values('Deepak',25000)
insert into tblEmployee values('Yogesh',40000)
insert into tblEmployee values('Savio',20000)

Step 2: In designer file

In designer file, create two Gridview controls named as grdDataTable and grdDataSet. Below is the designer file code.

div align="center">
asp:GridView ID="grdDataTable" runat="server" BackColor="White" BorderColor="#CCCCCC"
BorderStyle="None" BorderWidth="1px" CellPadding="3" Width="300px">
FooterStyle BackColor="White" ForeColor="#000066" />
HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
RowStyle ForeColor="#000066" />
SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
SortedAscendingCellStyle BackColor="#F1F1F1" />
SortedAscendingHeaderStyle BackColor="#007DBB" />
SortedDescendingCellStyle BackColor="#CAC9C9" />
SortedDescendingHeaderStyle BackColor="#00547E" />
asp:GridView>
br />
asp:GridView ID="grdDataSet" runat="server" BackColor="White" BorderColor="#CC9966"
BorderStyle="None" BorderWidth="1px" CellPadding="4" Width="300px">
FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
RowStyle BackColor="White" ForeColor="#330099" />
SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
SortedAscendingCellStyle BackColor="#FEFCEB" />
SortedAscendingHeaderStyle BackColor="#AF0101" />
SortedDescendingCellStyle BackColor="#F6F0C0" />
SortedDescendingHeaderStyle BackColor="#7E0000" />
asp:GridView>
div>

Step 3: In Code-Behind file

Step 1: Add below namespaces.

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

Step 2: Below is the C-Sharp code to bind GridView.

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
    {
        grdDataTable.DataSource = LoadDataTableFromDataReader();
        grdDataTable.DataBind();
        grdDataSet.DataSource = LoadDataSetFromDataReader();
        grdDataSet.DataBind();
    }
}
//Load DataTable From DataReader
public DataTable LoadDataTableFromDataReader()
{
//string MyConnection = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string MyConnection = @"server=XXXX;database=XXXX;uid=XXXX;password=XXXX;";
    SqlConnection con = new SqlConnection(MyConnection);
    SqlCommand cmd = new SqlCommand("select *from tblEmployee", con);
    con.Open();
    SqlDataReader sdr = cmd.ExecuteReader();
//create a DataTable
    DataTable dt = new DataTable();
// use Load method to load data into DataTable
    dt.Load(sdr);
    con.Close();
return dt;
}
//Load DataSet From DataReader
public DataSet LoadDataSetFromDataReader()
{
//string MyConnection = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string MyConnection = @"server=XXXX;database=XXXX;uid=XXXX;password=XXXX;";
    SqlConnection con = new SqlConnection(MyConnection);
    SqlCommand cmd = new SqlCommand("select *from tblEmployee", con);
    con.Open();
    SqlDataReader sdr = cmd.ExecuteReader();
//create a DataSet
    DataSet ds = new DataSet();
//add a DataTable
    ds.Tables.Add("EmployeeData");
// use Load method to load data into DataTable
    ds.Tables[0].Load(sdr);
    con.Close();
return ds;
}

Output:

Output will be shown as below.