南洋之勾魂降头完整版:使用ObjectDataSource 控件自定义自己的分页(vs2008新控件Listvi...

在看贴子http://msdn.microsoft.com/zh-cn/magazine/cc337898.aspx 时,里边有一段话,如下:

请注意：因为 DataPager 完全依赖 ListView 执行实际的数据分页，而 ListView 又依赖 DataSource 控件，所以对于其它数据绑定控件也存在相同的分页限制。例如，对于 SqlDataSource 控件，仅当其设置为 DataSet 模式时分页才能正常工作，这意味着需要将整个结果集加载到内存中才能执行分页。当然，您可以使用自定义 DataSource 控件或使用 ObjectDataSource 控件自定义自己的分页。

就来试一下做一小例子.

1. objectdatasource前端代码如下:

SelectMethod是指向的查数据方法名字;

SelectCountMethod是指向查找所有数据总行数的方法;

TypeName是上边两个方法所在的类的full type name.

selectParameters可以在前端指定或后端代码指定, 如下:

            this.ObjectDataSource1.SelectParameters.Clear();
            this.ObjectDataSource1.SelectParameters.Add(new Parameter("url", DbType.String, "aaa.aspx"));
            this.ObjectDataSource1.SelectParameters.Add(new Parameter("langCode", DbType.String, bp.FrontLanguage));
            this.ObjectDataSource1.SelectParameters.Add(new Parameter("codedefault", DbType.String, codedefault));
            this.ObjectDataSource1.SelectParameters.Add(new Parameter("maximumRows", DbType.Int32, ConfigurationManager.AppSettings["NewsPageSize"]));
            this.ObjectDataSource1.SelectParameters.Add(new Parameter("startRowIndex", DbType.Int32, "1"));

2. SelectMethod和SelectCountMethod的指向的方法代码如下(注意方法的参数和上面的selectParameters)

        public int SelectCountForContentAllInfo(string url, string langCode, string codedefault, int maximumRows, int startRowIndex)
        {
            string filterString = " CT_URL='" + url + "' and LangCode='" + langCode + "' ";
            int returnValue = base.SelectCount(filterString); //base.SelectCount返回数据所有行数
            return returnValue;
        }

        public List SelectAllDataForNewsPageing(string url, string langCode, string codedefault, int maximumRows, int startRowIndex)
        {
            List list = new List();
            string filterString = " CT_URL='" + url + "' and LangCode='" + langCode + "' ";
            list = base.SelectData(maximumRows, startRowIndex + 1, " PublishDate desc ", filterString); //.OrderByDescending(ca => ca.PublishDate).ToList();

            //base.SelectData是根据条件, 每页显示行数参数值maximumRows(以前叫pagesize), 从那一行开始startRowIndex
            if (list.Count == 0 && langCode.ToLower() != codedefault.ToLower())
            {
                filterString = " CT_URL='" + url + "' and LangCode='" + codedefault + "' ";
                list = base.SelectData(maximumRows, startRowIndex + 1, " PublishDate desc ", filterString);
            }

            return list;
        }

3. 根据PageSize和startRowIndex查分页数据的存储过程跟一般一样.

 ALTER PROCEDURE [dbo].[存储过程名字]
  @TableName nvarchar(128),
  @MaxRows int,
  @StartRow int,
  @SortExpression nvarchar(200),
  @FilterString nvarchar(2000)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
--  Only for Debug
-- SET @TableName = 'tblPurchaseOrder'
-- SET @MaxRows = 10
-- SET @StartRow = 1
-- SET @SortExpression = 'OrderDate'

 DECLARE @Filter NVARCHAR(2100);
 DECLARE @SqlString NVARCHAR(4000);
 DECLARE @ParmDefinition NVARCHAR(500);

 DECLARE @EndRow int

 SET @EndRow = @MaxRows - 1 + @StartRow

 IF @FilterString is null or @FilterString = ''
  BEGIN
   SET @Filter = ''
  END
  ELSE
  BEGIN 
   SET @Filter = ' WHERE ('+ @FilterString +') '
  END

 /* Specify the parameter format one time. */
 SET @ParmDefinition = http://www.360doc.com/mailto:N'@StartRowNum int, @EndRowNum int';

  SET @SqlString = N'
   WITH TempTable AS
   (
    SELECT  *, ROW_NUMBER() OVER (ORDER BY ' + @SortExpression + ') AS RowNum
    FROM  [' + @TableName + '] ' + @Filter + '
   )
   SELECT  *  FROM  TempTable
   WHERE RowNum BETWEEN @StartRowNum AND @EndRowNum
   ORDER BY ' + @SortExpression
 PRINT @SqlString

  EXECUTE sp_executesql @SqlString
   , @ParmDefinition
   , @StartRowNum = @StartRow
   , @EndRowNum = @EndRow;
END