分页sql

1.通用的分页sql

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

ALTER PROCEDURE [dbo].[PPagedSql]
(
 @TableName VARCHAR(4000),          --表名或视图或关联表
 @ColumnName VARCHAR(4000),   --字段名
 @WhereString VARCHAR(4000),  --条件语句(必须加WHERE)
 @SortString VARCHAR(4000),   --排序字段(必须加ORDER BY)
 @PageSize INT = 10,    --每页多少条记录
 @PageIndex INT = 1,    --指定当前为第几页
 @RowCount INT OUTPUT    --返回总记录数
)
AS
 
BEGIN
 DECLARE @PageCount INT;  
 DECLARE @StartIndex INT;
    DECLARE @EndIndex INT;
    DECLARE @RowCountSql NVARCHAR(4000);
    DECLARE @PagedSql NVARCHAR(4000);

    SET @RowCountSql = N'SELECT @RowCount = COUNT(*) FROM ' + @TableName + ' ' + @WhereString;
 --返回总记录数
    EXEC sp_executesql @RowCountSql,N'@RowCount INT OUT',@RowCount OUTPUT;

 SET @PageCount = CEILING((@RowCount*1.0) / @PageSize)
 IF @PageCount>0 AND @PageCount < @PageIndex
  BEGIN
   SET @PageIndex = @PageCount;
  END
    SET @StartIndex = (@PageIndex-1)*@PageSize + 1;
    SET @EndIndex = @PageIndex*@PageSize;

    SET @PagedSql = N'(SELECT ROW_NUMBER() OVER (' + @SortString + ') AS RowId,' + @ColumnName +' FROM ' + @TableName + ' ' + @WhereString;
    SET @PagedSql = 'SELECT * FROM ' + @PagedSql + ') AS t WHERE RowId BETWEEN ' + LTRIM(STR(@StartIndex)) + ' AND ' + LTRIM(STR(@EndIndex));
    --执行分页数据
 EXEC sp_executesql @PagedSql
END

 

 

1.专用的分页sql

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

ALTER PROCEDURE [dbo].[PPagedSceneOrder]
(
 @OrderId VARCHAR(10),
 @AgentId VARCHAR(22),
 @PayType TINYINT,
 @Status TINYINT,
 @TimeType TINYINT,
 @BeginTime DATETIME,
 @EndTime DATETIME,
 @ContactName NVARCHAR(17),
 @Mobile VARCHAR(13),
 @SceneName VARCHAR(62),
 @TicketName VARCHAR(62),
 @PageSize INT=10,
 @PageIndex INT=1,
 @IsRowCount BIT=0,
 @RowCount INT OUTPUT
)
AS
BEGIN
 DECLARE @StartIndex INT;
    DECLARE @EndIndex INT;
 DECLARE @PagedSql NVARCHAR(4000);
 DECLARE @RowCountSql NVARCHAR(4000);
 DECLARE @WhereString NVARCHAR(4000);

 SET @StartIndex = (@PageIndex-1)*@PageSize + 1
    SET @EndIndex = @PageIndex*@PageSize

 SET @PagedSql='SELECT ROW_NUMBER() OVER (ORDER BY OS.OrderId DESC)AS RowId,
 O.OrderId, O.Status,
 OC.ContactName, OC.Mobile,
 OS.TicketNum,OS.PlanGoDate,OS.SalePrice,OS.AgentId,OS.CreateTime,OS.PayType,
 ST.TicketName,
 S.SceneName
 FROM Orders AS O INNER JOIN
 OrderContact AS OC ON O.OrderId = OC.OrderId INNER JOIN
 OrderScene AS OS ON O.OrderId = OS.OrderId INNER JOIN
 SceneTicket AS ST ON OS.TicketId = ST.TicketId INNER JOIN
 Scene AS S ON S.SceneId = ST.SceneId'

 SET @RowCountSql='SELECT @RowCount=COUNT(*) FROM Orders AS O INNER JOIN
 OrderContact AS OC ON O.OrderId = OC.OrderId INNER JOIN
 OrderScene AS OS ON O.OrderId = OS.OrderId INNER JOIN
 SceneTicket AS ST ON OS.TicketId = ST.TicketId INNER JOIN
 Scene AS S ON S.SceneId = ST.SceneId';

 SET @WhereString='WHERE 1=1'; 
 IF @OrderId<>'' AND @OrderId IS NOT NULL
 BEGIN  
  SET @WhereString=@WhereString+' AND (O.OrderId LIKE @OrderId)'
 END 
 IF @AgentId<>'' AND @AgentId IS NOT NULL
 BEGIN
  SET @WhereString=@WhereString+' AND (OS.AgentId LIKE @AgentId)'
 END 
 IF @PayType IS NOT NULL
 BEGIN
  SET @WhereString=@WhereString+' AND (ST.PayType=@PayType)'
 END
 IF @Status IS NOT NULL
 BEGIN
     SET @WhereString=@WhereString+' AND (O.Status=@Status)'
 END
 IF @TimeType=0
  BEGIN
   SET @WhereString=@WhereString+' AND (OS.CreateTime>=@BeginTime AND OS.CreateTime<@EndTime)'
  END
 ELSE IF @TimeType=1
  BEGIN
   SET @WhereString=@WhereString+' AND (OS.PlanGoDate>=@BeginTime AND OS.PlanGoDate<@EndTime)'
  END
 IF @ContactName<>'' AND @ContactName IS NOT NULL
  BEGIN
   SET @WhereString=@WhereString+' AND (OC.ContactName LIKE @ContactName)'
  END
 IF @Mobile<>'' AND @Mobile IS NOT NULL
  BEGIN
   SET @WhereString=@WhereString+' AND (OC.Mobile LIKE @Mobile)'
  END
 IF @SceneName<>'' AND @SceneName IS NOT NULL
  BEGIN
   SET @WhereString=@WhereString+' AND (S.SceneName LIKE @SceneName)'
  END
 IF @TicketName<>'' AND @TicketName IS NOT NULL
  BEGIN
   SET @WhereString=@WhereString+' AND (ST.TicketName LIKE @TicketName)'
  END

 IF (@WhereString<>'' OR @WhereString IS NOT NULL)
        BEGIN           
            SET @PagedSql=@PagedSql+' '+@WhereString;
   SET @RowCountSql=@RowCountSql+' '+@WhereString;
        END

 SET @OrderId=N'%'+@OrderId+N'%';
 SET @AgentId=N'%'+@AgentId+N'%';
 SET @ContactName=N'%'+@ContactName+N'%';
 SET @Mobile=N'%'+@Mobile+N'%';
 SET @SceneName=N'%'+@SceneName+N'%';
 SET @TicketName=N'%'+@TicketName+N'%';

 SET @PagedSql='SELECT * FROM (' + @PagedSql + ') AS t WHERE RowId BETWEEN '+STR(@StartIndex)+' AND '+STR(@EndIndex);

 IF (@IsRowCount=1)
        BEGIN
   EXEC sp_executesql @RowCountSql,N'@OrderId VARCHAR(10),@AgentId VARCHAR(22), @PayType TINYINT, @Status TINYINT, @BeginTime datetime, @EndTime datetime, @ContactName VARCHAR(17),@Mobile VARCHAR(13),@SceneName VARCHAR(60), @TicketName VARCHAR(62),@RowCount INT OUT',
   @OrderId,@AgentId,@PayType,@Status,@BeginTime,@EndTime,@ContactName,@Mobile,@SceneName,@TicketName,@RowCount OUTPUT
        END
 EXEC sp_executesql @PagedSql,N'@OrderId VARCHAR(10),@AgentId VARCHAR(22), @PayType TINYINT, @Status TINYINT, @BeginTime datetime, @EndTime datetime, @ContactName VARCHAR(17),@Mobile VARCHAR(13),@SceneName VARCHAR(60), @TicketName VARCHAR(62)',
@OrderId,@AgentId,@PayType,@Status,@BeginTime,@EndTime,@ContactName,@Mobile,@SceneName,@TicketName

END

 

3.客户程序端分页sql

public static string GetPagedSql(string tableName, string columnName, string whereString, string sortString, AspNetPager aspNetPager)
        {
            return GetPagedSql(tableName, columnName, whereString, sortString, aspNetPager.PageSize, aspNetPager.CurrentPageIndex, true);
        }
        public static string GetPagedSql(string tableName, string columnName, string whereString, string sortString, AspNetPager aspNetPager, bool isRowCount)
        {
            return GetPagedSql(tableName, columnName, whereString, sortString, aspNetPager.PageSize, aspNetPager.CurrentPageIndex, isRowCount);
        }
        /// <summary>
        /// 获取分页sql(如果当前页索引>实际记录页数，则当前页索引=实际记录页数)
        /// </summary>
        /// <param name="tableName">表名、视图、关联表</param>
        /// <param name="columnName">列名</param>
        /// <param name="whereString">条件语句</param>
        /// <param name="sortString">排序语句</param>
        /// <param name="pageSize">每页记录条数</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="isRowCount">是否需要统计记录条数</param>
        /// <returns>分页sql</returns>
        public static string GetPagedSql(string tableName, string columnName, string whereString, string sortString, int pageSize, int pageIndex, bool isRowCount)
        {
            return string.Format(@"DECLARE @StartIndex INT,@EndIndex INT,@RowCount INT,@PageCount INT;
SET @StartIndex=({1}-1)*{0}+1;SET @EndIndex={1}*{0};
IF {6}=1
BEGIN
    SELECT @RowCount=COUNT(*) FROM {2} {4};
    SET @PageCount=CEILING((@RowCount*1.0)/{0})
    IF @PageCount>0 AND @PageCount<{1}
     BEGIN
      SET @StartIndex=(@PageCount-1)*{0}+1;
      SET @EndIndex=@PageCount*{0};
     END
END               
SELECT * FROM (SELECT ROW_NUMBER() OVER ({5})AS RowId, {3} FROM {2} {4}) AS t WHERE RowId BETWEEN @StartIndex AND @EndIndex;SELECT @RowCount", pageSize, pageIndex, tableName, columnName, whereString, sortString, Convert.ToInt32(isRowCount));
        }
        public static string GetPagedSql1(string tableName, string columnName, string whereString, string sortString, int pageSize, int pageIndex, bool isRowCount)
        {
            int startIndex = pageSize * (pageIndex - 1) + 1;
            int endIndex = pageSize * pageIndex;
            return string.Format(@"SELECT * FROM (SELECT ROW_NUMBER() OVER ({3})AS RowId, {1} FROM {0} {2}) AS t WHERE RowId BETWEEN {4} AND {5};
IF {6}=1 SELECT COUNT(*) FROM {0} {2};", tableName, columnName, whereString, sortString, startIndex, endIndex, Convert.ToInt32(isRowCount));
        }

 

 

 4.页面调用代码

 private void InitControl(bool isRowCount)
    {
        string orderId = this.tbOrderId.Text.Trim();
        string beginDate = this.dpBeginDate.Text;
        string endDate = this.dpEndDate.Text;
        string direction = this.ddlDirection.SelectedValue;
        QueryHelper queryHelper = new QueryHelper();
        StringBuilder whereString = new StringBuilder("WHERE 1=1");
        if (!string.IsNullOrEmpty(orderId))
        {
            whereString.Append(" AND (OLN.OrderId LIKE @OrderId)");
            queryHelper.AddInParameter("@OrderId", string.Format("%{0}%", orderId));
        }
        if (!string.IsNullOrEmpty(beginDate))
        {
            whereString.Append(" AND (OLN.CreateTime >= @BeginDate)");
            queryHelper.AddInParameter("@BeginDate", beginDate);
        }
        if (!string.IsNullOrEmpty(endDate))
        {
            whereString.Append(" AND (OLN.CreateTime < @EndDate)");
            queryHelper.AddInParameter("@EndDate", DateTime.Parse(endDate).AddDays(1));
        }
        if (!string.IsNullOrEmpty(direction))
        {
            whereString.Append(" AND (OLN.Direction = @Direction)");
            queryHelper.AddInParameter("@Direction", direction);
        }

        string tableName = "Orders AS O INNER JOIN OrderLeaveNote AS OLN ON O.OrderId = OLN.OrderId INNER JOIN OrderContact AS OC ON OC.OrderId = OLN.OrderId LEFT JOIN UserOperator AS UO ON OLN.OperatorId = UO.OperatorId ";
        string columnName = "O.OrderType, O.OrderId, OLN.OrderStatus, OLN.Note, OLN.Direction, OLN.OperatorId, OLN.CreateTime, UO.OperatorName, UO.OperatorLoginId, OC.ContactName";
        string sortString = "ORDER BY OLN.OrderId DESC, OLN.CreateTime DESC";
        queryHelper.CommandText = PagedHelper.GetPagedSql(tableName, columnName, whereString.ToString(), sortString, this.AspNetPager1, isRowCount);
        DataSet ds = queryHelper.ExecuteDataSet();
        this.gvOrderLeaveNote.DataSource = ds.Tables[0];
        this.gvOrderLeaveNote.DataBind();
        if (isRowCount)
        {
            this.AspNetPager1.RecordCount = (int)ds.Tables[1].Rows[0][0];
        }
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        this.AspNetPager1.CurrentPageIndex = 1;
        this.InitControl(true);
    }
    protected void AspNetPager1_PageChanged(object sender, EventArgs e)
    {
        this.InitControl(false);
    }

--------------------------------------------------------------------------

private void InitControl(bool isRowCount)
    {
        string orderId = this.tbOrderId.Text;
        string agentId = this.tbAgentId.Text.Trim();
        string payType = this.ddlPayType.SelectedValue;
        string status = this.ddlStatus.SelectedValue;
        byte timeType = this.rbtnReserveDate.Checked ? (byte)0 : (byte)1;
        string beginDate = this.dpBeginDate.Text;
        string endDate = this.dpEndDate.Text;
        string contactName = this.tbContactName.Text.Trim();
        string mobile = this.tbMobile.Text.Trim();
        string sceneName = this.tbSceneName.Text.Trim();
        string ticketName = this.tbTicketName.Text.Trim();

        DbCommand dbCommand = DbHelper.DB.GetStoredProcCommand("PPagedSceneOrder");
        DbHelper.DB.AddInParameter(dbCommand, "OrderId", DbType.String, string.IsNullOrEmpty(orderId) ? DBNull.Value : (object)orderId);
        DbHelper.DB.AddInParameter(dbCommand, "AgentId", DbType.String, string.IsNullOrEmpty(agentId) ? DBNull.Value : (object)agentId);
        DbHelper.DB.AddInParameter(dbCommand, "PayType", DbType.String, string.IsNullOrEmpty(payType) ? DBNull.Value : (object)payType);
        DbHelper.DB.AddInParameter(dbCommand, "Status", DbType.String, string.IsNullOrEmpty(status) ? DBNull.Value : (object)status);
        DbHelper.DB.AddInParameter(dbCommand, "TimeType", DbType.Byte, timeType);
        DbHelper.DB.AddInParameter(dbCommand, "BeginTime", DbType.DateTime, DateTime.Parse(beginDate));
        DbHelper.DB.AddInParameter(dbCommand, "EndTime", DbType.DateTime, DateTime.Parse(endDate).AddDays(1));
        DbHelper.DB.AddInParameter(dbCommand, "ContactName", DbType.String, string.IsNullOrEmpty(contactName) ? DBNull.Value : (object)contactName);
        DbHelper.DB.AddInParameter(dbCommand, "Mobile", DbType.String, string.IsNullOrEmpty(mobile) ? DBNull.Value : (object)mobile);
        DbHelper.DB.AddInParameter(dbCommand, "SceneName", DbType.String, string.IsNullOrEmpty(sceneName) ? DBNull.Value : (object)sceneName);
        DbHelper.DB.AddInParameter(dbCommand, "TicketName", DbType.String, string.IsNullOrEmpty(ticketName) ? DBNull.Value : (object)ticketName);
        DbHelper.DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, this.AspNetPager1.PageSize);
        DbHelper.DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, this.AspNetPager1.CurrentPageIndex);
        DbHelper.DB.AddInParameter(dbCommand, "@IsRowCount", DbType.Boolean, isRowCount);
        DbHelper.DB.AddOutParameter(dbCommand, "RowCount", DbType.Int32, 4);

        DataTable dt = DbHelper.DB.ExecuteDataSet(dbCommand).Tables[0];
        if (isRowCount)
        {
            this.AspNetPager1.RecordCount = (int)DbHelper.DB.GetParameterValue(dbCommand, "RowCount");
        }
        this.gvOrderScene.DataSource = dt;
        this.gvOrderScene.DataBind();
    }

-------------------------------------------

 private void InitControl()
    {
        string userId = this.tbUserId.Text.Trim();
        string operatorText = this.tbOperator.Text;
        string beginDate = this.dpBeginDate.Text;
        string endDate = this.dpEndDate.Text;
        QueryHelper queryHelper = new QueryHelper();
        StringBuilder whereString = new StringBuilder("WHERE 1=1");
        if (!string.IsNullOrEmpty(userId))
        {
            whereString.AppendFormat(" AND (O.UserId LIKE '{0}')", string.Format("%{0}%", DbHelper.FilterSqlKeyword(DbHelper.FilterBadChar(userId))));
        }
        if (!string.IsNullOrEmpty(operatorText))
        {
            whereString.AppendFormat(" AND (O.OperatorLoginId LIKE '{0}' OR O.OperatorName LIKE '{0}')", string.Format("%{0}%", DbHelper.FilterSqlKeyword(DbHelper.FilterBadChar(operatorText))));
        }
        if (!string.IsNullOrEmpty(beginDate))
        {
            whereString.AppendFormat(" AND L.CreateTime >= '{0}'", DateTime.Parse(beginDate));
        }
        if (!string.IsNullOrEmpty(endDate))
        {
            whereString.AppendFormat(" AND L.CreateTime < '{0}'", DateTime.Parse(endDate).AddDays(1));
        }

        string tableName = "UserLog L LEFT JOIN UserOperator O ON (L.OperatorId=O.OperatorId)";
        string columnName = "L.*,O.UserId,O.OperatorLoginId,O.OperatorName";
        string sortString = "ORDER BY L.LogId DESC";
        DbCommand dbCommand = DbHelper.DB.GetStoredProcCommand("PPagedSql");
        DbHelper.DB.AddInParameter(dbCommand, "TableName", DbType.String, tableName);
        DbHelper.DB.AddInParameter(dbCommand, "ColumnName", DbType.String, columnName);
        DbHelper.DB.AddInParameter(dbCommand, "WhereString", DbType.String, whereString.ToString());
        DbHelper.DB.AddInParameter(dbCommand, "SortString", DbType.String, sortString);
        DbHelper.DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, this.AspNetPager1.PageSize);
        DbHelper.DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, this.AspNetPager1.CurrentPageIndex);
        DbHelper.DB.AddOutParameter(dbCommand, "RowCount", DbType.Int32, 4);

        DataTable dt = DbHelper.DB.ExecuteDataSet(dbCommand).Tables[0];
        this.AspNetPager1.RecordCount = (int)DbHelper.DB.GetParameterValue(dbCommand, "RowCount");
        this.gvUserLog.DataSource = dt;
        this.gvUserLog.DataBind();
    }

 

 

参考：

http://www.cnblogs.com/wangjun8868/archive/2009/07/10/1520336.html
http://www.cnblogs.com/hinslam/archive/2011/09/01/2161827.html