1. Create Stored Proc in Database for read Table for Page :
CREATE PROCEDURE dbo.GetListPatientsbyPage
@LimitC INT,
@PageC int,
@TotalC int output
AS
BEGIN
SELECT Birthday, UID, Surname,Name,MiddleName
,rowNum, (rowNum / @LimitC) + 1 as pageNum
FROM (
SELECT a.Birthday,UID, Surname,Name,MiddleName
, Row_Number() OVER (order by surname) as rowNum
FROM regPatients a
) x
WHERE (rowNum / @LimitC) + 1 = @pageC
set @TotalC=(Select count(*) FROM regPatients a with (nolock))
END
where 3 parameter:
@LimitC - Row Per Page
@PageC - number Page
@TotalC - count all rows in request
2.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="PatientCabinet.aspx.cs" Inherits="PatientCabinet.PatientCabinet" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Collections.Generic" %>
<script runat="server">
StoreReadDataEventArgs e;
protected void db_onSelecting(object sender, SqlDataSourceCommandEventArgs e)
{
// on selecting set number page - not woking - why??
// SqlDataSource1.SelectParameters["pagec"].DefaultValue = this.e.Page.ToString();
}
protected void db_onSelected(object sender, SqlDataSourceStatusEventArgs e)
{
(this.Store1.Proxy[0] as PageProxy).Total = (int) e.Command.Parameters["@TotalC"].Value; // set total row after select from db
}
protected void Pat_ReadData(object sender, StoreReadDataEventArgs e)
{
this.e = e; //? for set in onselecting event - from example - but not working
SqlDataSource1.SelectParameters["pageC"].DefaultValue =e.Page.ToString(); // set page number before request
}
</script>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" OnSelecting="db_onSelecting"
OnSelected="db_onSelected" SelectCommandType="StoredProcedure"
ConnectionString="<%$ ConnectionStrings:FDConnectionString %>"
SelectCommand="dbo.getListPatientsbyPage">
<SelectParameters>
<asp:QueryStringParameter Name="LimitC" Type="Int32" QueryStringField="LimitC" DefaultValue="15" />
<asp:QueryStringParameter Name="PageC" Type="Int32" QueryStringField="PageC" DefaultValue="1"/>
<asp:parameter name="TotalC" type="Int32" direction="Output" defaultvalue="0" />
</SelectParameters>
</asp:SqlDataSource>
<ext:ResourceManager ID="ResourceManager1" runat="server" />
<ext:GridPanel
runat="server"
ID="GridPanel1"
Title="Patient"
Frame="true"
Height="300">
<Store>
<ext:Store
ID="Store1"
runat="server"
DataSourceID="SqlDataSource1"
OnRefreshData="Store_RefreshData"
OnReadData="Pat_ReadData"
>
<Proxy>
<ext:PageProxy />
</Proxy>
<Model>
<ext:Model ID="Model1" runat="server" IDProperty="Patient">
<Fields>
<ext:ModelField Name="Birthday" Type="Date"/>
<ext:ModelField Name="UID" />
<ext:ModelField Name="Surname" />
<ext:ModelField Name="Name" />
<ext:ModelField Name="MiddleName" />
<ext:ModelField Name="rowNum" />
<ext:ModelField Name="pageNum" />
</Fields>
</ext:Model>
</Model>
</ext:Store>
</Store>
<ColumnModel ID="ColumnModel1" runat="server">
<Columns>
<ext:DateColumn ID="DateColumn1" runat="server" DataIndex="BirthDate" Text="BirthDate" Width="110" Format="yyyy-MM-dd" />
<ext:Column ID="Column2" runat="server" DataIndex="UID" Text="UID" Width="150" />
<ext:Column ID="Column3" runat="server" DataIndex="Surname" Text="Surname" Width="150" />
<ext:Column ID="Column4" runat="server" DataIndex="Name" Text="Name" Width="150" />
<ext:Column ID="Column5" runat="server" DataIndex="MiddleName" Text="MiddleName" Width="100" />
<ext:Column ID="Column6" runat="server" DataIndex="rowNum" Text="rowNum" Width="100" />
<ext:Column ID="Column7" runat="server" DataIndex="pageNum" Text="pageNum" Width="100" />
</Columns>
</ColumnModel>
<View>
<ext:GridView ID="GridView1" runat="server">
</ext:GridView>
</View>
<SelectionModel>
<ext:RowSelectionModel ID="RowSelectionModel1" runat="server" Mode="Multi" />
</SelectionModel>
<BottomBar>
<ext:PagingToolbar ID="PagingToolbar1"
runat="server"
DisplayInfo="true"
DisplayMsg="Displaying patient {0} - {1} of {2}"
EmptyMsg="No patient to display"
/>
</BottomBar>
</ext:GridPanel>
<ext:Label ID="Label1" runat="server" />
</form>
</body>
</html>
Its work fine!
one moment - why not working set number page ( @pagec) in selecting event ? i don understand.. :)