公司购物网分页存储过程（留着以备后用）

最新推荐文章于 2024-08-12 09:25:35 发布

转载最新推荐文章于 2024-08-12 09:25:35 发布 · 65 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/shyhoo/archive/2012/09/21/2696674.html

本文介绍了一个用于SQL Server的自定义分页存储过程 AspNetPagerPROC 的实现细节，并提供了一个示例方法 GetOrdersListPager3 来展示如何使用此存储过程进行分页查询。

USE [Ezagoo]
GO
/****** 对象: StoredProcedure [dbo].[AspNetPagerPROC] 脚本日期: 09/21/2012 11:52:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[AspNetPagerPROC]
(
@PAGESIZE int,            --每页行数
@CURRPAGE int,            --当前页数
@TABLENAME nvarchar(4000), --表名
@KEYID nvarchar(70),      --主键
@WHERE nvarchar(4000),     --条件
@SORT nvarchar(80),       --排序
@COLS nvarchar(4000),      --列名
@COUNT nvarchar(50),     --针对获得总条数时需要的特殊条件，一般为*
@ROWS int output          --总行数
)
as
begin
set nocount on
declare
@befROWS int,
@sql nvarchar(4000)
set @befROWS=@PAGESIZE*(@CURRPAGE-1)
set @sql='SELECT TOP '+CAST(@PAGESIZE as nvarchar(50))+' '+@COLS+' FROM '+@TABLENAME+' WHERE '+@WHERE+' AND '+@KEYID+' NOT IN (SELECT TOP '+CAST(@befROWS as nvarchar(50))+' '+@KEYID+' FROM '+@TABLENAME+' WHERE '+@WHERE+' '+@SORT+' ) '+@SORT+''
EXEC (@sql)
print @sql
-------总条数------
set @sql=N'SELECT @ROWS=COUNT('+@COUNT+') FROM '+@TABLENAME+' WHERE '+@WHERE+' '+''
exec sp_executesql @sql,N'@ROWS int out',@ROWS out
select @ROWS
return @ROWS
end

--配套使用方法

        /// <summary>
        /// 获取订单列表
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="CurrPage"></param>
        /// <param name="ListCount"></param>
        /// <returns></returns>
        public static DataTable GetOrdersListPager3(string strWhere, string orderBy, int pageSize, int currPage, ref int listCount)
        {
            if (strWhere == "")
            {
                strWhere = "1=1";
            }

            if (orderBy == "")
            {
                orderBy = " ORDER BY OrderID ";
            }
            DataTable dt = null;
            SqlParameter[] parameters = new SqlParameter[9];
            parameters[0] = new SqlParameter("@PAGESIZE", SqlDbType.Int);
            parameters[0].Value = pageSize;

parameters[1] = new SqlParameter("@CURRPAGE", SqlDbType.Int);
parameters[1].Value = currPage;

parameters[2] = new SqlParameter("@TABLENAME", SqlDbType.NVarChar, 50);
parameters[2].Value = "V_ChargeBack";

parameters[3] = new SqlParameter("@KEYID", SqlDbType.NVarChar, 50);
parameters[3].Value = "OrderID";

parameters[4] = new SqlParameter("@WHERE", SqlDbType.NVarChar, 4000);
parameters[4].Value = strWhere;

parameters[5] = new SqlParameter("@SORT", SqlDbType.NVarChar, 50);
parameters[5].Value = orderBy;

parameters[6] = new SqlParameter("@COLS", SqlDbType.NVarChar, 200);
parameters[6].Value = "*";

parameters[7] = new SqlParameter("@COUNT", SqlDbType.NVarChar, 50);
parameters[7].Value = "*";

parameters[8] = new SqlParameter("@ROWS", SqlDbType.Int);
parameters[8].Direction = ParameterDirection.Output;

            try
            {
                dt = EzagooDB.GetDataTablePager(out listCount, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

转载于:https://www.cnblogs.com/shyhoo/archive/2012/09/21/2696674.html