如何分页存储过程的结果 [英] How do I page the results of a Stored Procedure
问题描述
我有一个存储过程可以返回几个连接表的结果。我想在服务器上分页结果。
我得到一个ISingleResult< type>结果。我有很多分页扩展方法,这是我到目前为止:
public static 列表< T> GetList< T>(此 ISingleResult< T>来源, int pageSize, int pageIndex, out int resultCount)
{
列表< T> result = null ;
int count = 0 ;
TransactionWrapper(()= >
{
IEnumerable< T> agg = source;
count = source。 Count();
if (pageSize > 0 )
{
agg = agg.Skip(pageSize * pageIndex);
agg = agg.Take(pageSize);
}
结果= agg.ToList();
});
resultCount = count;
返回结果;
}
我得到查询结果不能多次枚举。
有没有办法解决这个问题?
如果没有,那么我将不得不将我的SP更改为视图并丢失一些查询功能,这是宜居的。
谢谢^ _ ^
Andy
< blockquote>无法编写存储过程;没有办法选择从程序返回的特定范围的行。
似乎有四种选择:
- 使用
.ToList()
,正如Er Daljeet Singh在评论中所说:
result = source。 ToList();
count = result.Count;
if (pageSize > 0 )
{
result = result.Skip(pageSize * pageIndex).Take(pageSize).ToList();
}
- 使用两个单独的存储过程 - 一个用于计算总结果,另一个用于检索单页数据。
- 将存储过程转换为视图或 a表值函数 [ ^ ]。
- 使用自定义扩展方法提取单页数据并计算一次点击中的记录总数。这样,您不必将所有结果复制到
列表< T>
,然后将大部分结果丢弃。
这样的事情应该有效:
public static class 扩展名
{
public < span class =code-keyword> static List< T> GetPage< T>(此 IEnumerable< T>来源, int pageSize, int pageIndex, out int totalCount)
{
if (source == null ) throw new ArgumentNullException( source) ;
if (pageSize < 0 ) throw new ArgumentOutOfRangeException( pageSize);
if (pageIndex < 0 ) throw new ArgumentOutOfRangeException( pageIndex);
if (pageSize == 0 )
{
var result = source.ToList();
totalCount = result.Count;
返回结果;
}
int index = 0 ;
int startIndex = pageSize * pageIndex;
int endIndex = startIndex + pageSize;
var pageOfData = new 列表< T>(pageSize);
foreach (T item in source)
{
if (startIndex < = index&& index < endIndex)
{
pageOfData.Add(item);
}
index ++;
}
totalCount = index;
return pageOfData;
}
}
...
public static 列表< T> GetList< T>(此 ISingleResult< T>来源, int pageSize, int pageIndex, out int resultCount)
{
列表< T> result = null ;
int count = 0 ;
TransactionWrapper(()= >
{
result = source.GetPage(pageSize,pageIndex, out count);
});
resultCount = count;
返回结果;
}
Hi,
I have a stored procedure that return results from a few joined tables. I want to page the results at the server.
I get a ISingleResult<type> result. I have a number of extension methods for paging and here is what I have so far:
public static List<T> GetList<T>(this ISingleResult<T> source, int pageSize, int pageIndex, out int resultCount)
{
List<T> result = null;
int count = 0;
TransactionWrapper(() =>
{
IEnumerable<T> agg = source;
count = source.Count();
if (pageSize > 0)
{
agg = agg.Skip(pageSize * pageIndex);
agg = agg.Take(pageSize);
}
result = agg.ToList();
});
resultCount = count;
return result;
}
I get the "the query results cannot be enumerated more than once".
Is there a way around this?
If not then I will have to change my SP into a view and lose a little query functionality, which is livable.
Thanks ^_^
Andy
Stored procedures cannot be composed; there's no way to select a certain range of rows returned from the procedure.
There appear to be four options:
- Use
.ToList()
, as Er Daljeet Singh suggested in the comments:
result = source.ToList(); count = result.Count; if (pageSize > 0) { result = result.Skip(pageSize * pageIndex).Take(pageSize).ToList(); }
- Use two separate stored procedures - one to count the total results, and one to retrieve a single page of data.
- Convert your stored procedure to a view or a table valued function[^].
- Use a custom extension method to extract a single page of data and count the total number of records in one hit. That way, you don't have to copy all of the results to a
List<T>
and then throw most of them away.
Something like this should work:
public static class Extensions { public static List<T> GetPage<T>(this IEnumerable<T> source, int pageSize, int pageIndex, out int totalCount) { if (source == null) throw new ArgumentNullException("source"); if (pageSize < 0) throw new ArgumentOutOfRangeException("pageSize"); if (pageIndex < 0) throw new ArgumentOutOfRangeException("pageIndex"); if (pageSize == 0) { var result = source.ToList(); totalCount = result.Count; return result; } int index = 0; int startIndex = pageSize * pageIndex; int endIndex = startIndex + pageSize; var pageOfData = new List<T>(pageSize); foreach (T item in source) { if (startIndex <= index && index < endIndex) { pageOfData.Add(item); } index++; } totalCount = index; return pageOfData; } } ... public static List<T> GetList<T>(this ISingleResult<T> source, int pageSize, int pageIndex, out int resultCount) { List<T> result = null; int count = 0; TransactionWrapper(() => { result = source.GetPage(pageSize, pageIndex, out count); }); resultCount = count; return result; }
这篇关于如何分页存储过程的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!