怎么利用存储过程分页

1.数据库中的sql存储过程

``
create proc [dbo].[PageList]
(
@PageIndex int,
@PageSize int,
@TableName varchar(200),
@CoumnName varchar(500),
@Where varchar(500),
@OrderBy varchar(500),
@Count int out
)
as
declare @sql nvarchar(2000)

set @sql=’ select ‘+@CoumnName+’ from (select '+@CoumnName+
’ ,row_number() over(order by ‘+@OrderBy+’ ) as number from '+@TableName+ ’ where '+@Where+
’ ) t where t.number between '+ Cast(((@PageIndex-1)@PageSize+1) as varchar(200)) + ’ and '+
Cast((@PageIndex@PageSize)as varchar(200));
print @sql;
exec(@sql);

set @sql='select count(*) from ‘+@TableName+’ where '+@Where;
print @sql;

**2.在数据访问层根据存储过程建立对应的类**

public class BaseRepository
{
public List GetPageList(int pageIndex,
int pageSize,
string tableName,
string columnName,
string where,
string orderBy,
out int count)
{
string sql = @“EXECUTE PageList @Page,@Limit,@TableName,@CoumnName,@Where,@OrderBy,@Count”;
SqlParameter[] parameters = {
new SqlParameter() {
DbType = System.Data.DbType.Int32,
ParameterName = “@Page”,
Value = pageIndex
},
new SqlParameter() {
DbType = System.Data.DbType.Int32,
ParameterName = “@Limit”,
Value = pageSize
},
new SqlParameter() {
DbType = System.Data.DbType.String,
ParameterName = “@TableName”,
Value = tableName
},
new SqlParameter() {
DbType = System.Data.DbType.String,
ParameterName = “@CoumnName”,
Value = columnName
},
new SqlParameter() {
DbType = System.Data.DbType.String,
ParameterName = “@Where”,
Value = where
},
new SqlParameter() {
DbType = System.Data.DbType.String,
ParameterName = “@OrderBy”,
Value = orderBy
},
new SqlParameter() {
DbType = System.Data.DbType.Int32,
ParameterName = “@Count”,
Value = 0
}
};
DataSet dataSet = DBHelper.GetDataSet(sql, parameters);
count = Convert.ToInt32(dataSet.Tables[1].Rows[0][0]);
return DataTableHelper.DataTableTolist(dataSet.Tables[0]); //反射
}
}

**3.数据访问层**

public List SelectAdminInfoChuCunGuoChen(int page, int limit,out int count,string loginName,string riQi,int RoleId)
{
string tableName = “AdminInfo”;
string columnName = “*”;
string where = “1=1”;
if (!string.IsNullOrEmpty(loginName))
{
where += " and LoginName = ‘"+ loginName+"’";
}
if (!string.IsNullOrEmpty(riQi))
{
where += " and CreateTime > ‘" + riQi+"’";
}
if (RoleId!=0) {
where += " and RoleId = ‘" + RoleId + "’ ";

        }
        string orderBy = nameof(AdminInfo.AdminId);

        return GetPageList<AdminInfo>(page, limit, tableName, columnName, where, orderBy, out count);
        
    }

**4.业务逻辑层**

public List SelectAdminInfoChuCunGuoChen(int page, int limit, out int count, string login, string riQi,int RoleId)
{

        return adminInfoRepository.SelectAdminInfoChuCunGuoChen(page, limit, out count, login, riQi, RoleId);
    }

**5.控制器**

public JsonResult SelectAdminInfoChuCunGuoChen(int page, int limit,string loginName1,string riQi,string roleId)
{
int count = 0;
int RoleId = 0;
if (roleId!=null) {
RoleId = Int32.Parse(roleId);
}
List list = adminInfoService.SelectAdminInfoChuCunGuoChen(page,limit,out count, loginName1, riQi, RoleId);
TableModle laydata = new TableModle();
laydata.code = 0; //数据状态，给0就好
laydata.msg = “”; //状态信息，给空就行
laydata.count = count; //总条数
laydata.data = list.Skip((page-1)* limit).Take(limit).ToList();
return Json(laydata, JsonRequestBehavior.AllowGet);
}

**6.需要的laydata类**

public class TableModle
{
///
/// Layui Json模板
///
public TableModle()
{
this.code = 0;
this.msg = string.Empty;
}
public int code { get; set; }
public string msg { get; set; }
public int count { get; set; }
public List data { get; set; }
}