利用存储过程进行真分页获取数据

ajy135

于 2019-04-16 11:48:54 发布

阅读量129

点赞数

本文链接：https://blog.csdn.net/ajy135/article/details/89329889

版权

HTML工作

1. 引用CSS文件

2. 引用JS文件

3. 再table下面加一个div，定义样式pagination2

4. JS处理代码

var pagesize = 10;
var allCount = 0;
var Currentpage = 0;
var xxtemppage;
function pageselectCallback(page_index, jq) {
InventoryCheck(page_index);
xxtemppage = page_index;
return false;
}
//设置分页
function SetPage(rowcount) {
if (Currentpage == 0) {
var initPagination = function () {
var num_entries = rowcount / pagesize;
// 创建分页
$("#PaginationFormula").pagination(rowcount, {
num_edge_entries: 2, //边缘页数
num_display_entries: 4, //主体页数
prev_text: "上一页",
next_text: "下一页",
callback: pageselectCallback,
items_per_page: pagesize //每页显示1项

});
}();
Currentpage = 100;
}
}

},
error: function (e) {
alert("获取零件信息失败，请检查。"); return;
}
});
}

5. 后端接收代码及处理代码

public object GetFormulaList()
{
int currentpage = CheckResponse.GetIntResponseArg("cp");
int pagesize = CheckResponse.GetIntResponseArg("ps");
string Name = CheckResponse.GetResponse("name");
bFormula bll = new bFormula();
formula_Page data = new formula_Page();
int allcount = 0;
List<mV_Formula> list = new List<mV_Formula>();
string strwhere = " formula like '%" + Name + "%' and isDelete =0 ";
list = bll.GetList(pagesize, currentpage, out allcount, strwhere);
data.CurrentData = list;// lstUser; ;
data.AllCount = allcount;
return data;
}

public partial class formula_Page
{
public formula_Page()
{
CurrentData = new List<mV_Formula>();
}
/// <summary>
/// 分页后的当前页数据
/// </summary>
public System.Collections.Generic.List<mV_Formula> CurrentData { get; set; }
/// <summary>
/// 总个数
/// </summary>
public int AllCount { get; set; }
}

public DataSet GetList(int PageSize, int PageIndex, out int RecordSum, string strWhere)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("UP_GetRecordByPage");
db.AddInParameter(dbCommand, "tblName", DbType.AnsiString, "V_formula_line");
db.AddInParameter(dbCommand, "fldName", DbType.AnsiString, "id");
db.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
db.AddOutParameter(dbCommand, "RecordSum", DbType.Int32, 0);
db.AddInParameter(dbCommand, "OrderType", DbType.Boolean, 0);
db.AddInParameter(dbCommand, "strWhere", DbType.AnsiString, strWhere);
DataSet ds = db.ExecuteDataSet(dbCommand);

RecordSum = (int)db.GetParameterValue(dbCommand, "@RecordSum");

return db.ExecuteDataSet(dbCommand);
}

6. 数据库分页获取代码（存储过程）

/*
修改描述：
添加了全部显示记录的功能
判别条件@PageIndex=0
*/
ALTER PROCEDURE [dbo].[UP_GetRecordByPage]
   @tblName varchar(50),--表名
   @fldName varchar(20)= null,--主键名
   @PageSize int = 10,--每页的大小
   @PageIndex int = 1,--第几页
   @RecordSum int output, --记录数
   @OrderType tinyint = 1, --排序类型 1== ASC（升序）和 0==DESC（降序）：
   @strWhere varchar(4000) = null --过滤条件
AS
   set nocount on
   declare @sSQL nvarchar(4000)
   set @PageIndex=@PageIndex+1
   set @RecordSum = 0
   if @PageIndex<0 set @PageIndex=0

   if (@strWhere is NULL or rtrim(@strWhere)='')
       set @sSQL = 'SELECT @RecordSum = count(*) FROM [' + @tblName + '] '
   else
       set @sSQL = 'SELECT @RecordSum = count(*) FROM [' + @tblName + '] WHERE ' + @strWhere
   --select @tblName
   --select @sSQL
   --return

   execute sp_executesql
   @sSQL,
   N'@RecordSum int output ',
   @RecordSum output

   declare @sSort varchar(100)
   declare @sSignal varchar(10)
   declare @sMaxmin varchar(10)

   if @OrderType = 1
       begin
           set @sSort = ' order by ' +@fldName + ' ASC'
           set @sSignal = '>'
           set @sMaxmin = 'MAX'
       end
   else
       begin
           set @sSort = ' order by ' +@fldName + ' DESC'
           set @sSignal = '<'
           set @sMaxmin = 'MIN'
       end

   --select @sSort
   --return

   if (@PageIndex = 0)
       begin
       if (@strWhere is NULL or rtrim(@strWhere)='')
       set @sSQL =
           'SELECT * FROM [' + @tblName +
           + '] ' + @sSort
       else
       set @sSQL =
               'SELECT * FROM [' + @tblName +
               + '] WHERE ' + @strWhere + @sSort
       end
   else if (@PageIndex = 1)
       begin
       if (@strWhere is NULL or rtrim(@strWhere)='')
       set @sSQL =
           'SELECT TOP '+cast(@PageSize as varchar) + ' * FROM [' + @tblName +
           + '] ' + @sSort
       else
       set @sSQL =
               'SELECT TOP '+cast(@PageSize as varchar) + ' * FROM [' + @tblName +
               + '] WHERE ' + @strWhere + @sSort
   end
   else
   begin

       if (@strWhere is NULL or rtrim(@strWhere)='')
       set @sSQL =
           'SELECT TOP '+cast(@PageSize as varchar) + ' * FROM [' + @tblName +
           + '] WHERE ' +@fldName +@sSignal +' (SELECT '+ @sMaxmin +'(' +@fldName + ')
           FROM (SELECT TOP ' + cast(@PageSize * (@PageIndex-1) as varchar) + ' ' + @fldName +' FROM [' + @tblName +
           + '] '+ @sSort+') as T)' + @sSort
       else
       set @sSQL =
               'SELECT TOP '+cast(@PageSize as varchar) + ' * FROM [' + @tblName +
               + '] WHERE ' + @strWhere +' and ' + @fldName + @sSignal + ' (SELECT '+@sMaxmin+'('+@fldName+')
               FROM (SELECT TOP ' + cast(@PageSize * (@PageIndex -1) as varchar) + ' ' + @fldName +' FROM [' + @tblName +
               + '] where ' +@strWhere + @sSort+' ) as T )' + @sSort

   end
   print @sSQL
   execute(@sSQL)