In this article i'm going to explain about how to load gridview records based on selecting page numbers wise, for this articles i'm using SQL Server Sample Database i.e NorthWind and Pubs Database and you can download this database from below section.
Download Database
Database
Create Stored Procedure
i'm using proc_getCustomerLoading StoredProcedure and this storedprocedure have two parameters i.e @pageIndex and @pageSize. Just imagine is that @pageIndex is nothing but a PageNumber, @pageSize it indicate how many records you want to display in Page
Create procedure [dbo].[proc_getCustomerLoading](
@pageIndex int,
@pageSize int)
As
Begin
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER ( ORDER BY [CustomerID] ASC) AS RowNo,
[CustomerID],
[CompanyName],
[ContactName],
[City],
[Country]
INTO #LoadCustomer
FROM Customers;
DECLARE @recordCount AS INT;
SELECT @recordCount = count(*) FROM #LoadCustomer;
SELECT * FROM #LoadCustomer
WHERE RowNo BETWEEN (@pageIndex - 1) * @pageSize + 1 AND (((@pageIndex - 1) * @pageSize + 1) + @pageSize) - 1;
DROP TABLE #LoadCustomer;
End
HTML Markup : Add Page :
In Design page we need to add one Dropdownlist and one Gridview Control. Dropdownlist is bind with numbers and its indicate or work as pageNumbers
For Example :- If i change the values of Dropdownlist immediately Dropdownlist SelectedIndexChanged Event Fire and its creates connection to database and pass values { i.e @pageIndex=1, @pageSize=10 (Default Values) } through Stored Procedure proc_getCustomerLoading.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridviewLoadingData.aspx.cs" Inherits="LoadingData_Application.GridviewLoadingData" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div style="width:100%; padding-left:50px;">
<table>
<tr>
<td>
Page Number is :
<asp:DropDownList ID="ddl_PageIndex" runat="server" style="width:150px; padding:3px;" AutoPostBack="true" OnSelectedIndexChanged="ddl_PageIndex_SelectedIndexChanged">
<asp:ListItem Text="1"></asp:ListItem>
<asp:ListItem Text="2"></asp:ListItem>
<asp:ListItem Text="3"></asp:ListItem>
<asp:ListItem Text="4"></asp:ListItem>
<asp:ListItem Text="5"></asp:ListItem>
<asp:ListItem Text="6"></asp:ListItem>
<asp:ListItem Text="7"></asp:ListItem>
<asp:ListItem Text="8"></asp:ListItem>
<asp:ListItem Text="9"></asp:ListItem>
<asp:ListItem Text="10"></asp:ListItem>
<asp:ListItem Text="11"></asp:ListItem>
<asp:ListItem Text="12"></asp:ListItem>
<asp:ListItem Text="13"></asp:ListItem>
<asp:ListItem Text="14"></asp:ListItem>
<asp:ListItem Text="15"></asp:ListItem>
<asp:ListItem Text="16"></asp:ListItem>
<asp:ListItem Text="17"></asp:ListItem>
<asp:ListItem Text="18"></asp:ListItem>
<asp:ListItem Text="19"></asp:ListItem>
<asp:ListItem Text="20"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<div style="height:300px; overflow:auto; width:100%;">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4">
<Columns>
<asp:TemplateField HeaderText="Row Number">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bindundefined"RowNo") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bindundefined"RowNo") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="CustomerID">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bindundefined"CustomerID") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bindundefined"CustomerID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Company Name">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bindundefined"CompanyName") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bindundefined"CompanyName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Contact Name">
<EditItemTemplate>
<asp:TextBox ID="TextBox4" runat="server" Text='<%# Bindundefined"ContactName") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bindundefined"ContactName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<EditItemTemplate>
<asp:TextBox ID="TextBox5" runat="server" Text='<%# Bindundefined"City") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bindundefined"City") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<EditItemTemplate>
<asp:TextBox ID="TextBox6" runat="server" Text='<%# Bindundefined"Country") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Bindundefined"Country") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<RowStyle BackColor="White" ForeColor="#003399" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SortedAscendingCellStyle BackColor="#EDF6F6" />
<SortedAscendingHeaderStyle BackColor="#0D4AC4" />
<SortedDescendingCellStyle BackColor="#D6DFDF" />
<SortedDescendingHeaderStyle BackColor="#002876" />
</asp:GridView>
<br />
</div>
</form>
</body>
</html>
C# Coding
C# Coding : Namespace
Adding Namespaces
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.Configuration; using System.Data.SqlClient; using System.Web.Services;
C# Coding : Page Load
In Page_Load Event simply i'm calling GetCustomerLoadingData(1, 10); method by default i'm passing @pageIndex = 1 and @pageSize = 10 and return type of this method is Dataset and directly i'm binding to GridView control. Initially gridview control display 10 records
namespace LoadingData_Application
{
public partial class GridviewLoadingData : System.Web.UI.Page
{
static SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbCon"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GridView1.DataSource = GetCustomerLoadingData(1, 10);
GridView1.DataBind();
}
}
}
}
C# Coding : GetCustomerLoadingData
this GetCustomerLoadingData(int gridpageIndex, int totalpageSize) method is used to get no of records based on a @pageIndex and this method i'm calling many times...
private static DataSet GetCustomerLoadingData(int gridpageIndex, int totalpageSize)
{
SqlCommand cmd = new SqlCommand("proc_getCustomerLoading", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pageIndex", gridpageIndex);
cmd.Parameters.AddWithValue("@pageSize", totalpageSize);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
C# Coding : DropDownList SelectedIndexChanged Event
Whenever i change dropdownlist values this below event execute and its call GetCustomerLoadingData(int gridpageIndex, int totalpageSize) and passing parameter i.e @pageIndex value is now gettting from dropdownlist and @pageSize by default this value is 10 and result binding to gridview control
protected void ddl_PageIndex_SelectedIndexChanged(object sender, EventArgs e)
{
int no = Convert.ToInt16(ddl_PageIndex.SelectedItem.Text);
DataSet ds = GetCustomerLoadingData(no, 10);
if (ds.Tables[0].Rows.Count > 0)
{
GridView1.DataSource = ds;
GridView1.DataBind();
}
else
{
GridView1.EmptyDataText = " No Record Found....";
GridView1.DataBind();
}
}
}
}
0 Comments