EF調用存儲過程實現分頁

1、通用分頁代碼

public static List<T> SelectPageList<T>(string sqlstr, int pageIndex, int pagesize, string orderByField, ref int totalCount) where T : class

{

SqlParameter[] spm = new SqlParameter[5];

spm[0] = new SqlParameter("@Sql", sqlstr);

spm[1] = new SqlParameter("@PageIndex", pageIndex);

spm[2] = new SqlParameter("@PageSize", pagesize);

spm[3] = new SqlParameter("@OrderByField", orderByField);

spm[4] = new SqlParameter("@TotalRecord", totalCount);

spm[4].Direction = ParameterDirection.Output;

var data = db.Database.SqlQuery<T>("exec Pro_PageProcedure @Sql,@PageIndex,@PageSize,@OrderByField,@TotalRecord out", spm).ToList();

totalCount = Convert.ToInt32(spm[4].Value.ToString());

return data;

}

2、通用的SQL分頁存儲過程

CREATE PROCEDURE [dbo].[Pro_PageProcedure]

@Sql nvarchar(max), --表名

@PageIndex int = 1 , --指定當前為第幾頁

@PageSize int, --每頁多少條記錄

@OrderByField nvarchar(1000), --row_number需要的排序欄位

@TotalRecord int output --返回總頁數

as

Declare @_sql nvarchar(max);

--計算總記錄數

set @_sql = "select @TotalRecord = count(*) from (" + @Sql + ") a"

EXEC sp_executesql @_sql,N"@TotalRecord int OUTPUT",@TotalRecord OUTPUT--計算總記錄數

Declare @StartRecord int

Declare @EndRecord int

set @StartRecord = (@pageIndex-1)*@PageSize + 1

set @EndRecord = @StartRecord + @pageSize - 1

set @_sql ="select * from ( select ROW_NUMBER() Over(" + @OrderByField + ") as _ttRowId,* from (" + @Sql + ") _tt0 ) _tt1 "

set @_sql = @_sql + "where _ttRowId between " + CAST(@StartRecord as nvarchar) + " and " + CAST(@EndRecord as nvarchar)

Exec(@_sql)

3、調用示例

/// <summary>

/// 獲取分頁列表

/// </summary>

/// <returns></returns>

public List<FixedAnswer> GetPageList(int pageIndex, int pageSize, ref int totalCount)

{

string strSql = "select * from FixedAnswer";

string orderfied = "order by id desc";

return SelectPageList<FixedAnswer>(strSql, pageIndex, pageSize, orderfied, ref totalCount);

}

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！