GridView Using StoredProcedure With Sqldatareader In ASP.NET

In this article i show you how to use sqldatareader 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 Designation { get; set; }
    public int Salary { get; set; }
    public string City { get; set; }
 
}
Step7:- Source code
        <asp:GridView ID="GridView1" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84"              BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">
            <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
            <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
            <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
            <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
            <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#FFF1D4" />
            <SortedAscendingHeaderStyle BackColor="#B95C30" />
            <SortedDescendingCellStyle BackColor="#F1E5CE" />
            <SortedDescendingHeaderStyle BackColor="#93451F" />
        </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.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:-