C#方法 Repeater、webdiyer:AspNetPager分页、存储过程分页

超、凢脫俗 2022-04-12 10:22 229阅读 0赞

此方法是基于三次架构、实体创建，可基于此修改

前台HTML:

后台代码：

Model类

public class Partners
        {
           //PartnersID, PartnersName, PartnersUrl, PCreateDate
            private int partnersID;
    
            public int PartnersID
            {
                get { return partnersID; }
                set { partnersID = value; }
            }
            private string partnersName;
    
            public string PartnersName
            {
                get { return partnersName; }
                set { partnersName = value; }
            }
            private string partnersUrl;
    
            public string PartnersUrl
            {
                get { return partnersUrl; }
                set { partnersUrl = value; }
            }
            private DateTime? pCreateDate;
    
            public DateTime? PCreateDate
            {
                get { return pCreateDate; }
                set { pCreateDate = value; }
            }

类：DAL

public List<Partners> GetEDM_CountryPropertyList( int pageSize, int pageIndex, ref int recordCount)
            {
                List<Partners> entities = new List<Partners>();
    
                SqlParameter[] pars = { 
                                          
                    new SqlParameter("@PageSize", pageSize),
                    new SqlParameter("@PageIndex", pageIndex),
                    SqlHelper.MakeParam("@RecordCount",SqlDbType.Int,4,ParameterDirection.Output,null)
                                      };
                SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "dbo.PartnertsList", pars);
                try
                {
                    while (reader.Read())
                    {
                        Partners cp = new Partners()
                        {
                            //PartnersID, PartnersName, PartnersUrl, PCreateDate
                            PartnersID = Field.GetInt(reader, "PartnersID"),
                            PartnersName = Field.GetString(reader, "PartnersName"),
                            PartnersUrl = Field.GetString(reader, "PartnersUrl"),
                            PCreateDate = Field.GetDateTime(reader, "PCreateDate"),
                        };
                        entities.Add(cp);
                    }
                }
                catch (Exception ex)
                {
                    recordCount = -1;
                }
                finally
                {
                    if (reader != null)
                        reader.Close();
                }
    
                recordCount = Convert.ToInt32(pars[2].Value);
                return entities;
            }

类：BLL

public class PartnersBLL
        {
           private readonly DLL.PartnersData country = new DLL.PartnersData();
            /// <summary>
            /// 国家房产信息 列表 分页
            /// </summary>
            /// <param name="ECP_countryId"></param>
            /// <param name="PropertyState"></param>
            /// <param name="pageSize"></param>
            /// <param name="pageIndex"></param>
            /// <param name="recordCount"></param>
            /// <returns></returns>
           public List<Partners> GetEDM_CountryPropertyList( int pageSize, int pageIndex, ref int recordCount)
            {
                return country.GetEDM_CountryPropertyList( pageSize, pageIndex, ref recordCount);
            }
    
            /// <summary>
            ///  分页  移民局
            /// </summary>
            /// <param name="?"></param>
            /// <param name="pageSize"></param>
            /// <param name="pageIndex"></param>
            /// <param name="recordCount"></param>
            /// <returns></returns>
           public List<Immigration> GetEDM_ImmigrationList(int pageSize, int pageIndex, ref int recordCount)
           {
               return country.GetEDM_ImmigrationList(pageSize, pageIndex, ref recordCount);
           }
        }

后台代码：

protected void Page_Load(object sender, EventArgs e)
            {
                
                if (!IsPostBack)
                {
                    PageBind();
                    PageBingUser();
                    PageBingMess();
                    PageBindImmigration();
                }
            }
            protected void PageBind()
            {
    
                int recordCount = 0;
                List<Partners> list = new List<Partners>();
                BLL.PartnersBLL country = new BLL.PartnersBLL();
                //this.pagerBarList.PageSize, this.pagerBarList.CurrentPageIndex, ref recordCount
                list = country.GetEDM_CountryPropertyList( this.pagerBarList.PageSize, this.pagerBarList.CurrentPageIndex, ref recordCount);
    
                rptCampaignList.DataSource = list;
                rptCampaignList.DataBind();
                //判断有多少条数据的
                this.pagerBarList.RecordCount = recordCount;
            }
            protected void pagerBarList_PageChanged(object sender, EventArgs e)
            {
               
                this.PageBind();
            }
            /// <summary>
            /// 删除或修改
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            protected void lnkBtnOp_Command(object sender, CommandEventArgs e)
            {
                string infoID = e.CommandArgument.ToString();
                //Dictionary<string, object> dic = new Dictionary<string, object>();
                //dic.Add("EMID", infoID);
                if (e.CommandName.Equals("del"))
                {
                    Dictionary<string, object> dic = new Dictionary<string, object>();
                    dic.Add("PartnersID", infoID);
                    if (BLL.BLL.Delete(dic, "Partners"))
                    {
                        Response.Write("<script>alert('操作成功！');window.location.href='PartnersList.aspx';</script>");
                    }
                    else
                    {
                        Response.Write("<script>alert('操作失败！');window.location.href='PartnersList.aspx';</script>");
                    }
                }
            }

需配置：Web.config文件

<system.web>
        <pages controlRenderingCompatibilityVersion="3.5" validateRequest="false" clientIDMode="AutoID" enableEventValidation="false" >
          <controls>
            <add tagPrefix="webdiyer" namespace="Wuqi.Webdiyer" assembly="AspNetPager" />
          </controls>
        </pages>
     </system.web>

所用到的存储过程：

USE [Production]
    GO
    /****** Object:  StoredProcedure [dbo].[PartnertsList]    Script Date: 01/06/2013 17:39:53 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE  [dbo].[PartnertsList]
    
    	@PageSize INT,
    	@PageIndex INT,
    	@RecordCount INT OUTPUT
    		
    AS
    BEGIN
    
       BEGIN TRAN
       DECLARE @Sql NVARCHAR(MAX), @Where NVARCHAR(MAX)
       
       --默认值 @SQL 语句取得总行数 
    	SET @Sql = 'SELECT @RecordCount = COUNT(1) FROM dbo.Partners  '
    	SET @Where = ''
    	
    	--查询当前活动下的邮件列表
    	
    		if(@Where!='') set @Where=@Where+' and '
    	
    	
        IF(@Where != '') SET @Where = ' WHERE ' + @Where
    
    	--合并 @SQL 查询行数
    	SET @Sql = @Sql + @Where
    	print @Sql
    	EXEC sp_executesql @Sql,
    	N'@RecordCount INT OUTPUT',
    	@RecordCount = @RecordCount OUTPUT
    	
    	--合并 @SQL 查询
    	SET @Sql = 'SELECT * FROM (
    	SELECT ROW_NUMBER() OVER(ORDER BY PartnersID DESC) AS RowNumber, dbo.Partners.* FROM dbo.Partners ' + @Where + ') as tmp 
    	WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize'
         
         
         
         EXEC sp_executesql @Sql,
    	N'@PageSize INT,
    	@PageIndex INT',
    	@PageSize,
    	@PageIndex
    	
        IF @@ERROR<>0
    	BEGIN
    		ROLLBACK TRAN
    	END
    	ELSE
    	BEGIN
    		COMMIT TRAN
    	END
    END