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
|