Encapsulates the pagination method for a stored procedure with EF code First

1.5 have not done MVC project, still miss (because the project is still the original ASPX), the individual or like MVC, recently began to regain the MVC, feel both familiar and unfamiliar.

Record the encapsulated pagination code

First, I'm going to use EF codefirst. Because there can be more pure code that no longer has the edmx of these things instead of actually using code first to generate the database. So I use

is Codefirst but the essence is still the database first. We can find information on the Internet.

Write a good method in the base class

 Public classbaseservice{ Public StaticXocontext db =NULL;  PublicBaseservice () {db=NewXocontext (); }           Public StaticList<t> selectpagelist<t> (stringSQLSTR,intPageIndex,intPageSizestringOrderbyfield,ref intTotalCount)whereT:class{sqlparameter[] SPM=Newsqlparameter[5]; spm[0] =NewSqlParameter ("@Sql", SQLSTR); spm[1] =NewSqlParameter ("@PageIndex", PageIndex); spm[2] =NewSqlParameter ("@PageSize", pagesize); spm[3] =NewSqlParameter ("@OrderByField", Orderbyfield); spm[4] =NewSqlParameter ("@TotalRecord", TotalCount); spm[4]. Direction =ParameterDirection.Output; vardata = db. Database.sqlquery<t> ("exec pro_pageprocedure @Sql, @PageIndex, @PageSize, @OrderByField, @TotalRecord out", SPM).           ToList (); TotalCount= Convert.ToInt32 (spm[4].           Value.tostring ()); returndata; }}

Because the direct use of LINQ paging way to feel efficiency is not very high, so instead of using a stored procedure, the above method in a generic way, can be flexibly called. Stored procedures Online A lot, you can also write one, the following is the stored procedures I use

CREATE PROCEDURE [dbo]. [Pro_pageprocedure] @Sql nvarchar (max),--Table name @PageIndexint=1,            --Specifies the current page @PageSizeint,                    --how many records per page @OrderByField nvarchar ( +),        --Row_number the sort fields you need @TotalRecordintOutput--return Total pages asDeclare @_sql nvarchar (max); --calculate the total number of recordsSet@_sql ='Select @TotalRecord = count (*) from ('+ @Sql +') A'EXEC sp_executesql @_sql,n'@TotalRecord int OUTPUT', @TotalRecord output--calculate total number of records Declare @StartRecordintDeclare @EndRecordint        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 asnvarchar) +' and'+ CAST (@EndRecord asnvarchar) Exec (@_sql)

The above method is written in the base class, each subclass inherits the base class can call this method directly to the paging query, the following is one of the subclasses call

/// <summary>        ///get a list of pages/// </summary>        /// <returns></returns>         PublicList<fixedanswer> Getpagelist (intPageIndex,intPageSize,ref inttotalcount) {                        stringstrSQL ="SELECT * from Fixedanswer"; stringOrderfied ="ORDER BY id DESC"; returnSelectpagelist<fixedanswer> (strSQL, PageIndex, PageSize, Orderfied,reftotalcount); }

Note that the Entity object class Fixedanswer is the corresponding table fixedanswer, if we may not need to query all the fields of this table, it is necessary to have some field extraction appear to create a corresponding entity class, because sqlquery<t> The object returned by the method requires that the object property be one by one-corresponding. The query can also be used in this way

It seems a bit troublesome to create a corresponding entity object each time, but sometimes it is good to use it, and there is a direct return to the DataTable on the Internet.

Limited ability to express blog, only used to record, do not like to spray.

From: http://www.cnblogs.com/gangtienanzi/p/5377492.html

Encapsulates the pagination method for a stored procedure with EF code First