---

后台代码：

#region 字段
protected int page;
protected int pageSize;
protected string keywords = "paging";
protected int totalCount;
#endregion

#region 绑定数据
///

/// 绑定数据
///

private void RptBind( bool isReSearch)
{
if (UserInfo == null)
{
if (!IsLogin())
{
return;
}
}
DataTable dt = new DataTable();
Hashtable hs = new Hashtable();
if (isReSearch)
{
if (!string.IsNullOrEmpty(Request.QueryString["OrderDate"]))
{
this.ddlOrderDate.SelectedValue = Request.QueryString["OrderDate"].ToString();
}
if (!string.IsNullOrEmpty(Request.QueryString["OrderStatus"]))
{
this.ddlOrderStatus.SelectedValue = Request.QueryString["OrderStatus"].ToString();
}
}

hs.Add("memberID", UserInfo.ID);
hs.Add("createDate", this.ddlOrderDate.SelectedValue);
hs.Add("status", this.ddlOrderStatus.SelectedValue);
if (!string.IsNullOrEmpty(this.txtKey.Text) && this.txtKey.Text != "商品名称/订单号")
{
hs.Add("nameOrNo", this.txtKey.Text);
}
else
{
hs.Add("nameOrNo", string.Empty);
}
this.pageSize = 5; //设置每页显示多少条记录
if(isReSearch)
{
this.page = StrToInt(HttpContext.Current.Request.QueryString["page"], 1);
}
else
{
this.page = 1;
}
DataSet ds = OrderService.GetOrdersByMembe(hs, this.pageSize, this.page, out this.totalCount);
if (ds != null && ds.Tables.Count > 0)
{
dt = ds.Tables[0];
OrderDetailData = ds.Tables[1];
}
this.rptOrderList.DataSource = dt;
this.rptOrderList.DataBind();

#region 绑定页码
string pageUrl = string.Empty;
pageUrl = CombUrlTxt("OrderList.aspx", "keywords={0}&page={1}&OrderDate={2}&OrderStatus={3}",
this.keywords, "__id__", ddlOrderDate.SelectedValue, ddlOrderStatus.SelectedValue);

PageContent.InnerHtml = OutPageList(this.pageSize, this.page, this.totalCount, pageUrl, 8);
#endregion
}

#region 替换指定的字符串
///

/// 替换指定的字符串
///

/// 原字符串
/// 旧字符串
/// 新字符串
///
public static string ReplaceStr(string originalStr, string oldStr, string newStr)
{
if (string.IsNullOrEmpty(oldStr))
{
return "";
}
return originalStr.Replace(oldStr, newStr);
}
#endregion
#endregion

SQL 分页

USE [HClub_dev]
GO
/****** Object: StoredProcedure [dbo].[P_GetOrdersDetail] Script Date: 2014/4/28 12:03:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: zzq
-- Create date: 2014-04-3
-- Description: 获取订单和订单详情
-- Test: exec P_GetOrdersDetail '111','r','01','2014-04-08',8,0
-- =============================================
ALTER PROCEDURE [dbo].[P_GetOrdersDetail]
@MemberID int,
@NameOrNo NVARCHAR(100),
@Status NVARCHAR(20),
@CreateDate dateTime,
@PageSize int,
@PageIndex int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @sqlText AS nvarchar(4000)
DECLARE @sqlTable AS nvarchar(4000)
DECLARE @queryStr AS NVARCHAR(4000)
DECLARE @subQuery AS NVARCHAR(4000)

SET @subQuery = 'select orderId from TM_OrderDetail where Name like ''%' + @NameOrNo + '%''';
SET @queryStr = 'SerialNo,BillTotal,Id,MemberID,logisticsTotal,Status,CreateDate,BillTotal + logisticsTotal as TotalPrice from TM_Order
WHERE MemberID='''+Convert(varchar(50),@MemberID)+''' AND Status = '''+Convert(varchar(50),@Status)+''' AND CreateDate >= '''+Convert(varchar(50),@CreateDate)+'''
AND (Id in ('+@subQuery+') OR Serialno='''+Convert(varchar(50),@NameOrNo)+''')'

SET @sqlTable = 'SELECT TOP ' + CAST((@PageIndex + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr;
SET @sqlText =
'SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' +
'FROM (' + @sqlTable + ') AS tableA ' +
'WHERE Id NOT IN(SELECT TOP ' +
CAST(@PageIndex * @pageSize AS varchar(30)) + ' Id FROM (' + @sqlTable + ') AS tableB)'
EXEC (@sqlText)

-- select
-- SerialNo
-- ,BillTotal
-- ,Id
-- ,MemberID
-- ,logisticsTotal
-- ,Status
-- ,CreateDate
-- ,BillTotal + logisticsTotal as TotalPrice
--from TM_Order where MemberID=@MemberID and [Status]=@Status and CreateDate>=@CreateDate
-- and (Id in(select orderId from TM_OrderDetail where Name like '%' + @NameOrNo + '%' )or Serialno=@NameOrNo)

select od.*,sc.PicUrl5,g.Name as GoodsName,sc.Name as SpecContentName,s.Name as SizeName from TM_Order o
LEFT JOIN TM_OrderDetail od ON o.Id = od.OrderID
LEFT JOIN TM_SpecContent sc ON od.SpecContentId = sc.Id AND od.GoodsID = sc.GoodsID
LEFT JOIN TM_Size s ON od.SizeId = s.Id and s.SpecContentID = od.SpecContentId
LEFT JOIN TM_Goods g ON od.GoodsID = g.Id
where o.MemberID= @MemberID and o.[Status]=@Status and o.CreateDate>=@CreateDate
and (od.Name like '%' + @NameOrNo + '%' or o.Serialno=@NameOrNo)

select count(*)
from TM_Order where MemberID=@MemberID and [Status]=@Status and CreateDate>=@CreateDate
and (Id in(select orderId from TM_OrderDetail where Name like '%' + @NameOrNo + '%' )or Serialno=@NameOrNo)
END