1、定义一个用来装载适合所有类的分页结果类
PageDataView的Items一个泛型属性，所以可以适合所有的类，简洁而通用
public class PageDataView<T> { private int _TotalNum; public PageDataView() { this._Items = new List<T>(); } public int TotalNum { get { return _TotalNum; } set { _TotalNum = value; } } private IList<T> _Items; public IList<T> Items { get { return _Items; } set { _Items = value; } } public int CurrentPage { get; set; } public int TotalPageCount { get; set; } }
2、定义一个通用的获取分页数据的类
ProcGetPageData就是通用的分页存储过程
Create PROCEDURE [dbo].[ProcGetPageData] ( @TableName VARCHAR(1000), --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID @PrimaryKey NVARCHAR(100), --主键，可以带表头 a.AID @Fields NVARCHAR(2000) = '*',--读取字段 @Condition NVARCHAR(3000) = '',--Where条件 @CurrentPage INT = 1, --开始页码 @PageSize INT = 10, --页大小 @Sort NVARCHAR(200) = '', --排序字段 @RecordCount INT = 0 OUT ) AS DECLARE @strWhere VARCHAR(2000) DECLARE @strsql NVARCHAR(3900) IF @Condition IS NOT NULL AND len(ltrim(rtrim(@Condition)))>0 BEGIN SET @strWhere = ' WHERE ' + @Condition + ' ' END ELSE BEGIN SET @strWhere = '' END IF (charindex(ltrim(rtrim(@PrimaryKey)),@Sort)=0) BEGIN IF(@Sort='') SET @Sort = @PrimaryKey + ' DESC ' ELSE SET @Sort = @Sort+ ' , '+@PrimaryKey + ' DESC ' END SET @strsql = 'SELECT @RecordCount = Count(1) FROM ' + @TableName + @strWhere EXECUTE sp_executesql @strsql ,N'@RecordCount INT output',@RecordCount OUTPUT IF @CurrentPage = 1 --第一页提高性能 BEGIN SET @strsql = 'SELECT TOP ' + str(@PageSize) +' '+@Fields + ' FROM ' + @TableName + ' ' + @strWhere + ' ORDER BY '+ @Sort END ELSE BEGIN /* Execute dynamic query */ DECLARE @START_ID NVARCHAR(50) DECLARE @END_ID NVARCHAR(50) SET @START_ID = CONVERT(NVARCHAR(50),(@CurrentPage - 1) * @PageSize + 1) SET @END_ID = CONVERT(NVARCHAR(50),@CurrentPage * @PageSize) SET @strsql = ' SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum, '+@Fields+ ' FROM '+@TableName + @strWhere +') AS XX WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY XX.rownum ASC' END EXEC(@strsql) RETURN
3、获取分页数据
调用分页存储过程的公共类：
public class Common { public static string DBConnString = System.Configuration.ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString; public static System.Data.IDbConnection GetConn() { return new System.Data.SqlClient.SqlConnection(Common.DBConnString); } public static PageDataView<T> GetPageData<T>(PageCriteria criteria,object param=null) { using (var conn = Common.GetConn()) { var p = new DynamicParameters(); string proName = "ProcGetPageData"; p.Add("TableName", criteria.TableName); p.Add("PrimaryKey", criteria.PrimaryKey); p.Add("Fields", criteria.Fields); p.Add("Condition", criteria.Condition); p.Add("CurrentPage", criteria.CurrentPage); p.Add("PageSize", criteria.PageSize); p.Add("Sort", criteria.Sort); p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output); conn.Open(); var pageData = new PageDataView<T>(); pageData.Items = conn.Query<T>(proName, p, commandType: CommandType.StoredProcedure).ToList(); conn.Close(); pageData.TotalNum = p.Get<int>("RecordCount"); pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize)); pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage; return pageData; } } }
下面是dapper通用万能的泛型分页公共类的使用方法
public static PageDataView<MSys_Admin> GetList(string name, string loginName, int page,int pageSize=10) { PageCriteria criteria = new PageCriteria(); criteria.Condition = "1=1"; if (!string.IsNullOrEmpty(name)) criteria.Condition += string.Format(" and Name like '%{0}%'", name); if (!string.IsNullOrEmpty(loginName)) criteria.Condition += string.Format(" and LoginName like '%{0}%'", loginName); criteria.CurrentPage = page; criteria.Fields = "*"; criteria.PageSize = pageSize; criteria.TableName = "Sys_Admin a"; criteria.PrimaryKey = "UID"; var r = Common.GetPageData<MSys_Admin>(criteria); return r; }
PageCriteria是一个封装查询条件相关信息的类。
public class PageCriteria { private string _TableName; public string TableName { get { return _TableName; } set { _TableName = value; } } private string _Fileds = "*"; public string Fields { get { return _Fileds; } set { _Fileds = value; } } private string _PrimaryKey = "ID"; public string PrimaryKey { get { return _PrimaryKey; } set { _PrimaryKey = value; } } private int _PageSize = 10; public int PageSize { get { return _PageSize; } set { _PageSize = value; } } private int _CurrentPage = 1; public int CurrentPage { get { return _CurrentPage; } set { _CurrentPage = value; } } private string _Sort = string.Empty; public string Sort { get { return _Sort; } set { _Sort = value; } } private string _Condition = string.Empty; public string Condition { get { return _Condition; } set { _Condition = value; } } private int _RecordCount; public int RecordCount { get { return _RecordCount; } set { _RecordCount = value; } } }
4、视图绑定
视图文件的头部加上：
@model PageDataView<MSys_Admin>
<table cellspacing="1" cellpadding="3" class="tablehead" style="background:#CCC;"> <thead> <tr> <th align="center">名称</th> <th align="center">登录名</th> <th align="center">添加时间</th> <th align="center">操作</th> </tr> </thead> <tbody> @if (Model != null) { foreach (var item in Model.Items) { <tr> <td align="center">@item.Name</td> <td align="center">@item.LoginName</td> <td align="center">@item.CreateTime.ToString("yyyy-MM-dd HH:mm:ss")</td> <td align="center"> <form method="post" action="@Url.Action("Delete")"> <input type="hidden" name="UID" value="@item.UID" /> <input type="submit" onclick="return confirm('确定要删除吗？')" value="删除" class="btn" /> <a href="@Url.Action("Edit", new {ID=item.UID })" class="add">修改</a> </form> </td> </tr> } } </tbody> </table> <!--tab end--> <div class="pagination"> @Html.PageLinks(Model, p => string.Format("/Admin?name={0}&loginName={1}&page={2}", ViewBag.Name,@ViewBag.LoginName, p), 10) </div>