Wednesday, 10 August 2016

Load DataTable or DataSet from SqlDataReader in C# ASP.Net

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.

Load DataTable or DataSet from SqlDataReader in C-Sharp ASPNet
Share:

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON