GridView Using StoredProcedure With DataBoundControls In ASP.NET

In this article i show you how to use gridview with sqldatareader and databound controls  in asp.net
step1:-Create table
CREATE TABLE tblEmp
(
Id  int primary key identity,
FirstName   varchar(50),
LastName   varchar(50),
Designation   varchar(50),
Salary          int,
City   varchar(50),
)
Step2:-Insert some data in table

INSERT INTO tblEmp VALUES('SIVA','Garika','UiDeveloper',15000,'Hydarabad')
INSERT INTO tblEmp VALUES('Narayana','Garika','Developer',25000,'Hydarabad')
INSERT INTO tblEmp VALUES('Kiran','Thota','Developer',35000,'Hydarabad')
INSERT INTO tblEmp VALUES('Madhu','Thota','Developer',19000,'Hydarabad')
INSERT INTO tblEmp VALUES('Manasa','Javisetty','Testar',15000,'Hydarabad')
INSERT INTO tblEmp VALUES('Malavika','Dongari','Developer',15000,'Hydarabad')
INSERT INTO tblEmp VALUES('Mounika','Javisetty','Testar',15000,'Hydarabad')
INSERT INTO tblEmp VALUES('Anji','Dongari','Developer',25000,'Hydarabad')
INSERT INTO tblEmp VALUES('Aravind','Choodi','Developer',32000,'Hydarabad')
INSERT INTO tblEmp VALUES('Avinash','Choodi','Developer',26000,'Hydarabad')

Step3:-Create stored procedure
Create Procedure USP_GetEmployeeData
AS
BEGIN
select *from tblEmp
END
Step4:-Execute procedure

EXEC USP_GetEmployeeData
Output:-

Step5:-Go to visuavl studio

put connections in web.config file
Step6:-Take one class as Emp
public class Emp
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string FullName { get; set; }
    public string Designation { get; set; }
    public int Salary { get; set; }
    public string City { get; set; }

}
Step7:- Source code
        <asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server" BackColor="White"      BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">
            <AlternatingRowStyle BackColor="#DCDCDC" />
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" />
                <asp:BoundField DataField="FullName" HeaderText="FullName" />
                <asp:BoundField DataField="Designation" HeaderText="Designation" />
                <asp:BoundField DataField="Salary" HeaderText="Salary" />
                <asp:BoundField DataField="City" HeaderText="City" />
            </Columns>
            <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
            <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White"/>
            <PagerStyle ForeColor="Black" HorizontalAlign="Center" BackColor="#999999"
             />
            <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
            <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White"
              />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#0000A9" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#000065" />
        </asp:GridView>

Step8:-Code behind
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            GetEmpyoyeeData();
        }
    }
    private void GetEmpyoyeeData()
    {
        List<Emp> list = new List<Emp>();
        string str = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
        SqlConnection con = new SqlConnection(str);
        SqlCommand cmd = new SqlCommand("USP_GetEmployeeData", con);
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            Emp obj = new Emp();
            obj.ID = Convert.ToInt32(dr["Id"]);
            obj.FirstName = dr["FirstName"].ToString();
            obj.LastName = dr["LastName"].ToString();
            obj.FullName=dr["FirstName"].ToString()+ " "+dr["LastName"].ToString();
            obj.Designation = dr["Designation"].ToString();
            obj.Salary = Convert.ToInt32(dr["Salary"]);
            obj.City = dr["City"].ToString();
            list.Add(obj);
        }
        GridView1.DataSource = list;
        GridView1.DataBind();
        con.Close();    
    }
OutPut:-