使用临时表配合ObjectDataSource进行分页

TempTablePaging_ObjectDataSource.aspx

        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="LoadAllProduct" TypeName="ProductBLL" DataObjectTypeName="Product"

        EnablePaging="True" MaximumRowsParameterName="maxRows" StartRowIndexParameterName="startIndex" SelectCountMethod="CountAll" SortParameterName="sortedBy"

        ></asp:ObjectDataSource>

   

 

 

 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Configuration;

using System.Data;

using System.Data.Common;

using System.Data.SqlClient;

using System.Web;

public class ProductBLL

{

    protected int _count = -1;

    public ProductBLL()

     {    }

 

    public List<Product> LoadAllProduct(int startIndex, int maxRows, string sortedBy)

    {

        List<Product> products = new List<Product>();

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

           

    string commandText = @"

     -- 为分页建立一张临时表

    CREATE TABLE #TempPageTable

    (

        IndexId int IDENTITY (0, 1) NOT NULL,

         id int   

     )

 

    -- 读取数据插入临时表

    INSERT INTO #TempPageTable

     (

         [id]

     )

     SELECT

         [Productid]   

     FROM Products";

 

    if (sortedBy != "")

        {     commandText += " ORDER BY " + sortedBy;        }

         commandText += @" SET @totalRecords = @@ROWCOUNT

      

    SELECT

         src.[ProductID],

         src.[ProductName],

         src.[CategoryID],

         src.[Price],

        src.[InStore],

        src.[Description]  

     FROM Products src, #TempPageTable p

     WHERE 

         src.[productid] = p.[id] AND

        p.IndexId >= @StartIndex AND p.IndexId < (@startIndex + @maxRows)";

        

        if (sortedBy != "") {

              commandText += " ORDER BY " + sortedBy;

         }

 

         SqlCommand command = new SqlCommand(commandText, conn);

         command.Parameters.Add(new SqlParameter("@startIndex", startIndex));

         command.Parameters.Add(new SqlParameter("@maxRows", maxRows));

         command.Parameters.Add(new SqlParameter("@totalRecords", SqlDbType.Int));

         command.Parameters["@totalRecords"].Direction = ParameterDirection.Output;

 

         conn.Open();

         SqlDataReader dr = command.ExecuteReader();

         while (dr.Read()) {

              Product prod = new Product();

 

              prod.ProductID = (int)dr["ProductID"];

              prod.ProductName= (string)dr["ProductName"];

            prod.CategoryID = (int)dr["CategoryID"];

              prod.Price = (decimal)dr["price"];

            prod.InStore=(Int16)dr["InStore"];

            prod.Description=(String)dr["Description"];

              products.Add(prod);

         }

 

         dr.Close();

         conn.Close();

         _count = (int)command.Parameters["@totalRecords"].Value;

         return products;

     }

 

    public int CountAll()

    {        return _count;    }

 

}