void Bind(string PostHeld, int currentPage)
{
int pageSize = 20;
int totalRowCount = 0;
int startRowNumber = ((currentPage) * pageSize) + 1;
DataTable dt = Repository.GetEmployeeListWithPaging(PostHeld,startRowNumber, pageSize,out totalRowCount);
//dt.DefaultView.Sort = sort;
DataTable dt2 = ApplyFiltersWithSorting(dt, sort, "");
GridView1.VirtualItemCount = totalRowCount;
GridView1.DataSource = dt2;
GridView1.DataBind();
int ip = 0;
try
{
//ip = Convert.ToInt32(dt2.Rows.Count);
ip = totalRowCount;
}
catch { }
if (Session["AccessType"].ToString() == "All")
{
//lblBadge.Text = new DataAccess().Read("pendingApprovalUS").Tables[0].Rows[0][0].ToString();
//lblCorrectionRequests.Text = new DataAccess().Read("CorrectionRequestCountUS").Tables[0].Rows[0][0].ToString(); ;
}
else if (Session["AccessType"].ToString() == "Approver")
{
//lblBadge.Text = new DataAccess().Read("pendingApprovalDS").Tables[0].Rows[0][0].ToString();
//lblCorrectionRequests.Text = new DataAccess().Read("CorrectionRequestCountDS").Tables[0].Rows[0][0].ToString(); ;
}
int ss = CalculateSanctionedStrength(Institute, Discipline);
ltrSS.Text = "Sanctioned Strength: " + ss.ToString();
ltrCount.Text = "- Total employees in-position: " + ip.ToString();
ltrVacancy.Text = "= Total vacancy: " + (ss - ip).ToString();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Bind(Session["postheld"].ToString(), GridView1.PageIndex);
}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
Bind(Session["postheld"].ToString(), e.NewPageIndex);
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
Bind(Session["postheld"].ToString(), GridView1.PageIndex);
}
// calling of paging function
public static DataTable GetEmployeeListWithPaging(string PostHeld, int startIndex, int pageSize , out int totalCount)
{
SqlParameter parTotalCount = new SqlParameter("TotalCount", SqlDbType.Int);
parTotalCount.Direction = ParameterDirection.Output;
SqlParameter[] param = { new SqlParameter("postheld", PostHeld),
new SqlParameter("StartIndex", startIndex),
new SqlParameter("PageSize",pageSize) ,
parTotalCount };
DataTable dt = new DataAccess().Read("read_cadre_info_paging", param).Tables[0];
totalCount = Convert.ToInt32(parTotalCount.Value);
return dt;
}
}
//Also you can get dataaccess.cs in PMS folder
aspx page
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" DataKeyNames="ID,MainID" OnRowDataBound="GridView1_RowDataBound" CssClass="table table-bordered table-responsive" AllowPaging="True" AllowCustomPaging="true" OnPageIndexChanging="GridView1_PageIndexChanging" PageSize="20" FirstPageText="First" PreviousPageText="Previous" NextPageText="Next" LastPageText="Last" AllowSorting="True" OnRowDeleting="GridView1_RowDeleting" OnInit="GridView1_Init" OnRowCommand="GridView1_RowCommand" OnSelectedIndexChanged="OnSelectedIndexChanged">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<%# (Container.DataItemIndex + 1) + (GridView1.PageIndex * 20)%>
</ItemTemplate></ <asp:TemplateField> </Columns></asp:GridView >
stored procedure
USE [PMS]
GO
/****** Object: StoredProcedure [dbo].[read_cadre_info_paging] Script Date: 07-12-2022 16:46:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[read_cadre_info_paging]
@postheld varchar(50),
@StartIndex int,
@PageSize int,
@TotalCount int OutPut
AS
BEGIN
with cte as (select Max(ee.ID) as ID,MainID,'temp'=Count(MainID),disc.Discipline,disc.DisciplineID,'AdjustedDiscipline'=Adisc.Discipline,'AdjustedDisciplineID'=ee.AdjustedDiscipline,Title,Name,'FMSID'=RIGHT('00000'+convert(varchar(6),ee.FMSID),6),Gender,Category,RosterCategory,DOB,AppointmentDate,AppointmentDateSS,'PresentDesignation'=ids.Designation,AppointmentDatePresent,PresentInstitutePostingDate,'Institute'=inst.I_Aym+', '+inst.I_City,'InstituteID'=inst.InstituteID_cms,DOR,'SMD'=smd.Div_Name,'SMDID'=smd.Div_Code,Mobile,ee.Email,Remarks,InstituteRemarks,'State'=st.State_Name,'StateID'=st.State_Id,DeputationFrom,DeputationTo,StudyLeaveFrom,StudyLeaveTo,WSP,'RegionalStation'=ip.I_Aym+','+ip.I_City,ee.DateTime,'PostHeld'=ict.CadreType,'CadreTypeID'=ee.PostHeld,'PresentDesignationID'=ids.DesignationID,'AiprTime'=af.DateTime,'AiprFile'=af.AIPRFile,'VigilanceStatus'=ivt.StatusText,'VigilanceStatusId'=ivt.Id,ee.PreviousOrganization,au.emp_code,do.State_Name as domicile_name,do.State_Id as domicile_id from icms_employee ee
left join icms_Discipline disc on disc.DisciplineID=ee.Discipline
left join icms_Discipline Adisc on Adisc.DisciplineID=ee.AdjustedDiscipline
left join Permisnet_new.dbo.Institute inst on inst.InstituteID_cms=ee.Institute
left join Permisnet_new.dbo.Division smd on smd.Div_Code=inst.Div_Code
left join Permisnet_new.dbo.State st on st.State_Id=inst.I_State_ID
left join Permisnet_new.dbo.Institute ip on ip.InstituteID_cms=ee.RegionalStation
left join icms_CadreType ict on ict.CadreTypeID=ee.PostHeld
left join icms_Designation ids on ids.DesignationID=ee.PresentDesignation
left join (select * from AIPR_Files where Flag='1' and aipr_year=YEAR(GETDATE())) af on af.FMSID=ee.FMSID--year wise AIPR
left join (SELECT State_Id,State_Name,profile_pic,m_tongue,erp_id
FROM Add_info ai join Permisnet_new.dbo.State s on s.State_Id=ai.domicile) do on do.erp_id=ee.FMSID
left join (select * from icms_VigilanceStatus where Flag='1') iv on iv.FMSID=ee.FMSID
left join (select * from pms.dbo.Auth_user) au on au.erp_id=ee.FMSID
left join icms_VigilanceStatusTypes ivt on ivt.Id=iv.VigilanceStatus
where PostHeld=@postheld and ee.Flag='true' and Retired='false'
group by MainID,ee.ID,disc.Discipline,disc.DisciplineID,Adisc.Discipline,ee.AdjustedDiscipline,Title,Name,ee.FMSID,Gender,Category,RosterCategory,DOB,AppointmentDate,AppointmentDateSS,ids.Designation,AppointmentDatePresent,PresentInstitutePostingDate,inst.I_Aym,inst.I_City,inst.InstituteID_cms,DOR,smd.Div_Name,smd.Div_Code,Mobile,ee.Email,Remarks,InstituteRemarks,st.State_Name,st.State_Id,DeputationFrom,DeputationTo,StudyLeaveFrom,StudyLeaveTo,WSP,ip.I_Name,ip.i_aym,ip.I_City,ee.DateTime,ict.CadreType,ee.PostHeld,ids.DesignationID,af.DateTime,af.AIPRFile,ivt.StatusText,ivt.Id,ee.PreviousOrganization,au.emp_code,do.State_Name,do.State_Id ),
cte1 as(
select ROW_NUMBER() over(order by Institute, Discipline, PresentInstitutePostingDate asc) as RowNumber,* from cte
)
select * into #t from cte1
SET @TotalCount = (SELECT count(*) from #t)
SELECT * from #t where RowNumber between @startIndex and (@startIndex+@PageSize-1)
END
No comments:
Post a Comment