SubsDotNet: Get Data From DB On Gridview Using StoredProcedure In Asp.Net

Write code in UI section i.e GridViewWithStoredProcedure.aspx:

Gridview Control WithOut Template Field:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridViewWithStoredProcedure.aspx.cs" Inherits="GridViewWithStoredProcedure" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
            <Columns>
                <asp:BoundField DataField="EmpID" HeaderText="EmpID" ReadOnly="True" SortExpression="EmpID" />
                <asp:BoundField DataField="Ename" HeaderText="Ename" SortExpression="Ename" />
                <asp:BoundField DataField="Job" HeaderText="Job" SortExpression="Job" />
                <asp:BoundField DataField="Sal" HeaderText="Sal" SortExpression="Sal" />
            </Columns>
        </asp:GridView>
        <br />
        <asp:Button ID="Button1" runat="server" Text="Show" OnClick="Button1_Click" />
    </div>
    </form>
</body>
</html>

Add Connection String in web.config file:

<connectionStrings>
<add name="conStr" connectionString="Server=Subas-PC;Database=SubasDB;User id=sa;Pwd=123"/>
</connectionStrings>

Write Your Procedure in Sql Server for Show Data On Gridview for Employee Table:

Create Procedure SP_GetAllEmpDetails
As
Begin
Select * from Employee
End

Write code in CodeBehind section i.e GridViewWithStoredProcedure.aspx.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class GridViewWithStoredProcedure : System.Web.UI.Page
{
    SqlConnection cn;
    SqlDataAdapter da;
    DataSet ds;
    string strSqlQuery;
    protected void Page_Load(object sender, EventArgs e)
    {

        if (Page.IsPostBack)
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
            BindEmpData();
        }
    }
    void BindEmpData()
    {
        da = new SqlDataAdapter("SP_GetAllEmpDetails", cn);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        ds = new DataSet();
        da.Fill(ds, "Employee");
        GridView1.DataSource = ds.Tables["Employee"];
        GridView1.DataBind();
    }

    protected void Button1_Click(object sender, EventArgs e)
    {

        BindEmpData();
    }
}

SubsDotNet

Thursday, 17 October 2013

Get Data From DB On Gridview Using StoredProcedure In Asp.Net

No comments:

Post a Comment