sql server分页

SQL server代码
-- =============================================
-- Author:        <...>
-- Create date: <2017/7/28>
-- Description:    <分页>
-- =============================================
ALTER PROCEDURE [dbo].[Pg_PaginOrderBy] 
    @tblName varchar(255),   -- 表名 
    @fldName varchar(500)='*',   -- 显示字段名 
    @OrderfldName varchar(255),  -- 排序字段名 
    @PageSize int = 10,   -- 页尺寸 
    @PageIndex int = 1,   -- 页码 
    @IsReCount bit = 0,   -- 返回记录总数, 非 0 值则返回 
    @OrderType bit = 0,   -- 设置排序类型, 非 0 值则降序 
    @strWhere varchar(1000) = ''  -- 查询条件 (注意: 不要加 where) 
AS
BEGIN
    declare @strSQL varchar(6000)  -- 主语句 
declare @strSQL1 varchar(2000)  -- 主语句 
declare @strTmp varchar(100)   -- 临时变量 
declare @strOrder varchar(400)  -- 排序类型 
set @strTmp = ' where  rownum >'+str((@PageIndex-1)*@PageSize) 
if @OrderType != 0 
begin 
  set @strOrder = ' order by ' + @OrderfldName +' desc' 
end 
else 
begin 
  set @strOrder = ' order by ' + @OrderfldName +' asc' 
end 

set @strSQL =' select top ' + str(@PageIndex*@PageSize) 
+ '  *, ROW_NUMBER() OVER('+@strOrder+') AS rownum from ' + @tblName + '' ; 
if @strWhere != '' 
    set @strSQL = @strSQL+ ' where '+ @strWhere ; 
if @PageIndex = 1 
begin   
  set @strSQL ='  select top ' + str(@PageSize) + ' ' + @fldName 
+ ' from  '+@tblName  +' ' 
    if @strWhere != '' 
        set @strSQL = @strSQL + ' where '+@strWhere +' '+@strOrder 
    else 
        set @strSQL = @strSQL +' '+@strOrder 
end 
else 
    begin 
        set @strSQL =' select '  + @fldName + ' from ( '+@strSQL+' ) as tmp1 '+@strTmp; 
    end 
if @IsReCount != 0 
begin 
    set @strSQL = @strSQL+' select count(1) as Total from  ['+@tblName  +'] ' 
    if @strWhere != '' 
        set @strSQL = @strSQL+' where '+@strWhere 
end 
exec( @strSQL) 

END

 

C#调用代码：

bool _orderType = false;    //排序
            int pageNumber = int.Parse(Request.Form["page"].ToString());
            int pageSize = int.Parse(Request.Form["limit"].ToString());

DataSet ds = new DataSet();
            ds = Leyp.SQLServerDAL.Factory.getProductsStockDAL().Pg_pageingOrderBy("V_BuyReceipt", "* ", "ReceiptOrderDate", pageSize, pageNumber, _orderType, _strwhere);
            DataTable dt = ds.Tables[0];

 

 public DataSet Pg_pageingOrderBy(string tblName, string fldName, string OrderfldName, int PageSize, int PageIndex, bool OrderType, string strWhere)
        {
            //Sor.ISessionImp.SqlSession sis = new Sor.ISessionImp.SqlSession();
            DataSet set = new DataSet();
            SqlParameter[] paras = 
            {
                new SqlParameter("@tblName",SqlDbType.NVarChar),
                new SqlParameter("@fldName",SqlDbType.NVarChar),
                new SqlParameter("@OrderfldName",SqlDbType.NVarChar),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@PageIndex",SqlDbType.Int),
                new SqlParameter("@IsReCount",SqlDbType.Bit),
                new SqlParameter("@OrderType",SqlDbType.Bit),
                new SqlParameter("@strWhere",SqlDbType.NVarChar)
            };
            paras[0].Value = tblName;
            paras[1].Value = fldName;
            paras[2].Value = OrderfldName;
            paras[3].Value = PageSize;
            paras[4].Value = PageIndex;
            paras[5].Value = false;
            paras[6].Value = OrderType;
            paras[7].Value = strWhere;

            return SQLHelper.RunProcedure("Pg_PaginOrderBy", paras, "dd");
        }

 

 

java调用步骤：

// 通过@Resource注解引入JdbcTemplate对象
@Resource
private JdbcTemplate jdbcTemplate;

boolean _orderType = false;    //排序
int pageNumber = Integer.parseInt(request.getParameter("page"));//页数
int pageSize = Integer.parseInt(request.getParameter("limit"));//数据条数

List list = cm.pageload(jdbcTemplate, "V_AppendStock", "* ", "CreateDate", pageNumber, pageSize, 0, _strwhere);

/*string tblName, string fldName, string OrderfldName, int PageSize, int PageIndex, bool OrderType, string strWhere*/
public List pageload(JdbcTemplate jdbcTemplate, String tblName, String fldName, String OrderfldName, int pagenum, int pagesize, int OrderType, String strWhere) {
    int iPageIndex = pagenum;//(pagenum - 1) * pagesize;
    int iPageSize = pagesize;
    int IsReCount = 1;
    OrderType = 0;
    /*String strWhere = " 1 =1 ";
    List<t_Supplier> suppliers = new ArrayList<>();*/
    String[] param = new String[8];
    param[0] = tblName;
    param[1] = fldName;
    param[2] = OrderfldName;
    param[3] = Integer.toString(iPageSize);
    param[4] = Integer.toString(iPageIndex);
    param[5] = Integer.toString(IsReCount);
    param[6] = Integer.toString(OrderType);
    param[7] = strWhere;

    List resultList = execteProcList(jdbcTemplate, "", "Pg_PaginOrderBy", param);


    //List<Users> userss = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper(Users.class));
    return resultList;
}

//执行分页存储过程
public List execteProcList(JdbcTemplate jdbcT, final String dbKey, final String procedure, final String[] param) {
    //JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
    List resultList = (List) jdbcT.execute(
            new CallableStatementCreator() {

                @Override
                public CallableStatement createCallableStatement(Connection con)
                        throws SQLException {
                    String strProc = "{ call " + procedure + " (";
                    for (int i = 0; i < param.length; i++) {
                        if (i != param.length - 1) {
                            strProc += "?,";
                        } else {
                            strProc += "?";
                        }
                    }
                    strProc += ")}";
                    CallableStatement cs = con.prepareCall(strProc);
                    for (int i = 1; i <= param.length; i++) {
                        cs.setString(i, param[i - 1]);
                    }
                    return cs;
                }
            }
            , new CallableStatementCallback() {

                @Override
                public Object doInCallableStatement(CallableStatement cs)
                        throws SQLException, DataAccessException {
                    ResultSet rs = cs.executeQuery();
                    ResultSetMetaData rmd = rs.getMetaData();
                    int columnCount = rmd.getColumnCount();
                    List<Map<String, Object>> resultsMap = new ArrayList<Map<String, Object>>();
                    while (rs.next()) {
                        Map<String, Object> mso = new HashMap<String, Object>(columnCount);
                        for (int i = 1; i <= columnCount; i++) {
                            mso.put(rmd.getColumnName(i), rs.getObject(i));
                            //mso.put(s, rs.getString(s));
                        }
                        resultsMap.add(mso);

                    }
                    rs.close();
                    return resultsMap;
                }

            });

    return resultList;


}

 

//执行增删改查存储过程
public int execte(JdbcTemplate jdbcT, final String dbKey, final String procedure, final String[] param) {
    //JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
    String result = "";
    int resultList = (int) jdbcT.execute(
            new CallableStatementCreator() {

                @Override
                public CallableStatement createCallableStatement(Connection con)
                        throws SQLException {
                    String strProc = "{ call " + procedure + " (";
                    for (int i = 0; i < param.length; i++) {
                        if (i != param.length - 1) {
                            strProc += "?,";
                        } else {
                            strProc += "?";
                        }
                    }
                    strProc += ")}";
                    CallableStatement cs = con.prepareCall(strProc);
                    for (int i = 1; i <= param.length; i++) {
                        cs.setString(i, param[i - 1]);
                    }
                    return cs;
                }
            }
            , new CallableStatementCallback() {

                @Override
                public Object doInCallableStatement(CallableStatement cs)
                        throws SQLException, DataAccessException {
                    Boolean bl = cs.execute();
                    int tmp = cs.getUpdateCount();
                    return tmp;
                }

            });

    return resultList;


}

 

String[] param = new String[1];
param[0] = AppendID;
List list = cm.execteProcList(jdbcTemplate, "", "p_AppendStockDetail_getListByAppendID", param);