2 split-General stored procedure paging (not in version)

Guide:
Dbo.proc_listpage
(@tblName nvarchar( A----The connection @fldname to the table or tables that you want to display nvarchar( -= ' * ',----the list of fields to display @pagesize int= 1,----The number of records displayed per page @page int= Ten,----to display the record for that page @pagecount int= 1Output,----The total number of pages after the query results @counts int= 1Output,----the number of records queried @fldsort nvarchar( A= NULL,----sort field list or condition @sort bit= 0,----Sort method, 0 is ascending, 1 is descending (if a multiple-field arrangement sort refers to the order of the last sorted field (the last sort field is not ordered)--Program reference: ' Sorta ASC,SORTB desc,sortc ' Strcondition nvarchar( 1000= NULL,----query condition, no where@id required nvarchar( the),----primary key for the primary table @dist bit= 0----whether to add the query field DISTINCT default 0 does not add/1 Add) ASSET NOCOUNT ondeclare @sqlTmp nvarchar( 1000----to store dynamically generated SQL statement declare @strTmp nvarchar( 1000----The query statement that declare the total number of query results @strID nvarchar( 1000----The query statement declare that gets the beginning or end ID of the query @strSortType nvarchar( Ten)----Data Collation Adeclare @strFSortType nvarchar( Ten)----Data Collation Bdeclare @SqlSelect nvarchar( - M)----SQL constructs declare for queries that contain distinct @SqlCounts nvarchar( - M----The total number of queries containing distinct SQL constructs if @Dist = 0Begin Set @SqlSelect = ' SELECT ' Set @SqlCounts = ' Count (*) ' endelsebegin Set @SqlSelect = ' SELECT distinct ' Set @SqlCount s = ' Count (DISTINCT ' + @ID + ') ' endif @Sort = 0Begin Set @strFSortType = ' ASC ' Set @strSortType = ' DESC ' endelsebegin set @strFSortType = ' DESC ' Set @strSortType = ' ASC ' E nd--------generate a query statement----------here @strtmp statement if @strCondition is null or @strCondition = '--not set display condition begin set @sqlTmp = @fldName + ' from ' + @tblName set @strTmp = @SqlSelect + ' @Counts = ' + @SqlCounts + ' from ' + @tblName set @strID = ' from ' + @tblNameendelsebegin Set @sqlTmp = + @fldName + ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition set @strTmp = @Sq lselect+ ' @Counts = ' + @SqlCounts + ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition set @strID = ' from ' + @tblName + ' where (1>0) ' + @strConditionend----Get the total number of query results-----EXEC sp_executesql @strTmp, N ' @Counts int out ', @Counts outdeclare @t Mpcounts intIf @Counts = 0Set @tmpCounts = 1else Set @tmpCounts = @Counts--Gets the total number of pages set @pageCount = (@tmpCounts + @pageSize- 1)/@pageSize/** The current page is greater than the total number of pages **/if @page > @pageCount set @page = @pageCount--/*-----Data Paging 2-point processing-------* * DECLARE @ PageIndex int--Total/page size declare @lastcount int--Total% page size Set @pageIndex = @tmpCounts/@pageSize set @lastcount = @tmpCounts% @pageSize if @lastcount > 0Set @pageIndex = @pageIndex + 1else Set @lastcount = @pagesize--//*** Display paging if @strCondition is null or @strCondition = '--no display condition is set to begin if @pageIndex < 2or @page <= @pageIndex/ 2+ @pageIndex% 2--data processing of the first half part begin
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR( 4) + "+ @fldName + ' from" + @tblName + ' where ' + @ID + ' not in (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page- 1) as Varchar( -) + "+ @ID + ' from" + @tblName + ' ORDER BY ' + @fldSort + ' + @strFSortType + ') ' + ' ORDER BY ' + @fldSort + ' + ' + @strFSortTyp E
End ELSE BEGIN set @page = @pageIndex-@page + 1--Data processing of the second part if @page <= 1--Last page data shows set @strTmp = @SqlSelect + ' * FROM (' + @SqlSelect + ' top ' + CAST (@lastcount as VARCHAR( 4) + "+ @fldName + ' from" + @tblName + ' ORDER BY ' + @fldSort + ' + @strSortType + ') as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @ Strfsorttype
Else
Set @strTmp = @SqlSelect + ' * FROM (' + @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR( 4) + "+ @fldName + ' from" + @tblName + ' where ' + @ID + ' not in (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page- 2) + @lastcount as Varchar( -) + "+ @ID + ' from" + @tblName + ' ORDER BY ' + @fldSort + ' + @strSortType + ') ' + ' ORDER BY ' + @fldSort + ' + @strSortType + ' As TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
End end ELSE--there are query criteria begin if @pageIndex < 2or @page <= @pageIndex/ 2+ @pageIndex% 2--data processing of the first half part begin
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR( 4) + "+ @fldName + ' from" + @tblName + ' where ' + @ID + ' not in (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page- 1) as Varchar( -) + ' + @ID + ' from ' + @tblName + ' Where (1>0) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType + ') ' + ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType
End ELSE begin
Set @page = @pageIndex-@page + 1--Data processing of the second part if @page <= 1--Last page data shows set @strTmp = @SqlSelect + ' * FROM (' + @SqlSelect + ' top ' + CAST (@lastcount as VARCHAR( 4) + "+ @fldName + ' from" + @tblName + ' WHERE (1>0) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + ') as Tem PTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType else set @strTmp = @SqlSelect + ' * FROM (' + @SqlSelect + ' top ' + CAST (@pageSize As VARCHAR( 4) + "+ @fldName + ' from" + @tblName + ' where ' + @ID + ' not in (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page- 2) + @lastcount as Varchar( -) + ' + @ID + ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + ') ' + @strCo Ndition + ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
End------Return query Results-----EXEC sp_executesql @strTmp--print @strTmpSET NOCOUNT off
Because the project uses a GUID as the primary key field, a query with not in is used here
Invoke method:
Common Paging data reading function
Note: Open and close the connection outside the function call, and turn off the data reader
SqlCommand Object///query Table/table Union///field name to query///per page data size///Current page///sort field///sort order 0 Descending 1 Ascending///filter condition///primary table primary key
Returns the SqlDataReader ref
public static void Cutpagedata (SqlConnection conn, ref SqlCommand Comm, String _tblname, string _fldname, int _pagesize, I NT _page, string _fldsort, int _sort, string _strcondition, string _id, ref SqlDataReader _DR)
{
Note: Open and close the connection outside the function call, and turn off the data reader
Comm = new SqlCommand ("Proc_listpage", conn);
Comm.commandtype = CommandType.StoredProcedure; Comm. Parameters.Add ("@tblName", SqlDbType.NVarChar, 200);
Comm. parameters["@tblName"]. Value = _tblname;
Comm. Parameters.Add ("@fldName", SqlDbType.NVarChar, 500);
Comm. parameters["@fldName"]. Value = _fldname;
Comm. Parameters.Add ("@pageSize", SqlDbType.Int);
Comm. parameters["@pageSize"]. Value = _pagesize;
Comm. Parameters.Add ("@page", SqlDbType.Int);
Comm. parameters["@page"]. Value = _page;
Comm. Parameters.Add ("@fldSort", SqlDbType.NVarChar, 200);
Comm. parameters["@fldSort"]. Value = _fldsort;
Comm. Parameters.Add ("@Sort", sqldbtype.bit);
Comm. parameters["@Sort"]. Value = _sort;
Comm. Parameters.Add ("@strCondition", SqlDbType.NVarChar, 1000);
Comm. parameters["@strCondition"]. Value = _strcondition;
Comm. Parameters.Add ("@ID", SqlDbType.NVarChar, 150);
Comm. parameters["@ID"]. Value = _id;
Comm. Parameters.Add ("@Counts", SqlDbType.Int, 0);
Comm. parameters["@Counts"]. Direction = ParameterDirection.Output;
Comm. Parameters.Add ("@pageCount", SqlDbType.Int, 0);
Comm. parameters["@pageCount"]. Direction = ParameterDirection.Output;
_DR = Comm. ExecuteReader ();
}
Call for example:
Cutpagedata (conn, ref comm, "Vox_cdsinger", "id, Cdsinger, cdsingertype, area, Cdsingerreadme", page, "id", 1, strfilt ER, "id", ref DR);
Corresponding Description:
Cutpagedata (data connection object, ref SqlCommand object, "Require table or view name", "field to query", read data bar per page, current page, "sort field can be multiple word Geru" (addtime desc, Visitcounts Note that the last field here does not add DESC or ASC the last field corresponds to the following collation) ", Sort by (1 DESC 0 ASC), where condition (no longer add where condition add such as: ' and visitcounts> 100 '), table primary key, ref returned SqlDataReader object);
[Last modified by yestar2000, at 2007-07-03 01:08:58]

This article turns from
http://www.yestar2000.com/article.asp?id=775