Asp.Net: Bind or Populate Gridview control using Dataset and stored procedure.

Aman Sharma
0
Introduction:  

Here, I will explain how to populate or bind Gridview control from database using dataset and stored procedure.  

Demo:


Steps to follow:

Create table in database:

Create Table “Student_Info”  in Database “Blog” and Enter some data to display.
Column Name
DataType
Student_Id
Int
Student_name
Varchar(100)
Age
Int
Class
Varchar(100)

Create connection string in webconfig file:

  <connectionStrings>
<add name="con" connectionString="Data Source=localhost; initial catalog=Blog; integrated Security=true"></add
  </connectionStrings>

Design Asp.Net webForm:

<fieldset style="width:450px"><legend>Populate Gridview Using Dataset and stored Procedure</legend>
    <div>
        <asp:GridView ID="GridView1" runat="server" AllowPaging="true" Width="420px"  AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" PageSize="7" OnPageIndexChanging="GridView1_PageIndexChanging">
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
            <Columns>
                <asp:BoundField DataField="Student_name" HeaderText="Name" />
                <asp:BoundField DataField="Age" HeaderText="Age" />
                <asp:BoundField DataField="Class" HeaderText="Class" />
            </Columns>
          
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
         
        </asp:GridView>
    </div></fieldset>


Asp.Net Code:

Using C#:

 //add following napespaces

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

 //write following code
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Fill_grid();
        }
    }
//bind grid
    public void Fill_grid()
    {
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        SqlCommand cmd = new SqlCommand("Fill_Dataset", con);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter adpData = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adpData.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
        con.Close();
    }
//Gridview page indexing

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        Fill_grid();
    }


Using VB.Net:

'Import Following namespaces

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

'Write following Namespaces

Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
'Page load event
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Fill_grid()
End If
End Sub

'Bind grid
Public Sub Fill_grid()
If con.State = ConnectionState.Closed Then
con.Open()
End If
Dim cmd As New SqlCommand("Fill_Dataset", con)
cmd.CommandType = CommandType.StoredProcedure
Dim adpData As New SqlDataAdapter(cmd)Dim dt As New DataTable()
adpData.Fill(dt)
GridView1.DataSource = dt
GridView1.DataBind()
con.Close()
End Sub
'implementation of Gridview Pagination
Protected Sub GridView1_PageIndexChanging(sender As Object, e As GridViewPageEventArgs) Handles GridView1.PageIndexChanging
GridView1.PageIndex = e.NewPageIndex
Fill_grid()
End Sub



Post a Comment

0Comments
Post a Comment (0)

#buttons=(Accept !) #days=(20)

Our website uses cookies to enhance your experience. Learn More
Accept !