Wednesday, December 7, 2022

Grid view real paging with output parameter ASP.NET C# - TotalRowcount

 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